Tom,

We are in 9iR2 now and you have mentioned that we should
create directory for UTL_FILE_DIR instead of the init.ora
parameter.

My question is, we currently have multiple directories set
to the UTL_FILE_DIR parameter in the init.ora file.

utl_file_dir=/app/db1/logs
utl_file_dir=/app/db2/data

The syntax for creating a directory allows only one directory.

Can you please guide me how do I take care of this..

Thanks
Pushparaj

Can't find any help on the use of DIRECTORY objects. The problem persists even after carefully applying the init.ora changes, and instance bounces.

Tom:- this worked for me. (Win2K and 9i2)
1. as sysdba, using EM/configuration, insert the directory path in initialized parameters value for utl_file_dir
2. shut down instance in EM as sysoper
3. Win2k, add any local user/password in Log on/this account in Oracleservices properties in computer management
4. start up instance in EM as sysoper
5. repeat for all instances
UTL_FILE will work in PLSQL

when i set utl_file_dir=c:\\ in init.ora i do not have any problems
but when i set utl_file_dir=c:\ i face problems saying that oracle cannot recognize the path set .. but on another server on windows it works fine

is that escape character really required or not .. i am confused as some of our applications are running properly with a setting c:\
please clarify the same why is it so

thanks in advance
p.s: waiting eagerly for your new book ( hope not getting delayed further)

Hi Tom,
You said:
>>>>>>>>>>>>>
if you have 9iR2 -- you should use DIRECTORY objects instead of utl_file_dir,
easier, more secure and does not require a restart...
>>>>>>>>>>>>>
We need to generate .txt files.
Please show us how to manipulate that is open and write
to these files.
We created a directory but it generated the error
ORA-29280
Thanks as always

That was really helpful. Thanks as always, I thought oracle was not case sensitive. But guess this was an exception.
Well Tom another query.
I was going through the docs and it says that Create Directory can be used to write to BFILES only and External Tables.
But in this case we need to generate .txt files in ASCII. Like normal files. Is this possible using Create Directory or should I set up UTL_FILE_DIR.
Another question:
Suppose I set up UTL_FILE_DIR as 'c:iles*'
and in that folder (fiels) exists 3 more folders. So can we use the UTL utility to write to individual folders.
I think we can but still would like to know your answers.
Thanks as always.

This info was wrong.
Suppose I set up UTL_FILE_DIR as 'c:iles*'
and in that folder (fiels) exists 3 more folders. So can we use the UTL utility
Correct Version
Suppose I set up UTL_FILE_DIR as 'c:iles*'
and in that folder->files, exist 3 more folders. So can we use the UTL utility.
Thanks

Hi Tom,
We created a directory for each of the folders in the 'files' folder.

For e.g. create directory Test as 'c:ilessacha';

This is a sample of the procedure which calls one the above mentioned directory and attempts to write to it:

CREATE OR REPLACE PROCEDURE Cargue_Billeteria IS
ARCHIVO_CARGUE UTL_FILE.FILE_TYPE;
LINEA VARCHAR2(100);
NUM_REGISTROS NUMBER;
BEGIN
ARCHIVO_CARGUE := UTL_FILE.FOPEN('SACHA','BILLSONAP.txt','R');

NUM_REGISTROS := 0;

ARCHIVO
LOOP
BEGIN
UTL_FILE.GET_LINE(ARCHIVO_CARGUE,LINEA);
DBMS_OUTPUT.PUT_LINE(LINEA);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
NUM_REGISTROS := NUM_REGISTROS + 1;
END LOOP;
UTL_FILE.FCLOSE(ARCHIVO_CARGUE);
COMMIT;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_PATH '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_MODE '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_FILEHANDLE '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_OPERATION '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: READ_ERROR '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: WRITE_ERROR '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INTERNAL_ERROR '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_MAXLINESIZE '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR: NO_DATA_FOUND '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: VALUE_ERROR '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: OTHERS '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
END;
/

It executes successfully but then throws the error Ora-29289.
The user executing this procedure has Create any directory privilege.
Also it has READ and WRITE privilege on this directory object.
The fact to note was that this directory existed in sys schema. So is this by design or is there a fault.
Sorry for bothering you again and again.
Thanks as always.

Sacha is the name of the directory which was created based on the name of the folder which exists in the files folder
For e.g C:-> Files -> Sacha
So we created a directory by the name of Sacha on that folder.
So it goes like Create directory Sacha as 'C: Test sacha';
Sorry I put in TEST.
Error regretted.
Thanks.

Sacha,

I got the same errors when I tried to install my work at home. After losing much hair <g>, I found that I needed to specify a DIRECTORY if it was on NTFS, or use the UTL_FILE style for FAT32. This is not discussed anywhere in the documentation, so it might just be me. I am running Oracle 9iR2 on Windows 2000.

I got around it by trying DIRECTORY first, and then specifying the path if it failed, by fetching it back from ALL_DIRECTORIES.

l_directory is the 'DIRECTORY'
l_filename is the name of the file I want to read.
l_fh is file handle

-- Open the file
BEGIN
BEGIN
l_fh := utl_file.fopen(s_directory, l_filename, 'r');
EXCEPTION
WHEN utl_file.invalid_path THEN
SELECT directory_path
INTO l_directory
FROM all_directories
WHERE directory_name = l_directory;

l_fh := utl_file.fopen(l_directory, l_filename, 'r');
END;
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20001, 'Directory not found - Please see application manager.');
WHEN utl_file.invalid_operation THEN
raise_application_error(-20000, 'File not found - Please see application manager.');
END;

Takmeister

Wierd Name Dude ;)
Well my name is not "Sacha" :D
Anyway the directory exists as I created the folder and then used the Create Directory Command. The permission on the folder is granted to everyone. And the file system is NTFS, absolutely sure. Just can't get hold of the reason. So i am asking Tom for help. Thanks anyway friend :)

Hi Tom,
I am trying to open a file with UTL_FILE. from pl/sql. I am on oracle 9.2.on UNIX (server). When I am trying the following code, i am receiving the errors as listed at the bottom of the code.. please help me find where the problem is..

SQL>create or replace directory TEMP as '/home/test'
Directory created

DECLARE 
 V_OutputFile UTL_FILE.FILE_TYPE; 
 V_Path VARCHAR (30):='TEMP'; 
 V_FileName VARCHAR2 (30):= 'myfile.txt'; 

BEGIN 
 V_OutputFile := UTL_FILE.FOPEN(V_Path, V_FileName, 'w');
 DBMS_OUTPUT.PUT_LINE('File opened succesfully'); 
 UTL_FILE.FCLOSE (V_OutputFile); 
EXCEPTION
    WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
END;

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation

Note: I am able to read the file ('myfile.txt') from the DIRECTORY 'TEMP' when I am using DBMS_LOB. However, it returns the above error when I try to open it with UTL_FILE.

please advise..
 

Tom,
I have done the following:
SQL>grant write on directory TEMP to public;
Grant succeeded
SQL>grant read on directory TEMP to public;
Grant succeeded

I tried to open the file in the read mode also
V_OutputFile := UTL_FILE.FOPEN(V_Path, V_FileName, 'r')
but it still returns the same error.

Does the file need any other permissions other than these granted (read & write).?  since the same DB USER was able to read the file with DBMS_LOB, I assume UTL_FILE  must also be able to. but it is not.  please advise..
 

Hi Tom,
Thank you so much for the suggession. the file system was not accessible from oracle in 'write' mode.

in addition to "grant write on directory.." on the database level, I have changed the permision level of the file directory on the unix based database server to 777. It is now working! and able to open the file in 'write' mode.

Thank you,
Ravi.

Hello Tom,

Here is my problem (rather stupid one).
I've tried to use UTL_FILE and did all 'as in text book',
but could not get through UTL_FILE.INVALID_PATH exception.
I ran following script on Windows XP box, Ora 9.2.0.1.0 as SYSDBA:

-- Just to be sure that utl_file_dir does not interfere:
show parameter utl_file_dir

COLUMN value FOR A100 NEW_VAL DD
select name, value
from v$parameter where name = 'user_dump_dest';
CREATE OR REPLACE DIRECTORY DEBUG_DIR AS '&DD';

SELECT DIRECTORY_PATH
FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME = 'DEBUG_DIR'
;

SET SERVEROUTPUT ON

DECLARE
l_handle utl_file.file_type;
l_file VARCHAR2(100);
l_Location VARCHAR2(100);
BEGIN
l_Location := '&DD';
DBMS_OUTPUT.Put_Line('..l_Location = ' || l_Location);

l_file := 'test.txt';
DBMS_OUTPUT.Put_Line('..l_file = ' || l_file);

l_handle := utl_file.fopen(
location => l_Location
,filename => l_file
,open_mode => 'a'
,max_linesize => 32767
);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.Put_Line('File location (' || l_Location ||
') or name (' || l_file ||
') was invalid');
WHEN OTHERS THEN
if utl_file.is_open(l_handle) then
utl_file.fclose(l_handle);
end if;
DBMS_OUTPUT.Put_Line('Unexpected Oracle error ' || SQLERRM);
END;
/

Here is output:

SYS@LENA> show parameter utl_file_dir

NAME TYPE VALUE
------------------------------------ ----------- ------
utl_file_dir string

SYS@LENA> select name, value
2 from v$parameter where name = 'user_dump_dest';
NAME VALUE
---------------------------------------- -------------------------------------
user_dump_dest d:\Oracle\admin\lena\udump

Elapsed: 00:00:00.00
SYS@LENA> CREATE OR REPLACE DIRECTORY DEBUG_DIR AS '&DD';
old 1: CREATE OR REPLACE DIRECTORY DEBUG_DIR AS '&DD'
new 1: CREATE OR REPLACE DIRECTORY DEBUG_DIR AS 'd:\Oracle\admin\lena\udump'

Directory created.

SYS@LENA> SELECT DIRECTORY_PATH
2 FROM ALL_DIRECTORIES
3 WHERE DIRECTORY_NAME = 'DEBUG_DIR'
4 ;

DIRECTORY_PATH
----------------------------------------
d:\Oracle\admin\lena\udump

SYS@LENA> DECLARE
2 l_handle utl_file.file_type;
............................

27 DBMS_OUTPUT.Put_Line('Unexpected Oracle error ' || SQLERRM);
28 END;
29 /
old 6: l_Location := '&DD';
new 6: l_Location := 'd:\Oracle\admin\lena\udump';
..l_Location = d:\Oracle\admin\lena\udump
..l_file = test.txt
File location (d:\Oracle\admin\lena\udump) or name (test.txt) was invalid

PL/SQL procedure successfully completed.

So where is my error? Is it in the way I use UTL_FILE or
the way I set up the directory?

Thank you for your patience

BTW I read JL response to Don B. discussion - a lot of fun. Where is your promised last parts of trilogy?

Thanks a lot, it works on Ora9. Documentation is a bit confusing, because FOPEN description does not clearly mention that you should use DIRECTORY_NAME, not the DIRECTORY_PATH if DIRECTORY object is a target and path if UTL_FILE_DIR is a target. It's even motre confusing because on Ora8 neither DIRECTORY_NAME nor DIRECTORY_PATH works despite I've set DIRECTORY exactly the same way.
BTW I tested under SYSDBA just because it happens to be my private test DB and I do not care that much about it. Today I tested on our 'production' test DB with grants and all.

hi tom ..
tom i am using utl_file to write text file . following code used for this purpose ..

CREATE OR REPLACE procedure autoexecute is
t utl_file.file_type ;
ch varchar2(2000) ;
cursor c1 is
select * from HOROSCOPEDATA where to_char(hsdate,'dd-mm-yyyy') = to_char(sysdate,'dd-mm-yyyy') ;
c1var c1%rowtype ;
begin
open c1 ;
loop
fetch c1 into c1var ;
exit when c1%notfound ;
if c1var.catagoryid=1 then
t := utl_file.fopen ('//192.168.1.129/data/,'ARIES.txt','W') ;
utl_file.put_line(t,'ARIES='||c1var.content);
utl_file.put_line(t,''||'&'||'end=1');
utl_file.fclose(t);
elsif c1var.catagoryid=2 then
t := utl_file.fopen('//192.168.1.129/data/','TAURUS.txt','W') ;
utl_file.put_line(t,'TAURUS='||c1var.content);
utl_file.put_line(t,''||'&'||'end=1');
utl_file.fclose(t);
end if ;
end loop ;
close c1 ;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_PATH '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_MODE '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_FILEHANDLE '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_OPERATION '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: READ_ERROR '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: WRITE_ERROR '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INTERNAL_ERROR '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
DBMS_OUTPUT.PUT_LINE('ERROR: INVALID_MAXLINESIZE '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR: NO_DATA_FOUND '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('ERROR: VALUE_ERROR '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: OTHERS '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
end autoexecute;


and i have set init.ora file
utl_file_dir = '//192.168.1.129/data/'

now the problem is that when i used this package to write file locally then i have set init.ora to
utl_file_dir = *
then it cauzed no problem but when i used to write file on a network it generate exception utl_file.INVALID_OPERATION .
plz guide me how can i write text file on a network i am current using 10g 10.2.0.1.0 and tell me about necessaray parameter related with this work ... or can i write file using path like </code> http://202.122.134.12/data/horoscope/

. i have read and write file access on these folder and path ...
to plz guide me regarding both issue ....
or which one is suitable .. to write file locally or using 

http:// <code>... path ...
thanx ..
Regards ..
Babar Ali

tom one more thing .if i dont handle exception then following error when we execute procedure to write file on a network ...
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 204
ORA-06512: at "TEST.AUTOEXECUTE", line 13
ORA-06512: at line 1


and i have also restart my server after changes made on init.ora
Regards
Babar Ali

hi tom ..
utl_file_dir = \\afaq\data\
utl_file.open('\\afaq\data\'.'file.txt','W');
when i set this patameter in init.ora file it cauz error ..

ORA-01034: Oracle not available
ORa-27101: share memory realm doesnot exist

hi tom ..
i am using linux and 10g install on it ..
i had set init.ora
utl_file_dir = //192.186.1.23/data/value/
and code use to write file on network system ...
utl_file.open('//192.168.1.23/data/value/','file1.txt','W') ;

*where as data is share folder
is it correct way to write file on network system ...
cauz it does not work well .....
Regards
babar

Hi Tom,

If the V$parameter suggests that the utl_file_dir represents a destination as :

/dumps-01/databases/output

and if we create a sub-directory xyz under this folder as

/dumps-01/databases/output/xyz, will utl_file fail to create a file under the /dumps-01/databases/output/xyz destination.

Are sub-folders not included to be written by utl_file.put_line under the utl_file_dir path.

thanks

Tom,
I am using UTL_FILE to create files. I am getting a
ORA-29283: invalid file operation..
I checked the utl_file_dir in INIT.ora and its set to '*'.
Why would i still get the error? the thing is the same procedure works in one directory and fails in other directory with the ora-29283 error,,

for instance

/ods/dwh/dat --- fails
/ods/dwh/main --works..

ANy ideas?

Tom:

When I tried SYS.UTL_FILE to write a file, I got the error ORA-29289: directory access denied.
When I try the following procedure under my Oracle userid vxn2, it still failed.
SQL> l
1 declare
2 f1 utl_file.file_type;
3 begin
4 f1 := utl_file.fopen('TESTDIR','t3.txt','w');
5 utl_file.put_line(f1,'This is a test');
6 utl_file.fclose(f1);
7* end;
SQL> /
declare
*
ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 4

The DBAs have granted READ, WRITE privileges to TESTDIR as shown:
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
DIRECTORY_NAME
------------------------------
VXN2 READ
TESTDIR

VXN2 WRITE
TESTDIR

The TESTDIR is defined as follows:
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS TESTDIR
/home/vxn2/app/sm

From UNIX server, I have granted privileges to PUBLIC for the directory as shown below:
eidbdev01 $ ls -al /home/vxn2/app/sm
total 8
drwxrwxrwx 2 vxn2 staff 256 Mar 23 11:25 .
drwxrwxrwx 3 vxn2 staff 256 Mar 22 16:34 ..
-rwxrwxrwx 1 vxn2 staff 1292 Mar 23 11:25 test1.csv

Any idea what's wrong ??

Thanks,

Van

$ oerr ora 29289
29289, 00000, "directory access denied"
// *Cause:  A directory object was specified for which no access is granted.
// *Action: Grant access to the directory object using the command
//          GRANT READ ON DIRECTORY [object] TO [username];.
[tkyte@desktop ~]$

Tom,
I have a similar problem with Oracle 9i R2 on Linux.
This is what i did to use utl_file:

sql>grant execute on utl_file to bms;
sql>create or replace directory dir_test as '/oracle/temp';
sql>grant read,write on directory dir_test to bms;

$ chmod 777 DIR_TEST

--------------------------
create or replace procedure RebuildFragIndexes(schema_owner varchar2)
is
pMaxHeight integer := 4;
pMaxLeafsDeleted integer := 20;
vCount integer := 0;
fh UTL_FILE.file_type;
begin
for c1 in ( select i.index_name
from sys.dba_indexes i, sys.dba_segments s
where i.owner=schema_owner
AND i.index_type = 'NORMAL'
and s.segment_type='INDEX'
and i.index_name = s.segment_name )
loop
execute immediate 'analyze index ' || schema_owner || '.' || c1.index_name ||
' validate structure';
FOR C2 IN (select name index_name
from index_stats
WHERE (height > pMaxHeight)
or
( lf_rows > 0
and del_lf_rows > 0
and (del_lf_rows* 100 / lf_rows)>pMaxLeafsDeleted
)
)
LOOP
vCount := vCount + 1;

fh := UTL_FILE.fopen ('DIR_TEST', 'file_test.txt', 'w');
dbms_output.put_line('File opened for writing');
UTL_FILE.put_line(fh, 'Rebuilding index ' || schema_owner || '.' || C2.index_name || '...');
execute immediate 'alter index ' || schema_owner|| '.'|| C2.index_name || ' rebuild' ||
' nologging compute statistics';
END LOOP;
end loop;
UTL_FILE.put_line(fh,'Total Indexes rebuilt for ' || schema_owner || ': ' || to_char(vCount));
UTL_FILE.fclose (fh);
vCount := 0;
end RebuildFragIndexes;
/
-------ERROR----------------
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 714
ORA-06512: at "BMS.REBUILDFRAGINDEXES", line 36
ORA-06512: at line 1

I tried executing this procedure from TOAD & SQLPLUS using the schema 'BMS'.I get the same error in both.
Pls advise if you see something wrong.........

Thank you.

Thanks Tom..so where should I place my fopen statement if I want to open the file only once irrespective of the rows returned.

Because I still get the same error, doesn't matter where I place the statement..

Also Tom I am not an Oracle expert like you to debate whether to rebuild the indexes or not...but everytime I rebuild the index there is a performance gain and never a degradation on my OLTP database.Though I tried several times to note what parameters are changing after the index rebuild, I didnt notice any specific change.So i donno what exactly happens during index rebuild.I know you always advice to measure the before & after stats...the issue is we do not know exactly where to look at and what stats to keep our eye on..

Regards,

I just cant get this working wherever I place the fopen.

Can u give me an example using UTL_FILE package pls?I want to make use of 9i R2 directories.

1.should the directory & the file be created at OS level explicitly?

Thanks

Hi Tom

We've been trying to writeusing utl_file but keep getting an invalid path message.  utl_file_directory is not set in the init.ora file.  I must be missing something simpe:

FSQNT25 C:\>sqlplus "sys/******* as sysdba"

SQL*Plus: Release 9.0.1.4.0 - Production on Mon Nov 5 13:48:14 2007

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production

SQL> create or replace directory TEST1 as 'd:\Temp_hyp';

Directory created.

SQL> grant read, write on directory TEST1 to ltop_owner;

Grant succeeded.

SQL> conn ltop_owner/********
Connected.
SQL> ho dir d:\Temp_Hyp
 Volume in drive D is Data
 Volume Serial Number is 9450-D14B

 Directory of d:\Temp_Hyp

31/07/2007  12:36       <DIR>          .
31/07/2007  12:36       <DIR>          ..
               0 File(s)              0 bytes
               2 Dir(s)  56,255,631,360 bytes free

SQL> declare
  2    f utl_file.file_type;
  3  begin
  4    f := utl_file.fopen('TEST1', 'something.txt', 'w');
  5    utl_file.put_line(f, 'line one: some text');
  6    utl_file.put_line(f, 'line two: more text');
  7    utl_file.fclose(f);
  8  EXCEPTION
  9    WHEN utl_file.invalid_path THEN
 10      RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
 11    WHEN utl_file.invalid_mode THEN
 12      RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
 13    WHEN utl_file.invalid_filehandle THEN
 14      RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
 15    WHEN utl_file.invalid_operation THEN
 16      RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
 17    WHEN utl_file.read_error THEN
 18      RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
 19    WHEN utl_file.write_error THEN
 20      RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
 21    WHEN utl_file.internal_error THEN
 22      RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
 23    WHEN OTHERS THEN
 24      RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
 25
 26  end;
 27  /
declare
*
ERROR at line 1:
ORA-20001: utl_file.invalid_path
ORA-06512: at line 10


SQL>

Hello Tom,
Since output data from UTL_FILE is not supported in ASM, what is the work around?

Thanks