CODEWITHSUNDEEP
oracle-databaseplsqldatabase-cursor
user4993731
user4993731

Reputation:

ORA-01007: variable not in select list

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. 

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;

Whenever I'm executing above stored procedure I below error 

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

Upvotes: 8

Views: 52873

Answers (2)

APC
APC

Reputation: 146229

Oracle hurls ORA-01007 when the columns of our query don't match the target variable.

Line 53 is this line FETCH c2 INTO De_Dub_rec;, so the clue is the projection of the cursor doesn't match the record type.

Your free-text SELECT statement is messily laid out, which makes debugging hard. Let's tidy up the projection:

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 ...

Now it becomes easy to see that the column order is different from the type's attribute order:

   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)       
   );

So your code is trying to put a string into a date variable (and vice versa, but at least Oracle can cast that).

All of which goes to prove that clear layout is not a silly OCD thing. Discipline in writing code helps us write better code quicker by highlighting obvious errors.

Upvotes: 15

David Aldridge
David Aldridge

Reputation: 52346

I think that I would tackle this problem by having a synonym that is dedicated to this process, and which you redefine to point at the appropriate source table prior to selecting from it. Then you can use regular SQL, which will be much more simple.

Alternatively, instead of constructing this cursor you can define an appropriate insert statement dynamically and use execute immediate to run it.

The cursor approach is more complicated, slower, and (as you have seen) more liable to have coding errors.

Upvotes: 0

Related Questions