This is pretty basic for PL/SQL but worth a quick review.
When you do a SELECT … INTO in a PL/SQL block, if it returns no rows the exception NO_DATA_FOUND is raised.
Why is this important? Consider this block of code:
DECLARE
today DATE;
BEGIN
SELECT
sysdate
INTO today
FROM
dual
WHERE
1 = 2;
IF today IS NULL THEN
RETURN;
END IF;
dbms_output.put_line(today);
END;
/
This is a simplified example of something I just ran into. The idea was that the select was getting a value from the table in a variable. If nothing was returned, exit out of the procedure, otherwise continue processing. Of course, the predicate wasn’t “1=2” in the code I was reviewing, this is to simulate the data not existing in the table.
Important Note: The column being selected is a NOT NULL column, so the returned value will never be null so long as the condition was TURE. It would only be “NULL” if the condition was FALSE, as in, no rows returned.
What does happen when this is run, is that the unhandled exception NO_DATA_FOUND is returned to the calling environment. The IF checking for the NULL is not run when there is no data returned, the exception is raised and the execution immediately returns to the calling environment in an error state.
ORA-01403: no data found
ORA-06512: at line 4
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.
Not likely the intent of the coder. Maybe, but given that IF right after the SELECT … INTO it seems highly unlikely that this was the intention. If it was the intention to have it exit in error, then the IF shouldn’t be there.
How to fix this? The most direct way is to handle the exception locally like this.
DECLARE
today DATE;
BEGIN
BEGIN
SELECT
sysdate
INTO today
FROM
dual
WHERE
1 = 2;
EXCEPTION
WHEN no_data_found THEN
RETURN;
END;
dbms_output.put_line(today);
END;
/
The double “BEGIN” in this example looks odd, the code I was working with this was a large procedure (3200+ lines long) so it was just a small part of the code.
The intent here with surrounding the SELECT … INTO with an anonymous block is to handle the exception locally. Now the block runs with:
PL/SQL procedure successfully completed.
Which is likely what the original coder was intending to have happen. There are other things to consider, you could have an exception handler at the bottom of the code to handle all NO_DATA_FOUND exceptions rained, assuming you want to do the same thing if any of the SELECT … INTO statements.
The advantage of the technique I show here, you can handle each one on its own and decided what to do for each one separately. Which might be nothing. To do nothing, just put in NULL; in place of the RETURN; in the example I have above.
Have a great 2022 everyone!!
No comments:
Post a Comment