Avoiding TOO_MANY_ROWS Error In Oracle

Discussion in 'Oracle' started by pradeep, Nov 30, 2007.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    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;
     /
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,336
    Likes Received:
    377
    Trophy Points:
    83
  3. lead.smart34

    lead.smart34 New Member

    Joined:
    Feb 14, 2008
    Messages:
    77
    Likes Received:
    0
    Trophy Points:
    0
    good explanation.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice