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 Go to next message
aimy
Messages: 219
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 #683383 is a reply to message #683382] Wed, 06 January 2021 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes you can, you have use EXECUTE IMMEDIATE for this (as you do it for TRUNCATE).

Re: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter [message #683384 is a reply to message #683383] Wed, 06 January 2021 14:13 Go to previous messageGo to next message
aimy
Messages: 219
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 #683385 is a reply to message #683384] Wed, 06 January 2021 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know which line is line 47, so can't help.

Execute each time you change the code:
select line, text from dba_source where owner='SRPETL' and name='Z_MIE_TEST_3' and type='PROCEDURE' order by line;

This:
Quote:
execute immediate 'SRC:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@'||db_link;
should be:
execute immediate 'begin :src := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@'||db_link||'; end;' using out SRC;
Read EXECUTE IMMEDIATE in your version of PL/SQL Language Reference manual (version you should ALWAYS post in ALL your questions).

Re: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter [message #683390 is a reply to message #683385] Thu, 07 January 2021 05:08 Go to previous messageGo to next message
aimy
Messages: 219
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 #683395 is a reply to message #683390] Thu, 07 January 2021 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67743
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But now it throws error after executed.
Of course, why these '' after FALSE???
Also SRC is a variable of the procedure so it has to be turned to a bind variable like RET and included in USING clause but as an IN parameter.

Please, once again, read the documentation about this statement.
Please, once again and for the last time, post your Oracle version, with 4 decimals.

Re: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter [message #683400 is a reply to message #683395] Thu, 07 January 2021 15:32 Go to previous messageGo to next message
aimy
Messages: 219
Registered: June 2006
Senior Member
Thanks.

Sorry, my Oracle SQL is SQL*Plus: Release 11.2.0.1.0

I'll try again.

Thanks Mic.
Re: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter [message #683401 is a reply to message #683400] Thu, 07 January 2021 15:43 Go to previous messageGo to next message
aimy
Messages: 219
Registered: June 2006
Senior Member
sorry but I got lost here..

execute immediate 'begin :RET := DBMS_HS_PASSTHROUGH.FETCH_ROW@'||db_link||'(SRC,FALSE);'' end;' using in SRC;
I got confused where to apply this guide https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems017.htm

Is it select into? And how do I pun SRC as IN and RET as out altogether?

Thanks.
Re: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter [message #683402 is a reply to message #683401] Fri, 08 January 2021 00:35 Go to previous message
Michel Cadot
Messages: 67743
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?


Previous Topic: opening and closing balance
Next Topic: Substring Query in Oracle SQL
Goto Forum:
  


Current Time: Thu Feb 25 07:36:21 CST 2021