RE: UTL_FILE

[Andreas . Haunschmidt]

Title: UTL_FILE

Hi Eric!

-----Original Message-----
From: Harrington, Eric [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 11, 2003 8:30 PM
To: Multiple recipients of list ORACLE-L
Subject: UTL_FILE

Any help would be appreciated...

Is it possible to write a file to the local OS if the database is remote? Should I be using UTL_FILE or another package?  

Not possible as far as I know with UTL_FILE. Some Oracle client software (e.g.: Forms) uses a similar

package 'TEXT_IO', which allows access to local ( client side ) files.

I'm attempting to write to a file on my local OS (Windows NT 4.0). The database version is 9i r2 and resides on a HP-UX box. I've created a directory object as follows:

CREATE OR REPLACE DIRECTORY utl_file_dir AS 'c:\'; 

The directory you with files for UTL_FILE access must

be on the server side (your HP-UX box).

So you could do something like

CREATE OR REPLACE DIRECTORY utl_file_dir AS '/tmp';

The pathname of the file should respect the server's OS conventions, on

UNIX you don't use drive letters like 'c:',  the UNIX directory separator is '/' not '\'

This is why you get ORA-29280.

Of course you could mount a remote share (NFS, SAMBA for MS-Windows...)

on your UNIX machine to allow the Oracle server to read/write files,

but I'd prefer local drives ( bad performance, network problems...).

The output from the SQL statement: select * from all_directories; follows:

OWNER  DIRECTORY_NAME DIRECTORY_PATH

------------- ------------------------------  ----------------------------

SYS       UTL_FILE_DIR           c:\

File handle code from my procedure follows:

  l_FileHandle := UTL_FILE.FOPEN('UTL_FILE_DIR','role.txt','r');

When executing the associated procedure I get the following error:

ORA-29280: invalid directory path

Thanks, Eric Harrington 

 

regards

    Andreas