UTL_FILE

Zephyr wrote:
 > Hi,
 >
 > I want to write from a procedure into a file on the server using
UTL_FILE
 > and do not have a parameter
 > UTL_FILE_DIR= in my init.ora specifiing a path. The server version is
 > 9.2.0.4 and as far as I know,
 > I do not need UTL_FILE_DIR with this version. I'am using 'create
 > directory...' instead.
 >
 > This is my code for writing in a file on a windows machine
 >
 > p_FileDir varchar2 (50) := 'C:\';

<snip>

I'll stop here. You mentioned that you are correctly using DIRECTORY
object in 9i instead of utl_file_dir then what are you doing specifying
this parameter? You should specify the DIRECTORY name in the first
parameter of fopen and also make sure the user has read/write
priviliges on the directory object e.g.

SQL> create or replace directory FOO as 'c:\temp';

Directory created.

SQL> grant read,write on directory foo to foo_user;

Grant succeeded.

SQL> connect foo_user/foo_user
Connected.
SQL> declare
2 fHandle_ utl_file.file_type;
3 begin
4 fHandle_ := utl_file.fopen('FOO', 'foo_test.txt', 'w');
5 utl_file.put_line(fHandle_, 'Foo write test');
6 utl_file.fclose(fHandle_);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> host notepad c:\temp\foo_test.txt

Regards
/Rauf<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: UTL_FILE 

Zephyr wrote:
 > Hi,
 >
 > I want to write from a procedure into a file on the server using UTL_FILE
 > and do not have a parameter
 > UTL_FILE_DIR= in my init.ora specifiing a path. The server version is
 > 9.2.0.4 and as far as I know,
 > I do not need UTL_FILE_DIR with this version. I'am using 'create
 > directory...' instead.
 >
 > This is my code for writing in a file on a windows machine
 >
 > p_FileDir varchar2 (50) := 'C:\';
 > --p_FileDir varchar2 (50) := '\\elseserver\results';
 > p_FileName varchar2 (20) := 'user.txt';
 >
 > v_FileHandle := UTL_FILE.FOPEN (p_FileDir, p_FileName, 'w');
 > UTL_FILE.PUT_LINE (v_FileHandle, v_text);
 >
 > I tried several volumes, folders, notations and UNC notation, but always get
 >
 > ORA-29280: invalid directory path
 >
 > Has anyone a hint, or do I need the UTL_FILE_DIR parameter set on the
 > oracle server?
 >
 >
 > Thanx
 > Patrick
 >
 >

Hi Patrick,

here's a couple of (blatent) hints on setting up UTL_FILE. Enjoy.

<a style='text-decoration: underline;' href="http://www.jlcomp.demon.co.uk/faq/utl_file.html" target="_blank">http://www.jlcomp.demon.co.uk/faq/utl_file.html</a>
<a style='text-decoration: underline;' href="http://www.jlcomp.demon.co.uk/faq/utlfiledebug.html" target="_blank">http://www.jlcomp.demon.co.uk/faq/utlfiledebug.html</a>

I suspect the first one above will probably explain your problem(s).

Cheers,
Norm.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: UTL_FILE 

Thanx,

this solves my problem, but I can't write in UNC notation pathes
even if the oracle user has write access to the UNC share.

I always get

ORA-29283: invalid file operation

Is it possible to write in UNC pathes?

Thanx
Patrick
 > 4 fHandle_ := utl_file.fopen('FOO', 'foo_test.txt', 'w');<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: UTL_FILE 

On Tue, 15 Feb 2005 16:03:58 +0100, "Zephyr" <p.neumann DeleteThis @ids-scheer.de> wrote:

 >this solves my problem, but I can't write in UNC notation pathes
 >even if the oracle user has write access to the UNC share.
 >
 >I always get
 >
 >ORA-29283: invalid file operation
 >
 >Is it possible to write in UNC pathes?

Yep.

$ sqlplus test/test

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Feb 15 21:56:25 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace directory FOO as '//server/andyh/';

Directory created.

SQL> declare
2 fHandle_ utl_file.file_type;
3 begin
4 fHandle_ := utl_file.fopen('FOO', 'foo_test.txt', 'w');
5 utl_file.put_line(fHandle_, 'Foo write test');
6 utl_file.fclose(fHandle_);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> host cat //server/andyh/foo_test.txt
Foo write test

Running sqlplus under cygwin on Windows, hence the Unix-ish cat working on a
UNC path - but obviously Oracle isn't running under Cygwin so it doesn't affect
the proof that Oracle can access UNC paths.

To get it to work, changed the service to log on as my user which has access
to that share, instead of LocalSystem.

--
Andy Hassall / <andy DeleteThis @andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: UTL_FILE