Adding a new table to the replication engine

--DROP TABLE CUST.CUSTOMER_ACCOUNTS;
CREATE TABLE CUST.CUSTOMER_ACCOUNTS 
   (	
    CUST_ID    NUMBER,
	ACCNO      NUMBER, 
	ACCNAME    VARCHAR2(100 BYTE), 
	OPEN_DT    DATE, 
	ACCTYPE    VARCHAR2(10 BYTE)
   ) TABLESPACE CUST;

Insert sample data into CUSTOMER_ACCOUNTS table

INSERT INTO  CUSTOMER_ACCOUNTS VALUES (100001,  200001, 'NOVAK DJOKOVIC', '06-JAN-25','SAVINGS');  
INSERT INTO  CUSTOMER_ACCOUNTS VALUES (100001,  200002, 'NOVAK DJOKOVIC', '06-JAN-25','CURRENT');

GRANT SELECT ON CUST.CUSTOMER_ACCOUNTS TO REPLICA;

Register the new table with change notification existing registration id.

DECLARE
  P_REG_ID NUMBER;
  P_TABLE_NAME VARCHAR2(200);
BEGIN
  P_REG_ID := 602;
  P_TABLE_NAME := 'CUSTOMER_ACCOUNTS';

  REG_OBJ_WT_CN(
    P_REG_ID => P_REG_ID,
    P_TABLE_NAME => P_TABLE_NAME
  );
--rollback; 
END;

Verify the table is successfuly registered by querying user_change_notification_regs table. 

Changes for replication in REPLICA schema 

Create the CUSTOMER_ACCOUNTS table.

CREATE TABLE REPLICA.CUSTOMER_ACCOUNTS 
   (	
    CUST_ID    NUMBER,
	ACCNO      NUMBER, 
	ACCNAME    VARCHAR2(100 BYTE), 
	OPEN_DT    DATE, 
	ACCTYPE    VARCHAR2(10 BYTE),
	ARCHIVE_LOG_HEADER_ID	NUMBER(20,0),
	ARCHIVE_DATE	DATE

   ) TABLESPACE REPLICA;

Entry into ARCHIVE_RULES table in REPLICA schema for CUSTOMER_ACCOUNTS table

NUM_RULE_ID	TXT_RULE_DESC	TXT_RULE_QUERY	TXT_ACTIVE_FLG	TXT_ARCHIVE_TABLE	TXT_DELETE_TABLE	TXT_TBL_OWNER
100002	replicate the customer accounts data to REPLICA schema	SELECT UNIQUE_ID FROM CUST.REPL_QUEUE WHERE REPL_STAT = 'PNDNG' AND TBL_NAME = 'CUSTOMER_ACCOUNTS'	Y	CUSTOMER_ACCOUNTS	REPL_QUEUE	CUST

In the main schema create the procedure to bulk insert table into CUSTOMER_ACCOUNTS table

CREATE OR REPLACE PROCEDURE CUSTOMER_ACCT_DATA_PUMP AS 
   CURSOR CUST
   IS
   SELECT * 
   FROM CUSTOMER
   WHERE CUST_ID = 100001;
   
   customer_id NUMBER ; 
   trans_id    NUMBER ;
   sb_acct_no  NUMBER ;
   ca_acct_no  NUMBER ;
   
   DAY_COUNT CONSTANT INTEGER := 3;
   CUSTOMER_COUNT CONSTANT INTEGER := 5;
   TRANSACTION_COUNT INTEGER := 25;
   
   DAY_RESET_COUNT NUMBER:= 0;
   
   dt_trde_dt DATE;   
   CURSOR TRANS
   IS
   SELECT *
   FROM DAILY_TRANSACTIONS
   WHERE TRANS_ID = 200001;
BEGIN
  SELECT MAX(CUST_ID)
  INTO  customer_id
  FROM  CUSTOMER;
  
  SELECT MAX(ACCNO)
  INTO  sb_acct_no
  FROM  CUSTOMER_ACCOUNTS
  WHERE ACCTYPE = 'SAVINGS';
  
  SELECT MAX(ACCNO)
  INTO  ca_acct_no
  FROM  CUSTOMER_ACCOUNTS
  WHERE ACCTYPE = 'CURRENT';
  
  SELECT MAX(TRANS_ID)
  INTO  trans_id
  FROM  DAILY_TRANSACTIONS;
  
  SELECT TRDE_DT
  INTO dt_trde_dt
  FROM CURR_TRDE_DT;
  
FOR i IN 1 .. DAY_COUNT
LOOP
dt_trde_dt := dt_trde_dt + 1;
DAY_RESET_COUNT := DAY_RESET_COUNT + 1; 
--TRANSACTION_COUNT := TRANSACTION_COUNT * 1.05;
  UPDATE CURR_TRDE_DT
  SET
    TRDE_DT = dt_trde_dt;
  FOR rec IN CUST
  LOOP
    FOR i IN 1 .. CUSTOMER_COUNT
    LOOP
      customer_id := customer_id + 1;
      sb_acct_no := sb_acct_no + 1;
      ca_acct_no := ca_acct_no + 1;
      INSERT INTO CUSTOMER
        (
        CUST_ID,
        CUST_NAME,
        PHONE_NUMBER,
        EMAIL,
        ADDR_LINE_1,
        ADDR_LINE_2,
        ADDR_LINE_3,
        COUNTRY_ID,
        REGION_ID,
        GENDER
        )
        VALUES
        (
        customer_id,
        rec.CUST_NAME || '-' || customer_id,
        rec.PHONE_NUMBER,
        rec.EMAIL,
        rec.ADDR_LINE_1 || customer_id,
        rec.ADDR_LINE_2,
        rec.ADDR_LINE_3,
        rec.COUNTRY_ID,
        rec.REGION_ID,
        rec.GENDER
        );
        
        INSERT INTO  CUSTOMER_ACCOUNTS VALUES (customer_id,  sb_acct_no, rec.CUST_NAME || '-' || customer_id, dt_trde_dt,'SAVINGS'); 
        
        COMMIT;
        INSERT INTO  CUSTOMER_ACCOUNTS VALUES (customer_id,  ca_acct_no, rec.CUST_NAME || '-' || customer_id, dt_trde_dt,'CURRENT');
        COMMIT;
        
        FOR rec IN TRANS
        LOOP
              
              FOR i IN 1 .. TRANSACTION_COUNT
              LOOP
                trans_id := trans_id + 1;
                INSERT INTO DAILY_TRANSACTIONS
                (
                TRANS_ID,
                TRANS_DATE,
                CUST_ID,
                TRANS_AMT,
                TRANS_CRNCY,
                TRANS_IND,
                ACCT_NO,
                BNK_NAME,
                BNK_LOCATION,
                BANK_IFSC
                )
                VALUES
                (
                  trans_id,
                  dt_trde_dt,
                  customer_id,
                  rec.TRANS_AMT + trans_id,
                  rec.TRANS_CRNCY,
                  rec.TRANS_IND,
                  rec.ACCT_NO,
                  rec.BNK_NAME,
                  rec.BNK_LOCATION,
                  rec.BANK_IFSC
                );
                COMMIT;
              END LOOP;  --} transction 10 loop
        END LOOP;  --} transaction main loop
        COMMIT;
      END LOOP; --} customer 10 loop
    END LOOP; --} CUSTOMER MAIN LOOP  
    COMMIT;

END LOOP; --} END trade date loop
END CUSTOMER_ACCT_DATA_PUMP;

With all the setup in place we now simple have to start the data pump and the replication engine will automatically replicate the data from the new table also into the replication schema.

BEGIN
  CUSTOMER_ACCT_DATA_PUMP();
  COMMIT; 
END;

If we now query the CUSTOMER_ACCOUNTS table in the REPLICA schema it will show all the data replicated in this schema.

Thought for the day. 

  Blessings of Wisdom

My child, from your youth choose discipline,

  and when you have gray hair you will still find wisdom.

Come to her like one who plows and sows

  and wait for her good harvest

For when you cultivate her you will toil but little

  and soon you will eat of her produce.

She seems very harsh to the undisciplined

  fools cannot remain with her

She will be like a heavy stone to test them

  and they will not delay in casting her aside

For wisdom is like her name

  She is not readily perceived by many

Sirach 6:18-22