DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Error Message : UTL_FILE package

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi,
    to my knowledge the error message returned by oracle is not correct.
    am i correct.
    In this example i hv not given the path, i hv not opened the file.

    when am i suppose to get this error message????
    "User-Defined Exception"

    SQL> ed
    Wrote file afiedt.buf

    1 declare
    2 s varchar2(10);
    3 ftyp UTL_FILE.FILE_TYPE;
    4 begin
    5 s:='abc';
    6 utl_file.put_line(ftyp,s);
    7 exception when others then
    8 dbms_output.put_line('err >>> '||sqlcode||sqlerrm);
    9* end;
    SQL> set serverout on
    SQL> /
    err >>> 1User-Defined Exception



    Cheers!
    OraKid.

  2. #2
    Join Date
    Nov 2001
    Posts
    13
    it is correct the error message will give the
    line no of the UTL_FILE package.

    it says it is USER DEFINED, error
    Srinivas Reddy Tatireddy

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Oracle have used User-Defined exceptions in UTL_FILE rather than 'real' errors or RAISE_APPLICATION_ERROR style errors. If you remove your exception handler you get something like this:

    declare
    s varchar2(10);
    ftyp UTL_FILE.FILE_TYPE;
    begin
    s:='abc';
    utl_file.put_line(ftyp,s);
    end;
    /

    *
    ERROR at line 1:
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at "SYS.UTL_FILE", line 301
    ORA-06512: at line 6

    This is effectively the same as:

    DECLARE
    e_my_exception EXCEPTION;
    BEGIN
    RAISE e_my_exception;
    END;
    /

    ERROR at line 1:
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at line 4

    The only difference is that Oracle is reporting that the unhandled exception occurred within a procedure call with UTL_FILE.

    Cheers!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    ya thanx guys
    Cheers!
    OraKid.

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    The point I am trying to put across is:
    Even if a package defines a user defined exception, the message should have been an appropriate one. In this specific case, since the file handle itself was not opened, a more appropriate error message wud have been "Invalid File Handle".

    Have any of u come across a similar case occuring in any of the other supplied packages?
    Cheers!
    OraKid.

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    It's not an uncommon thing to do. Oracle has exposed the exceptions to you so you can trap them yourself and make the messages as specific as you want:

    BEGIN
    -- Do UTL_FILE stuff here.
    EXCEPTION
    WHEN Utl_File.INVALID_PATH THEN
    -- Do something
    WHEN Utl_File.INVALID_MODE THEN
    -- Do something
    WHEN Utl_File.INVALID_FILEHANDLE THEN
    -- Do something
    WHEN Utl_File.INVALID_OPERATION THEN
    -- Do something
    WHEN Utl_File.WRITE_ERROR THEN
    -- Do something
    WHEN Utl_File.INTERNAL_ERROR THEN
    -- Do something
    WHEN OTHERS THEN
    -- Do something
    END;
    /

    If you look in the Oracle Supplied Packages manual you will see this approach is used in almost all of them. The first page for each package lists all exposed procedures, functions and exceptions.

    Are you having a specific problem or is it just that you didn't expect this?

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    An exception is defined using:

    ex_small_feet EXCEPTION;

    Unless you link this exception to an existing error message using a pragma, how will the exception know what is a meaningful error message? If there isn't a suitable Oracle error what can you do?

    Oracle are not expecting you to expose these types of errors to a user. They are expecting you to trap them using the exposed exceptions and decide what you want to say to the user.

    I think you are confusing this approach with RAISE_APPLICATION_ERROR which is supplied with a user-defined error number and message.

    Does that answer your question or have I missed the point?
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hey tim,

    thx for ur time.

    to answer ur first question ('Are you having a specific problem or is it just that you didn't expect this?'), yes, i didnt expect this error msg for this scenario. in fact i was expecting a write error exception or invalid filehandle exception. in the procedure that had this problem, i am scanning thru a cursor and the file was opened within the cursor. in one of the cases, the cursor returned no rows and threw this exception. since i was also using user-defined exceptions, i thought the problem was elsewhere. took me some time to figure this out. :-)

    and no, i am not confusing this with raise_application_error.

    thx once again.


    Cheers!
    OraKid.

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    No problem fella
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width