PLS-00428 INTO clause expected error in PLSQL Procedure

Question

I have a procedure in PLSQL. I am getting these errors while compiling them: Compilation errors for PROCEDURE FCBSADM.EXTRACT_MONTHLY_DATA

Error: PLS-00428: an INTO clause is expected in this SELECT statement Line: 11 Text: SELECT DISTINCT intl_prod_id,bill_date

Error: Hint: Parameter 'pid_billdate' is declared but never used in 'extract_monthly_data' Line: 1 Text: create or replace procedure extract_monthly_data(pid_billdate DATE) is

Moreover, How can build a connection between pid_billdate and ab.bill_date? I am open to advices. Thank you from now.

    create or replace procedure extract_monthly_data(pid_billdate DATE) is
v_cnt      NUMBER;
TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER; -- whatever the table is 
l_prod_ids   prod_ids_t;  
begin

   execute immediate 'truncate table gpu';
                  
   v_cnt := 0;
   
   SELECT DISTINCT intl_prod_id,bill_date

      FROM apld_bill_rt abr,
           acct_bill ab
      WHERE     abr.cdate > SYSDATE - 1000 
          AND abr.CHRG_TP = 'INSTALLMENT'
          AND abr.TAX_CATG_ID = 'NOTAX'
          AND abr.acct_bill_id = ab.acct_bill_id
          ORDER BY bill_date;
    
    FOR indx IN 1 .. l_prod_ids.COUNT
    loop  
      INSERT INTO GPU
         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.ACCT_BILL_ID = ABR.ACCT_BILL_ID 
                AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id  
                AND bap.intl_prod_id = abr.intl_prod_id 
                AND ABR.CHRG_TP = 'INSTALLMENT' 
                AND abr.cdate > SYSDATE - 5
                AND ABR.intl_prod_id = l_prod_ids(indx)
                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(+);                    

      v_cnt := v_cnt + 1;

      IF MOD (v_cnt, 1000) = 0
      THEN
         COMMIT;
      END IF;
  
    end loop;
    COMMIT;

end;

this is the last version of it

    create or replace procedure extract_monthly_data(pid_billdate DATE) is
v_cnt      NUMBER;
TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER; -- whatever the table is 
l_prod_ids   prod_ids_t;  
begin

   execute immediate 'truncate table gpu';
                  
   v_cnt := 0;
   
   SELECT DISTINCT abr.intl_prod_id, ab.bill_date BULK COLLECT INTO l_prod_ids
      FROM apld_bill_rt abr,
           acct_bill ab
      WHERE     abr.cdate > SYSDATE - 1000 
          AND abr.CHRG_TP = 'INSTALLMENT'
          AND abr.TAX_CATG_ID = 'NOTAX'
          AND abr.acct_bill_id = ab.acct_bill_id
          ORDER BY bill_date;
    
    FOR indx IN 1 .. l_prod_ids.COUNT
    loop  
      INSERT INTO GPU
      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
            JOIN acct_bill ab ON AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID 
            JOIN FCBSADM.PROD_DST pds ON 
            JOIN bill_acct_prod bap ON bap.intl_prod_id = abr.intl_prod_id 

                AND bap.intl_prod_id = pds.intl_prod_id 
                AND bap.intl_prod_id = p.intl_prod_id
            JOIN prod p ON bap.intl_prod_id = p.intl_prod_id
            JOIN bill_acct ba1 ON ab.intl_bill_acct_id = ba1.intl_bill_acct_id 
            JOIN bill_acct ba2 ON ba1.intl_bill_acct_id = ba2.parent_bill_acct_id 
                AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id  
            LEFT OUTER JOIN prod_cmpg pg ON p.intl_prod_id = pg.intl_prod_id
            WHERE ABR.CHRG_TP = 'INSTALLMENT' 
            AND abr.cdate > SYSDATE - 5
            AND ABR.intl_prod_id =ANY (
              SELECT intl_prod_id -- no DISTINCT, no ORDER BY
              FROM apld_bill_rt abr
                JOIN acct_bill ab ON abr.acct_bill_id = ab.acct_bill_id
              WHERE abr.cdate > SYSDATE - 1000 
                AND abr.CHRG_TP = 'INSTALLMENT'
                AND abr.TAX_CATG_ID = 'NOTAX'
              );                    

      v_cnt := v_cnt + 1;

      IF MOD (v_cnt, 1000) = 0
      THEN
         COMMIT;
      END IF;
  
    end loop;
    COMMIT;

end;

This is the table for insertion

    create table GPU
(
  acct_bill_id        NUMBER(16),
  bill_no             VARCHAR2(30 CHAR),
  inv_id              VARCHAR2(20 CHAR),
  bill_date           DATE,
  bill_acct_id        NUMBER(38),
  parent_bill_acct_id NUMBER(38),
  due_date            DATE,
  cmpg_id             NUMBER,
  net_amt             NUMBER(16,2),
  due_amt             NUMBER(16,2),
  prod_num            VARCHAR2(32 CHAR),
  dst_id              NUMBER(22) not null,
  descr               VARCHAR2(100 CHAR),
  intl_prod_id        NUMBER(14) not null
);

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2021-09-08T11:37:44

Must be like this:

SELECT DISTINCT intl_prod_id
BULK COLLECT INTO l_prod_ids
FROM ...

and even better, use modern ANSI join syntax and don't use any loop. Would be similar to this:

create or replace procedure extract_monthly_data AS
BEGIN
   execute immediate 'truncate table gpu';

   INSERT INTO GPU
     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
            JOIN acct_bill ab ON AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID 
            JOIN FCBSADM.PROD_DST pds ON 
            JOIN bill_acct_prod bap ON bap.intl_prod_id = abr.intl_prod_id 
                AND bap.intl_prod_id = pds.intl_prod_id 
                AND bap.intl_prod_id = p.intl_prod_id
            JOIN prod p ON bap.intl_prod_id = p.intl_prod_id
            JOIN bill_acct ba1 ON ab.intl_bill_acct_id = ba1.intl_bill_acct_id 
            JOIN bill_acct ba2 ON ba1.intl_bill_acct_id = ba2.parent_bill_acct_id 
                AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id  
            LEFT OUTER JOIN prod_cmpg pg ON p.intl_prod_id = pg.intl_prod_id
      WHERE ABR.CHRG_TP = 'INSTALLMENT' 
            AND abr.cdate > SYSDATE - 5
            AND ABR.intl_prod_id =ANY (
              SELECT intl_prod_id -- no DISTINCT, no ORDER BY
              FROM apld_bill_rt abr
                JOIN acct_bill ab ON abr.acct_bill_id = ab.acct_bill_id
              WHERE abr.cdate > SYSDATE - 1000 
                AND abr.CHRG_TP = 'INSTALLMENT'
                AND abr.TAX_CATG_ID = 'NOTAX'
              );   
    COMMIT;
END;

Don't understand your second question, perhaps you are looking for

       AND (ABR.intl_prod_id, pid_billdate) =ANY (
              SELECT intl_prod_id, bill_date
              FROM apld_bill_rt abr
              ...
              )

PLS-00428 INTO clause expected error in PLSQL Procedure

1 Answers