ORA-01422: Exact Fetch Returns More Than One Row

ORA-01422 means Oracle returned more rows than an exact fetch expected, usually because a PL/SQL SELECT ... INTO statement returned multiple rows instead of one. The fix is to make the query return the intended single row or change the code to process multiple rows. Start by locating the failing statement: when the error appears during an import, DROP TABLE, or an application job, that statement may be inside a trigger or called package.

Use the failure context to narrow the investigation.

Where the Error Appears Likely Cause Appropriate Fix
A lookup expected to identify one record Incomplete filtering or duplicate business keys Correct the predicate and enforce the intended uniqueness
A query joining several tables A one-to-many relationship multiplies the result Correct the join or process the matching rows
A query intended to produce a list Ordinary SELECT ... INTO is receiving multiple rows Use a cursor loop or collection-based processing
An import or DDL command A schema or database trigger performs a failing exact fetch Find and repair the trigger’s lookup
An EBS or HCM job Application code or a configuration lookup encounters unexpected matches Trace the failing routine and apply a supported correction

What ORA-01422 Means

An ordinary PL/SQL SELECT ... INTO retrieves one result row into variables or a record. Its behavior depends on how many rows the query produces:

  • No rows: Oracle raises NO_DATA_FOUND, associated with ORA-01403.
  • One row: Oracle assigns the selected values to the targets.
  • Multiple rows: Oracle raises TOO_MANY_ROWS, associated with ORA-01422.

The number of variables controls how many columns you receive, not how many rows. Selecting four columns into four variables still requires a single result row.

The rows do not have to contain different values. Two matching rows with the same selected name can still cause the exception.

A query that runs successfully in a SQL worksheet can therefore fail inside PL/SQL. The worksheet displays a result set; an ordinary exact fetch requires that result set to contain one row. Adding ORDER BY changes the order, not the row count.

After TOO_MANY_ROWS, do not rely on the contents of the INTO targets. They are undefined for that failed fetch.

How to Fix ORA-01422 in PL/SQL

Fix ORA-01422 by locating the failing exact fetch, inspecting its matching rows, and making the query match the intended result.

Step 1: Locate the Exact Fetch That Failed

  1. Capture the complete error stack, including every ORA-06512 entry.
  2. Identify the named procedure, package body, function, or trigger and its reported line.
  3. Inspect that line and any routines it calls for an ordinary SELECT ... INTO.

ORA-06512 identifies a PL/SQL location in the error stack; it is not a separate root cause.

If the source is accessible through the data dictionary, inspect it with:

SELECT line, text
FROM all_source
WHERE owner = :owner
  AND name = :object_name
  AND type = :object_type
ORDER BY line;

Supply the owner, object name, and object type exactly as stored. Common type values include PACKAGE BODY, PROCEDURE, FUNCTION, and TRIGGER. Unquoted object names are normally stored in uppercase.

The colon-prefixed values in this article are bind parameters supplied by your application or SQL client.

If an exception handler hides the original location, record DBMS_UTILITY.FORMAT_ERROR_BACKTRACE through the application’s logger before re-raising the exception. Console output alone may not be visible in a background job.

Step 2: Inspect the Query’s Actual Matching Rows

  1. Run the failing query as a read-only SELECT, removing the PL/SQL INTO clause.
  2. Preserve its joins, predicates, and actual bind values.
  3. Include identifying columns so you can distinguish separate records.

For example, a lookup by department may unexpectedly match several employees:

SELECT e.employee_id,
       e.department_id,
       e.last_name
FROM employees e
WHERE e.department_id = :department_id
ORDER BY e.employee_id;

These examples assume an illustrative employees table with the displayed columns. Substitute your application’s tables and columns.

Look for the reason the additional rows qualify:

  • A missing business-key component. A lookup may need both a customer identifier and an organization identifier.
  • An incomplete join. Joining an order to its lines produces a row per matching line, not necessarily a row per order.
  • Duplicate data. A value treated as unique by application code may have no corresponding database constraint.
  • Parameter shadowing. A predicate such as employee_id = employee_id can compare a column with itself rather than with the intended parameter. Qualify columns with table aliases and use distinct parameter names, such as p_employee_id.

Reproduce the relevant execution context as well. A query run under another user, application context, or session can return different rows. A separate diagnostic COUNT(*) does not guarantee that a later fetch will see the same data under concurrent activity.

Step 3: Match the Fetch to the Intended Result

The right fix is to make the query’s row count match the caller’s actual requirement, not merely to suppress the exception.

Choose the behavior the application actually needs.

When exactly one specific record should exist

Filter by the complete identifying key. If employee_id is a primary key, a lookup by employee_id cannot match multiple employees:

DECLARE
  l_last_name employees.last_name%TYPE;
BEGIN
  SELECT e.last_name
  INTO l_last_name
  FROM employees e
  WHERE e.employee_id = :employee_id;

  DBMS_OUTPUT.PUT_LINE(l_last_name);
END;
/

Run it with the intended employee identifier and verify that it returns the correct person. Decide explicitly whether a missing employee should remain an error or receive application-specific handling.

If the business key is duplicated, repair the confirmed data inconsistency and establish an appropriate unique constraint. Do not delete records merely because their displayed values look alike; check relationships and ownership first.

When the requirement is to choose one ranked result

Limit the query only when selecting one result is the intended behavior, for example, choosing the most recently hired employee in a department.

On Oracle Database 12c and later:

DECLARE
  l_last_name employees.last_name%TYPE;
BEGIN
  SELECT e.last_name
  INTO l_last_name
  FROM employees e
  WHERE e.department_id = :department_id
  ORDER BY e.hire_date DESC NULLS LAST,
           e.employee_id DESC
  FETCH FIRST 1 ROW ONLY;

  DBMS_OUTPUT.PUT_LINE(l_last_name);
END;
/

The unique employee identifier breaks ties between equal hire dates. Without a complete ordering rule, the selected row may not be predictable.

For older Oracle versions, order inside an inline view and apply the row limit outside it:

DECLARE
  l_last_name employees.last_name%TYPE;
BEGIN
  SELECT ranked.last_name
  INTO l_last_name
  FROM (
    SELECT e.last_name
    FROM employees e
    WHERE e.department_id = :department_id
    ORDER BY e.hire_date DESC NULLS LAST,
             e.employee_id DESC
  ) ranked
  WHERE ROWNUM = 1;

  DBMS_OUTPUT.PUT_LINE(l_last_name);
END;
/

Putting the ROWNUM condition and ORDER BY at the same query level can select a row before sorting.

Test the selected result against the business rule, including tied dates and null dates. Neither version creates a result when no employee matches; the ordinary fetch can still raise NO_DATA_FOUND.

When every matching row should be processed

Replace the ordinary exact fetch with a cursor FOR loop:

BEGIN
  FOR employee_rec IN (
    SELECT e.employee_id, e.last_name
    FROM employees e
    WHERE e.department_id = :department_id
    ORDER BY e.employee_id
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(
      employee_rec.employee_id || ': ' || employee_rec.last_name
    );
  END LOOP;
END;
/

The loop opens, fetches, and closes its cursor automatically. It processes every matching row and runs zero iterations when nothing matches.

Verify that each expected employee appears once. Replace the output statement with the intended per-row operation only after confirming the result set. If that operation changes data, define its transaction and failure behavior deliberately.

When the result should be an aggregate

Use an aggregate only when the application wants a count, total, minimum, or maximum. An ungrouped COUNT(*), for example, produces a result even when no underlying rows match.

Do not add MAX() merely to conceal duplicate records. Also remember that GROUP BY can produce multiple aggregate rows, which can still violate an ordinary exact fetch.

Step 4: Compile and Rerun the Original Caller

  1. Apply the chosen change to the containing routine or trigger.
  2. Compile it and resolve any compilation errors.
  3. Rerun the original failing operation with the original inputs.
  4. Verify the returned value, processed records, or generated output, not just the absence of an exception.

For code that performs DML, test the change outside production first and confirm its commit and rollback behavior. Catching an exception is not, by itself, a transaction policy.

If the corrected query still raises ORA-01422, capture the new backtrace. Another exact fetch farther along the same execution path may now be failing.

ORA-01422 During Import or DROP TABLE

A DDL command can fire schema-level or database-level triggers. Those triggers can call PL/SQL containing an exact fetch, so the error may have little to do with the table being imported or dropped.

Inspect Schema and Database DDL Triggers

Use the complete stack to identify the trigger and any package it calls. Inspect enabled DDL triggers through your database administration tools or the relevant trigger dictionary views.

A schema DDL audit trigger can fail during import when this exact session lookup matches multiple rows, as in the Nazmul Huda import example:

select PROGRAM into l_programname from v$session where audsid=sys_context('USERENV','SESSIONID');

The import cannot complete successfully through that trigger path until the lookup is made single-row or redesigned to handle its actual result.

To inspect the matching sessions, capture the failing session’s audit-session identifier and use it as the bind value:

SELECT sid,
       serial#,
       audsid,
       username,
       type,
       program
FROM v$session
WHERE audsid = :failing_audsid;

Running SYS_CONTEXT from a separate SQL client returns that client’s context, not the importer’s. A diagnostic query executed in another session can therefore miss the original condition.

Make Session Lookups Unambiguous

Keep the session identifiers distinct:

  • SID identifies a session within an instance but can be reused after that session ends.
  • SERIAL# helps distinguish successive sessions using the same SID.
  • AUDSID is the audit-session identifier compared by the failing query.

For a previously identified session, use SID+SERIAL# rather than SID alone. When identifying sessions across RAC instances through GV$SESSION, include INST_ID as well.

The “SID 0” shorthand needs care here: the failing predicate compares AUDSID, and background sessions can share AUDSID = 0. Do not treat a zero-valued audit-session identifier as identifying one session.

If the trigger needs the program for its own current session, a local-instance lookup can use the current session’s actual SID. Inside a trigger that already declares l_programname, the replacement statement is:

SELECT s.program
INTO l_programname
FROM v$session s
WHERE s.sid = TO_NUMBER(SYS_CONTEXT('USERENV', 'SID'));

This targets the currently executing session within the local instance. It is different from tracking an externally recorded session identifier, where the serial number matters.

Excluding system usernames or background processes does not establish that the remaining row is the intended session.

After changing the trigger, compile it, check that its required privileges remain valid, and test both the original DDL operation and the audit record it should produce. Do not disable auditing triggers as a default workaround; coordinate any approved trigger change with the database owner.

ORA-01422 in E-Business Suite and HCM Extracts

Packaged applications need a different repair boundary: investigate the failing lookup, but do not patch application-owned code or delete configuration rows based only on the error text.

EBS_APPLPTMP Lookup Failures

An “error in retrieving value for ebs_applptmp due to ORA-01422” indicates that the retrieval path encountered an exact-fetch mismatch. It does not, by itself, prove that the temporary directory is missing or has incorrect permissions.

Collect the full job log, package backtrace, application release, and affected execution context. Have the application administrator identify the lookup and inspect whether its predicates or configuration data produce unexpected matches.

Use supported configuration tools or an approved repair for the confirmed cause. If Oracle-maintained code is involved, provide the diagnostic evidence to Oracle Support rather than altering it directly. After the correction, rerun the failed operation and verify its temporary-file and final-output behavior.

ReportPublisher.setDeliveryOpt Failures

An error naming ReportPublisher.setDeliveryOpt requires checking the report-delivery phase as well as the extract itself. Processed extract records do not prove that delivery completed successfully.

Inspect the flow log, generated output, delivery options, and delivery status. The message alone does not identify a duplicated option or establish which patch applies.

Before rerunning a flow, determine whether output was already delivered to avoid duplicate delivery. Apply the supported configuration correction or product fix, then verify the affected delivery operation and its destination.

Verify the Fix and Prevent Recurrence

A successful run with the original input is necessary, but it is not enough to establish that the lookup is reliable.

Test these conditions before promoting the change:

  • No matching records: The application handles absence intentionally.
  • One matching record: The correct values are returned.
  • Multiple qualifying records: The code either processes them, selects the intended ranked result, or rejects a genuine integrity violation.
  • Ranking ties: The tie-breaker produces a predictable result.
  • Joined data: Additional child records do not unexpectedly multiply a single-record lookup.
  • The original execution context: Jobs and triggers work under their actual users, privileges, and session context.

Prevent recurrence with database constraints that reflect genuine business uniqueness. Do not assume that today’s data proves a query will always be single-row.

Keep object names, backtraces, and relevant bind values in diagnostic logging, while excluding sensitive data. Your final check should be the original application operation and its expected output or audit effect, not merely an isolated query that no longer raises an exception.

Frequently Asked Questions

Is ORA-01422 the Same as ORA-01427?

No. ORA-01422 concerns an exact fetch receiving too many rows, commonly in PL/SQL SELECT ... INTO. ORA-01427 means a single-row subquery returned multiple rows within a SQL statement; both involve cardinality mismatches, but the failing constructs differ.

Can DISTINCT Fix ORA-01422?

DISTINCT helps only when eliminating duplicate selected values is part of the intended query semantics and leaves one result row. It does not guarantee a single row, and it can hide multiple underlying records that should have been distinguished. Correct the key or join when those records represent different entities.

Should I Catch TOO_MANY_ROWS and Continue?

Catch TOO_MANY_ROWS only when the application has a defined response, such as logging and reporting an ambiguous lookup. Do not silently continue with the target variables or use an empty handler to make the error disappear. If multiple records are legitimate, change the fetch to process them rather than treating them as an exception.