Oracle-Variable without data

Asked

Viewed 1,727 times

1

I have a problem and a doubt at the same time, I made a function to see invoiced orders and declared a function variable so: 

select filsaida
  into v_loja
  from mov_itped where pedido= p_pedido and status <> 6;
end ;

When you have some record that matches the clause WHERE, the function returns normal, but when it does not return record, gives the error ORA-01403: data not found.

sql oracle function

  • Could you put the function? Or the statement and part of the return?

2 answers

5


In a "into" condition you must return only one record. If return several will give the exception ORA-01422, and if return no will give the exception you quoted.

If you have a default value, for your variable when the record does not exist you can use an Exception block treating the NO_DATA_FOUND exception. Ex:

Declare
  numero integer;
begin
  begin
    select 1 into numero 
      from dual 
     where 1 = 2;
  exception
    when NO_DATA_FOUND then
      numero := 0;
  end;
  dbms_output.put_line(numero);
end;

  • Opa vlw friend by the help, the Exception worked right tried to put a default value for variable but had not worked had used CASE and IF ELSE the two of them not working was getting crazy kkk.

-1

In fact, a very simple way to get around this problem is instead of putting select 1 for anything, put select Count(*) for anything, because Count, if the Where clause is not met will already return 0 zero, otherwise return 1, ai you can treat zero as negative and >0 with positive.

Browser other questions tagged sql oracle function

You are not signed in. Login or sign up in order to post.