Oracle Error ORA-01403

Error code

ORA-01403

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (same wording)

Category

PL/SQL & Triggers

Last reviewed

24 October 2026

What it means

ORA-01403 means a SELECT INTO in PL/SQL returned no rows, so there was no value to put into the variable; PL/SQL raised the predefined exception NO_DATA_FOUND.

A SELECT ... INTO statement must return exactly one row. Oracle's PL/SQL reference states that when it returns no rows, PL/SQL raises NO_DATA_FOUND immediately, before you can test SQL%NOTFOUND. If no handler catches it, the caller sees ORA-01403, normally with ORA-06512 lines that point to the SELECT INTO. Its opposite, more than one row, is ORA-01422.

NO_DATA_FOUND is the one predefined exception whose SQLCODE is positive (+100) rather than negative. Oracle's error reference describes it simply as "no data was found from the objects", which can also mean the end of a fetch. Ordinary SQL behaves differently: a plain SELECT that matches nothing just returns no rows, and an UPDATE or DELETE that matches nothing changes zero rows without an error.

Common causes

• A SELECT INTO looks up a key that does not exist, such as an id from a URL, a deleted row or a value with different case or trailing spaces.
• The WHERE clause is stricter than intended: an extra condition, a date compared with a time part, or a filter on a status the row does not have yet.
• The row exists but is not visible to the session: it was inserted by another session that has not committed, or a security policy hides it.
• The code assumes optional data is always there, such as a customer's default address or a configuration row.
• The value in the WHERE clause is NULL, for example an unset parameter, and = NULL never matches any row.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT line, text
FROM all_source
WHERE owner = 'SHOP'
  AND name = 'GET_CUSTOMER_EMAIL'
  AND line BETWEEN 3 AND 9
ORDER BY line;

SELECT COUNT(*) AS matches
FROM shop.customers
WHERE id = 4711;   -- the value the code was given

SELECT id, email, LENGTH(email) AS len
FROM shop.customers
WHERE UPPER(TRIM(email)) = UPPER(TRIM('Sara@Example.com'));

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

CREATE OR REPLACE FUNCTION shop.get_customer_email (p_id IN shop.customers.id%TYPE)
  RETURN shop.customers.email%TYPE
IS
  v_email shop.customers.email%TYPE;
BEGIN
  SELECT email INTO v_email FROM shop.customers WHERE id = p_id;
  RETURN v_email;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

CREATE OR REPLACE PROCEDURE shop.apply_discount (p_customer_id IN NUMBER)
IS
  v_rate NUMBER;
BEGIN
  SELECT MAX(rate) INTO v_rate
  FROM shop.customer_discounts
  WHERE customer_id = p_customer_id;

  IF v_rate IS NOT NULL THEN
    UPDATE shop.orders SET discount_rate = v_rate
    WHERE customer_id = p_customer_id AND status = 'OPEN';
  END IF;
END;
/

CREATE OR REPLACE PROCEDURE shop.remind_open_orders (p_customer_id IN NUMBER)
IS
BEGIN
  FOR r IN (SELECT id FROM shop.orders
            WHERE customer_id = p_customer_id AND status = 'OPEN') LOOP
    shop.send_reminder(r.id);
  END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE shop.ship_order (p_order_id IN NUMBER)
IS
  v_status shop.orders.status%TYPE;
BEGIN
  BEGIN
    SELECT status INTO v_status FROM shop.orders WHERE id = p_order_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20010, 'Order ' || p_order_id || ' does not exist');
  END;
  -- continue with v_status
END;
/

Examples

Looking up a customer id that does not exist

Not reproduced here: no Oracle Database is installed for this site. The message text is from Oracle's error reference, and the ORA-06512 line follows the layout of Oracle's PL/SQL examples; the table and the line number are illustrative.

DECLARE
  v_email customers.email%TYPE;
BEGIN
  SELECT email INTO v_email FROM customers WHERE id = 4711;
END;
/

ORA-01403: no data found
ORA-06512: at line 4

A trailing space in the key

Not reproduced here: no Oracle Database is installed for this site. The result shows the documented message; the names and line number are illustrative. With a VARCHAR2 column the comparison is exact, so the trailing space means no row matches.

DECLARE
  v_id customers.id%TYPE;
BEGIN
  -- the stored value is 'sara@example.com', the input has a trailing space
  SELECT id INTO v_id FROM customers WHERE email = 'sara@example.com ';
END;
/

ORA-01403: no data found
ORA-06512: at line 5

How to prevent it

Decide for each SELECT INTO what should happen when there is no row, and write that down in code: a local handler, an aggregate, a cursor loop or a clear application error. Validate and normalise keys (trim, case) at the edge of the application.

Use SELECT INTO only for queries on a primary or unique key, or with an aggregate, so "exactly one row" is guaranteed or deliberately handled.

Common questions

Learn more on CodeWithSQL

• Exception handling in SQL
• Stored procedures and functions

Official documentation

• ORA-01403 (Oracle Database Error Messages)
• SELECT INTO statement (Oracle PL/SQL Language Reference 19c)
• Cursors overview, implicit cursor attributes (Oracle PL/SQL Language Reference 19c)
• Predefined exceptions (Oracle PL/SQL Language Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.