Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle PL/SQL - ORA-01403 “No data found” when using “SELECT INTO”

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
like image 924
JavaSheriff Avatar asked Dec 02 '14 20:12

JavaSheriff


People also ask

How do you handle no data found exception in PL SQL?

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.

How do you handle no data found error?

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.

How do you handle no data found exception in cursor FOR loop?

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.

How do I fix error ORA-06512?

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).


2 Answers

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;
like image 76
nil Avatar answered Sep 28 '22 14:09

nil


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.

like image 25
T.S. Avatar answered Sep 28 '22 16:09

T.S.