Unable to continue the exception loop in the Oracle stored procedure

advertisements

I am trying to return list of number from a stored procedure. My stored procedure gets stopped in for loop when exception occur. I have added exception clause and control is going inside the exception clause to continue the loop but still no luck.

How to continue the loop when exception occur? Thanks.

     CREATE OR REPLACE PROCEDURE getNumber(l_list IN CUSTOMLIST, l_output OUT NUMLIST)
     IS
            n_num varchar2(5);
     BEGIN
            l_output := NUMLIST();

            FOR i IN l_list.FIRST .. l_list.LAST LOOP
                            l_output.EXTEND(l_list.LAST);
                      BEGIN
                        SELECT NUM into n_num
                            FROM sometable WHERE some condition;

                            l_output(i) := n_num;
                            EXCEPTION
                            WHEN NO_DATA_FOUND THEN
                             DBMS_OUTPUT.PUT_LINE('TRUE');
                             CONTINUE;
                      END;
            END LOOP;
END;


Maybe you should handle other exceptions too. Try with adding

    WHEN OTHERS THEN
       [statements]