Forum Stats

  • 3,780,545 Users
  • 2,254,408 Discussions
  • 7,879,377 Comments

Discussions

Error management

TomOn
TomOn Member Posts: 1
edited Sep 18, 2018 6:20AM in SQL & PL/SQL

Hey guys, I have a problem with error management I want to get a message ERRORTEST using EXCEPTION and OUT parameter in procedure, here's the code

declare

     L_TYPE NUMBER;

     L_CODE NUMBER;

     L_DATE DATE;

     L_ERROR VARCHAR2(200 BYTE) ;

PROCEDURE LINST(

     L_TYPE IN NUMBER,

     L_CODE IN NUMBER,

     L_DATE IN DATE,

     L_ERROR OUT VARCHAR2)

IS

BEGIN

INSERT INTO G_TBL ("LIN_TYPE", "LIN_CODE", "LIN_DATE")

VALUES (L_TYPE,  L_CODE, L_DATE) ;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

L_ERROR := 'ERRORTEST' ;

return L_ERRROR;

END;

BEGIN

LINST(1,12, sysdate) ;

END;

Frank Kulash

Best Answer

  • BEDE
    BEDE Oracle Developer Member Posts: 2,327 Gold Trophy
    edited Sep 18, 2018 6:20AM Accepted Answer

    One way to handle errors in a procedure would be

    create or replace procedure pr_x(..., p_status out number, p_message out varchar2) is

    begin

       begin

          ....

       exception

           when .... --- non-fatal exceptions which require some processing

               ....

           when others then --- fatal exception, no further processing

              p_status=-1; --- error code that would be significant for the application calling the procedure

              p_message:=$$plsql_unit||':'||$$plsql_line||':'||sqlcode||:||sqlerrm;

              return;

       end

       begin

          ....

       exception

           when .... --- non-fatal exceptions which require some processing

                 .....

           when others then --- fatal exception, no further processing

              p_status=-13; --- error code that would be significant for the application calling the procedure

              p_message:=$$plsql_unit||':'||$$plsql_line||':'||sqlcode||:||sqlerrm;

              return;

       end

       p_status:=1;

       p_message:='OK';

    exception

           when others then --- completely unforeseen exception, not caught in other places

              p_status=-10000; --- error code that would be significant for the application calling the procedure

              p_message:=$$plsql_unit||':'||$$plsql_line||':'||sqlcode||:||sqlerrm;

    end;

Answers

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited Sep 18, 2018 5:35AM

    This:

    return @L_ERRROR;

    isn't valid oracle syntax the @ symbol is used for database links and nothing else.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,370 Red Diamond
    edited Sep 18, 2018 5:42AM

    HI,

    TomOn wrote:Hey guys, I have a problem with error management I want to get a message ERRORTEST using EXCEPTION and OUT parameter in procedure, here's the codedeclare L_TYPE NUMBER; L_CODE NUMBER; L_DATE DATE; L_ERROR VARCHAR2(200 BYTE) ;PROCEDURE LINST( L_TYPE IN NUMBER, L_CODE IN NUMBER, L_DATE IN DATE, L_ERROR OUT VARCHAR2)ISBEGININSERT INTO G_TBL ("LIN_TYPE", "LIN_CODE", "LIN_DATE") VALUES (L_TYPE, L_CODE, L_DATE) ;COMMIT;EXCEPTIONWHEN OTHERS THENL_ERROR := 'ERRORTEST' ;return @L_ERRROR;END;BEGINLINST(1,12, sysdate) ;END;

    What is

    @L_ERROR

    ?  Is that Oracle?

    If linst requires 4 arguments, and you call it with only 3 arguments, then that's an error in the calling code; the error handling inside linst will have no effect.

    Think carefully before using EXCEPTION, and even more carefully before using WHEN OTHERS.

    WHEN OTHERS should be followed by RAISE.  What is the big picture here?  What kinds or errors could happen?  Why do you need your own error message?

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited Sep 18, 2018 5:44AM

    You call LINST with 3 parameter, but the procedure requires 4 parameter (the 4th out parameter is missing).

    You are trying to use a SQL-Server construct with Oracle.

    This is not wise.

    Your procedure returns a fixed error message on error, so you do not know what the real error was (primary key violation, check constraint violation, space deficit).

    Just use the exception handling capabilities in Oracle. You can catch the exception with the complete error message (SQLERRM) in the exception handler of the outer block (if needed).

    Frank Kulash
  • BEDE
    BEDE Oracle Developer Member Posts: 2,327 Gold Trophy
    edited Sep 18, 2018 5:45AM

    @L_ERRROR looks like MS SQl Server stuff...

    And how would you expect an anonymous PL/SQL block to have return L_ERRROR?

    A function may return a value.

    In a procedure the out parameter is simply assigned a value. And a procedure has simple return without any value after it.

    Frank Kulash
  • cormaco
    cormaco Member Posts: 1,768 Bronze Crown
    edited Sep 18, 2018 5:55AM

    You can read this forum document about exception handling:

    https://community.oracle.com/docs/DOC-915661

    Frank Kulash
  • BEDE
    BEDE Oracle Developer Member Posts: 2,327 Gold Trophy
    edited Sep 18, 2018 6:20AM Accepted Answer

    One way to handle errors in a procedure would be

    create or replace procedure pr_x(..., p_status out number, p_message out varchar2) is

    begin

       begin

          ....

       exception

           when .... --- non-fatal exceptions which require some processing

               ....

           when others then --- fatal exception, no further processing

              p_status=-1; --- error code that would be significant for the application calling the procedure

              p_message:=$$plsql_unit||':'||$$plsql_line||':'||sqlcode||:||sqlerrm;

              return;

       end

       begin

          ....

       exception

           when .... --- non-fatal exceptions which require some processing

                 .....

           when others then --- fatal exception, no further processing

              p_status=-13; --- error code that would be significant for the application calling the procedure

              p_message:=$$plsql_unit||':'||$$plsql_line||':'||sqlcode||:||sqlerrm;

              return;

       end

       p_status:=1;

       p_message:='OK';

    exception

           when others then --- completely unforeseen exception, not caught in other places

              p_status=-10000; --- error code that would be significant for the application calling the procedure

              p_message:=$$plsql_unit||':'||$$plsql_line||':'||sqlcode||:||sqlerrm;

    end;

This discussion has been closed.