1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    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,285
    Likes Received:
    364
    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