I have a pl sql code that execute three queries sequentially to determine a match level and do some logic
The issue is - when first query has no results (completely valid scenario) I get ORA-01403 No data found.
I understand that I need to incorporate [ Exception clause when NO_DATA_FOUND ]- but how to add it and continue to the next query?
PL/SQL Code
SELECT A into PARAM A FROM SAMPLE WHERE SOME CONDITION;
-- GOT ORA-01403 No data found HERE
MATCH_LEVEL =1;
if A is null then
do some logic;
end if
SELECT A INTO PARAM_B FROM SAMPLE WHERE SOME OTHER CONDITION
MATCH_LEVEL =2
if A is null then
do some logic 2;
end if
SELECT A INTO PARAM_B FROM SAMPLE WHERE SOME OTHER CONDITION
MATCH_LEVEL =3
if A is null then
do some logic 3;
end if
END PL/SQL Code
Answer: To prevent the PLSQL code from dropping to the exception code when a record is not found, you'll have to perform a count first to determine the number of records that will be returned. For example: -- Check to make sure that at least one record is returned SELECT COUNT(1) INTO v_count FROM sales.
The SELECT INTO statement does not accept an empty result set. To avoid a no data found exception being thrown when a result set is empty, this condition should be handled by either an exit handler, an emptiness check, or by assigning default values.
Declare a new EXCEPTION in the declarations section, perhaps call it NAME_IS_NULL. In the loop body, check to see if the EMP_NAME is NULL (before the INSERT statement). If it is, throw the NAME_IS_NULL exception.
There are 3 ways to resolve Ora-06512: Fix the error causing the unhandled error. Write an exception handler for the unhandled error. Contact the database administrator (DBA).
Declare
--your declarations
begin
SELECT A into PARAM A FROM SAMPLE WHERE SOME CONDITION;
-- GOT ORA-01403 No data found HERE
Begin
MATCH_LEVEL =1;
if A is null then
do some logic;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('Error...');
END;
--- and son on for other blocks
end;
Just surround your SELECT INTO
with begin-end;
begin
-- your faulty statement here
Exception
When NO_DATA_FOUND Then
-- Do what you want or nothing
WHEN TOO_MANY_ROWS THEN
-- what if you get more then one row? and need specific handler for this
When OTHERS Then
-- do something here or nothing (optional - may happen if you have more than your SELECT INTO between 'begin' and 'Exception')
end;
This is like try
block of PL/Sql
With this technique you can log the reason your statement failed.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With