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

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?

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;
Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Oracle PL / SQL stored function ORA-01422 and ORA-06512

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录