Avoiding TOO_MANY_ROWS errors in PL/SQL

Oracle's PL/SQL language has two basic mechanisms for getting data from the database: SELECT and cursors. SELECT is designed to return a single row into local variables; cursors give you the ability to select multiple rows (i.e., a "rowset") and process them one at a time.

When you use SELECT in a PL/SQL block, it's important to make sure that exactly one row will always be returned by your query. If more than one row is returned, the TOO_MANY_ROWS exception occurs. Listing A shows an example from Oracle's HR sample schema: There is more than one employee with the last name King, so the script fails.
DECLARE

   l_employee_last_name         employees.last_name%TYPE;

   l_employee_full_name         VARCHAR2(52);

BEGIN

   l_employee_last_name := 'King';

   SELECT last_name || ', ' || first_name

   INTO l_employee_full_name

   FROM employees

   WHERE last_name = l_employee_last_name;

   DBMS_OUTPUT.PUT_LINE (l_employee_full_name);

END;

/

There are four ways to make sure your code is safe from this error.

  • Select rows using the primary key. In Listing A, I selected by last_name, and it's quite likely there will be more than one employee with the same last name. If I change the code to select by employee_id instead, I'll avoid the error because only one row in a table can have the same value in the primary key column(s).
  • Select aggregate functions. By definition, the aggregate functions (AVERAGE, COUNT, MIN, MAX, and SUM) return only one row as a result. Even if its WHERE clause has no matching rows, a COUNT of those rows will return one row -- the answer "0." Selecting the MIN or MAX of a table is frequently done in this way to determine processing limits.
  • Limit the query using ROWNUM. If there is a possibility of returning multiple rows, but any row is as good as any other row, try adding the following condition to the query: AND ROWNUM < 2. This will limit the results to the first row of the set. But remember that it's the first physical row returned -- adding ORDER BY to a query with ROWNUM in it will not give you the highest or lowest row, only the first one.
  • Put the SELECT in its own block. If you surround the SELECT with its own inner block, you can code an exception handler to supply a reasonable value if an error occurs. Listing B shows the previous query enhanced in this way.
DECLARE

   l_employee_last_name         employees.last_name%TYPE;

   l_employee_full_name         VARCHAR2(52);

BEGIN

   l_employee_last_name := 'King';

   BEGIN

      SELECT last_name || ', ' || first_name

      INTO l_employee_full_name

      FROM employees

      WHERE last_name = l_employee_last_name;

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         l_employee_full_name := '(not on file)';

      WHEN TOO_MANY_ROWS THEN

         l_employee_full_name := '(duplicate name)';

   END;

   DBMS_OUTPUT.PUT_LINE (l_employee_full_name);

END;

/

Bob Watkins (OCP, MCITP, MCDBA, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. He is a Senior Consultant and Managing Partner at B. Watkins, a database consulting and training firm in the Dallas / Fort Worth area. Visit Bob's site.

------------------------------------------------------------------------------------------

Get Oracle tips in your inbox

TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!