Avoiding TOO_MANY_ROWS Error In Oracle

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. There are four ways to make sure your code is safe from this error...

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 Shabbir, so the script fails.

Code:

DECLARE
 
 l_employee_last_name employees.last_name%TYPE;
 l_employee_full_name VARCHAR2(52);
 
 BEGIN
 
 l_employee_last_name := 'Shabbir';
 
 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.

Code:

DECLARE
 
 l_employee_last_name employees.last_name%TYPE;
 l_employee_full_name VARCHAR2(52);
 
 BEGIN
 
 l_employee_last_name := 'Shabbir';
 
 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;
 /

 

good explanation.