You need to handle the exception, for example
Exception
When no_data_found then
dbms_output.put_line ('no data found');
When utl_file.internal_error then
dbms_output.put_line('internal error');
When utl_file.invalid_filehandle then
dbms_output.put_line('invalid filehandle');
when utl_file.invalid_mode then
dbms_output.put_line('invalid mode');
when utl_file.invalid_operation then
dbms_output.put_line('invalid operation');
when utl_file.invalid_path then
dbms_output.put_line('invalid path');
when utl_file.read_error then
dbms_output.put_line('read error');
when utl_file.write_error then
dbms_output.put_line('write error');
when value_error then
dbms_output.put_line('value error');
when others then
dbms_output.put_line('unspecified exception raised');
------------------------------------------------------
Setting the utl_file parameter to * is a dangerous thing to do. It allows writes to
any file to which Oracle can write.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-----Original Message-----
Sent: Friday, April 27, 2001 10:56 AM
To: Multiple recipients of list ORACLE-L
Morning all,
I've got a problem with the UTL_FILE package on a 7.3.4 database. The
UTL_FILE_DIR initialization parameter is set to *. I define my
filespecs as follows:
file_one UTL_FILE.FILE_TYPE;
BEGIN
file_one:= UTL_FILE.FOPEN('/oracle/P01','Jul_Dec96.csv','W');
The file gets created okay:
grendel:orap01> ls -al *.csv
-rw-r--r-- 1 orap01 dba 0 Apr 27 11:18 Jul_Dec96.csv
I perform some selects and various data stuff then I go to add a
line(output severely reduced for testing purposes):
UTL_FILE.PUT_LINE(file_one,v_zcoanum);
I get :
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 87
ORA-06512: at "SYS.UTL_FILE", line 218
ORA-06512: at "SAPR3.COAPRODX3", line 123
ORA-06512: at line 1
The variable v_zcoanum is ALWAYS present - DBMS_OUTPUT.PUT_LINE shows it
just fine. I've bounced the instance, re-run the utlfile.sql, and tried
changing target directories. I've even tried a PUTF -
UTL_FILE.PUTF(file_one,'%s', v_zcoanum); but get the same type of
errors:
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 87
ORA-06512: at "SYS.UTL_FILE", line 188
ORA-06512: at "SYS.UTL_FILE", line 273
ORA-06512: at "SYS.UTL_FILE", line 299
ORA-06512: at "SAPR3.COAPRODX3", line 123
ORA-06512: at line 1
I'm wondering if I need to re-run catproc.sql? This same procedure
works on a test instance on a different box (same O/S, same
init<SID>.ora). Generally I'd see these types of errors when the
UTL_FILE package isn't installed or there are too many open file handles
tried to eliminate this by bouncing the instance). Does anybody have a
clue here?
Thanks,
David A. Barbour
Oracle DBA, OCP (Obviously not a path to enlightenment)
Worn to a frazzle staring at my own code and out of ideas
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: David A. Barbour
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
