Friday, April 17, 2015

PL/SQL Brain Teaser: Raising NO_DATA_FOUND - let me count the ways

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.


I had a file, and its content was Divine.
Read past the end, line by line.

Most times Oracle does the trick,
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.

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.

24 comments:

  1. I queried a row, but I was not able,
    Then I looked for the wrong entry in a plsql table

    ReplyDelete
  2. No fair, Connor. One stanza, two instigators. But I was not clear on the rules, so nice work! Here's the explanation:

    1. 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;

    ReplyDelete
  3. no rhyming (sorry):

    SQL> 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

    ReplyDelete
  4. I had a file, and its content was Divine.
    Read to the end, line by line.

    ReplyDelete
  5. Thanks, JBB. But shouldn't it be:

    "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;
    /

    ReplyDelete
  6. @Zohar, how about this for a rhyming version:

    Most 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.

    ReplyDelete
  7. Thank you Steven, "read past" is correct, whilst "to the end" was not.
    Well that's four different ways to have a NO_DATA_FOUND. I'll keep scratching my head thinking of other ways.

    ReplyDelete
  8. (Kind of a dupe - For i in 1..5 loop for a list of 4 items):

    Four dreams in my heart I opened to thee,
    Reached though for a fifth and were lost unto me.

    ReplyDelete
  9. @will, can you post a code example so I can see exactly what you mean?

    ReplyDelete
  10. Sure (contrived and forced as it is) - worst practices included for free!

    DECLARE

    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;

    ReplyDelete
  11. Great thanks - reference to undefined index value. Got it!

    Note however that BULK COLLECT INTO will NOT raise NDF even if no rows are found. Just returns an empty collection.

    ReplyDelete
  12. Here it is, straight from twitter... :)
    using 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

    ReplyDelete
  13. ORA-1403: No Data Found can be found in Oracle Data Guard , although this is rare situation.

    Under 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.

    ReplyDelete
  14. Some more info wrt to my previous comment http://docs.oracle.com/cd/B28359_01/server.111/b28294/troubleshooting.htm#CHDFEDBG

    A.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:

    ReplyDelete
  15. declare
    text 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

    ReplyDelete
  16. It can be in PL/SQL Collection. Here is an example.

    SQL> 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

    ReplyDelete
  17. Hello Steven,

    Another 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

    ReplyDelete
  18. Compatibility "not found" said old APEX.
    Shiny 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.

    ReplyDelete
  19. 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.

    ReplyDelete
  20. Using 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

    ReplyDelete
  21. A call to FUNCTION inside PL-SQL which returns nothing

    INSERT/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.

    ReplyDelete
  22. delete from dual;

    then wait a bit...
    a lot of angry developers will appear

    ReplyDelete
  23. 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