RE: UTL_FILE error

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Fri, 27 Apr 2001 12:15:12 -0700
Message-ID: <F001.002F47B3.20010427114742@fatcity.com>

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
ian_at_slac.stanford.edu

-----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: dbarbour_at_nucentrix.net

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: ListGuru_at_fatcity.com (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: ian_at_SLAC.Stanford.EDU

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