CREATE OR REPLACE PROCEDURE SRPSTL.Z_MIE_TEST_3 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) := 'UAT CBS';
frequency VARCHAR2(50) := 'DAILY';
method VARCHAR2(50) := 'TRUNCATE';
custom_text VARCHAR2(45) := null;

BEGIN

    EXECUTE IMMEDIATE 'TRUNCATE TABLE Z_MIE_B_USR112_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;
/

SQL> desc Z_MIE_B_USR112_BC_ACCT
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACCT_ID                                   NOT NULL NUMBER(20)
 CUST_ID                                   NOT NULL NUMBER(20)
 ACCT_CODE                                 NOT NULL VARCHAR2(288)
 ACCT_NAME                                          VARCHAR2(4000)
 ACCT_TYPE                                          VARCHAR2(9)
 STATUS_TIME                               NOT NULL DATE
 CREATE_OPER_ID                                     NUMBER(20)
 CREATE_TIME                                        DATE
 LOADING_DATE                                       DATE

MySQL [(none)]> desc usr_112.BC_ACCT
    -> ;
+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| ACCT_ID             | bigint(20)   | NO   | PRI | NULL    |       |
| CUST_ID             | bigint(20)   | NO   | MUL | NULL    |       |
| ACCT_NAME           | varchar(512) | YES  |     | NULL    |       |
| ACCT_TYPE           | varchar(1)   | YES  |     | NULL    |       |
| STATUS_TIME         | datetime     | NO   |     | NULL    |       |
| CREATE_OPER_ID      | bigint(20)   | YES  |     | NULL    |       |
| CREATE_TIME         | datetime     | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+