Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Source Table & Target Table
icon5.gif  Dynamic Source Table & Target Table [message #686549] Tue, 11 October 2022 04:57 Go to next message
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 Go to previous messageGo to next message
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 #686551 is a reply to message #686550] Tue, 11 October 2022 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68342
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 11 May 2021 09:47

...3/ Feedback in your previous topics
...
No feedback, no version, no help.

Re: Dynamic Source Table & Target Table [message #686552 is a reply to message #686551] Tue, 11 October 2022 06:48 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Sorry again.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Re: Dynamic Source Table & Target Table [message #686553 is a reply to message #686552] Tue, 11 October 2022 10:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3199
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 Go to previous messageGo to next message
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?
Re: Dynamic Source Table & Target Table [message #686565 is a reply to message #686557] Wed, 12 October 2022 11:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3199
Registered: January 2010
Location: Connecticut, USA
Senior Member
You can't. You need to either use dynamic SQL type 4 (DBMS_SQL package) or create multiple record type and use proper one depending on table name you are selecting from.

SY.
Re: Dynamic Source Table & Target Table [message #686577 is a reply to message #686565] Sat, 15 October 2022 11:56 Go to previous message
aimy
Messages: 225
Registered: June 2006
Senior Member
I see.

Noted.

Luckily the source structure will likely to maintain the same as existing.

Thanks so much again.
Previous Topic: Fine Grain Access Policy
Next Topic: bulk date update add months (merged)
Goto Forum:
  


Current Time: Mon Jan 30 21:32:26 CST 2023