oracle PLS-00402：游标的 SELECT 列表中需要别名以避免重复的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32027601/
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
PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names
提问by
I have written a stored procedure to pull the data from three different table using join but I'm not able get the result.I'm also trying to pass dynamic table but there is error occurred.
我已经编写了一个存储过程来使用 join 从三个不同的表中提取数据，但我无法得到结果。我也在尝试传递动态表，但发生了错误。
CREATE OR REPLACE Procedure DE_DUP_PRO1 (Dy_File_Name in varchar2)
--RETURN NUMBER
AS
v_hol varchar2(300);
CURSOR De_DUB_CUR IS
SELECT S.TRANS_GUID AS OLD_TRANS_GUID,
H.TRANS_GUID AS NEW_TRANS_GUID,
CASE
WHEN H.TRANS_GUID IS NULL
THEN 0
ELSE 1
END as TRN_STAT,
P.INTR_PHARMACY_ID, S.EXTRNL_PHARMACY_ID, S.PHARMACY_NM, S.PHARMACY_ADDR, S.SUPPLIERS_PSCR_DRUG_CD, S.PSCR_DRUG_IPU_CD,
'IPU', S.PSCR_DRUG_DESC, S.DSPNSD_DRUG_PACK_SIZE, S.RX_ID, S.RX_ITEM_SEQ, S.RX_REPEAT_STATUS, S.RX_TYP, S.EXMT_STATUS,
S.PSCR_QTY, S.NRSG_HM_IND, S.RX_DSPNSD_DT, S.RX_DSPNSD_TM, S.SUPPLIERS_DSPNSD_DRUG_CD, S.DSPNSD_DRUG_IPU_CD, 'IPU',
S.DSPNSD_DRUG_DESC, S.GENERIC_USE_MARKER, S.DSPNSD_UNIT_OF_QTY, S.DSPNSD_QTY, 'EUR', S.COST_OF_DSPNSD_QTY, S.VERBOSE_DOSAGE
FROM (SELECT stg.*, row_number() over ( partition BY key_clmns_hash ORDER BY 1 ) AS RN FROM T_MCL_30404_20150317_020 stg ) s
LEFT 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 AND s.detl_clmns_hash <> h.detl_clmns_hash WHERE S.RN = 1;
BEGIN
FOR De_Dub_rec IN De_DUB_CUR
LOOP
DBMS_OUTPUT.PUT_LINE ( De_Dub_rec.OLD_TRANS_GUID || '|' || De_Dub_rec.NEW_TRANS_GUID || '|' || De_Dub_rec.TRN_STAT || '|' || De_Dub_rec.P.INTR_PHARMACY_ID || '|' || De_Dub_rec.S.EXTRNL_PHARMACY_ID
|| '|' || De_Dub_rec.S.PHARMACY_NM || '|' || De_Dub_rec.S.PHARMACY_ADDR || '|' || De_Dub_rec.S.SUPPLIERS_PSCR_DRUG_CD|| '|' || De_Dub_rec.S.PSCR_DRUG_IPU_CD || '|' || 'IPU'
|| '|' || De_Dub_rec.S.PSCR_DRUG_DESC || '|' || De_Dub_rec.S.DSPNSD_DRUG_PACK_SIZE || '|' || De_Dub_rec.S.RX_ID || '|' || De_Dub_rec.S.RX_ITEM_SEQ || '|' || De_Dub_rec.S.RX_REPEAT_STATUS
|| '|' || De_Dub_rec.S.RX_TYP || '|' || De_Dub_rec.S.EXMT_STATUS || '|' || De_Dub_rec.S.PSCR_QTY || '|' || De_Dub_rec.S.NRSG_HM_IND || '|' || De_Dub_rec.S.RX_DSPNSD_DT
|| '|' || De_Dub_rec.S.RX_DSPNSD_TM || '|' || De_Dub_rec.S.SUPPLIERS_DSPNSD_DRUG_CD || '|' || De_Dub_rec.S.DSPNSD_DRUG_IPU_CD || '|' || 'IPU' || '|' || De_Dub_rec.S.DSPNSD_DRUG_DESC
|| '|' || De_Dub_rec.S.GENERIC_USE_MARKER || '|' || De_Dub_rec.S.DSPNSD_UNIT_OF_QTY || '|' || De_Dub_rec.S.DSPNSD_QTY || '|' || 'EUR' || '|' || De_Dub_rec.S.COST_OF_DSPNSD_QTY
|| '|'|| De_Dub_rec.S.VERBOSE_DOSAGE );
END LOOP;
-- RETURN 0;
END DE_DUP_PRO1;
/
whenever I'm execute stored procedure I get below error
每当我执行存储过程时，都会出现以下错误
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3 PL/SQL: SQL Statement ignored
19/96 PL/SQL: ORA-00942: table or view does not exist
31/9 PL/SQL: Statement ignored
31/32 PLS-00364: loop index variable 'DE_DUB_REC' use is invalid
LINE/COL ERROR(Now it is resolved)
-------- -----------------------------------------------------------------
28/7 PL/SQL: Statement ignored
28/7 PLS-00402: alias required in SELECT list of cursor to avoid
duplicate column names
采纳答案by sstan
Your problem is that your query is selecting a few literal string values without setting any aliases:
您的问题是您的查询选择了一些文字字符串值而不设置任何别名：
select ..., 'IPU', ... , 'IPU', ..., 'EUR', ...
In the above case, Oracle will auto-generate ugly aliases that look something like this:
在上述情况下，Oracle 将自动生成如下所示的丑陋别名：
select ..., 'IPU' AS "'IPU'", ..., 'IPU' AS "'IPU'", ..., 'EUR' AS "'EUR'", ...
So as you can see, you now have 3 very ugly column names that are very awkward to work with, and 2 of them are duplicate, resulting in the error you are getting.
因此，如您所见，您现在有 3 个非常难看的列名，使用起来非常尴尬，其中 2 个是重复的，从而导致您遇到错误。
Consider giving them proper distinct aliases to avoid the ambiguity. This is just an example, but you should give a more meaningful alias according to the meaning of the value:
考虑给他们适当的不同别名以避免歧义。这只是一个例子，但你应该根据值的含义给出一个更有意义的别名：
select ..., 'IPU' AS some_col_1, ..., 'IPU' AS some_col_2, ..., 'EUR' AS some_col_3, ...
The funny thing is that you are notcurrently using those 3 values when reading the query in your cursor for loop. When reading/looping through your cursor, instead of trying to read the 3 values from the cursor, you simply hard-code the values again as you are printing them out.
有趣的是，在读取游标 for 循环中的查询时，您当前没有使用这 3 个值。在读取/循环游标时，不要尝试从游标中读取 3 个值，只需在打印这些值时再次对其进行硬编码。
So in fact, if you really don't care about reading the 3 values from the cursor, just remove them from the query altogether. Otherwise, replace your hard-coded values from your DBMS_OUTPUT.PUT_LINE(...)with the aliases that you set.
所以实际上，如果您真的不关心从游标中读取 3 个值，只需将它们从查询中完全删除即可。否则，用您DBMS_OUTPUT.PUT_LINE(...)设置的别名替换您的硬编码值。
So once your query is fixed, instead of:
因此，一旦您的查询被修复，而不是：
DBMS_OUTPUT.PUT_LINE(... || 'IPU' || ... || 'IPU' || ... || 'EUR' || ...);
You should probably use the cursor like this:
您可能应该像这样使用光标：
DBMS_OUTPUT.PUT_LINE(... || De_Dub_rec.some_col_1 || ... || De_Dub_rec.some_col_2 || ... || De_Dub_rec.some_col_3 || ...);
回答by Sandeep
As per my understanding the below code of line is generating the error due to column ambiguity.
根据我的理解，由于列歧义，下面的代码行会产生错误。
SELECT stg.*, row_number() over ( partition BY key_clmns_hash ORDER BY 1
Just explicitly mention the column name with aliases in the above line instead of using stg.* and that will solve your problem
只需在上一行中明确提及带有别名的列名，而不是使用 stg.* 就可以解决您的问题
