Dec 132013
 

Another puzzle, when we execute the function below it returns NO_DATA_FOUND because the table is empty:

create function raises_no_data_found
  return integer is
  a_variable integer;
begin
 
  select no_records
  into   a_variable   
  from   this_empty_table;
   
  return a_variable;
   
end;
/

declare
  var integer;
begin
  var := raises_no_data_found;
end;
/

Error report:
ORA-01403: no data found
ORA-06512: at "CHRIS.RAISES_NO_DATA_FOUND", line 6
ORA-06512: at line 4
01403. 00000 -  "no data found"

Without changing the function in anyway or adding data to the table in question, we can call this function in a way that prevents the NO_DATA_FOUND exception from being raised. How?

  6 Responses to “PL/SQL Quiz: How can we stop this function raising NO_DATA_FOUND (without changing it)?”

  1. SELECT raises_no_data_found() FROM dual

  2. A couple of ways that I can think of, i) call it in a SELECT statement, e.g. SELECT function FROM dual etc, ii) rename the table and make a view called the same thing and the view returns a row. You’re not technically adding a row to the table or changing the function! ;-)

  3. drop function raises_no_data_found;

    [just kidding]

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)