No_data_found in a function called from sql

Yesterday I made a comment on OracleNerd’s latest post on coding style, that when calling a plsql function from SQL, any NO_DATA_FOUND exception will be handled by SQL and a null will be returned by SQL.

Having done a fruitless search on what exceptions would and wouldn’t be handled, I started writing an initial draft of this post. Later I found this post from Lewis C about whether this behaviour was a bug of a feature (I recommend reading it, notably the behaviour mentioned in comments from Gary Myers and Jonathan Lewis).

Bug or feature, it’s behaviour worth noting especially if you’re testing a function consisted of just calling from SQL.

Anyway, using this list of named exceptions published at PSOUG, I rattled through a quick test of behaviour against raised named exceptions.


CREATE OR REPLACE FUNCTION domtest
RETURN NUMBER
AS
BEGIN
RAISE no_data_found;
END;
/



SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 2 08:43:10 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production

DOM@DOMTEST>select domtest from dual
2 /


DOMTEST
----------


Elapsed: 00:00:00.00
DOM@DOMTEST>


No surprises there then.

What about too many rows?
Well, here’s when it got slightly interesting – well only ever so slightly perhaps.


CREATE OR REPLACE FUNCTION domtest
RETURN NUMBER
AS
BEGIN
RAISE too_many_rows;
END;
/


I was initially running my tests in Toad, so I guess it serves me right.
I was surprised to see that the error was handled and I also got a NULL returned.
I was not expecting that. And later, when I saw Lewis C’s article mentioned above, my result didn’t tally with his observations.
And I was all set to say that this behaviour had changed between versions and that it had changed in my XE version but….

Sure enough when I came to write the demo of the case in this post using SQL*Plus as a better illustration – I got the result I had initially expected.


DOM@DOMTEST>/
select domtest from dual
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CASH.DOMTEST", line 5


So, the handling of the error depends not completely surprisingly on the client used.

The other interesting observation was also a Toadism involving the not_logged_on exception:


CREATE OR REPLACE FUNCTION domtest
RETURN NUMBER
AS
BEGIN
RAISE NOT_LOGGED_ON;
END;
/


In SQL*Plus, the result is:


DOM@DOMTEST>l
1* select domtest from dual
DOM@DOMTEST>/
select domtest from dual
*
ERROR at line 1:
ORA-01012: not logged on
ORA-06512: at "CASH.DOMTEST", line 5


The error message is exactly the same in Toad, but you then have to disconnect and reconnect to do anymore work, unlike in SQL*Plus where you can continue on your merry way.

The Toad error message was Background session could not connect to Oracle. The original/background connection to Oracle has been closed (as a cleanup by Toad presumably, given the different SQL*Plus behaviour). Incidentally and also not surprisingly, Toad uses a number of connections per “Toad connection” – hence the reason you can deadlock yourself if you try to kill a job using the job tab of the schema browser.

Anyway, hardly challenging stuff but very slightly interesting nevertheless. Sometimes it’s nice to take a break from the more complex daily stuff that’s doing your head in.