Re: Why not getting INVALID_PATH?

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);

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 Wed Dec 22 1999 - 05:59:20 CST