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
init.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
init.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
> init.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
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
> init.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
> --
