For LOOP with group by insert into ORA-06550 Column NOT ALLOWED HERE error-CodePudding

I am having problem with writing procedure for insert into table that has select statement with group by in for loop.

Here is look at my code:

begin
FOR R IN (WITH
bez_oib
AS
    (SELECT a.EXT_ACCOUNT_ID,
            a.ACCOUNT_ID,
            c.NAME,
            c.ATTRIBUTE_VALUE_1     AS oib3
       FROM MD_ACCOUNTS a, MD_CONTACTS c
      WHERE     a.ACCOUNT_ID = c.REFERENCE_ID
            AND c.REFERENCE_TYPE_ID = 2
            AND c.CONTACT_TYPE_ID = 1
            AND a.EXT_ACCOUNT_ID IN (  SELECT a.EXT_ACCOUNT_ID
                                         FROM MD_ACCOUNTS a
                                     GROUP BY a.EXT_ACCOUNT_ID
                                       HAVING COUNT (DISTINCT a.account_id) > 1)
            AND c.ATTRIBUTE_VALUE_1 IS NULL),
sa_oib
AS
    (SELECT a.EXT_ACCOUNT_ID,
            a.ACCOUNT_ID,
            c.NAME,
            c.ATTRIBUTE_VALUE_1     AS oib2
       FROM MD_ACCOUNTS a, MD_CONTACTS c
      WHERE     a.ACCOUNT_ID = c.REFERENCE_ID
            AND c.REFERENCE_TYPE_ID = 2
            AND c.CONTACT_TYPE_ID = 1
            AND a.EXT_ACCOUNT_ID IN (  SELECT a.EXT_ACCOUNT_ID
                                         FROM MD_ACCOUNTS a
                                     GROUP BY a.EXT_ACCOUNT_ID
                                       HAVING COUNT (DISTINCT a.account_id) > 1)
            AND c.ATTRIBUTE_VALUE_1 IS NOT NULL)
SELECT BEZ_OIB.EXT_ACCOUNT_ID as SIFRA_KK,
     BEZ_OIB.ACCOUNT_ID as a,
     BEZ_OIB.NAME as c ,
     BEZ_OIB.oib3 as b ,
     SUM (IH1.REST_TO_PAY)     AS PREOSTALI_DUG_BEZ_OIB,
     SA_OIB.EXT_ACCOUNT_ID as s,
     SA_OIB.ACCOUNT_ID as d,
     SA_OIB.NAME as e,
     SA_OIB.oib2 as f,
     SUM (IH2.REST_TO_PAY)     AS PREOSTALI_DUG_SA_OIB
FROM bez_oib,
     sa_oib,
     CA_INVOICE_HEADERS IH1,
     CA_INVOICE_HEADERS IH2
 WHERE     bez_oib.EXT_ACCOUNT_ID = sa_oib.EXT_ACCOUNT_ID
     AND bez_oib.NAME <> sa_oib.name
     AND BEZ_OIB.ACCOUNT_ID = IH1.ACCOUNT_ID( )
     AND SA_OIB.ACCOUNT_ID = IH2.ACCOUNT_ID( )
     AND BEZ_OIB.EXT_ACCOUNT_ID NOT IN ('30150151054',
                                        '33671110205',
                                        '31841000148',
                                        '31840520300',
                                        '30200150216',
                                        '33670011076')
GROUP BY BEZ_OIB.EXT_ACCOUNT_ID,
     BEZ_OIB.ACCOUNT_ID,
     BEZ_OIB.NAME,
     BEZ_OIB.oib3,
     SA_OIB.EXT_ACCOUNT_ID,
     SA_OIB.ACCOUNT_ID,
     SA_OIB.NAME,
     SA_OIB.oib2) 
     LOOP
insert into dun_exception (ID, TYPE, VALUE, ACTION, VALID_FROM, VALID_TO, STATE, NOTE, CREATED_BY, CREATE_DATE, MODIFIED_BY, MODIFY_DATE, PROC_NAME, BUYER_ID, REASON) values (DUN_EXCEPTION_SEQ.nextval,'BUYER_ID',r.sifra_kk,null,date'2021-10-26', date'2022-12-31',1,'Jedna ŠK, različit naziv, ima OIB - nema OIB', 'sbelcic',sysdate,null,null,nul,null,null);
END LOOP;
END;

After I try to execute it I get the following error:

Error report - ORA-06550: line 66, column 351: PL/SQL: ORA-00984: column not allowed here

Column in particular error referers to last line of group by statement.

The code should insert values into table but it isn't working.

Can someone help?

CodePudding user response：

That's nul, Stjepane.

<snip>    
VALUES (DUN_EXCEPTION_SEQ.NEXTVAL,
     'BUYER_ID',
     r.sifra_kk,
     NULL,
     DATE '2021-10-26',
     DATE '2022-12-31',
     1,
     'Jedna ŠK, različit naziv, ima OIB - nema OIB',
     'sbelcic',
     SYSDATE,
     NULL,
     NULL,
     nul,                  --> here
     NULL,
     NULL);