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!