When I try to join and insert SQL using the cursor, I get an error saying, "To avoid duplicate column names, I need another name in the cursor's SELECT list.Why is that?
Is there something wrong with the bottom part?
r_ABCc_ABC%ROWTYPE; -- Defining variables to substitute records retrieved by SELECT
v_CNT NUMBER: = 0;
BEGIN
OPEN c_ABC;
LOOP
FETCHc_ABC INTER_ABC;
EXIT WHEN c_ABC%NOTFOUND;
v_CNT: = v_CNT+1;
The select statement written in c_ABC instead of the code in the question statement may be incorrect.
I think the error you asked me about this time is Oracle's PLS-00402.
Oracle documentation states the following error:
PLS-00402—An alias is required in the SELECT list of the cursor to avoid duplicate column names.
Cause: The cursor is declared in a SELECT statement containing duplicate column names.This reference is unclear.
Action: Replace duplicate column names with alias in SELECT list.
If you try to get the same column name in the cursor select statement as described above, an error occurs as an unclear reference.
Try defining an alias so that the column names used for the cursor do not overlap.
Below is a sample code of NG and OK stored.
If you comment on line 5 and delete the comment on line 7, the compilation will go through.
CREATE OR REPLACE PROCEDURE TEST 00402
IS
CURSORc_ABCIS
-- NG SELECT statement
SELECT a.val, b.val
-- OK SELECT statement
-- SELECT a.val as a_val, b.val as b_val
FROM (select 1 idx, 'hoge' val from dual)a
, (select 1 idx, 'fuga' val from dual)b
WHERE a.idx = b.idx;
r_ABCc_ABC%ROWTYPE;
BEGIN
OPEN c_ABC;
LOOP
FETCHc_ABC INTER_ABC;
EXIT WHEN c_ABC%NOTFOUND;
END LOOP;
CLOSE c_ABC;
END;
/
© 2024 OneMinuteCode. All rights reserved.