Main HR schema objects for data replications
As SYSTEM user or a DBA user, create the SYSTEM tablespace and user.
CREATE TABLESPACE HR
DATAFILE 'E:\install\21cXE\oradata\XE\XEPDB2\HR\HR.dbf'
SIZE 50M AUTOEXTEND ON
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER HR IDENTIFIED BY HR;
ALTER USER HR DEFAULT TABLESPACE HR QUOTA UNLIMITED ON HR;
ALTER USER HR TEMPORARY TABLESPACE TEMP;
GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO HR;
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO HR;
Create the replication queue table to insert data to be replicated in REPLICA schema
CREATE TABLE HR.REPL_QUEUE
( TBL_NAME VARCHAR2(100 BYTE),
UNIQUE_ID VARCHAR2(100 BYTE),
REPL_STAT VARCHAR2(10 BYTE),
OPRTN VARCHAR2(10 BYTE)
) TABLESPACE HR;
CREATE UNIQUE INDEX UI_UNIQ_ID ON REPL_QUEUE(UNIQUE_ID);
GRANT SELECT, DELETE ON REPL_QUEUE TO REPLICA;
CREATE OR REPLACE TRIGGER TR_REPL_QUEUE_EMPLOYEES
AFTER INSERT OR UPDATE ON EMPLOYEES
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
(
'EMPLOYEES',
: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
(
'EMPLOYEES',
:NEW.ROWID,
'PNDNG',
'UPDATE'
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL;
END;
END IF;
END TR_REPL_QUEUE_EMPLOYEES;
/
CREATE OR REPLACE TRIGGER TR_REPL_QUEUE_JOB_HISTORY
AFTER INSERT OR UPDATE ON JOB_HISTORY
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
(
'JOB_HISTORY',
: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
(
'JOB_HISTORY',
:NEW.ROWID,
'PNDNG',
'UPDATE'
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL;
END;
END IF;
END TR_REPL_QUEUE_JOB_HISTORY;
/
Once the objects are created we run a script EMPLOYEE_DATA_PUMP to pump dummy data into the EMPLOYEES and JOB_HISTORY tables.
create or replace PROCEDURE EMPLOYEE_DATA_PUMP
AS
CURSOR C1
IS
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
DAY_COUNT CONSTANT INTEGER := 3;
DAY_RESET_COUNT NUMBER:= 0;
dt_trde_dt DATE;
dt_start_dt DATE;
v_employee_id NUMBER := 300;
v_commit_cnt NUMBER := 0;
BEGIN
SELECT TRDE_DT
INTO dt_trde_dt
FROM CUST.CURR_TRDE_DT;
FOR i IN 1 .. DAY_COUNT
LOOP
dt_trde_dt := dt_trde_dt + 1;
dt_start_dt := dt_trde_dt;
FOR i IN 1 .. 100
LOOP --{
v_employee_id := v_employee_id + 1;
v_commit_cnt := v_commit_cnt + 1;
INSERT INTO EMPLOYEES
(
EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID
)
SELECT
v_employee_id,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
dt_trde_dt,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
FOR i IN 1 .. DAY_COUNT
LOOP --{
dt_start_dt := dt_start_dt + 1;
INSERT INTO JOB_HISTORY
(
EMPLOYEE_ID,
START_DATE,
END_DATE,
JOB_ID,
DEPARTMENT_ID
)
SELECT
v_employee_id,
dt_start_dt ,
END_DATE,
JOB_ID,
DEPARTMENT_ID
FROM JOB_HISTORY
WHERE EMPLOYEE_ID = 114;
END LOOP; --} end job history loop
IF v_commit_cnt = 1000
THEN
COMMIT;
v_commit_cnt := 0;
END IF;
END LOOP ; --} -- end customer loop
END LOOP; --} END trade date loop
END;
/
BEGIN
EMPLOYEE_DATA_PUMP();
COMMIT;
--rollback;
END;
/
Additional Tables in HR schema
Create the accounts table and insert data into it.CREATE TABLE accounts (
accno NUMBER,
accname VARCHAR2(20),
open_dt DATE,
acctype VARCHAR2(1)
);
INSERT INTO accounts values (101,'John Smith', SYSDATE - 500, 'S');
INSERT INTO accounts values (102,'Jane Smith', SYSDATE - 400, 'S');
INSERT INTO accounts values (103,'John Doe', SYSDATE - 300, 'C');
INSERT INTO accounts values (104,'John Doe', SYSDATE - 450, 'S');
INSERT INTO accounts values (105,'Jane Doe', SYSDATE - 200, 'C');
CREATE TABLE transactions
(
trans_id NUMBER,
accno NUMBER,
trans_type VARCHAR2(1),
trans_dt DATE,
trans_amt NUMBER,
trans_mode VARCHAR2(20)
);
INSERT INTO transactions VALUES (1,101, 'D', SYSDATE - 100, 1000, 'Check');
INSERT INTO transactions VALUES (2,101, 'D', SYSDATE - 150, 2000, 'ATM');
INSERT INTO transactions VALUES (3,101, 'W', SYSDATE - 90, 1500, 'Transfer');
INSERT INTO transactions VALUES (4,102, 'D', SYSDATE - 100, 1200, 'Check');
INSERT INTO transactions VALUES (5,102, 'W', SYSDATE - 200, 1100, 'Check');
INSERT INTO transactions VALUES (6,103, 'D', SYSDATE - 150, 2000, 'ATM');
INSERT INTO transactions VALUES (7,103, 'D', SYSDATE - 120, 2500, 'Check');
INSERT INTO transactions VALUES (8,103, 'W', SYSDATE - 80, 1000, 'Check');
CREATE TABLE TDS_INFO
(
ACCNO NUMBER,
TDS_DATE DATE,
TDS_AMOUNT NUMBER
) TABLESPACE HR ;
Insert into TDS_INFO (ACCNO,TDS_DATE,TDS_AMOUNT) values (101,to_date('01-JAN-2025 12:32:37','DD-MON-YYYY HH24:MI:SS'),100);
Insert into TDS_INFO (ACCNO,TDS_DATE,TDS_AMOUNT) values (101,to_date('01-FEB-2025 12:33:13','DD-MON-YYYY HH24:MI:SS'),105);
Insert into TDS_INFO (ACCNO,TDS_DATE,TDS_AMOUNT) values (101,to_date('01-MAR-2025 12:33:35','DD-MON-YYYY HH24:MI:SS'),110);
Create Test Data for JSON_TRANSFORM
CREATE TABLE HR.TENNIS_STATS
( ID NUMBER,
FIRST_"name" VARCHAR2(100 BYTE),
LAST_"name" VARCHAR2(100 BYTE),
WORLD_RANKING NUMBER,
PLAYER_DATA CLOB
) TABLESPACE HR ;
--DELETE FROM TENNIS_STATS;
INSERT INTO TENNIS_STATS VALUES
(10001, 'Novak', 'Djokovic', 1, '{
"name": "Novak Djokovic",
"age": "38",
"hobbies": ["tennis", "hiking", "swimming"],
"address": {
"street": "123 Main St",
"city": "Anytown"
},
"isStudent": false,
"height": "6.1"
}');
INSERT INTO TENNIS_STATS VALUES
(10002, 'Rafael', 'Nadal', 2, '{
"name": "Rafael Nadal",
"age": "39",
"hobbies": ["tennis", "football", "swimming"],
"address": {
"street": "445 Side St",
"city": "Chinatown"
},
"isStudent": false,
"height": "5.11"
}');
INSERT INTO TENNIS_STATS VALUES
(10003, 'Roger', 'Federer', 3, '{
"name": "Roger Federer",
"age": "41",
"hobbies": ["tennis", "football", sking],
"address": {
"street": "326 Back St",
"city": "Swisstown"
},
"isStudent": false,
"height": "6.2"
}');
INSERT INTO TENNIS_STATS VALUES
(10004, 'Carlos', 'Alcaraz', 4, '{
"name": "Carlos Alcaraz",
"age": "22",
"hobbies": ["tennis", "football", trekking],
"address": {
"street": "553 Upper St",
"city": "Uptown"
},
"isStudent": false,
"height": "6.01"
}');
INSERT INTO TENNIS_STATS VALUES
(10005, 'Boris', 'Becker', 5, '{
"name": "Boris Becker",
"age": "55",
"hobbies": ["tennis", "football", commentry],
"address": {
"street": 725 Down St,
"city": "Downtown"
},
"isStudent": false,
"height": "6.05"
}');
SELECT individual elements from a JSON having individual elements, arrays and objects using JSON_VALUE
select JSON_VALUE(PLAYER_DATA,'$.name'),
JSON_VALUE(PLAYER_DATA,'$.age'),
JSON_VALUE(PLAYER_DATA,'$.hobbies[0]'),
JSON_VALUE(PLAYER_DATA,'$.hobbies[1]'),
JSON_VALUE(PLAYER_DATA,'$.hobbies[2]'),
JSON_VALUE(PLAYER_DATA,'$.address.street'),
JSON_VALUE(PLAYER_DATA,'$.address.city'),
JSON_VALUE(PLAYER_DATA,'$.isStudent'),
JSON_VALUE(PLAYER_DATA,'$.height')
from TENNIS_STATS
where id = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic'
AND JSON_VALUE(PLAYER_DATA,'$.hobbies[1]') = 'hiking'
AND JSON_VALUE(PLAYER_DATA,'$.address.city') = 'Anytown';
| NAME | AGE | HOBBY_1 | HOBBY_2 | HOBBY_3 | ADDRESS_STREET | ADDRESS_CITY | IS_STUDENT | HEIGHT |
|---|---|---|---|---|---|---|---|---|
| Novak Djokovic | 38 | tennis | hiking | swimming | 123 Main St | Anytown | false | 6.1 |
Thought for the day
Do not delay to turn back to the Lord,
and do not postpone it from day to day.
For suddenly the wrath of the Lord
will come upon you.
And at the time of punishment
you will perish.
Do not depend on dishonest wealth,
For it will not benefit you on
the day of calamity.
Sirach 5:7-8
No comments:
Post a Comment