Total Pageviews

2014/05/02

ORA-01422 Error From Oracle Function

Problem
I wrote an Oracle function on my own as bellows:
CREATE OR REPLACE FUNCTION F_FMS420R_GET_ACCUM_AMTA (S_YYY_MM IN VARCHAR2 --起始年月
 ,E_YYY_MM IN VARCHAR2 --結束年月
 ,ASP IN VARCHAR2 --性質別
) RETURN NUMBER IS AMT_A NUMBER(20, 2) := 0;

 BEGIN
SELECT AMT_A INTO AMT_A FROM
  (SELECT YEAR, ASP, SUM(AMT_A) AS AMT_A
   FROM FMS420FA
   WHERE (YYY_MM BETWEEN S_YYY_MM AND E_YYY_MM)
     AND ASP = ASP
   GROUP BY YEAR, ASP);

 RETURN (AMT_A);

 END F_FMS420R_GET_ACCUM_AMTA;

After I test it, it show this error message as bellows:
 ORA-01422: exact fetch returns more than requested number of rows (精確擷取傳回的列數超過所要求的列數) 
 ORA-06512: 在 "AP_PSR.F_FMS420R_GET_ACCUM_AMTA", line 9  
 ORA-06512: 在 line 11  

But this select SQL statement must return one record, if I provide all search criteria
SELECT YEAR,
       ASP,
       SUM(B.AMT_A) AS AMT_A
FROM FMS420FA B
WHERE B.YYY_MM BETWEEN :S_YYY_MM AND :E_YYY_MM
  AND B.ASP = :ASP
GROUP BY YEAR,
         ASP

Root cause

This error result from the parameter name cannot be equal to database column name. If yes, Oracle will not know what I am doing and ignore this search criteria. That's why it will have ORA-01422 error.

Solution
Just rename input parameter name from ASP to INPUT_ASP, then this problem can be resolved.
CREATE OR REPLACE FUNCTION F_FMS420R_GET_ACCUM_AMTA (S_YYY_MM IN VARCHAR2 --起始年月
 ,E_YYY_MM IN VARCHAR2 --結束年月
 ,INPUT_ASP IN VARCHAR2 --性質別
) RETURN NUMBER IS AMT_A NUMBER(20, 2) := 0;

 BEGIN
SELECT AMT_A INTO AMT_A FROM
  (SELECT YEAR, ASP, SUM(AMT_A) AS AMT_A
   FROM FMS420FA
   WHERE (YYY_MM BETWEEN S_YYY_MM AND E_YYY_MM)
     AND ASP = INPUT_ASP
   GROUP BY YEAR, ASP);

 RETURN (AMT_A);

 END F_FMS420R_GET_ACCUM_AMTA;


No comments: