oracle - ORA-01007 : variable not in select list

Question

我试图通过返回选择查询将多个值插入到特定表中，但我无法将其插入表中。如果我在某个地方做错了，请让我知道。在此先感谢您。

create or replace PROCEDURE DE_DUP_PROC1 (Dy_File_Name    IN     VARCHAR2,    
                                             SUPPLIER_CD     IN     VARCHAR2,    
                                             EXT_PHARMA_ID   IN     VARCHAR2,    
                                             FLAG_VALUE      IN     VARCHAR2,   
                                             ERR_COUNT       IN     VARCHAR2, 
                                             OUTPUT_STATUS   OUT    NUMBER)    

    AS    
        c2                    SYS_REFCURSOR;    
        De_Dub_rec1   VARCHAR2 (2000);
        v_sql  VARCHAR2 (2000);    
        v_sql1  VARCHAR2 (2000);
        ORGNIZATION_ID    NUMBER(20);  
        PHARMACY_ID NUMBER(38);
        v_dup_count VARCHAR2 (2000);
        SRC_ID NUMBER(38);
        DE_DUP_COUNT NUMBER(38);
        DE_REC_COUNT1 NUMBER(10) := 3;

       TYPE rec_typ IS RECORD    
       (    
          OLD_TRANS_GUID      VARCHAR2 (255),
          R_DSPNSD_DT         DATE,    
          DETL_CLMNS_HASH1      VARCHAR2(255),
          KEY_CLMNS_HASH1      VARCHAR2(255),
          SUPPLIER_PHARMACY_CD1 VARCHAR2(200)       
       );    
        De_Dub_rec      rec_typ;    

    BEGIN    

       IF DE_REC_COUNT1 > 0    
       THEN    

          OUTPUT_STATUS := 0;    
          dbms_output.put_line(OUTPUT_STATUS);    

       ELSE

                SRC_ID := SRC_FILE_ID_SEQ.nextval
            OPEN c2 FOR    
                ( ' SELECT S.TRANS_GUID AS OLD_TRANS_GUID,S.DETL_CLMNS_HASH AS DETL_CLMNS_HASH1 ,S.KEY_CLMNS_HASH AS KEY_CLMNS_HASH1,S.RX_DSPNSD_DT AS R_DSPNSD_DT,
                S.SUPPLIER_PHARMACY_CD AS SUPPLIER_PHARMACY_CD1 FROM (SELECT stg.*, row_number() over (partition BY key_clmns_hash ORDER BY 1) AS RN FROM 
                ' || Dy_File_Name || ' stg ) s JOIN ps_pharmacy p ON s.extrnl_pharmacy_id = p.extrnl_pharmacy_id LEFT JOIN ps_rx_hist H
                ON h.key_clmns_hash        = s.key_clmnS_hash
                AND h.rx_dspnsd_dt         = s.rx_dspnsd_dt
                AND s.supplier_pharmacy_cd = h.SUPPLIER_PHARMACY_CD
                WHERE S.RN > 1
                OR s.detl_clmns_hash = h.detl_clmns_hash ' );    

            LOOP    

                FETCH c2 INTO De_Dub_rec;    

                EXIT WHEN c2%NOTFOUND;    

                insert into PS_RX_DUPES(TRANS_GUID,DETL_CLMNS_HASH,KEY_CLMNS_HASH,RX_DSPNSD_DT,SUPPLIER_PHARMACY_CD,SRC_FILE_ID) 
                values(De_Dub_rec.OLD_TRANS_GUID,De_Dub_rec.DETL_CLMNS_HASH1,De_Dub_rec.KEY_CLMNS_HASH1,De_Dub_rec.R_DSPNSD_DT,De_Dub_rec.SUPPLIER_PHARMACY_CD1,SRC_ID);

                commit;
            END LOOP;     

         OUTPUT_STATUS := 1;
         dbms_output.put_line(OUTPUT_STATUS);

        END IF;    

    END DE_DUP_PROC1;

每当我执行上面的存储过程时，我下面的错误

declare
    OUTPUT_STATUS number(2);
begin
  DE_DUP_PROC1('T_MCL_10622_20150317_01526556','MCL','10622','BD','3',OUTPUT_STATUS);     
end;
Error at line 1    
 - ORA-01007: variable not in select list
   ORA-06512: at "PS_ADMIN.DE_DUP_PROC1", line 53
   ORA-06512: at line 6

Answer 1

当查询的列与目标变量不匹配时，Oracle会发送ORA-01007。
第53行是此行FETCH c2 INTO De_Dub_rec;，因此线索是光标的投影与记录类型不匹配。
您的自由文本SELECT语句布局困惑，这使调试变得很困难。让我们整理一下投影:

SELECT S.TRANS_GUID             AS OLD_TRANS_GUID
       , S.DETL_CLMNS_HASH      AS DETL_CLMNS_HASH1 
       , S.KEY_CLMNS_HASH       AS KEY_CLMNS_HASH1
       , S.RX_DSPNSD_DT         AS R_DSPNSD_DT
       , S.SUPPLIER_PHARMACY_CD AS SUPPLIER_PHARMACY_CD1 
FROM ...

现在可以很容易地看到列顺序与类型的属性顺序不同:

   TYPE rec_typ IS RECORD    
   (    
      OLD_TRANS_GUID        VARCHAR2 (255),
      R_DSPNSD_DT           DATE,    
      DETL_CLMNS_HASH1      VARCHAR2(255),
      KEY_CLMNS_HASH1       VARCHAR2(255),
      SUPPLIER_PHARMACY_CD1 VARCHAR2(200)       
   );    

因此，您的代码正在尝试将字符串放入日期变量中(反之亦然，但至少Oracle可以强制转换)。
所有这些都证明，清晰的布局并不是愚蠢的强制症。编写代码的纪律可以通过突出显示明显的错误来帮助我们更快地编写更好的代码。