Home » SQL & PL/SQL » SQL & PL/SQL » select data INTO variable "No data found error"
select data INTO variable "No data found error" [message #45321] Wed, 03 March 2004 05:56 Go to next message
raj_oracle
Messages: 5
Registered: March 2004
Junior Member
I am trying to write the query inside a procedure as "select empId into id_var where ........".This query giving me teh error as "No Data found". Probably for any condition the select query returning the null value and its trying to insert into id_var variable.So how to handlle this kind of situation , when u can expect the null value also to return??Can we do something so that if null value also returns , the query will execute fine.
�Pls someone help ...
Re: select data INTO variable "No data found error" [message #45324 is a reply to message #45321] Wed, 03 March 2004 07:38 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
That exception does not mean that the query returned a NULL value - it means that it returned no rows.

begin
  select empId
    into id_var
    from t
   where ...;
exception
  when no_data_found then
    null;  -- do nothing, id_var is NULL
    -- or default id_var to some value, however you want to handle it
  when others then
    raise;
end;
Re: select data INTO variable "No data found error" [message #45327 is a reply to message #45324] Wed, 03 March 2004 09:07 Go to previous messageGo to next message
raj_oracle
Messages: 5
Registered: March 2004
Junior Member
this code is insdie for loop and i dont want to stop the for execution after getting the null record , but by writting the exception block , for doesnot go further.How can i continue with for if this null comes also...
Re: select data INTO variable "No data found error" [message #45330 is a reply to message #45327] Wed, 03 March 2004 10:38 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The exception handling should be inside the loop.

begin
  for r in (select ...) loop
    begin
      select ...
        into ...
        from ...
       where ...;
 
      -- do something
 
    exception
      when no_data_found then
        null;  -- this will catch the exception, do nothing
               -- and let the loop continue
    end;
  end loop;
end;
Re: select data INTO variable "No data found error" [message #45334 is a reply to message #45321] Thu, 04 March 2004 01:19 Go to previous messageGo to next message
Ajendra Naraya Samal
Messages: 26
Registered: December 2003
Junior Member
How abt
select NVL(empId, 0) into id_var where condn..

But id_var will be having value 0 when there is NUL. On tht case u have to process yr logic for the rest of the lines of code.
Re: select data INTO variable "No data found error" [message #45339 is a reply to message #45334] Thu, 04 March 2004 07:38 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I believe we are dealing with the lack of a matching row here, not a row with a NULL value for the column.
Previous Topic: Outer join
Next Topic: UTL FILE
Goto Forum:
  


Current Time: Sun May 18 16:51:06 CDT 2025