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.

About these ads

4 Responses to No_data_found in a function called from sql

  1. Boneist says:

    “The error message is exactly the same in Toad, but you then have to disconnect and reconnect to do anymore work”

    You know about the “Test Connections” functionality under File? Means you can reconnect without having to specifically disconnect and reconnect.

  2. dombrooks says:

    Yep – I know that. I was just highlighting that Toad cleans up the connection on the basis of the error message whereas other clients don’t / might not.
    SQL*Plus doesn’t.
    SQLDeveloper does.

  3. chet says:

    That’s certainly something I ever knew (or realized); that calling a function from SQL resulting in passing NULL from no_data_found.

    Thanks for the followup/explanation post…
    chet

  4. Pingback: Pythian Group Blog » Blog Archive » Log Buffer #70: a Carnival of the Vanities for DBAs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 68 other followers

%d bloggers like this: