1 2 Previous Next 28 Replies Latest reply: Mar 5, 2014 11:35 AM by AnnPricks E RSS

Exception handling in a procedure!

446 Newbie
Currently Being Moderated


Hi-

i have a procedure like-

 

create or replace

procedure <proc_name>

(

in_empid IN USER_DEPT.EMPID%TYPE,

.

.

out_code OUT NUMBER

)

AS

BEGIN

INSERT INTO TAB1

(

empid

.

.

)

VALUES

(

in_empid

.

.

);

INSERT INTO TAB2

(

empid,

.

.

)

VALUES

(

in_empid

.

);

COMMIT;

out_code =0;

EXCEPTION

WHEN OTHERS THEN

rollback;

out_code :=1;

END <proc_name>

 

Here i wud like to throw the exception message with SQL err code and table name(here we are going to insert records for multiple tables).

Also,i need to ignore the null values while trying to insert for any of the tables.

  • 1. Re: Exception handling in a procedure!
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    you can use exception block handling on each insert query. for example

     

    create ....

    begin

       

        begin

            insert ... t1

         exception

              ----table t1

             when others then rollback; out_code := SQLCODE;

        end;

     

        begin

            insert ... t2

         exception

            ----  table t2

            when others then rollback; out_code := SQLCODE;

        end;

    end;

     

    ----

    Ramin Hashimzade

  • 2. Re: Exception handling in a procedure!
    446 Newbie
    Currently Being Moderated

    Thanks Ramin-

                      Is there any other way to catch exceptions in a single block for all the insert query(sqlcode+table name)...

     

                       Also,how to ignore null values in all these cases?

  • 3. Re: Exception handling in a procedure!
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    I think you don't need to invent vehicle )))

    In think you need this

    ORACLE-BASE - DML Error Logging in Oracle 10g Database Release 2

     

     

    446 wrote:

     

                       Also,how to ignore null values in all these cases?

    What do you mean??


    ----

    Ramin Hashimzade

  • 4. Re: Exception handling in a procedure!
    Karthick_Arp Guru
    Currently Being Moderated

    First, Exception WHEN OTHERS without RAISE or RAISE_APPLICATION_ERROR is most certainly a bug. You should never do that. Always raise the error to the calling program unit and let that handle it.

     

    Next, When a statement fails the control is returned to the EXCEPTION block. The subsequent statements are not executed. If you want the second INSERT to be executed even if the first fails then you need to use separate BEGIN END block for first statement.

     

    Basically your requirement is very vague. It would be better if you explain in detail what you are trying to achieve. I would suggest you read Re: 2. How do I ask a question on the forums? from FAQ to know the details that need to be supplied while asking a question.

  • 5. Re: Exception handling in a procedure!
    BluShadow Guru Moderator
    Currently Being Moderated

    As Ramin has pointed out, you'd need seperate execution blocks with individual exception handlers for each insert you're doing if you want to be able to determine the details specific to that insert.  There's no magical information held to give you all the details of what table was being accessed at the time of the exception.

     

    See this article I wrote about exception handling: PL/SQL 101 : Exception Handling

     

    Also, consider that you need to modularize your code.  Creating a single procedure or execution block that performs multiple tasks is not good modularization of your code.  Break it down into seperate procedures and have an overall procedure that calls those.  Each of the seperate procedures can have their own exception handler to deal with expected exceptions specific to that module of code, handle it properly and/or raise it up to the main calling code.

  • 6. Re: Exception handling in a procedure!
    446 Newbie
    Currently Being Moderated

    Thanks Ramin and Karthick--Earlier i faced some issues in accessing this post that's why i opened a new thread.Sorry for any incovienence caused.

    As Ramin and Karthick suggested, i have modified the SP as follows-

     

    create or replace procedure <proc_name>

    (

    in_empid IN USER_DEPT.EMPID%TYPE,

    out_code OUT NUMBER

    )

    AS

    BEGIN

          begin INSERT INTO TAB1 ( empid  )

                 VALUES (  in_empid );

                COMMIT;

                out_code:=0;

             when others then

           DBMS_OUTPUT.PUT_LINE('Error occur in USER_STORE:'||SQLERRM);

           dbms_output.put_line('backtrace: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

           out_code:=1;

            end;

         begin

         INSERT INTO TAB2 ( empid )

         VALUES( in_empid  );

       COMMIT;

    out_code =0;

    when others then

           DBMS_OUTPUT.PUT_LINE('Error occur in AUTH_PREFERENCE:'||SQLERRM);

           dbms_output.put_line('backtrace: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

           out_code:=1;

          end;

    EXCEPTION

    WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error occur:'||SQLERRM);

    dbms_output.put_line('backtrace: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

    rollback;

    out_code :=1;

    END <proc_name>

     

    1)Now i need to have dependancy on 1st and 2nd block(means the second insert should not even be attempted if the first one fails) likewuse we are plaaning to add tables to insert more tables).

     

    2)Also,would like to use VARRAY to pass the list of arguments.

     

    Thanks in advance..

  • 7. Re: Exception handling in a procedure!
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    1) one way if statement

    create or replace procedure < proc_name >(in_empid IN USER_DEPT.EMPID%TYPE,out_code OUT NUMBER) AS

    BEGIN

      begin

        INSERT INTO TAB1

          (empid)

        VALUES

          (in_empid);

        COMMIT;

        out_code := 0;

      when  others then

            DBMS_OUTPUT.PUT_LINE('Error occur in USER_STORE:' || SQLERRM);

            dbms_output.put_line('backtrace: ' ||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

            out_code := 1;

      end;

     

      if (out_code = 0) then

          begin

                INSERT INTO TAB2(empid)

                VALUES(in_empid);

                COMMIT;

                out_code = 0;

          when others then

               DBMS_OUTPUT.PUT_LINE('Error occur in AUTH_PREFERENCE:' || SQLERRM);

               dbms_output.put_line('backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

               out_code := 1;

          end;

      end if;

     

    EXCEPTION

    WHEN OTHERS THEN

         DBMS_OUTPUT.PUT_LINE('Error occur:' || SQLERRM);

         dbms_output.put_line('backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

         rollback;

         out_code := 1;

    END;

    2) What did you need to do with varray?

    Maybe this?

    Re: How to pass a string in a procedure

     

     

    ----

    Ramin Hashimzade

  • 8. Re: Exception handling in a procedure!
    446 Newbie
    Currently Being Moderated

    Thanks Ramin for your quick reponse.We suppose to pass 25 in parameters in this SP(due to space contraint,didn't mention all the in parameters)so my requirement is to make use of VARRAR.

     

    Also,is there any other way to accomplish the task-

    Now i need to have dependancy on 1st and 2nd block(means the second insert should not even be attempted if the first one fails) likewuse we are plaaning to add tables to insert more tables).

  • 9. Re: Exception handling in a procedure!
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    Why do you need other way? Why if statement is not way for you?

     

    ----

    Ramin Hashimzade

  • 10. Re: Exception handling in a procedure!
    AnnPricks E Guru
    Currently Being Moderated

    Do you want something like below?

    CREATE TABLE tab1(empid  NUMBER PRIMARY KEY,

                      ename  VARCHAR2(20)

                      );

    --- Error table

     

    CREATE TABLE tab2 (ORA_ERR_NUMBER$   NUMBER ,

                       ORA_ERR_MESG$     VARCHAR2(2000),

                       ORA_ERR_ROWID$    ROWID,

                       ORA_ERR_OPTYP$    VARCHAR2(2),

                       ORA_ERR_TAG$      VARCHAR2(4000),

                       inserted_dt         VARCHAR2(50) DEFAULT TRUNC(SYSDATE),

                       empid             VARCHAR2(10)

                       );


     

    CREATE OR REPLACE TYPE obj_empid IS OBJECT (empid NUMBER,

                                                ename VARCHAR2(20)

                                                );

    CREATE OR REPLACE TYPE nt_empid IS TABLE OF obj_empid;


     

    CREATE OR REPLACE PROCEDURE proc_test (in_empid  nt_empid,

                                           out_code  OUT  VARCHAR2

                                           )

    AS

    v_proc_name VARCHAR2(50) := 'PROC_TEST';

    v_ntempid nt_empid := in_empid;

    l_cnt NUMBER;

    BEGIN

    DELETE FROM tab2

    WHERE inserted_dt = TRUNC(SYSDATE)

    AND ORA_ERR_TAG$ LIKE '%'||v_proc_name||'%';

    COMMIT;

    INSERT INTO tab1

    SELECT empid,

            ename

    FROM TABLE(v_ntempid)

    LOG ERRORS INTO tab2(v_proc_name||'@tab1') REJECT LIMIT UNLIMITED;

    SELECT COUNT(*) INTO l_cnt

    FROM tab2

    WHERE inserted_dt = TRUNC(SYSDATE)

    AND ORA_ERR_TAG$ LIKE '%'||v_proc_name||'%';

    IF(l_cnt=0) THEN

      COMMIT;

    ELSE

      ROLLBACK;

    END IF;

    out_code := 'Procedure ' || v_proc_name || ' is completed with ' || l_cnt || ' errors';

    END;

  • 11. Re: Exception handling in a procedure!
    AnnPricks E Guru
    Currently Being Moderated

    Testcases for above solution

    SCOTT@orcl>VAR v_out VARCHAR2(100);

     

    SCOTT@orcl>EXEC proc_test(nt_empid(obj_empid(1000,'fghfgh')),:v_out);

     

    PL/SQL procedure successfully completed.


    SCOTT@orcl>PRINT v_out

     

    V_OUT

    --------------------------------------------------------------------------------

    Procedure PROC_TEST is completed with 0 errors


     

    SCOTT@orcl>SELECT * FROM tab1;

     

         EMPID ENAME

    ---------- --------------------

          1000 fghfgh


     

    SCOTT@orcl>SELECT * FROM tab2;

     

    no rows selected


    SCOTT@orcl>EXEC proc_test(nt_empid(obj_empid(1000,'fghfgh')),:v_out);

     

    PL/SQL procedure successfully completed.


     

    SCOTT@orcl>PRINT v_out

     

    V_OUT

    ----------------------------------------------------------------------------------------------------

    Procedure PROC_TEST is completed with 1 errors


    SCOTT@orcl>SELECT * FROM tab1;

     

         EMPID ENAME

    ---------- --------------------

          1000 fghfgh


    SCOTT@orcl>SELECT ORA_ERR_MESG$ FROM tab2;

     

    ORA_ERR_MESG$

    ----------------------------------------------------------------------------------------------------

    ORA-00001: unique constraint (SCOTT.SYS_C0042825) violated

     

  • 12. Re: Exception handling in a procedure!
    446 Newbie
    Currently Being Moderated

    Thanks ..

    if (out_code = 0) then

          begin

                INSERT INTO TAB2(empid)

                VALUES(in_empid);

                COMMIT;

                out_code = 0;

          when others then

     

    yes it works...since we have some 30 inparameters and need to insert the records for 5 more tables.Also, we have lakhs of records to insert.

    Moreover,i need to pass a list of data to the Stored procedure so that we can insert the same in multiple  rows of single table.

    Can we need to do it using VARRAY? else anyother way to do it?

    if yes,can anyone explain how to do it?

    thanks in advance

  • 13. Re: Exception handling in a procedure!
    AnnPricks E Guru
    Currently Being Moderated

    Ya that is what i have given some examples of DML error handling. You can use nested table to pass the parameters like

    CREATE OR REPLACE TYPE obj_empid IS OBJECT (empid NUMBER,

                                                ename VARCHAR2(20) --- You can mention your parameters

                                                );

    CREATE OR REPLACE TYPE nt_empid IS TABLE OF obj_empid; -- nested table holding all columns


    Then check my procedure how i insert into table from that input paramteter. The same way you can insert into 5 more tables while logging the error that time you need to pass your table name in orr_tag like
    LOG ERRORS INTO tab2(v_proc_name||'@tab1') REJECT LIMIT UNLIMITED;


    Please let me know in case of any issues

  • 14. Re: Exception handling in a procedure!
    446 Newbie
    Currently Being Moderated

    Thanks ANN..

    Based on your suggestions..i have modified my SP as follows..

     

    Since am not that much well versed in pl sql part,i need to keep things simple,so am good with exception handling part.

     

    Could you please help me out in handling of inserting multiple records in table by modifying the below SP?

     

    Whether it may be using nested table or varray

     

     

     

    create table t_err_log(prg_desc varchar2(50),err_msg varchar2(100));

    -----  Creating an error log table to store the log messages

    create or replace
    procedure proc1
    (
        in_gid             IN USR_STR.GUID%TYPE ,
        in_onlineId        IN USR_STR.ONLINEID%TYPE ,
        in_pwdStatus       IN USR_STR.PASSWORD_STATUS%TYPE ,
        in_pwdExpiry       IN USR_STR.PW_EXP_DATE%TYPE ,
        out_code OUT NUMBER )

    BEGIN
          Begin                    
          INSERT INTO USR_STR(GID,ONLINEID,PASSWORD_STATUS,PW_EXP_DATE,PARTYID)
          values(in_guid,in_onlineId,in_pwdStatus,in_pwdExpiry);(--likewise we have 30 columns)
          COMMIT;
          out_code:=0;
           when others then
           insert into t_err_log values('USR_STR','Error'||SQLERRM);
           out_code:=1;      
           end;
      
           BEGIN
           IF out_code=0 then
           INSERT INTO AUTH_id(GID,CH_PREF,CH_OPT)
           VALUES(in_gid,inChPref,inChOpt);
           COMMIT;
           out_code:=0;
           end if;
           when others then
           insert into t_err_log values('AUTH_PREFERENCE','Error'||SQLERRM);
           out_code:=1;
           end;
    EXCEPTION
    WHEN OTHERS THEN
    insert into t_err_log values('Others in main','Error'||SQLERRM);
    ROLLBACK
    out_code:=1;
    END proc1;

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points