title: ORA-01007: variable not in select list
body: <p>I'm trying to insert multiple value into a particular table by return select query I'm not able to insert into table.If I'm doing wrong somewhere please let me know.Thanks in advance.     </p>
  
  <pre><code>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 &gt; 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 &gt; 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;
  </code></pre>
  
  <p>Whenever I'm executing above stored procedure I below error    </p>
  
  <pre><code>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
  </code></pre>