UTL_FILE error
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 dba0 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 initSID.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).
RE: UTL_FILE error
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 dba0 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 initSID.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).
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 [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 dba0 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 initSID.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
RE: UTL_FILE error
Are you out of space or quota? I assume this UFS filesystem. We use AFS here for quite a bit of stuff. AFS permissions are more granular; a person could have permissions to create a file but not write to it. The chmod command is still available, it just doesn't do anything on AFS files. It is hard to tell from your system system prompt, grendel:orap01, that the file was created in the /oracle/P01 directory Ian -Original Message- Sent: Friday, April 27, 2001 2:36 PM To: Multiple recipients of list ORACLE-L 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 [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 dba0 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 initSID.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]