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: SQL
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: SQL
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;
/
|
