oracle PL/SQL stored function ORA-01422 and ORA-06512

Question

I want to write a PL/SQL stored function that takes a driver's employee number as a parameter and returns the driver's full name, cities he visited and how many times he visited the city as a nested table.

I wrote the function and it was compiled successfully. Here is the code for the nested table:

    CREATE OR REPLACE TYPE C_V 
AS OBJECT
(   FULLNAME VARCHAR(150),
    CITIES_VISITED VARCHAR(30),
    TOT_VISITS NUMBER(3)
);

CREATE OR REPLACE TYPE D_V_C
IS TABLE OF C_V;

Here is the function:

CREATE OR REPLACE FUNCTION DRIVERVISITEDCITIES ( D_E# NUMBER)
RETURN D_V_C
IS
  D_FULLNAME VARCHAR(150);
  CITIES_VISITED_BY VARCHAR (30);
  TOTAL_VISITS NUMBER(3);

CITY_VIS_DETAIL D_V_C := D_V_C();

BEGIN

  CITY_VIS_DETAIL.EXTEND();
  SELECT DISTINCT EMPLOYEE.FNAME || EMPLOYEE.INITIALS || EMPLOYEE.LNAME AS FULLNAME,
       UPPER(TRIPLEG.DESTINATION),
       COUNT(TRIPLEG.DESTINATION)
  INTO 
        D_FULLNAME,
        CITIES_VISITED_BY,
        TOTAL_VISITS
FROM EMPLOYEE
INNER JOIN DRIVER
ON DRIVER.E# = EMPLOYEE.E#
INNER JOIN TRIP
ON TRIP.L# = DRIVER.L#
INNER JOIN TRIPLEG
ON TRIPLEG.T# = TRIP.T#
WHERE EMPLOYEE.E# = D_E#
GROUP BY EMPLOYEE.FNAME||EMPLOYEE.INITIALS||EMPLOYEE.LNAME, TRIPLEG.DESTINATION
ORDER BY COUNT(TRIPLEG.DESTINATION) DESC;
RETURN CITY_VIS_DETAIL;
END;

However, when I tried to test the function it shows:

Error starting at line 1 in command:
SELECT DRIVERVISITEDCITIES(1) FROM DUAL
Error report:
SQL Error: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DRIVERVISITEDCITIES", line 13
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

Could anyone help me with this?

Answer 1

You have defined a collection variable but you're not populating it. Instead you're selecting into scalar variables. Clearly your query returns more than one row (because one driver has been on more than one trip) and that's why you're getting TOO_MANY_ROWS exception.

You need to select into that collection. Easiest way is with BULK COLLECT:

SELECT DISTINCT EMPLOYEE.FNAME || EMPLOYEE.INITIALS || EMPLOYEE.LNAME AS FULLNAME,
       UPPER(TRIPLEG.DESTINATION),
       COUNT(TRIPLEG.DESTINATION)
bulk collect into city_vis_detail  -- populate the collection like this
        D_FULLNAME,
        CITIES_VISITED_BY,
        TOTAL_VISITS
FROM EMPLOYEE
INNER JOIN DRIVER
ON DRIVER.E# = EMPLOYEE.E#
INNER JOIN TRIP
ON TRIP.L# = DRIVER.L#
INNER JOIN TRIPLEG
ON TRIPLEG.T# = TRIP.T#
WHERE EMPLOYEE.E# = D_E#
GROUP BY EMPLOYEE.FNAME||EMPLOYEE.INITIALS||EMPLOYEE.LNAME, TRIPLEG.DESTINATION
ORDER BY COUNT(TRIPLEG.DESTINATION) DESC;