ORA-1422 Exact fetch returns more than requested number of rows problem-CodePudding

I have a procedure(GPU_DATA_EXTRACTOR) which is used to extract data from multiple tables and writes into one table. Secondly I have a function(GETSOURCEBILLINGACCOUNTID) which is used for finding right billing account and makes easier procedure's work. It is called inside the procedure. I want to execute the procedure like if I insert a date it should be work but also should work if it is null too. But it doesnt work when I am trying to execute procedure as a null with this script below.

begin
       GPU_DATA_EXTRACTOR (null);
end;

The errors are: ORA-1422 Exact fetch returns more than requested number of rows ORA-6512 at "GETSOURCEBILLINGACCOUNTID", line 19 ORA-1403 no data found ORA-6512 at "GPU_DATA_EXTRACTOR", line 39 ORA-6512 at line 2

I am adding scripts below, I am open to any fix advices. Thank you from now.

    CREATE OR REPLACE FUNCTION getSourceBillingAccountId (pin_intl_prod_id   IN NUMBER,
                                             pin_bill_date      IN DATE)
      RETURN NUMBER
   AS
      tempBillAccount   NUMBER (14);
   BEGIN
      SELECT pth.from_intl_bill_acct_id
        INTO tempBillAccount
        FROM prod_to_hstr pth
       WHERE pth.sdate =
                (SELECT MIN (pth2.sdate)
                   FROM prod_to_hstr pth2
                  WHERE     pth2.intl_prod_id = pin_intl_prod_id
                        AND pth.intl_prod_id = pth2.intl_prod_id
                        AND pth2.sdate >= pin_bill_date);
                        
    RETURN tempBillAccount;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        SELECT INTL_BILL_ACCT_ID INTO tempBillAccount FROM BILL_ACCT_PROD BAP WHERE BAP.INTL_PROD_ID = pin_intl_prod_id;        
        
        RETURN tempBillAccount;
   END;

The procedure

CREATE OR REPLACE PROCEDURE GPU_DATA_EXTRACTOR (
   pid_billdate DATE)
IS
   c_limit   CONSTANT PLS_INTEGER DEFAULT 10000;

   CURSOR c1
   IS
      SELECT DISTINCT intl_prod_id
        FROM apld_bill_rt abr, acct_bill ab
       WHERE     abr.CHRG_TP = 'INSTALLMENT'
             AND abr.TAX_CATG_ID = 'NOTAX'
             AND abr.acct_bill_id = ab.acct_bill_id
             AND ab.bill_date =
                    NVL (
                       pid_billdate,
                       TRUNC (
                          (  TO_DATE ('01' || TO_CHAR (SYSDATE, 'MMYYYY'),
                                      'DDMMYYYY')
                           - 1 / 24 / 60 / 60)));

   --ab.chrg_date = (select max(l_billdate) from bill_acct_billcycle bab where bab.intl_bill_acct_id = ab.intl_bill_acct_id);

   TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER;

   l_prod_ids         prod_ids_t;
   
BEGIN
   EXECUTE IMMEDIATE 'truncate table GPU_INV';

   OPEN c1;

   LOOP
      FETCH c1
      BULK COLLECT INTO l_prod_ids
      LIMIT c_limit;

      EXIT WHEN l_prod_ids.COUNT = 0;

      FORALL indx IN 1 .. l_prod_ids.COUNT
     
      INSERT INTO GPU_INV
         SELECT AB.ACCT_BILL_ID,
                AB.BILL_NO,
                AB.INV_ID,
                AB.BILL_DATE,
                ba2.bill_acct_id,
                ba1.bill_acct_id parent_bill_acct_id,
                AB.DUE_DATE,
                PG.CMPG_ID,
                ABR.NET_AMT,
                AB.DUE_AMT,
                P.PROD_NUM,
                pds.DST_ID,
                ABR.DESCR,
                p.intl_prod_id
           FROM apld_bill_rt abr,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                bill_acct ba2,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND ab.bill_date = nvl(pid_billdate,trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60)))
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba2.intl_bill_acct_id = getSourceBillingAccountId( l_prod_ids(indx), nvl(pid_billdate, trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60))))
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND (bap.edate is null or ab.bill_Date <= bap.edate)
                AND p.intl_prod_id = pg.intl_prod_id( )
                AND ABR.intl_prod_id = l_prod_ids(indx)

UNION
    SELECT AB.ACCT_BILL_ID,
                AB.BILL_NO,
                AB.INV_ID,
                AB.BILL_DATE,
                ba1.bill_acct_id,
                ba1.bill_acct_id parent_bill_acct_id,
                AB.DUE_DATE,
                PG.CMPG_ID,
                ABR.NET_AMT,
                AB.DUE_AMT,
                P.PROD_NUM,
                pds.DST_ID,
                ABR.DESCR,
                p.intl_prod_id
           FROM apld_bill_rt abr,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND ab.bill_date = nvl(pid_billdate,trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60)))
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                --AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba1.intl_bill_acct_id = getSourceBillingAccountId( l_prod_ids(indx), nvl(pid_billdate, trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60))))
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND p.intl_prod_id = pg.intl_prod_id( )
                AND ABR.intl_prod_id = l_prod_ids(indx)
                AND (bap.edate is null or ab.bill_Date <= bap.edate);



    COMMIT;

    END LOOP;
   CLOSE c1;
end;

CodePudding user response：

First edit your function, because i feel inside an exception its not good to put some logic to return result :

CREATE OR REPLACE FUNCTION getSourceBillingAccountId(pin_intl_prod_id IN NUMBER,
                                                     pin_bill_date    IN DATE)
  RETURN NUMBER AS
  tempBillAccount NUMBER(14);
BEGIN

  SELECT DECODE(COUNT(*),
                0,
                (SELECT INTL_BILL_ACCT_ID
                   INTO tempBillAccount
                   FROM BILL_ACCT_PROD BAP
                  WHERE BAP.INTL_PROD_ID = pin_intl_prod_id),
                pth.from_intl_bill_acct_id)
    INTO tempBillAccount
    FROM prod_to_hstr pth
   WHERE pth.sdate = (SELECT MIN(pth2.sdate)
                        FROM prod_to_hstr pth2
                       WHERE pth2.intl_prod_id = pin_intl_prod_id
                         AND pth.intl_prod_id = pth2.intl_prod_id
                         AND pth2.sdate >= pin_bill_date);
  RETURN tempBillAccount;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;

For sure , your function will return a value or NULL if it returns no data.

I think it will solve your problem

Note: You also have a problem : Exact fetch returns more than requested number of rows

What is your logic if your cursor returns more than one row ? you should also apply some solution on it too ...

CodePudding user response：

It is the function that raises the error.

Procedure accepts date as a parameter and calls the function:

if parameter exists, it is used

if it doesn't exist (i.e. you pass NULL), the NVL function makes sure that "default" date is used:

nvl(pid_billdate, trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60))))

What is that value, anyway?

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60)) res from dual;

RES
-------------------
30.09.2021 00:00:00

Well, you could've done it in a simpler manner:

SQL> select last_day(add_months(trunc(sysdate), -1)) res from dual;

RES
-------------------
30.09.2021 00:00:00

Anyway: the above means that function will get some date as its parameter.

You handled NO_DATA_FOUND exception, but that's not your problem - function raised TOO_MANY_ROWS, and you didn't handle that.

I have no idea what you want to do in such a case. If you don't really care which one you'll get, take any. For example, using the MAX function:

SELECT MAX (pth.from_intl_bill_acct_id)
  INTO tempBillAccount
  FROM prod_to_hstr pth
 WHERE pth.sdate = (SELECT MIN (pth2.sdate)
                      FROM prod_to_hstr pth2
                     WHERE     pth2.intl_prod_id = pin_intl_prod_id
                           AND pth.intl_prod_id = pth2.intl_prod_id
                           AND pth2.sdate >= pin_bill_date);

Or, maybe you got two or more same values; then DISTINCT would work:

SELECT DISTINCT pth.from_intl_bill_acct_id

Or, you actually want to handle such a situation - in that case, add

  when too_many_rows then 
  ...

into exception handling section.