Dynamic Source Table & Target Table [message #686549] |
Tue, 11 October 2022 04:57 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
Hi.
I have created PL/SQL below.
CREATE OR REPLACE PROCEDURE SRPSIT.Z_Z_S13018_BB_BILL_CHG (year_month in varchar2) as
--===================================================================================--
tbl_name VARCHAR2(36) := 'Z_S13018_BB_BILL_CHG_'||year_month;
src_name VARCHAR2(50) := 'BILL_136.BB_BILL_CHARGE_'||year_month;
sql_query VARCHAR2(4000);
sql_statement VARCHAR2(4000);
type REC_TYPE is record (
BILLCHARGE_ID B_BILL136_BB_BILL_CHG_202208.BILLCHARGE_ID%type,
HOT_SEQ B_BILL136_BB_BILL_CHG_202208.HOT_SEQ%type,
OBJ_ID B_BILL136_BB_BILL_CHG_202208.OBJ_ID%type
);
type REC_TYPES is table of REC_TYPE;
type REC_CURSOR is ref cursor;
ref_cursor REC_CURSOR;
recs REC_TYPES;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||tbl_name;
sql_query := 'select BILLCHARGE_ID, HOT_SEQ, OBJ_ID from '||src_name||'@sitcbs';
open ref_cursor for sql_query;
fetch ref_cursor bulk collect into recs;
close ref_cursor;
FOR ind IN recs.FIRST .. recs.LAST
loop
-- execute immediate 'insert into '||tbl_name||'(BILLCHARGE_ID, HOT_SEQ, OBJ_ID) values
-- (recs(ind).BILLCHARGE_ID, recs(ind).HOT_SEQ, recs(ind).OBJ_ID)'
-- ;
-- insert into Z_S13018_BB_BILL_CHG_202208 (BILLCHARGE_ID, HOT_SEQ, OBJ_ID) values
-- (recs(ind).BILLCHARGE_ID, recs(ind).HOT_SEQ, recs(ind).OBJ_ID)
-- ;
execute immediate 'insert into '||tbl_name||'(BILLCHARGE_ID, HOT_SEQ, OBJ_ID) values
(recs(ind).BILLCHARGE_ID, ecs(ind).HOT_SEQ, recs(ind).OBJ_ID)'
;
-- execute immediate sql_statement using
-- recs(ind).BILLCHARGE_ID, recs(ind).HOT_SEQ, recs(ind).OBJ_ID
-- ;
end loop;
COMMIT;
END;
/
But when I execute exec Z_Z_S13018_BB_BILL_CHG('202208'), it says
ORA-00904: "RECS": invalid identifier
ORA-06512: at "SRPSIT.Z_Z_S13018_BB_BILL_CHG", line 54
ORA-06512: at line 2
[/note]
Where do I gone wrong?
Thanks.
|
|
|
Re: Dynamic Source Table & Target Table [message #686550 is a reply to message #686549] |
Tue, 11 October 2022 05:01 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
Table code, same for src_tbl (source_table) & tbl_name (target table)
[/code]
CREATE TABLE Z_S13018_BB_BILL_CHG_202208 (
BILLCHARGE_ID NUMBER(20,0) ,
HOT_SEQ NUMBER(10,0) ,
BILL_CYCLE_ID VARCHAR2(90)
)
[/code]
By the way, forgot to mention, how can I change this part below *B_BILL136_BB_BILL_CHG_202208* to dynamic src_tbl?
type REC_TYPE is record (
BILLCHARGE_ID B_BILL136_BB_BILL_CHG_202208.BILLCHARGE_ID%type,
HOT_SEQ B_BILL136_BB_BILL_CHG_202208.HOT_SEQ%type,
OBJ_ID B_BILL136_BB_BILL_CHG_202208.OBJ_ID%type
);
Is it possible?
Thanks
|
|
|
|
|
Re: Dynamic Source Table & Target Table [message #686553 is a reply to message #686552] |
Tue, 11 October 2022 10:14 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Get rid of:
execute immediate 'insert into '||tbl_name||'(BILLCHARGE_ID, HOT_SEQ, OBJ_ID) values
(recs(ind).BILLCHARGE_ID, ecs(ind).HOT_SEQ, recs(ind).OBJ_ID)'
;
And use
execute immediate 'insert into '||tbl_name||'(BILLCHARGE_ID, HOT_SEQ, OBJ_ID) values
(:1,:2,:3)'
using recs(ind).BILLCHARGE_ID,ecs(ind).HOT_SEQ,recs(ind).OBJ_ID;
SY.
|
|
|
Re: Dynamic Source Table & Target Table [message #686557 is a reply to message #686553] |
Tue, 11 October 2022 20:13 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
That does it!! Thank you so much! Really appreciate it.
By the way, forgot to mention, how can I change this part below *B_BILL136_BB_BILL_CHG_202208* to dynamic variable=src_name VARCHAR2(50) := 'BILL_136.BB_BILL_CHARGE_'||year_month;?
type REC_TYPE is record (
BILLCHARGE_ID B_BILL136_BB_BILL_CHG_202208.BILLCHARGE_ID%type,
HOT_SEQ B_BILL136_BB_BILL_CHG_202208.HOT_SEQ%type,
OBJ_ID B_BILL136_BB_BILL_CHG_202208.OBJ_ID%type
);
Is it possible?
|
|
|
|
|