Data Replication : CUST schema objects

As SYSTEM or a DBA user, we create the CUST tablespace

CREATE TABLESPACE CUST 
DATAFILE 'E:\install\21cXE\oradata\XE\XEPDB2\CUST\CUST.dbf' 
SIZE 50M AUTOEXTEND ON
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 524288 
SEGMENT SPACE MANAGEMENT AUTO;

We create the CUST schema.

CREATE USER CUST IDENTIFIED BY CUST;
GRANT CREATE SESSION TO CUST;
GRANT CONNECT TO CUST;
GRANT CREATE TABLE TO CUST;
GRANT CREATE PROCEDURE TO CUST;
GRANT CREATE ANY TYPE TO CUST;
GRANT CREATE ANY SEQUENCE TO CUST;
ALTER USER CUST DEFAULT TABLESPACE CUST;
ALTER USER CUST TEMPORARY TABLESPACE TEMP;
GRANT CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO CUST;
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO CUST;

--------------------------------------------------------
--  DDL for Table CURR_TRDE_DT
--------------------------------------------------------
--This table stores the current business date for GROWYOURMONEY bank systems
CREATE TABLE CUST.CURR_TRDE_DT 
   (	TRDE_DT DATE
   ) TABLESPACE CUST ;

GRANT SELECT ON CUST.CURR_TRDE_DT TO HR;   

INSERT INTO CURR_TRDE_DT VALUES ('13-NOV-24');

When creating the CUSTOMER table, there is a reference to the COUNTRIES and REGIONS tables in the HR schema. 

For this we need to give the following grants in the HR schema to CUST schema as below

GRANT SELECT, REFERENCES ON HR.COUNTRIES TO CUST;

GRANT SELECT, REFERENCES ON HR.REGIONS TO CUST;

--This table stores CUSTOMER data for the bank
DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER
(
  CUST_ID   NUMBER,
  CUST_NAME  VARCHAR2(100),
  PHONE_NUMBER	VARCHAR2(20 BYTE),
  EMAIL	VARCHAR2(30 BYTE),
  ADDR_LINE_1 VARCHAR2(500),
  ADDR_LINE_2 VARCHAR2(500),
  ADDR_LINE_3 VARCHAR2(500),
  COUNTRY_ID  CHAR(2 BYTE),
  REGION_ID   NUMBER,
  GENDER      VARCHAR2(20)
) TABLESPACE CUST;

ALTER TABLE CUSTOMER ADD CONSTRAINT PK_CUST PRIMARY KEY (CUST_ID);
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUST_CNTRY FOREIGN KEY (COUNTRY_ID) REFERENCES HR.COUNTRIES;
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUST_REGN FOREIGN KEY (REGION_ID) REFERENCES HR.REGIONS;

--This table stores the transaction data for the bank customers.
DROP TABLE DAILY_TRANSACTIONS;
CREATE TABLE DAILY_TRANSACTIONS
(
  TRANS_ID     NUMBER,
  TRANS_DATE   DATE,
  CUST_ID      NUMBER,
  TRANS_AMT    NUMBER,
  TRANS_CRNCY  VARCHAR2(3),
  TRANS_IND    VARCHAR2(10),
  ACCT_NO      VARCHAR2(50),
  BNK_NAME    VARCHAR2(100),
  BNK_LOCATION      VARCHAR2(100),
  BANK_IFSC    VARCHAR2(100)
) TABLESPACE CUST; 

ALTER TABLE DAILY_TRANSACTIONS ADD CONSTRAINT PK_TRANS PRIMARY KEY (TRANS_ID, TRANS_DATE); 
ALTER TABLE DAILY_TRANSACTIONS ADD CONSTRAINT FK_TRANS_CUST FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER;

--This table stores the rowid information for records inserted in MAIN tables
CREATE TABLE REPL_QUEUE 
   (	TBL_NAME VARCHAR2(100), 
	UNIQUE_ID VARCHAR2(100), 
	REPL_STAT VARCHAR2(10), 
	OPRTN VARCHAR2(10)
   ) TABLESPACE CUST ;

CREATE UNIQUE INDEX UI_UNIQ_ID ON REPL_QUEUE(UNIQUE_ID); 
GRANT SELECT, DELETE ON REPL_QUEUE TO REPLICA;

Create the triggers to insert data into the replication queue table to copy into replication schema.

CREATE OR REPLACE TRIGGER TR_REPL_QUEUE_CUSTOMER
AFTER INSERT OR UPDATE ON CUSTOMER
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
	IF INSERTING THEN
		BEGIN
			INSERT INTO REPL_QUEUE
			(
				TBL_NAME,
				UNIQUE_ID,
				REPL_STAT,
				OPRTN
			)
			VALUES
			(
			'CUSTOMER',
			:NEW.ROWID,
			'PNDNG',
			'INSERT'
			);
		EXCEPTION
		  WHEN DUP_VAL_ON_INDEX THEN
			NULL;
		END;
	ELSIF UPDATING THEN
		BEGIN
			INSERT INTO REPL_QUEUE
			(
				TBL_NAME,
				UNIQUE_ID,
				REPL_STAT,
				OPRTN
			)
			VALUES
			(
			'CUSTOMER',
			:NEW.ROWID,
			'PNDNG',
			'UPDATE'
			);
		EXCEPTION
		  WHEN DUP_VAL_ON_INDEX THEN
			NULL;
		END;
	END IF;
END TR_REPL_QUEUE_CUSTOMER;
/

CREATE OR REPLACE TRIGGER TR_REPL_QUEUE_DAILY_TRANSACTIONS
AFTER INSERT OR UPDATE ON DAILY_TRANSACTIONS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
	IF INSERTING THEN
		BEGIN
			INSERT INTO REPL_QUEUE
			(
				TBL_NAME,
				UNIQUE_ID,
				REPL_STAT,
				OPRTN
			)
			VALUES
			(
			'DAILY_TRANSACTIONS',
			:NEW.ROWID,
			'PNDNG',
			'INSERT'
			);
		EXCEPTION
		  WHEN DUP_VAL_ON_INDEX THEN
			NULL;
		END;
	ELSIF UPDATING THEN
		BEGIN
			INSERT INTO REPL_QUEUE
			(
				TBL_NAME,
				UNIQUE_ID,
				REPL_STAT,
				OPRTN
			)
			VALUES
			(
			'DAILY_TRANSACTIONS',
			:NEW.ROWID,
			'PNDNG',
			'UPDATE'
			);
		EXCEPTION
		  WHEN DUP_VAL_ON_INDEX THEN
			NULL;
		END;
	END IF;
END TR_REPL_QUEUE_DAILY_TRANSACTIONS;
/  

Scripts to insert data into CUSTOMER schema tables. Insert a sample record into the CUSTOMER and DAILY_TRANSACTIONS tables.

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
(
100001,
'NOVAK DJOKOVIC',
'+381 (0)11 3148648',
'djoko@gmail.com',
'Bulevar Arsenija Carnojevica 54a',
'11070 New Belgrade',
'Republic of Serbia',
'US',
2,
'MALE'
);

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
(
200001,
'09-NOV-2041',
100001,
25000,
'USD',
'CR',
'SRNVK3148648',
'BANK OF SERBIA',
'New Belgrade',
'SRBK0008956'
);

Procedure to pump dummy data for customers and their daily transactions.

create or replace PROCEDURE CUSTOMER_DATA_PUMP AS 
   CURSOR CUST
   IS
   SELECT * 
   FROM CUSTOMER
   WHERE CUST_ID = 100001;
   
   customer_id NUMBER ; 
   trans_id    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(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;
      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
        );
        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
                );
              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_DATA_PUMP;  
/

Run the procedure to insert the data

BEGIN
  ARCHIVE_MACHINE_DATA_PUMP();
  COMMIT; 
END;

Thought for the day

Do not reply on your wealth or say "I have enough" 

  Do not follow your inclination and strength in pursuing the desires of your heart 

Do not say "Who can have power over me?" 

  for the Lord will surely punish you

Sirach 5:1-3