tag:blogger.com,1999:blog-7849367040589270673.post6492767311493070087..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: PL/SQL Brain Teaser: When is NO_DATA_FOUND not?Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-7849367040589270673.post-11127328584149529272016-12-21T05:54:41.264-08:002016-12-21T05:54:41.264-08:00Exactly. NO_DATA_FOUND is, on the one hand, an exc...Exactly. NO_DATA_FOUND is, on the one hand, an exception like any other. And on the other hand, it is different, in that the lack of data often does not indicate any kind of actual error, but simply a data condition. And it was decided that when a function executed within a SQL statement fails with an unhandled NO_DATA_FOUND, that NULL would simply be returned.Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-76106583775043512152016-12-21T05:53:24.418-08:002016-12-21T05:53:24.418-08:00Thanks, everyone! Excellent responses - especially...Thanks, everyone! Excellent responses - especially that reminder about Coalesce. Edwin, I hadn't even thought of that!Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-62292044334865349662016-12-21T05:04:05.755-08:002016-12-21T05:04:05.755-08:00Sorry. The above code will throw an exception.
The...Sorry. The above code will throw an exception.<br />The code that could possibly work is:<br />DECLARE<br />n NUMBER;<br />BEGIN<br />select nvl(ndf(),1) into n from dual;<br />DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');<br />END;Swarnapriyanoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-37238429815695869182016-12-21T00:01:55.800-08:002016-12-21T00:01:55.800-08:00Oracle documentation says:
NO_DATA_FOUND 01403 +...Oracle documentation says: <br /><br />NO_DATA_FOUND 01403 +100<br /><br />Because this exception is used internally by some SQL functions to signal completion, you must not rely on this exception being propagated if you raise it within a function that is invoked as part of a query.<br /><br />Anonymoushttps://www.blogger.com/profile/00158161107332795754noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-49344129775335405462016-12-20T21:13:04.644-08:002016-12-20T21:13:04.644-08:00DECLARE
n NUMBER;
BEGIN
n := ndf();
DBM...DECLARE<br /> n NUMBER;<br />BEGIN<br /> n := ndf();<br /> DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');<br />END;<br />/Swarnapriyanoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-82763349339805156982016-12-20T12:31:12.974-08:002016-12-20T12:31:12.974-08:00My first thought before reading the comments was t...My first thought before reading the comments was to look for a way to short circuit the function call just like Edwin's first answer. <br /><br />It was indeed surprising for me, that the column list in a sql select-into doesn't raise NO_DATA_FOUND. I guess, the more you know. <br /><br />Any explanation on why it implicitly assumes a null instead of raising the error, or am I failing to notice something really obvious?<br /><br />I already spot checked other sql and plsql exceptions like INVALID_CURSOR, DUP_VAL_ON_INDEX, too_many_rows, etc. and it does raise all of them. (makes sense for too_many_rows) ruudvannoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-42528028401645394902016-12-20T06:50:21.792-08:002016-12-20T06:50:21.792-08:00in ANSI SQL no_data_found is not an error ?in ANSI SQL no_data_found is not an error ?Anonymoushttps://www.blogger.com/profile/00158161107332795754noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-47875606951322723292016-12-20T05:05:05.760-08:002016-12-20T05:05:05.760-08:00and , perhaps surprisingly for some,
select ndf ...and , perhaps surprisingly for some,<br /><br /> select ndf into n from dual;<br /><br />(EE Release 12.1.0.2.0)Edwin van Meerendonkhttps://www.blogger.com/profile/15599244675806305397noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-40631860756302480062016-12-20T05:01:54.091-08:002016-12-20T05:01:54.091-08:00At least this one
n := coalesce(1, ndf);At least this one<br /><br /> n := coalesce(1, ndf);Edwin van Meerendonkhttps://www.blogger.com/profile/15599244675806305397noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-79663160920961927182016-12-20T04:47:34.203-08:002016-12-20T04:47:34.203-08:00Would you accept this :
DECLARE
n NUMBER;
BE...Would you accept this :<br /><br />DECLARE<br /> n NUMBER;<br />BEGIN<br /> select count(1) into n from dual where ndf() = 1;<br /> DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');<br />END;<br />/<br />PPhttps://www.blogger.com/profile/06231514348394939521noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-5750296165206899602016-12-20T04:44:19.267-08:002016-12-20T04:44:19.267-08:00select ndf into n from dual;select ndf into n from dual;Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-29256015855660731522016-12-20T04:38:06.940-08:002016-12-20T04:38:06.940-08:00Hi Steven,
How about this?
DECLARE
n NUMBER...Hi Steven,<br /><br />How about this?<br /><br />DECLARE<br /> n NUMBER;<br />BEGIN<br /> select ndf into n from dual;<br /> DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');<br />END;<br />/<br /><br />NDF? What NDF?<br /><br />PL/SQL procedure successfully completed.<br /><br />Joaquin Gonzalezhttps://www.blogger.com/profile/12638256027643700661noreply@blogger.com