Home » SQL & PL/SQL » SQL & PL/SQL » function raise no error when call in select. (oracle 10g)
function raise no error when call in select. [message #443843] Wed, 17 February 2010 10:48 Go to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
hi All,

i have one question why function does no raise error no_data found when call in select statement.

1) create one function.

 CREATE OR REPLACE function fn_sal(v_id NUMBER) RETURN NUMBER 
  IS

 v_sal NUMBER;
 BEGIN   
  SELECT sal INTO v_sal FROM emp where empno=0; 
 RETURN v_sal;

  END;



2) call it in select statement.

SELECT fn_sal(e.sal),e.* FROM emp e 


select satement cause no error , it displayes all the records but null for the function cloumn.

why it not gives no_data_found error.

Best Regards
Jitender Sadh
==============
Re: function raise no error when call in select. [message #443844 is a reply to message #443843] Wed, 17 February 2010 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because this is how it is implemented.
NO_DATA_FOUND exception is trapped and function value is null.
This is the expected behaviour.

Regards
Michel

[Updated on: Wed, 17 February 2010 11:06]

Report message to a moderator

Re: function raise no error when call in select. [message #443846 is a reply to message #443844] Wed, 17 February 2010 11:24 Go to previous messageGo to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
hi,

where exception is trapped if i use same code in PL/SQL bolck it gives me error no_data_found
DECLARE
vv_sal NUMBER;  
function fn_sal(v_id NUMBER) RETURN NUMBER 
  IS

 v_sal NUMBER;
 BEGIN   
  SELECT sal INTO v_sal FROM emp where empno=0; 
 RETURN v_sal;

  END;



BEGIN
   vv_sal:=fn_sal(8001);
   Dbms_Output.put_line(vv_sal);
   END;


Best Regards
jitender Sadh
===============
Re: function raise no error when call in select. [message #443847 is a reply to message #443843] Wed, 17 February 2010 11:27 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have a read of this ask tom thread

[Updated on: Wed, 17 February 2010 11:27]

Report message to a moderator

Re: function raise no error when call in select. [message #443848 is a reply to message #443846] Wed, 17 February 2010 11:36 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is trapped in the SQL engine.

Regards
Michel
Previous Topic: update using subquery
Next Topic: FORALL with EXECUTE IMMEDIATE
Goto Forum:
  


Current Time: Mon Sep 26 06:02:13 CDT 2016

Total time taken to generate the page: 0.06220 seconds