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]