Why NO_DATA_FOUND Behavior Differs in SQL and PL/SQL

INTRO:

Many Oracle developers including myself for years, assume that when a PL/SQL function contains a SELECT INTO statement that returns no rows, it will raise a NO_DATA_FOUND exception. While this is true in a PL/SQL context, the behavior is different when the same function is called from SQL: the exception is silently suppressed, and the function simply returns NULL. This subtle difference can introduce unexpected logic bugs if you’re not aware of it. In this post, we’ll dive into this behavior, explain why it happens, and how to handle it properly.

NO_DATA_FOUND actually is not an error – it’s an exceptional condition. In SQL, when no rows match, in PL/SQL however, the same NO_DATA_FOUND signal is treated differently. Since PL/SQL expects a row to be returned in a SELECT INTO, the absence of data raises an exception. If not explicitly handled, it results in an error message and program interruption

So technically, both SQL and PL/SQL raise the same signal. But the client environment (SQL engine vs. PL/SQL runtime) interprets it differently. SQL assumes it’s normal. PL/SQL assumes it’s a problem unless the programmer says otherwise.

This behavior also explained by Tom Kyte in ASKTOM – NO_DATA_FOUND in Functions

A Simple Demonstration

Let’s create a table and a PL/SQL function.

SQL> CREATE TABLE sample_table (
  id    NUMBER PRIMARY KEY,
  name  VARCHAR2(100)
);

-- Insert one row
SQL> INSERT INTO sample_table VALUES (1, 'InsaneDBA');
COMMIT;

SQL> CREATE OR REPLACE FUNCTION fn_get_user_name(p_id NUMBER)
RETURN VARCHAR2 IS
  v_name VARCHAR2(100);
BEGIN
  SELECT name INTO v_name
  FROM sample_table
  WHERE id = p_id;
  RETURN v_name;
END;
/

Testing from PL/SQL (Exception Raised) :

SQL> BEGIN
      DBMS_OUTPUT.PUT_LINE(fn_get_user_name(99));
    END;
   /
BEGIN
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYSTEM.FN_GET_USER_NAME", line 5
ORA-06512: at line 2

https://docs.oracle.com/error-help/db/ora-01403/


More Details :
https://docs.oracle.com/error-help/db/ora-01403/
https://docs.oracle.com/error-help/db/ora-06512/

Testing from SQL (No Exception Raised) : Null Value Returned

SQL> select fn_get_user_name(99) as user_99, fn_get_user_name(1) as user_1 from dual;

USER_99    USER_1
__________ ____________
           InsaneDBA

Oracle SQL has no mechanism for propagating PL/SQL exceptions like NO_DATA_FOUND and back to the SQL layer. Instead, the function execution returns NULL. This is by design.

Here is the table of PL/SQL Predefined Exceptions from Database PL/SQL Language Reference Release 19:

The only positive error code is for NO_DATA_FOUND.

Exception Name	Error Code
ACCESS_INTO_NULL	-6530
CASE_NOT_FOUND	-6592
COLLECTION_IS_NULL	-6531
CURSOR_ALREADY_OPEN	-6511
DUP_VAL_ON_INDEX	-1
INVALID_CURSOR	-1001
INVALID_NUMBER	-1722
LOGIN_DENIED	-1017
NO_DATA_FOUND	+100
NO_DATA_NEEDED	-6548
NOT_LOGGED_ON	-1012
PROGRAM_ERROR	-6501
ROWTYPE_MISMATCH	-6504
SELF_IS_NULL	-30625
STORAGE_ERROR	-6500
SUBSCRIPT_BEYOND_COUNT	-6533
SUBSCRIPT_OUTSIDE_LIMIT	-6532
SYS_INVALID_ROWID	-1410
TIMEOUT_ON_RESOURCE	-51
TOO_MANY_ROWS	-1422
VALUE_ERROR	-6502
ZERO_DIVIDE	-1476

Oracle’s +100 return code for “no data found” aligns with the behavior described in the ANSI SQL/92 (ISO/IEC 9075-2:1992 Part 2: Embedded SQL) standard for embedded SQL. Although SQLCODE itself is not officially part of the ANSI standard, starting with SQL-92, SQLSTATE became mandatory, and SQLCODE was effectively deprecated in the context of standardized SQL. However, the +100 convention for “no data” is standardized and has been widely adopted by multiple database systems such as DB2, Informix, and others that implement embedded SQL in languages like C or COBOL.

Conclusion:

The difference in behavior between SQL and PL/SQL when handling NO_DATA_FOUND in functions is subtle but critical. If you assume the exception will propagate in all contexts, you risk introducing silent logic errors into your applications. Understanding this behavior can save you hours of debugging and help you write more robust, predictable code.

Hope it helps. See you on the next post – Part 4 : Aggregate Function Behaviors with No Matching Rows: GROUP BY vs. No GROUP BY