Oracle NO_DATA_FOUND in SQL vs PL/SQL

A PL/SQL function that raises NO_DATA_FOUND can behave differently depending on whether it is called from PL/SQL or SQL.

Called from PL/SQL: NO_DATA_FOUND propagates as ORA-01403: no data found.

Called from SQL: NO_DATA_FOUND from the function is treated as no data, and the function result becomes NULL.

For example, a function containing this statement – can raise ORA-01403 when called from a PL/SQL block:

SELECT name
INTO   v_name
FROM   sample_table
WHERE  id = p_id;
BEGIN
    DBMS_OUTPUT.PUT_LINE(fn_get_user_name(99));
END;
/

But the same function called from SQL can return NULL instead:

SELECT fn_get_user_name(99)
FROM dual;

This difference is important because code that expects NO_DATA_FOUND to propagate from a SQL-called function can silently produce NULL instead.

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.

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.

NO_DATA_FOUND: SQL vs PL/SQL Comparison Table

Handling NO_DATA_FOUND Inside the Function

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;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

Four FAQ (Frequently Asked Questions):

Why is NO_DATA_FOUND not raised when a PL/SQL function is called from SQL?

When NO_DATA_FOUND occurs inside a function invoked from SQL, the SQL context can treat that condition as no data and return NULL rather than exposing ORA-01403 to the caller.

What is the error code for NO_DATA_FOUND?

NO_DATA_FOUND corresponds to ORA-01403, and its PL/SQL SQLCODE value is +100.

Does SELECT INTO raise NO_DATA_FOUND?

Yes. In PL/SQL, a SELECT INTO statement that retrieves no rows raises NO_DATA_FOUND.

How should I handle NO_DATA_FOUND in a PL/SQL function?

Handle NO_DATA_FOUND explicitly in the function when the absence of a row is an expected condition. Depending on the application logic, you might return NULL, return another value, or raise an application-specific exception.

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

Also my other Common SQL/PLSQL Pitfalls – Blog Posts:

Common SQL and PL/SQL Pitfalls: Insights from Real-World Experience

NVL and DECODE: Lazy vs Eager Evaluation (Part 1)

Scalar Subquery Caching Behavior in a SQL Statement (Part 2)

Why NO_DATA_FOUND Behavior Differs in SQL and PL/SQL (Part 3)

Oracle SUM() Returns NULL or No Rows? GROUP BY Explained (Part 4)

Avoid Misusing LEFT JOIN in SQL Queries (Part 5)