How Do I (or Oracle) Raise NO_DATA_FOUND?
"Let me count the ways."
The brain teaser is: what are any or all of the ways that the NO_DATA_FOUND exception can be raised in a PL/SQL block?
Extra bonus points if you can provide an example in two rhyming lines, as you will find in the sonnet that inspired this brain teaser:
How Do I Love Thee? (Sonnet 43)
by Elizabeth Barrett Browning, 1806 - 1861
How do I love thee? Let me count the ways.
I love thee to the depth and breadth and height
My soul can reach, when feeling out of sight
For the ends of being and ideal grace.
I love thee to the level of every day’s
Most quiet need, by sun and candle-light.
I love thee freely, as men strive for right.
I love thee purely, as they turn from praise.
I love thee with the passion put to use
In my old griefs, and with my childhood’s faith.
I love thee with a love I seemed to lose
With my lost saints. I love thee with the breath,
Smiles, tears, of all my life; and, if God choose,
I shall but love thee better after death.
And now that many developers have contributed their examples and stanzas, I offer a greatest hits compilation:
How Do I (or Oracle) Raise NO_DATA_FOUND?
How do I raise NO_DATA_FOUND? Let me count they ways.
I queried a row, but I was not able,
Then I looked for the wrong entry in a PL/SQL table.
"Let me count the ways."
The brain teaser is: what are any or all of the ways that the NO_DATA_FOUND exception can be raised in a PL/SQL block?
Extra bonus points if you can provide an example in two rhyming lines, as you will find in the sonnet that inspired this brain teaser:
How Do I Love Thee? (Sonnet 43)
by Elizabeth Barrett Browning, 1806 - 1861
How do I love thee? Let me count the ways.
I love thee to the depth and breadth and height
My soul can reach, when feeling out of sight
For the ends of being and ideal grace.
I love thee to the level of every day’s
Most quiet need, by sun and candle-light.
I love thee freely, as men strive for right.
I love thee purely, as they turn from praise.
I love thee with the passion put to use
In my old griefs, and with my childhood’s faith.
I love thee with a love I seemed to lose
With my lost saints. I love thee with the breath,
Smiles, tears, of all my life; and, if God choose,
I shall but love thee better after death.
And now that many developers have contributed their examples and stanzas, I offer a greatest hits compilation:
How Do I (or Oracle) Raise NO_DATA_FOUND?
How do I raise NO_DATA_FOUND? Let me count they ways.
I queried a row, but I was not able,
Then I looked for the wrong entry in a PL/SQL table.
I had a file, and its content was Divine.
Read past the end, line by line.
Read past the end, line by line.
Most times Oracle does the trick,
But I can also raise it, through thin and thick.
But I can also raise it, through thin and thick.
Four dreams in my heart I opened to thee,
Reached though for a fifth and were lost unto me.
Reached though for a fifth and were lost unto me.
Oracle always adds something new,
So, what else is left for a mere mortal like you?
Just learn, play the PL/SQL Challenge and try.
Then you will better understand everything, how and why.
So, what else is left for a mere mortal like you?
Just learn, play the PL/SQL Challenge and try.
Then you will better understand everything, how and why.
I queried a row, but I was not able,
ReplyDeleteThen I looked for the wrong entry in a plsql table
No fair, Connor. One stanza, two instigators. But I was not clear on the rules, so nice work! Here's the explanation:
ReplyDelete1. We all (mostly) know about the "original" NO_DATA_FOUND:
DECLARE
s VARCHAR2(1);
BEGIN
SELECT 'x' INTO s
FROM dual
WHERE 1 = 2;
END;
That is, you execute an implicit query (SELECT-INTO) and no row is found.
That covers the first line in Connor's contribution.
2. Then, long ago, when Oracle introduction collections (aka, arrays) into PL/SQL, calling them initially PL/SQL tables, it was decided that if you try to "read" an element of the collection at an undefined index value, NO_DATA_FOUND is also raised, as in:
DECLARE
strings DBMS_SQL.VARCHAR2_TABLE;
BEGIN
DBMS_OUTPUT.PUT_LINE (strings(1));
END;
no rhyming (sorry):
ReplyDeleteSQL> r
1 begin
2 raise no_data_found;
3* end;
begin
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 2
I had a file, and its content was Divine.
ReplyDeleteRead to the end, line by line.
Thanks, JBB. But shouldn't it be:
ReplyDelete"Read past the end, line by line."
JBB is precisely right. If you use UTL_FILE to read past the end of a file, NO_DATA_FOUND will be raised! Hey, why not? It's just like reading a row in a table that doesn't exist, right? Right?!
Here's some code that you can use to test out JBB's stanza:
/* Need create any directory privilege to use */
CREATE OR REPLACE DIRECTORY test_dir AS 'c:\temp'
/
/* Create a file to be read */
DECLARE
l_file UTL_FILE.file_type;
BEGIN
l_file := UTL_FILE.fopen ('TEST_DIR', 'test.txt', 'W');
UTL_FILE.put_line (l_file, 'text');
UTL_FILE.fclose (l_file);
END;
/
DECLARE
l_line VARCHAR2 (32767);
l_file UTL_FILE.file_type;
BEGIN
l_file := UTL_FILE.fopen ('TEMP', 'test.txt', 'R');
UTL_FILE.get_line (l_file, l_line);
UTL_FILE.get_line (l_file, l_line);
UTL_FILE.fclose (l_file);
END;
/
@Zohar, how about this for a rhyming version:
ReplyDeleteMost times Oracle does the trick,
But I can also raise it, through thin and thick.
Certainly one way that NO_DATA_FOUND can be raised is by the programmer raising it herself explicitly with a RAISE statement.
Thank you Steven, "read past" is correct, whilst "to the end" was not.
ReplyDeleteWell that's four different ways to have a NO_DATA_FOUND. I'll keep scratching my head thinking of other ways.
(Kind of a dupe - For i in 1..5 loop for a list of 4 items):
ReplyDeleteFour dreams in my heart I opened to thee,
Reached though for a fifth and were lost unto me.
@will, can you post a code example so I can see exactly what you mean?
ReplyDeleteSure (contrived and forced as it is) - worst practices included for free!
ReplyDeleteDECLARE
TYPE heart IS TABLE OF varchar2(30) index by PLS_INTEGER;
myheart heart;
begin
SELECT table_name as thing
BULK COLLECT INTO myHeart
from dba_tables where rownum < 5;
for i in 1..5
LOOP
dbms_output.put_line('Thing '||i||': '||myheart(i));
END LOOP;
END;
Great thanks - reference to undefined index value. Got it!
ReplyDeleteNote however that BULK COLLECT INTO will NOT raise NDF even if no rows are found. Just returns an empty collection.
Here it is, straight from twitter... :)
ReplyDeleteusing dbms_lob.read when hitting the end of the lob (starting at the end of the lob, that is) raises NDF:
SQL> r
1 declare
2 a clob := 'zohar';
3 b varchar2(100);
4 amt binary_integer := 100;
5 pos integer := 6;
6 begin
7 dbms_lob.read(a, amt, pos, b);
8* end;
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_LOB", line 1064
ORA-06512: at line 7
ORA-1403: No Data Found can be found in Oracle Data Guard , although this is rare situation.
ReplyDeleteUnder normal circumstances the ORA-1403 error should not be seen in a Logical Standby environment. The error occurs when data in a SQL Apply managed table is modified directly on the standby database, and then the same data is modified on the primary database.
When the modified data is updated on the primary database and received by the SQL Apply engine, the SQL Apply engine verifies the original version of the data is present on the standby database before updating the record. When this verification fails, an ORA-1403: No Data Found error is thrown by Oracle Data Guard: SQL Apply.
Some more info wrt to my previous comment http://docs.oracle.com/cd/B28359_01/server.111/b28294/troubleshooting.htm#CHDFEDBG
ReplyDeleteA.10.4 Troubleshooting ORA-1403 Errors with Flashback Transactions
If SQL Apply returns the ORA-1403: No Data Found error, then it may be possible to use Flashback Transaction to reconstruct the missing data. This is reliant upon the UNDO_RETENTION initialization parameter specified on the standby database instance.
Under normal circumstances, the ORA-1403 error should not be seen in a logical standby database environment. The error occurs when data in a table that is being managed by SQL Apply is modified directly on the standby database and then the same data is modified on the primary database. When the modified data is updated on the primary database and is subsequently received on the logical standby database, SQL Apply verifies the original version of the data is present on the standby database before updating the record. When this verification fails, the ORA-1403: No Data Found error is returned.
The Initial Error
When SQL Apply verification fails, the error message is reported in the alert log of the logical standby database and a record is inserted in the DBA_LOGSTDBY_EVENTS view.The information in the alert log is truncated, while the error is reported in it's entirety in the database view. For example:
LOGSTDBY stmt: UPDATE "SCOTT"."MASTER"
SET
"NAME" = 'john'
WHERE
"PK" = 1 and
"NAME" = 'andrew' and
ROWID = 'AAAAAAAAEAAAAAPAAA'
LOGSTDBY status: ORA-01403: no data found
LOGSTDBY PID 1006, oracle@staco03 (P004)
LOGSTDBY XID 0x0006.00e.00000417, Thread 1, RBA 0x02dd.00002221.10
The Investigation
The first step is to analyze the historical data of the table that caused the error. This can be achieved using the VERSIONS clause of the SELECT statement. For example, you can issue the following query on the primary database:
declare
ReplyDeletetext varchar2(20000);
compressed raw(20000);
handle binary_integer;
uncompressed blob;
begin
text := 'utl_compress.lz_uncompress_extract can raise no_data_found';
compressed := utl_compress.lz_compress(utl_raw.cast_to_raw(text) ,1);
handle := utl_compress.lz_uncompress_open(compressed);
utl_compress.lz_uncompress_extract(handle, uncompressed);
utl_compress.lz_uncompress_extract(handle, uncompressed);
utl_compress.lz_uncompress_close(handle);
end;
/
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 161
ORA-06512: at "SYS.UTL_COMPRESS", line 146
ORA-06512: at line 11
It can be in PL/SQL Collection. Here is an example.
ReplyDeleteSQL> DECLARE
2 TYPE TT_DATA IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
3 T_DATA TT_DATA;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(T_DATA(1));
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5
Hello Steven,
ReplyDeleteAnother situation in which NO_DATA_FOUND is raised was added in Oracle 12c,
by enhancing the package DBMS_SQL for returning query results implicitly.
For example:
CREATE PROCEDURE plch_results AS
l_rc SYS_REFCURSOR;
BEGIN
OPEN l_rc FOR SELECT dname FROM scott.dept;
DBMS_SQL.RETURN_RESULT(l_rc);
OPEN l_rc FOR SELECT ename FROM scott.emp;
DBMS_SQL.RETURN_RESULT(l_rc);
END;
/
DECLARE
l_cursor INTEGER;
l_rc SYS_REFCURSOR;
l_names DBMS_SQL.varchar2_table;
BEGIN
l_cursor := DBMS_SQL.OPEN_CURSOR(treat_as_client_for_results => TRUE);
DBMS_SQL.PARSE( c => l_cursor,
statement => 'BEGIN plch_results; END;'
language_flag => DBMS_SQL.NATIVE );
DBMS_SQL.EXECUTE(l_cursor);
LOOP
BEGIN
DBMS_SQL.GET_NEXT_RESULT(l_cursor, l_rc);
EXCEPTIONS
WHEN NO_DATA_FOUND THEN
EXIT;
END;
FETCH l_rc BULK COLLECT INTO l_names ;
FOR i IN 1 .. l_names.COUNT
LOOP
DBMS_OUTPUT.put_line ( l_names(i) );
END LOOP;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(l_cursor);
END;
/
Oracle always adds something new,
So, what else is left for a mere mortal like you ?
Just learn, and remember, play the pl/sql challenge and try !
And then you will better understand everything, how and why :):):)
I think I am still a little bit better at Oracle, than at writing poetry, but who knows ?
The future still might prove the opposite :)
Thanks a lot & Best Regards,
Iudith Mentzel
Excellent, Iudith!
ReplyDeleteExcellent, Atul!
Compatibility "not found" said old APEX.
ReplyDeleteShiny new 5.0 has proper checks.
BEGIN APEX_UTIL.SET_COMPATIBILITY_MODE( 100, '4.1') ; END ;
In APEX 4.2:
ORA-01403: no data found
ORA-06512: at "APEX_040200.HTMLDB_UTIL", line 3690
ORA-06512: at line 1
In APEX 5.0:
ORA-20001: Application ID and current security group ID are not consistent.
Thanks, Kevan. Of course, this and some other instances of NDF noted on this post are not really "different" generators of the error - almost certainly, for example, the APEX_UTIL NDF was caused by a SELECT-INTO that found no row.
ReplyDeleteUsing DBMS_MVIEW.REFRESH with the "tab" argument (an DBMS_UTILITY.UNCL_ARRAY) without a last null entry raises a No Data Found; example here: http://nuijten.blogspot.nl/2015/04/refresh-multiple-materialized-views-in.html
ReplyDeleteA call to FUNCTION inside PL-SQL which returns nothing
ReplyDeleteINSERT/UPDATE/DELETE/MERGE operation on a table, but there is a trigger on table which causes NDF..!! Hidden error induced by Programmer....!!!
Was thinking a call to MERGE Statement dealing with INSERT/UPDATE but data gets changed in the mean time can also throw NDF.
delete from dual;
ReplyDeletethen wait a bit...
a lot of angry developers will appear
Interesting and funny idea. Have you tried it? I am not sure that works anymore. I just connected to sys, deleted from dual - 1 row deleted. Then selected COUNT(*) from dual and got 1. :-)
ReplyDelete