Continue sql stored proc processing in spite of exceptions

Question

I have to capture the exception message which occurs when the stored proc is executed. In the below stored proc, the proc executes and captured the first exception encountered. However, I the stored proc exits once the exception is encountered. Is there a way in which I can ask the stored proc to keep running even after encountering the exception

Kindly help !!!

PROCEDURE Test_script (OUT XYZRow)

    BEGIN

        DECLARE Veiw_Name VARCHAR(2147483647);
        DECLARE Object_Name VARCHAR(2147483647);
        DECLARE Object_Type VARCHAR(2147483647);
        DECLARE Domain_Name VARCHAR(2147483647);
        DECLARE Status VARCHAR(2147483647);
        DECLARE "READ" VARCHAR(100);
        DECLARE PUBLIC SetException EXCEPTION;

        for r as select 
                *
            from table1 do            

   for r2 as SELECT 

                    r1.name as Object_Nam,
                    r1.nameType as Object_Typ,
                    r1."domain" as domain_nam,
                    CASE r1.Status  AS Status,
                    CASE r1.c_R  AS READ_Stat
                     FROM function_xyz(r.PATH) r1 
               do

                 set Veiw_Name = r.PATH;
                set Object_Name = r2.Object_Nam;
                set Object_Type = r2.Object_Typ;
                set Domain_Name = r2.domain_nam;
                set Status = r2.Status;
                set "READ" = r2.Read_Stat;

   INSERT INTO XYZRow VALUES (Veiw_Name, Object_Name, Object_Type, Domain_Name, Status, "READ");

            end for;
        end for;
EXCEPTION
        WHEN System.SystemException THEN
             CALL PRINT(CURRENT_EXCEPTION.MESSAGE);
END

This looks like T-SQL not oracle PLSQL. have you tagged this correctly? — kevinskio, Feb 12 '16 at 20:25
Its actually in Cisco Information server earlier known as composite software.. a data virtualization tool.. the backend is oracle database though... Hope this helps ! — user5704279, Feb 12 '16 at 20:30
Ignoring exceptions is probably the worst kind of bad practice, so I hope this is not the production code, but anyway. The only way to "keep running" is to wrap every statement that can throw exception in `BEGIN - EXCEPTION WHEN - END` exception handler, instead of catching exceptions once. — Paul, Feb 12 '16 at 20:54

Marco Polo · Accepted Answer · 2016-02-13T17:07:37.237

That's because your exception is at the end of your procedure. You can trap the exception within the loop and it would continue.

Something like this would trap your exception when it occurs anywhere within the second loop and stay within first loop :

PROCEDURE Test_script (OUT XYZRow)

BEGIN

    DECLARE Veiw_Name VARCHAR(2147483647);
    DECLARE Object_Name VARCHAR(2147483647);
    DECLARE Object_Type VARCHAR(2147483647);
    DECLARE Domain_Name VARCHAR(2147483647);
    DECLARE Status VARCHAR(2147483647);
    DECLARE "READ" VARCHAR(100);
    DECLARE PUBLIC SetException EXCEPTION;

    for r as select * from table1 do

      --
      -- The entire LOOP will be trapped by the EXCEPTION
      --
      begin
        for r2 as SELECT

                r1.name as Object_Nam,
                r1.nameType as Object_Typ,
                r1."domain" as domain_nam,
                CASE r1.Status  AS Status,
                CASE r1.c_R  AS READ_Stat
                 FROM function_xyz(r.PATH) r1 
           do

             set Veiw_Name = r.PATH;
            set Object_Name = r2.Object_Nam;
            set Object_Type = r2.Object_Typ;
            set Domain_Name = r2.domain_nam;
            set Status = r2.Status;
            set "READ" = r2.Read_Stat;
            INSERT INTO XYZRow VALUES (Veiw_Name, Object_Name, Object_Type, Domain_Name, Status, "READ");
        end for;
      EXCEPTION
        WHEN System.SystemException THEN
         CALL PRINT(CURRENT_EXCEPTION.MESSAGE);
      END;
      --
      -- If exception occurs, execution will resume here and the first
      -- loop will continue
      --
    end for;

END

The exception actually occurs when we call function_xyz(r.PATH).. its is a stored proc which needs to be called.. When I am following your directions the proc is not able to catch the exception — user5704279, Feb 12 '16 at 21:43
I am using cursors in the for.. select .. do loop and when it calls the function_xyz it throws the error which I need to capture . any ideas how to crack it ?? — user5704279, Feb 13 '16 at 13:16
It looks somewhat like PL-SQL but the syntax is a little different so I'll give it my best shot but you'll probably have to tweak it. — Marco Polo, Feb 13 '16 at 16:47
Wppw Marco... Amazingg !! the code captures the exception and the loop continuing .. thanks .. amazing logic ... Great Bro !! — user5704279, Feb 13 '16 at 22:31
Don't forget to mark the answer as "usefull". Somehow that's our paycheck :) — Marco Polo, Feb 14 '16 at 15:15

Continue sql stored proc processing in spite of exceptions

1 Answers1