Re: Why not getting INVALID_PATH?

Interesting... I added the code you suggested and got SQL> exec foo
BEGIN foo; END;

*
ERROR at line 1:
ORA-20003: INVALID_OPERATION: The file could not be opened or operated on as requested.
ORA-06512: at "STAGING.FOO", line 35
ORA-06512: at line 1

I guess I was looking for the wrong error. The path was real.. the file name was bogus.. different than I expected I guess.

Thanks again,
Dc.

Thomas Kyte wrote:

> A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com>
> (if that email address didn't require changing)
> On Tue, 21 Dec 1999 21:43:06 -0500, you wrote:
>
> >Small piece of code utilizing UTL_FILE package.
> >I'm trying to open up a file that does not exist -
> >and I am getting - through use of an OTHERS handler
> >SQLCODE=1
> >SQLERRM=User defined exception.
> >I haven't defined any exceptions.
> >I'm wondering why I don't get "Invalid_path"
> >Remember..the file is non-existent
> >Here's the code
> >
> >create or replace procedure foo as
> >location VARCHAR2(30) :=/home/dropship
> >filename VARCHAR2(30;
> >open_mode VARCHAR2(30):='r';
> >handle UTL_FILE.FILE_TYPE;
> >v_error_code NUMBER;
> >v_error_msg VARCHAR2(250);
> >BEGIN
> >filename:='Non existant';
> >handle:=utl_file.fopen(location,filename,open_mode);
> >EXCEPTION
> >WHEN UTL_FILE.INVALID_PATH
> > THEN dbms_output.put_line('Invalid Path');
> >WHEN OTHERS
> > THEN
> > v_error_Code := SQLCODE;
> > v_error_msg := substr(SQLERRM,1,250);
> > dbms_output.put_line(sqlcode||' '||sqlerrm);
> > dbms_output.put_line('Others excetion handler ');
> >END;
> >
> >Now when I run this - why do I get
> >
> >SQL> exec foo;
> >1 User-Defined Exception
> >Others exception handler
> >
> >PL/SQL procedure successfully completed.
> >
> >Shouldn't I get Invalid Path?
> >And if not Invalid path, why user defined? I didn't define anything...
> >
>
> you are probably getting invalid path. they are raising a programmer defined
> exception called UTL_FILE.INVALID_PATH in their code -- that is the User-Defined
> Exception.
>
> Somewhere in your code, define these strings:
>
> g_invalid_path_msg constant varchar2(255) default
> 'INVALID_PATH: File location or filename was invalid.';
> g_invalid_mode_msg constant varchar2(255) default
> 'INVALID_MODE: The open_mode parameter in FOPEN was invalid.';
> g_invalid_filehandle_msg constant varchar2(255) default
> 'INVALID_FILEHANDLE: The file handle was invalid.';
> g_invalid_operation_msg constant varchar2(255) default
> 'INVALID_OPERATION: The file could not be opened or operated on as requested.';
> g_read_error_msg constant varchar2(255) default
> 'READ_ERROR: An operating system error occurred during the read operation.';
> g_write_error_msg constant varchar2(255) default
> 'WRITE_ERROR: An operating system error occurred during the write operation.';
> g_internal_error_msg constant varchar2(255) default
> 'INTERNAL_ERROR: An unspecified error in PL/SQL.';
>
> then, make your exception block look like:
>
> exception
> when utl_file.invalid_path then
> raise_application_error(-20001,g_invalid_path_msg);
> when utl_file.invalid_mode then
> raise_application_error(-20002,g_invalid_mode_msg);
> when utl_file.invalid_filehandle then
> raise_application_error(-20002,g_invalid_filehandle_msg);
> when utl_file.invalid_operation then
> raise_application_error(-20003,g_invalid_operation_msg);
> when utl_file.read_error then
> raise_application_error(-20004,g_read_error_msg);
> when utl_file.write_error then
> raise_application_error(-20005,g_write_error_msg);
> when utl_file.internal_error then
> raise_application_error(-20006,g_internal_error_msg);
> end;
>
> that way, the error reported back in sqlplus will be one of the strings defined
> abvoe.
>
> >- Dc.
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Thu Dec 23 1999 - 19:01:07 CST