Oracle PL / SQL - ORA-01403 "No data found" when using "SELECT INTO"
I have sql code that runs three queries in succession to determine the compliance level and does some logic
Problem: When the first query has no results (completely valid scenario) I get ORA-01403. No data found.
I understand that I need to enable [Exception clause when NO_DATA_FOUND], but how do I add it and continue with the next request?
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
source to share
Just arrange SELECT INTO
withbegin-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;
It looks like a try
blockPL/Sql
With this technique, you can register the reason your application failed.
source to share
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;
source to share
For a statement, SELECT ... INTO ...
the PL / SQL engine assumes that your query will have one and only one row. If there is no row or more than one, an exception is thrown.
FWIW, you can handle such cases without resorting to exception handling by using aggregate functions. Thus, there will always be only one row in the result set.
Assuming it A
can't be NULL
in your lines:
SELECT MAX(A) into PARAM A FROM SAMPLE WHERE SOME CONDITION;
-- A would be NULL if there was *no* row. Otherwise, it is *the* value for *the* row
MATCH_LEVEL =1;
if A is null then
do some logic;
end if
If the value NULL
is a possible case, just add an extra column COUNT(*)
:
SELECT MAX(A), COUNT(*) into A, HAS_FOUND_ROW FROM SAMPLE WHERE SOME CONDITION;
if HAS_FOUND_ROW > 0 then
...
end if;
source to share
Oracle won't let you open an implicit cursor (i.e. a statement select
in the body of your code) that doesn't return rows. You have two options (3 indeed, counting @ Sylvain's answer, but this is an unusual approach): use an explicit cursor or handle the error.
Explicit cursor
The explicit cursor is in a section DECLARE
to be opened and selected manually (or in a loop FOR
). This has the added benefit that if you set the query parameters correctly, you can write it once and use it multiple times.
DECLARE
a sample.a%type;
MATCH_LEVEL number;
cursor cur_params (some_column_value number) is
SELECT A FROM SAMPLE WHERE some_column = some_column_value;
BEGIN
MATCH_LEVEL := 1;
open cur_params (match_level);
fetch cur_params into a;
close cur_params;
if A is null then
null; --some logic goes here
end if;
MATCH_LEVEL := 2;
open cur_params (match_level);
fetch cur_params into a;
close cur_params;
if A is null then
null; --some logic goes here
end if;
end;
Handle the error
If you choose to handle this error, you need to create a block BEGIN...END
around the code that will throw the error. Ignoring the error, it is important that you ignore the specific error that you want to avoid when it is generated from the specific statement that you expect it to be. If you just added a section EXCEPTION
to an existing block BEGIN...END
, for example, you might not know which statement generated it, or even if it was indeed the error you expected.
DECLARE
a sample.a%type;
MATCH_LEVEL number;
BEGIN
MATCH_LEVEL := 1;
BEGIN
SELECT A into A FROM SAMPLE WHERE some_column = MATCH_LEVEL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null; --Do nothing
END;
if A is null then
null; --some logic goes here
end if;
MATCH_LEVEL := 2;
BEGIN
SELECT A into A FROM SAMPLE WHERE some_column = MATCH_LEVEL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null; --Do nothing
END;
if A is null then
null; --some logic goes here
end if;
end;
While I discourage him, you can catch any other errors in the same exception blocks. However, by definition, these errors would be unexpected, so it would be bad practice to discard them (you never know they even happened!). Generally speaking, if you are using a clause WHEN OTHERS
in your exception handling, the clause must always end with RAISE;
so that the error is propagated to the next level and not lost.
source to share