Re: UTL_FILE error

Thanks IAN,

It confirmed what I already expected - it's a "write error." I can't figure out why though. This directory is owned by the oracle user. It creates the file, but the refuses to write to it! I've changed the specification to 'A" and given /oracle and /P01 full permissions. Touched the file and chmod 777 the file. Still the error.

Reran catproc.sql (I'm really out there now). If I execute the sql statement: select osuser from v$session where audsid = userenv('sessionid'); I get orap01, which should be the right guy. Searched Metalink (response time isn't bad right now) with no luck.

This is on AIX 4.2.1 with which I used to pride myself on having some sort of familiarity. Pride goeth before a fall.

Any other ideas?

David A. Barbour
Oracle DBA, OCP(and still searching for enlightenment)

This is

"MacGregor, Ian A." wrote:
>
> 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
> 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: 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).