oracle ORA-01007: 变量不在选择列表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32485978/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01007: variable not in select list
提问by APC
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
回答by APC
Oracle hurls ORA-01007when the columns of our query don't match the target variable.
ORA-01007当我们查询的列与目标变量不匹配时，Oracle 会抛出异常。
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.
第 53 行是这一行FETCH c2 INTO De_Dub_rec;，所以线索是光标的投影与记录类型不匹配。
Your free-text SELECT statement is messily laid out, which makes debugging hard. Let's tidy up the projection:
您的自由文本 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 ...
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).
所以你的代码试图将一个字符串放入一个日期变量中（反之亦然，但至少 Oracle 可以转换它）。
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.
所有这些都证明了清晰的布局并不是愚蠢的强迫症。通过突出明显的错误，编写代码时的纪律可以帮助我们更快地编写更好的代码。
回答by David Aldridge
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.
我认为我将通过拥有一个专用于此过程的同义词来解决这个问题，并且在从中进行选择之前，您可以重新定义该同义词以指向适当的源表。然后就可以使用普通的SQL了，会简单很多。
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.
游标方法更复杂、更慢，并且（如您所见）更容易出现编码错误。
