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

Answer 1

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