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

    Exception handling in a procedure!

    446


      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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