ORA-01007: variable not in select list
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.
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on December 31, 2021Comments
-
Admin over 2 years
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