Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_HS_PASSTHROUGH - Database Link as Input Parameter
DBMS_HS_PASSTHROUGH - Database Link as Input Parameter [message #683382] |
Wed, 06 January 2021 13:05 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
CREATE OR REPLACE PROCEDURE SRPSTL.Z_MIE_TEST_3 (schema_name in varchar2, db_link in varchar2) as
/*
To populate data to Z_MIE_B_USR112_BC_ACCT from USR_112.BC_ACCT@UATCBS
exec SRPSTL.SP_GRANT_TAB('Z_MIE_B_USR112_BC_ACCT');
Notes:
1st creation - Friday, 18 December 2020 2:35:00 PM
*/
SRC BINARY_INTEGER;
RET BINARY_INTEGER;
ACCT_ID NUMBER (20);
CUST_ID NUMBER (20);
U_CUST_ID NUMBER (20);
ACCT_CODE VARCHAR2 (288);
ACCT_NAME VARCHAR2 (512);
ACCT_TYPE VARCHAR2 (9);
STATUS_TIME DATE;
CREATE_OPER_ID NUMBER (20);
CREATE_TIME DATE;
row_read number(10):=0;
row_update number(10):=0;
row_insert number(10):=0;
row_delete number(10):=0;
row_reject number(10):=0;
i number:=0;
j number:=0;
v_ErrorCode number;
v_Errortext varchar2(200);
start_time date := sysdate;
script VARCHAR2(123) := '/EDWH-DMT04/SRPETL/script/UAT/CBS/Daily/Truncate/load_B_USR112.sh';
proc_name VARCHAR2(36) := 'Z_MIE_TEST_3';
tbl_name VARCHAR2(36) := 'Z_MIE_B_USR112_BC_ACCT';
src_name VARCHAR2(36) := 'USR_112.BC_ACCT';
subject_area VARCHAR2(45) := 'PROD '||db_link;
frequency VARCHAR2(50) := 'DAILY';
method VARCHAR2(50) := 'TRUNCATE';
custom_text VARCHAR2(45) := schema_name;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE Z_MIE_B_'||schema_name||'_BC_ACCT';
SRC:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@uatCBS;
DBMS_HS_PASSTHROUGH.PARSE@uatCBS(SRC,
'select
ACCT_ID, CUST_ID, ACCT_CODE, ACCT_NAME, ACCT_TYPE,
sTATUS_TIME, CREATE_OPER_ID, CREATE_TIME,
(select count(*) from USR_112.BC_ACCT) as row_count
from
USR_112.BC_ACCT'
);
BEGIN
-- DBMS_OUTPUT.ENABLE(1000000);
RET:=0;
WHILE(TRUE)
LOOP
RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@uatCBS(SRC,FALSE);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,1,ACCT_ID);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,2,CUST_ID);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,3,ACCT_CODE);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,4,ACCT_NAME);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,5,ACCT_TYPE);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,6,STATUS_TIME);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,7,CREATE_OPER_ID);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,8,CREATE_TIME);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,9,row_read);
INSERT INTO Z_MIE_B_USR112_BC_ACCT
(ACCT_ID,
CUST_ID,
ACCT_CODE,
ACCT_NAME,
ACCT_TYPE,
STATUS_TIME,
CREATE_OPER_ID,
CREATE_TIME)
VALUES
(ACCT_ID,
CUST_ID,
ACCT_CODE,
ACCT_NAME,
ACCT_TYPE,
STATUS_TIME,
CREATE_OPER_ID,
CREATE_TIME)
;
row_insert := row_insert + 1;
END LOOP;
-- commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('END OF FETCH');
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@uatCBS(SRC);
END;
END;
insert into z_TRACKING_LOG
(SCRIPT_NAME, FREQUENCY, PROCEDURE_NAME, SOURCE_TABLE, TABLE_NAME, METHOD, ROWS_READ, ROWS_INSERTED, ROWS_UPDATED, ROWS_DELETED, ROWS_INSERTED_BDEL,
ROWS_INSERTED_ADEL, ROWS_REJECTED, START_TIME, END_TIME, ELAPSE_TIME, REMARKS, STATUS, SUBJECT_AREA)
values
(script, frequency, proc_name||replace(decode(custom_text,null,null,' ('||custom_text||')'),' ()'), src_name,
tbl_name, method, row_read, row_insert, row_update, row_delete, NULL, NULL, row_reject, start_time, sysdate, (sysdate-start_time)* 86400,
v_ErrorText, 'SUCCESS', subject_area);
COMMIT;
END;
/
How can I pass db_link to replace all the @UATCBS
I have tried many ways but to no avail.
Is it possible for me to achieve this?
Reason-wise because there are too many db links for the same table that need to be executed.
Thank you.
|
|
|
|
Re: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter [message #683384 is a reply to message #683383] |
Wed, 06 January 2021 14:13 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
Thanks Mic.
But I got this error:
exec Z_MIE_TEST_3('USR_121','CBSUSR01');
ORA-00900: invalid SQL statement
ORA-06512: at "SRPETL.Z_MIE_TEST_3", line 47
ORA-06512: at line 2
CREATE OR REPLACE PROCEDURE SRPETL.Z_MIE_TEST_3 (schema_name in varchar2, db_link in varchar2) as
/*
To populate data to Z_MIE_B_USR121_BC_ACCT from USR_121.BC_ACCT@CBSUSR01
exec SRPETL.SP_GRANT_TAB('Z_MIE_B_USR121_BC_ACCT');
Notes:
1st creation - Thursday, 7 January, 2021 04:01:43 AM
*/
SRC BINARY_INTEGER;
RET BINARY_INTEGER;
ACCT_ID NUMBER (20);
CUST_ID NUMBER (20);
U_CUST_ID NUMBER (20);
ACCT_CODE VARCHAR2 (288);
ACCT_NAME VARCHAR2 (512);
ACCT_TYPE VARCHAR2 (9);
STATUS_TIME DATE;
CREATE_OPER_ID NUMBER (20);
CREATE_TIME DATE;
row_read number(10):=0;
row_update number(10):=0;
row_insert number(10):=0;
row_delete number(10):=0;
row_reject number(10):=0;
i number:=0;
j number:=0;
v_ErrorCode number;
v_Errortext varchar2(200);
start_time date := sysdate;
script VARCHAR2(123) := '/EDWH-DMT04/SRPETL/script/UAT/CBS/Daily/Truncate/load_B_USR121.sh';
proc_name VARCHAR2(36) := 'Z_MIE_TEST_3';
tbl_name VARCHAR2(36) := 'Z_MIE_B_USR121_BC_ACCT';
src_name VARCHAR2(36) := 'USR_121.BC_ACCT';
subject_area VARCHAR2(45) := 'PROD '||db_link;
frequency VARCHAR2(50) := 'DAILY';
method VARCHAR2(50) := 'TRUNCATE';
custom_text VARCHAR2(45) := schema_name;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE Z_MIE_B_'||replace(schema_name,'_')||'_BC_ACCT';
execute immediate 'SRC:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@'||db_link;
DBMS_HS_PASSTHROUGH.PARSE@CBSUSR01(SRC,
'select
ACCT_ID, CUST_ID, ACCT_CODE, ACCT_NAME, ACCT_TYPE,
sTATUS_TIME, CREATE_OPER_ID, CREATE_TIME,
(select count(*) from '||schema_name||'.BC_ACCT) as row_count
from'
||schema_name||''
);
BEGIN
-- DBMS_OUTPUT.ENABLE(1000000);
RET:=0;
WHILE(TRUE)
LOOP
RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@CBSUSR01(SRC,FALSE);
DBMS_HS_PASSTHROUGH.GET_VALUE@CBSUSR01(SRC,1,ACCT_ID);
DBMS_HS_PASSTHROUGH.GET_VALUE@CBSUSR01(SRC,2,CUST_ID);
DBMS_HS_PASSTHROUGH.GET_VALUE@CBSUSR01(SRC,3,ACCT_CODE);
DBMS_HS_PASSTHROUGH.GET_VALUE@CBSUSR01(SRC,4,ACCT_NAME);
DBMS_HS_PASSTHROUGH.GET_VALUE@CBSUSR01(SRC,5,ACCT_TYPE);
DBMS_HS_PASSTHROUGH.GET_VALUE@CBSUSR01(SRC,6,STATUS_TIME);
DBMS_HS_PASSTHROUGH.GET_VALUE@CBSUSR01(SRC,7,CREATE_OPER_ID);
DBMS_HS_PASSTHROUGH.GET_VALUE@CBSUSR01(SRC,8,CREATE_TIME);
DBMS_HS_PASSTHROUGH.GET_VALUE@CBSUSR01(SRC,9,row_read);
INSERT INTO Z_MIE_B_USR121_BC_ACCT
(ACCT_ID,
CUST_ID,
ACCT_CODE,
ACCT_NAME,
ACCT_TYPE,
STATUS_TIME,
CREATE_OPER_ID,
CREATE_TIME)
VALUES
(ACCT_ID,
CUST_ID,
ACCT_CODE,
ACCT_NAME,
ACCT_TYPE,
STATUS_TIME,
CREATE_OPER_ID,
CREATE_TIME)
;
row_insert := row_insert + 1;
END LOOP;
-- commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('END OF FETCH');
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@CBSUSR01(SRC);
END;
END;
insert into z_TRACKING_LOG
(SCRIPT_NAME, FREQUENCY, PROCEDURE_NAME, SOURCE_TABLE, TABLE_NAME, METHOD, ROWS_READ, ROWS_INSERTED, ROWS_UPDATED, ROWS_DELETED, ROWS_INSERTED_BDEL,
ROWS_INSERTED_ADEL, ROWS_REJECTED, START_TIME, END_TIME, ELAPSE_TIME, REMARKS, STATUS, SUBJECT_AREA)
values
(script, frequency, proc_name||replace(decode(custom_text,null,null,' ('||custom_text||')'),' ()'), src_name,
tbl_name, method, row_read, row_insert, row_update, row_delete, NULL, NULL, row_reject, start_time, sysdate, (sysdate-start_time)* 86400,
v_ErrorText, 'SUCCESS', subject_area);
COMMIT;
END;
/
Thanks.
|
|
|
|
Re: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter [message #683390 is a reply to message #683385] |
Thu, 07 January 2021 05:08 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
Thanks a lot Mic.
Sorry forgot to mentioned which line.
But YES, it's the one that you have corrected:
execute immediate 'begin :src := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@'||db_link||'; end;' using out SRC;
Yes it works!! Thanks so much for this new lesson for me. Really appreciate it.
But how am I going to deal with this part then? Since it is not a variable and got some other SQL text involving the single quote (') as well.
DBMS_HS_PASSTHROUGH.PARSE@CBSUSR01(SRC,
'select
ACCT_ID, CUST_ID, ACCT_CODE, ACCT_NAME, ACCT_TYPE,
sTATUS_TIME, CREATE_OPER_ID, CREATE_TIME,
(select count(*) from '||src_name||') as row_count
from '||src_name||'')
;
And what about this part..
RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@CBSUSR01(SRC,FALSE);
I've changed to this
execute immediate 'begin :RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@'||db_link||'(SRC,FALSE)''; end;' using out RET;
But now it throws error after executed.
ORA-06550: line 1, column 63:
PLS-00103: Encountered the symbol "; end;" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || mul
Appreciate some help.
Thanks a lot.
|
|
|
|
|
|
Re: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter [message #683402 is a reply to message #683401] |
Fri, 08 January 2021 00:35 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Why do you read a 10.1 documentation if you are in 11.2?
11.2 documentation is there.
Anyway,
- "Is it select into?", I return you the question, is your a statement a SELECT?
- "And how do I pun SRC as IN and RET as out altogethe", just read the link you posted: "USING ... Specifies a list of input and/or output bind arguments."
EXECUTE_IMMEDIATE dynamic_string
[ INTO { define_variable [, define_variable ...] | record_name } ]
[ USING [ IN | OUT | IN OUT ] bind_argument [, [ IN | OUT | IN OUT ] bind_argument] ... ]
[ {RETURNING | RETURN } INTO bind_argument [, bind_argument]... ];
What can I say more?
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:41:12 CDT 2024
|