Data Management

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!

9 comments
michaelkornbluth
michaelkornbluth

OK, my 2??: 1:Select rows using the primary key Only good answer. If you're getting information from the DB about something, you have to know WHAT something you're asking about. If not the primary key, then some other "record identifier", a set of attributes that uniquely identify the record. That way, multiple rows are impossible, by the data rules. 2: Select aggregate functions OK. You've got an answer. But you picked it arbitrarily. Maybe the OTHER row was the right answer. 3:Limit the query using ROWNUM Same as 2. 4: Put the SELECT in its own block "NO DATA" is an error; How did you get the lastname in the first place? "DUPLICATE DATA" is a data error; why are you asking for a first name if there are more than one possible? Thus, a query that CAN return more than one row points to a design flaw, either in the table design or in the code that contains the SELECT. Fix the design error: don't hide it with patches that prevent the (correct) Oracle error message.

blarman
blarman

Thanks a lot. I have a lot of helper functions that I use that potentially encounter this error. They are business logic that should only find one row and can only return one row and wreak havoc when they don't. The last part about encasing the SELECT INTO statement in a BEGIN/END block makes my code simpler because then I don't have to count the number of rows in the cursor and use an IF statement to do the error handling. Plus, this runs much faster. Thanks a LOT!

abhinav.girotra
abhinav.girotra

Cursor is also an option and that will have less overheads than the implicit cursor which the statement given above is going to have.

michael.roblin
michael.roblin

DECLARE CURSOR c_emp(p_last_name VARCHAR2) IS SELECT last_name || ', ' || first_name FROM employees WHERE last_name = p_last_name; l_employee_full_name VARCHAR2(52); l_employee_check_name VARCHAR2(52); BEGIN OPEN c_emp; FETCH c_emp('King') INTO l_employee_full_name; IF c_emp%NOTFOUND THEN l_employee_full_name := '(not on file)'; ELSE FETCH c_emp INTO l_employee_check_name; IF c_emp%FOUND THEN l_employee_full_name := '(duplicate name)'; END IF; END IF; CLOSE c_emp; END; / This allows you to capture and report duplicates without resorting to exceptions

niels.bertram
niels.bertram

I agree the suggestions sound a bit like patch work to me too. I have been in Oracle DB and Forms development for a decade and rather get my design fixed than my fetches. If there is a chance of TOO_MANY_ROWS then there must be a reason for it. The only time I ever use TOO_MANY_ROWS is on legacy applications and then the exception block contains a severity log call. As for the TOO_MANY_ROWS vs. cursor handling, a third option is also missing -- BULK COLLECT ... Cheers

bwatkins
bwatkins

It's largely a myth that implicit cursors perform slower than explicit ones for small rowsets (n

bwatkins
bwatkins

Hi, Michael. You asked, "Why not use a cursor?" If you are doing dynamic SQL, or expect many rows back that you need to treat differently, then an explicit cursor is the right solution. But for single row retrievals from a static SQL statement, the SELECT...INTO... construct is the right solution. If you compare your code above to the SELECT...INTO..., you'll see that you had to add your own IF statements for boundary checking, which the standard SELECT has built-in. If you were to forget one, you'd be opening your code to risk if the contents of the database changes. Simpler code = better productivity and fewer bugs. I have to comment, though, on the phrase, "without resorting to exceptions." Exceptions are part of the design of the PL/SQL language, borrowed from the Ada language on which it is based. They're efficient and very useful, though I have to admit it took me a long time to get used to how they work.

kwaltenberg
kwaltenberg

Niels, ich habe versucht, Dich ?ber diese Seite anzumailen. Funktioniert aber nicht. Kannst aber mich anmailen, wenn Du magst.