Home » SQL & PL/SQL » SQL & PL/SQL » Strange behavior using function in SQL and PL/SQL (Oracle 11g/9i, Windows/Solaris)
Strange behavior using function in SQL and PL/SQL [message #419060] Wed, 19 August 2009 13:04 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Please check this below simple snipset. When I use this function within PL/SQL i'm getting expected exception but not while using it in SELECT statement.

SQL> CREATE OR REPLACE FUNCTION dummy1 
  2  RETURN NUMBER IS
  3  i NUMBER;
  4  BEGIN
  5           SELECT 1 INTO i FROM dual WHERE 1<>1;
  6               
  7           RETURN i;
  8  END;
  9  /

Function created.

SQL> BEGIN
  2  dbms_output.put_line(dummy1);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "DUMMY1", line 5
ORA-06512: at line 2


SQL> SELECT dummy1() FROM dual;

  DUMMY1()
----------


SQL> 


Not sure this is optimizer smartness or bug.

Any thoughts??
Re: Strange behavior using function in SQL and PL/SQL [message #419061 is a reply to message #419060] Wed, 19 August 2009 13:07 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No bug, it is the expected behaviour.

Regards
Michel
Previous Topic: Help Needed, New to PLSQL
Next Topic: how to get two different dates difference in day/hour/min/sec please
Goto Forum:
  


Current Time: Sat Dec 10 07:04:12 CST 2016

Total time taken to generate the page: 0.08178 seconds