Oracle Error ORA-01422

Error code

ORA-01422

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

PL/SQL & Triggers

Last reviewed

24 October 2026

What it means

ORA-01422 means a SELECT INTO (or a DML statement with RETURNING INTO) in PL/SQL returned more than one row, but its target variables can hold only one; PL/SQL raised the predefined exception TOO_MANY_ROWS.

A SELECT ... INTO into scalar variables or a record must return exactly one row. Oracle's current reference lists three causes: a SELECT in exact fetch mode returned more rows than requested (seen from client interfaces), a PL/SQL SELECT INTO returned more than one row, and a PL/SQL UPDATE, DELETE or INSERT ... RETURNING INTO returned more than one row. In PL/SQL this is the predefined exception TOO_MANY_ROWS.

The opposite case, no rows, is ORA-01403 no data found. The current reference's template ends with a placeholder for the number of rows requested; Oracle's documentation does not show how that number is printed, so this page leaves it out. Oracle Database 19c prints the shorter text "exact fetch returns more than requested number of rows".

Common causes

• The WHERE clause is not on a unique key: a customer can have several addresses, orders or emails, and the code assumed one.
• Duplicate rows exist where the design assumed uniqueness but no unique constraint enforces it.
• A join multiplies rows, for example joining to a child table, so one parent row becomes several.
• An UPDATE or DELETE with RETURNING INTO matches several rows, because its WHERE clause is wider than intended.
• A filter that used to narrow the result to one row, such as an "active" flag or a date range, now matches more than one.

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 COUNT(*) AS matches
FROM shop.addresses
WHERE customer_id = 4711
  AND is_active = 'Y';

SELECT customer_id, COUNT(*) AS copies
FROM shop.addresses
WHERE is_active = 'Y'
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY copies DESC;

SELECT c.constraint_name, c.constraint_type, cc.column_name, cc.position
FROM all_constraints c
JOIN all_cons_columns cc
  ON cc.owner = c.owner AND cc.constraint_name = c.constraint_name
WHERE c.owner = 'SHOP'
  AND c.table_name = 'ADDRESSES'
  AND c.constraint_type IN ('P', 'U')
ORDER BY c.constraint_name, cc.position;

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_active_address (p_customer_id IN NUMBER)
  RETURN shop.addresses.id%TYPE
IS
  v_id shop.addresses.id%TYPE;
BEGIN
  SELECT id INTO v_id
  FROM shop.addresses
  WHERE customer_id = p_customer_id AND is_active = 'Y'
  ORDER BY created_at DESC
  FETCH FIRST 1 ROW ONLY;   -- the newest active address wins
  RETURN v_id;
END;
/

CREATE OR REPLACE PROCEDURE shop.verify_active_addresses (p_customer_id IN NUMBER)
IS
  TYPE id_list IS TABLE OF shop.addresses.id%TYPE;
  v_ids id_list;
BEGIN
  SELECT id BULK COLLECT INTO v_ids
  FROM shop.addresses
  WHERE customer_id = p_customer_id AND is_active = 'Y';

  FOR i IN 1 .. v_ids.COUNT LOOP
    shop.verify_address(v_ids(i));
  END LOOP;
END;
/

CREATE UNIQUE INDEX shop.addresses_one_active_ux
  ON shop.addresses (CASE WHEN is_active = 'Y' THEN customer_id END);

CREATE OR REPLACE FUNCTION shop.get_only_active_address (p_customer_id IN NUMBER)
  RETURN shop.addresses.id%TYPE
IS
  v_id shop.addresses.id%TYPE;
BEGIN
  SELECT id INTO v_id FROM shop.addresses
  WHERE customer_id = p_customer_id AND is_active = 'Y';
  RETURN v_id;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    RAISE_APPLICATION_ERROR(-20020,
      'Customer ' || p_customer_id || ' has more than one active address');
END;
/

Examples

A customer with two active addresses

Not reproduced here: no Oracle Database is installed for this site. The message is the current wording from Oracle's error reference, without the row count its template ends with; Oracle Database 19c prints "exact fetch returns more than requested number of rows". Names and the line number are illustrative.

DECLARE
  v_id addresses.id%TYPE;
BEGIN
  SELECT id INTO v_id
  FROM addresses
  WHERE customer_id = 4711 AND is_active = 'Y';   -- two rows match
END;
/

ORA-01422: exact fetch returned more than the requested number of rows
ORA-06512: at line 4

RETURNING INTO from an UPDATE that matches several rows

Not reproduced here: no Oracle Database is installed for this site. Oracle's current reference names DML RETURNING INTO as one of the causes; the message format is from that reference and the names are illustrative. Use RETURNING BULK COLLECT INTO a collection when several rows are expected.

DECLARE
  v_total orders.total%TYPE;
BEGIN
  UPDATE orders SET total = total * 0.9
  WHERE customer_id = 4711
  RETURNING total INTO v_total;   -- the customer has three orders
END;
/

ORA-01422: exact fetch returned more than the requested number of rows
ORA-06512: at line 4

How to prevent it

Use SELECT INTO only on a primary or unique key, or with a rule that picks exactly one row (an aggregate, or ORDER BY with FETCH FIRST 1 ROW ONLY). Back every "only one" assumption with a unique constraint or index.

When a query can legitimately return several rows, write it as a cursor loop or BULK COLLECT from the start.

Common questions

Learn more on CodeWithSQL

• Stored procedures and functions
• Exception handling in SQL

Official documentation

• ORA-01422 (Oracle Database Error Messages)
• SELECT INTO statement (Oracle PL/SQL Language Reference 19c)
• Bulk SQL and bulk binding (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.