Hi Tom,
How do i make use of the query in the ref cursor inside a merge statement.
in the "ON" condition it says "Src.column_name " is an invalid identifier.
create or replace PROCEDURE TingTong (
in_tax_year IN tdir_le_mast.tax_year%TYPE,
in_link_name IN VARCHAR2 DEFAULT 0)
IS
type rc is ref cursor;
l_cur rc;
r_c1 tdir_le_mast%rowtype;
BEGIN
dbms_output.put_line('Before open cursor.');
open l_cur for 'SELECT TAX_YEAR,
LEID,
CDR_NO,
ENTITY_NAME,
FUNC_CURR,
GE_GECS_FLAG,
HO_LEID,
HO_CDR_NO,
DATE_OF_INCORPORATION,
COUNTRY_INCORPORATED,
US_TAX_TYPE,
CLASSIFICATION,
DASTM,
SCHD_O_REQD,
PFIC,
ADDR_LINE1_1A,
ADDR_LINE2_1A,
ADDR_LINE3_1A,
LAST_USER_UPDATE,
LAST_DATE_UPDATE,
INACTIVE_FLAG,
LAST_OPTION_ID,
SCHD_P_REQD,
PRIORYEAR_FUNC_CURR,
FILING_GROUP,
LOCAL_TAX_TYPE,
ENTITY_TYPE,
CDR_YORN,
EIN,
ACQUISITION_DATE,
SHORT_PERIOD_LEID,
IS_COMPONENT,
SCENARIO,
REPORTING_PERIOD,
HO_REPORTING_PERIOD,
EFILE_ID,
PARENT_ELIM_FLAG,
FSE_YORN,
DRE_CDR_NO,
DRE_LEID,
DRE_REPORTING_PERIOD
FROM tdir_le_mast@'||in_link_name||
' WHERE tax_year =' ||in_tax_year;
dbms_output.put_line('Before Fetch.');
fetch l_cur into r_c1;
loop
exit when l_cur%notfound;
dbms_output.put_line('Test Roll.');
MERGE INTO tdir_le_mast dst
USING (SELECT r_c1.TAX_YEAR,
r_c1.LEID,
r_c1.CDR_NO,
r_c1.ENTITY_NAME,
r_c1.FUNC_CURR,
r_c1.GE_GECS_FLAG,
r_c1.HO_LEID,
r_c1.HO_CDR_NO,
r_c1.DATE_OF_INCORPORATION,
r_c1.COUNTRY_INCORPORATED,
r_c1.US_TAX_TYPE,
r_c1.CLASSIFICATION,
r_c1.DASTM,
r_c1.SCHD_O_REQD,
r_c1.PFIC,
r_c1.ADDR_LINE1_1A,
r_c1.ADDR_LINE2_1A,
r_c1.ADDR_LINE3_1A,
r_c1.LAST_USER_UPDATE,
r_c1.LAST_DATE_UPDATE,
r_c1.INACTIVE_FLAG,
r_c1.LAST_OPTION_ID,
r_c1.SCHD_P_REQD,
r_c1.PRIORYEAR_FUNC_CURR,
r_c1.FILING_GROUP,
r_c1.LOCAL_TAX_TYPE,
r_c1.ENTITY_TYPE,
r_c1.CDR_YORN,
r_c1.EIN,
r_c1.ACQUISITION_DATE,
r_c1.SHORT_PERIOD_LEID,
r_c1.IS_COMPONENT,
r_c1.SCENARIO,
r_c1.REPORTING_PERIOD,
r_c1.HO_REPORTING_PERIOD,
r_c1.EFILE_ID,
r_c1.PARENT_ELIM_FLAG,
r_c1.FSE_YORN,
r_c1.DRE_CDR_NO,
r_c1.DRE_LEID,
r_c1.DRE_REPORTING_PERIOD
FROM dual)src
ON (src.tax_year = dst.tax_year )
WHEN MATCHED
THEN
UPDATE SET dst.TAX_YEAR = src.TAX_YEAR,
dst.LEID=src.LEID,
dst.CDR_NO=src.CDR_NO,
dst.ENTITY_NAME=src.ENTITY_NAME,
dst.FUNC_CURR=src.FUNC_CURR,
dst.GE_GECS_FLAG=src.GE_GECS_FLAG,
dst.HO_LEID=src.HO_LEID,
dst.HO_CDR_NO=src.HO_CDR_NO,
dst.DATE_OF_INCORPORATION=src.DATE_OF_INCORPORATION,
dst.COUNTRY_INCORPORATED=src.COUNTRY_INCORPORATED,
dst.US_TAX_TYPE=src.US_TAX_TYPE,
dst.CLASSIFICATION=src.CLASSIFICATION,
dst.DASTM=src.DASTM,
dst.SCHD_O_REQD=src.SCHD_O_REQD,
dst.PFIC=src.PFIC,
dst.ADDR_LINE1_1A=src.ADDR_LINE1_1A,
dst.ADDR_LINE2_1A=src.ADDR_LINE2_1A,
dst.ADDR_LINE3_1A=src.ADDR_LINE3_1A,
dst.LAST_USER_UPDATE=src.LAST_USER_UPDATE,
dst.LAST_DATE_UPDATE=src.LAST_DATE_UPDATE,
dst.INACTIVE_FLAG=src.INACTIVE_FLAG,
dst.LAST_OPTION_ID=src.LAST_OPTION_ID,
dst.SCHD_P_REQD=src.SCHD_P_REQD,
dst.PRIORYEAR_FUNC_CURR=src.PRIORYEAR_FUNC_CURR,
dst.FILING_GROUP=src.FILING_GROUP,
dst.LOCAL_TAX_TYPE=src.LOCAL_TAX_TYPE,
dst.ENTITY_TYPE=src.ENTITY_TYPE,
dst.CDR_YORN=src.CDR_YORN,
dst.EIN=src.EIN,
dst.ACQUISITION_DATE=src.ACQUISITION_DATE,
dst.SHORT_PERIOD_LEID=src.SHORT_PERIOD_LEID,
dst.IS_COMPONENT=src.IS_COMPONENT,
dst.SCENARIO=src.SCENARIO,
dst.REPORTING_PERIOD=src.REPORTING_PERIOD,
dst.HO_REPORTING_PERIOD=src.HO_REPORTING_PERIOD,
dst.EFILE_ID=src.EFILE_ID,
dst.PARENT_ELIM_FLAG=src.PARENT_ELIM_FLAG,
dst.FSE_YORN=src.FSE_YORN,
dst.DRE_CDR_NO=src.DRE_CDR_NO,
dst.DRE_LEID=src.DRE_LEID,
dst.DRE_REPORTING_PERIOD=src.DRE_REPORTING_PERIOD
WHEN NOT MATCHED
THEN
INSERT (dst.TAX_YEAR,
dst.LEID,
dst.CDR_NO,
dst.ENTITY_NAME,
dst.FUNC_CURR,
dst.GE_GECS_FLAG,
dst.HO_LEID,
dst.HO_CDR_NO,
dst.DATE_OF_INCORPORATION,
dst.COUNTRY_INCORPORATED,
dst.US_TAX_TYPE,
dst.CLASSIFICATION,
dst.DASTM,
dst.SCHD_O_REQD,
dst.PFIC,
dst.ADDR_LINE1_1A,
dst.ADDR_LINE2_1A,
dst.ADDR_LINE3_1A,
dst.LAST_USER_UPDATE,
dst.LAST_DATE_UPDATE,
dst.INACTIVE_FLAG,
dst.LAST_OPTION_ID,
dst.SCHD_P_REQD,
dst.PRIORYEAR_FUNC_CURR,
dst.FILING_GROUP,
dst.LOCAL_TAX_TYPE,
dst.ENTITY_TYPE,
dst.CDR_YORN,
dst.EIN,
dst.ACQUISITION_DATE,
dst.SHORT_PERIOD_LEID,
dst.IS_COMPONENT,
dst.SCENARIO,
dst.REPORTING_PERIOD,
dst.HO_REPORTING_PERIOD,
dst.EFILE_ID,
dst.PARENT_ELIM_FLAG,
dst.FSE_YORN,
dst.DRE_CDR_NO,
dst.DRE_LEID,
dst.DRE_REPORTING_PERIOD
)
VALUES (src.TAX_YEAR,
src.LEID,
src.CDR_NO,
src.ENTITY_NAME,
src.FUNC_CURR,
src.GE_GECS_FLAG,
src.HO_LEID,
src.HO_CDR_NO,
src.DATE_OF_INCORPORATION,
src.COUNTRY_INCORPORATED,
src.US_TAX_TYPE,
src.CLASSIFICATION,
src.DASTM,
src.SCHD_O_REQD,
src.PFIC,
src.ADDR_LINE1_1A,
src.ADDR_LINE2_1A,
src.ADDR_LINE3_1A,
src.LAST_USER_UPDATE,
src.LAST_DATE_UPDATE,
src.INACTIVE_FLAG,
src.LAST_OPTION_ID,
src.SCHD_P_REQD,
src.PRIORYEAR_FUNC_CURR,
src.FILING_GROUP,
src.LOCAL_TAX_TYPE,
src.ENTITY_TYPE,
src.CDR_YORN,
src.EIN,
src.ACQUISITION_DATE,
src.SHORT_PERIOD_LEID,
src.IS_COMPONENT,
src.SCENARIO,
src.REPORTING_PERIOD,
src.HO_REPORTING_PERIOD,
src.EFILE_ID,
src.PARENT_ELIM_FLAG,
src.FSE_YORN,
src.DRE_CDR_NO,
src.DRE_LEID,
src.DRE_REPORTING_PERIOD
);
end loop;
close l_cur;
Exception
when others then
dbms_output.put_line('Test failed.'||SQLCODE||':'||SQLERRM);
END;
/
----------------------------------------
DBMS Output for the code: if I used SRC in the "ON" of Merge statement.
----------------------------------------
Before open cursor.
Before Fetch.
Test Roll.
Test failed.-904:ORA-00904: "SRC"."TAX_YEAR": invalid identifier
Can you remove the 'when others' statement, and then run this and show us the entire error stack.
Then we can see exactly what is going on
================================================
In your merge, you are selecting the contents of variables from dual. Just because the names of the *variables* match your column names does not mean those columns will be aliased automatically. You need to add the aliases, eg
SQL> create table t1 ( tax_year int, leid int, cdr_no int, DRE_REPORTING_PERIOD int );
Table created.
SQL> insert into t1 values (1,1,1,1);
1 row created.
SQL> create table t2 ( tax_year int, leid int, cdr_no int, DRE_REPORTING_PERIOD int );
Table created.
SQL> insert into t2 values (1,1,1,1);
1 row created.
SQL>
SQL>
SQL> set serverout on
SQL> declare
2 type rc is ref cursor;
3 l_cur rc;
4 r_c1 t1%rowtype;
5
6 BEGIN
7 dbms_output.put_line('Before open cursor.');
8 open l_cur for 'SELECT TAX_YEAR,
9 LEID,
10 CDR_NO,
11 DRE_REPORTING_PERIOD
12 FROM t1';
13
14 dbms_output.put_line('Before Fetch.');
15 loop
16 fetch l_cur into r_c1;
17 exit when l_cur%notfound;
18 dbms_output.put_line('Test Roll.');
19
20 MERGE INTO t2 dst
21 USING (SELECT r_c1.TAX_YEAR tax_year, <====
22 r_c1.LEID leid, <====
23 r_c1.CDR_NO cdr_no, <====
24 r_c1.DRE_REPORTING_PERIOD DRE_REPORTING_PERIOD <====
25 FROM dual) src
26 ON (src.tax_year = dst.tax_year )
27 WHEN MATCHED
28 THEN
29 UPDATE SET dst.LEID=src.LEID,
30 dst.CDR_NO=src.CDR_NO,
31 dst.DRE_REPORTING_PERIOD=src.DRE_REPORTING_PERIOD
32 WHEN NOT MATCHED
33 THEN
34 INSERT (dst.TAX_YEAR,
35 dst.LEID,
36 dst.CDR_NO,
37 dst.DRE_REPORTING_PERIOD
38 )
39 VALUES (src.TAX_YEAR,
40 src.LEID,
41 src.CDR_NO,
42
43 src.DRE_REPORTING_PERIOD
44 );
45
46 end loop;
47 close l_cur;
48
49 END;
50 /
Before open cursor.
Before Fetch.
Test Roll.
PL/SQL procedure successfully completed.