Oracle Tips: select into …no data found error, and exception handling in PLSQL code
DECLARE L_Term_ID NUMBER; BEGIN BEGIN SELECT NVL(Term_ID,-1) INTO L_Term_ID FROM blog_terms WHERE UPPER(slug) = UPPER('no data to query') AND rownum = 1 ; DBMS_OUTPUT.PUT_LINE(L_Term_ID); END;
if you run the query above, and no data return we get this error
01403. 00000 - "no data found" *Cause: No data was found from the objects. *Action: There was no data from the objects which may be due to end of fetch. PL/SQL procedure successfully completed.
One of the solution is to create a exception block “NO_DATA_FOUND,TOO_MANY_ROWS” where it will return -1, working example:
DECLARE L_Term_ID NUMBER; BEGIN BEGIN SELECT NVL(Term_ID,-1) INTO L_Term_ID FROM blog_terms WHERE UPPER(slug) = UPPER('no data to query'); EXCEPTION WHEN NO_DATA_FOUND THEN L_Term_ID:= -1; EXCEPTION WHEN TOO_MANY_ROWS THEN L_Term_ID:= -1; END; DBMS_OUTPUT.PUT_LINE(L_Term_ID); END;
