Oracle带GROUP BY的FOR循环插入报ORA-06550/ORA-00984错误求解

问题根因 #

ORA-00984: column not allowed here错误和你写的GROUP BY逻辑无关，是INSERT语句的VALUES子句中存在拼写错误：你将null误写为nul，Oracle无法识别nul这个关键字，会默认将其当作列名处理，而VALUES子句中不允许直接引用列名，因此触发报错。

修正代码 #

只需要把插入语句里的nul改为null即可正常运行，完整修正后代码如下：

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,null,null,null);
END LOOP;
END;

可选优化建议 #

如果没有逐行处理的特殊需求，可以去掉FOR LOOP，直接用INSERT INTO ... SELECT语法完成批量插入，性能会更高：

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)
SELECT DUN_EXCEPTION_SEQ.nextval,
       'BUYER_ID',
       BEZ_OIB.EXT_ACCOUNT_ID as 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,
       null,
       null,
       null
FROM (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
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);

内容的提问来源于stack exchange，提问作者GodDamnLawyer