PL/SQL SELECT INTO: missing rows, duplicate rows and clear failure paths

A customer lookup must distinguish three outcomes: exactly one customer, no matching customer, or an ambiguous match. These are different business situations. Returning an arbitrary row makes the program appear successful while hiding a data problem.

Scenario

Illustrative lab: a support application looks up a customer by an external reference. The reference is expected to identify one customer, but a staging feed has introduced a duplicate.

Worked example

Oracle Database 19c-compatible PL/SQL. Reviewed against official exception documentation; not executed on an Oracle instance in this release.

SET SERVEROUTPUT ON;
DECLARE
  v_name VARCHAR2(30);
BEGIN
  SELECT name INTO v_name
  FROM (SELECT 1 id, 'Anu' name FROM dual
        UNION ALL SELECT 1, 'Bala' FROM dual)
  WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No matching customer');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Ambiguous customer reference');
END;
/

Expected output

Ambiguous customer reference

Walk through the code

1. Enable output in a SQL*Plus-compatible client so the demonstration message is visible. Other clients may provide their own output panel.
2. Declare a variable for the single expected name. The inline dataset deliberately contains two rows with id 1.
3. SELECT INTO requests one scalar result. Two matching rows trigger TOO_MANY_ROWS before the success message.
4. The specific handlers give the two failure cases different messages. This teaching block prints them; a real API should return a defined outcome or raise an application error under its contract.

Reason about the design

Decide cardinality in the data model before deciding how to handle exceptions. If a customer reference is unique by definition, validate and enforce that rule at the appropriate boundary. A count followed by a separate lookup is not an atomic guarantee that the second statement sees the same data. A lookup that legitimately returns multiple customers needs a collection or cursor interface instead of a scalar SELECT INTO.

Common mistakes

• Adding ROWNUM = 1 only to hide duplicate data. If selecting one of many is a real requirement, define an ordering and tie-break rule.
• Using WHEN OTHERS THEN NULL and reporting success. Unexpected failures should remain visible to the caller and operational logs.
• Logging full customer details unnecessarily. Prefer a safe correlation identifier and a non-sensitive error category.

Troubleshooting checklist

• Run the selection predicate as a read-only query and inspect how many rows it produces.
• Check whether joins multiply rows before blaming the base customer table.
• Review constraints and the agreed external-reference contract. Correct existing duplicates before adding a constraint.

Try it yourself

Change the predicate to id = 9 and expect No matching customer. Then remove the second row and expect Anu. Explain why each result follows from the number of matching rows.

Interview follow-up

Why is selecting COUNT(*) before SELECT INTO not a reliable concurrency solution?

Questions learners ask

Should every missing row be an exception?

Not necessarily. The database raises NO_DATA_FOUND for scalar SELECT INTO, but the application may translate an expected absence into a documented not-found result.

Does this block commit changes?

No. It only queries an inline dataset and prints a message.

Primary reference

Official documentation for this topic. Check the documentation for your deployed product version before applying the example.