Generate JSON data from normalized relational schema using triggers

View All Scripts Login to Run Script

Script Name Generate JSON data from normalized relational schema using triggers
Description Generate JSON data from normalized relational schema using triggers
Area SQL General / JSON
Contributor Beda Hammerschmidt (Oracle)
Created Thursday December 10, 2020

Statement 1

alter session set NLS_DATE_FORMAT='YYYY-MM-DD'

Statement 2

alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD"T"HH24:MI:SS.FF'

Statement 3
```
drop trigger SALES_ORDERS_TRIG
```
Statement 4
```
drop table SALES_ORDER_LINES
```
Statement 5
```
drop table SALES_ORDERS
```
Statement 6
```
drop table ADDRESSES
```
Statement 7
```
drop table CUSTOMERS
```
Statement 8
```
drop table PRODUCTS
```
Statement 9
```
drop table SALES_DOCUMENTS
```

Statement 10

create table PRODUCTS
(
  SKU            varchar2(30)   not null,
  PRICE          number         not null,
  DESCRIPTION    varchar2(100),
  constraint PRODUCTS_PK primary key (SKU)
)

Statement 11

create table CUSTOMERS
(
  CUSTOMER_ID    number         not null,
  CUSTOMER_NAME  varchar2(100)  not null,
  constraint CUSTOMERS_PK primary key (CUSTOMER_ID)
)

Statement 12

create table ADDRESSES
(
  ADDRESS_ID     number         not null,
  CUSTOMER_ID    number         not null,
  STREET         varchar2(100),
  CITY           varchar2(30),
  PROVINCE       varchar2(30),
  ZIP_CODE       number,
  constraint ADDRESS_PK primary key (ADDRESS_ID),
  constraint ADDRESS_FK1 foreign key (CUSTOMER_ID)
                          references CUSTOMERS (CUSTOMER_ID)
)

Statement 13

create table SALES_ORDERS
(
  ORDER_ID       number         not null,
  CUSTOMER_ID    number         not null,
  ORDER_DATE     date           not null,
  SHIP_ADDRESS   number         not null,
  constraint SALES_ORDERS_PK primary key (ORDER_ID),
  constraint SALES_ORDERS_FK1 foreign key (CUSTOMER_ID)
                               references CUSTOMERS (CUSTOMER_ID),
  constraint SALES_ORDERS_FK2 foreign key (SHIP_ADDRESS)
                               references ADDRESSES (ADDRESS_ID)
)

Statement 14

create table SALES_ORDER_LINES
(
  ORDER_ID       number         not null,
  SKU            varchar2(30),
  QUANTITY       number         not null,
  constraint SALES_ORDER_LINES_PK primary key (ORDER_ID, SKU),
  constraint SALES_ORDER_LINES_FK1 foreign key (ORDER_ID)
                                    references SALES_ORDERS (ORDER_ID),
  constraint SALES_ORDER_LINES_FK2 foreign key (SKU)
                                    references PRODUCTS (SKU)
)

Statement 15

create table SALES_DOCUMENTS
(
  STAMP timestamp not null,
  DOC   clob,
  check (DOC is json)
)

Statement 16

create or replace trigger SALES_ORDERS_TRIG
  after update on SALES_ORDERS for each row
declare
  ID number := :NEW.ORDER_ID;
begin
  insert into SALES_DOCUMENTS(STAMP, DOC)
  select SYS_EXTRACT_UTC(SYSTIMESTAMP),
         JSON_OBJECT('id'        value :NEW.ORDER_ID,
                     'customer'  value C.CUSTOMER_NAME,
                     'orderDate' value :NEW.ORDER_DATE,
                     'address'   value JSON_OBJECT('street'  value A.STREET,
                                                   'city'    value A.CITY,
                                                   'state'   value A.PROVINCE,
                                                   'zipCode' value A.ZIP_CODE),
                     'lines'     value X.LINES returning clob)
  from CUSTOMERS    C,
       ADDRESSES    A,
       (select JSON_ARRAYAGG(JSON_OBJECT('sku'         value L.SKU,
                                         'description' value P.DESCRIPTION,
                                         'quantity'    value L.QUANTITY,
                                         'price'       value P.PRICE)
                             order by L.SKU returning clob) LINES
           from SALES_ORDER_LINES L,
                PRODUCTS          P
          where L.ORDER_ID = :NEW.ORDER_ID
            and L.SKU      = P.SKU
          group by L.ORDER_ID) X
 where C.CUSTOMER_ID = :NEW.CUSTOMER_ID
   and A.ADDRESS_ID  = :NEW.SHIP_ADDRESS;
end;

Statement 17

insert into PRODUCTS(SKU, PRICE, DESCRIPTION)
     values ('TP-1001', 19.99, 'Toilet Paper')

Statement 18

insert into PRODUCTS(SKU, PRICE, DESCRIPTION)
     values ('PT-1002', 9.95, 'Paper Towels')

Statement 19
```
commit
```

Statement 20

insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME)
     values (2001, 'John Doe')

Statement 21

insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME)
     values (2002, 'Jane Smith')

Statement 22
```
commit
```

Statement 23

insert into ADDRESSES(ADDRESS_ID, CUSTOMER_ID,
                      STREET, CITY, PROVINCE, ZIP_CODE)
     values (3001, 2001, '123 Main Street', 'Bedrock', 'CA', 90210)

Statement 24

insert into ADDRESSES(ADDRESS_ID, CUSTOMER_ID,
                      STREET, CITY, PROVINCE, ZIP_CODE)
     values (3002, 2002, '456 Rocky Road', 'Bedrock', 'CA', 90210)

Statement 25

insert into ADDRESSES(ADDRESS_ID, CUSTOMER_ID,
                      STREET, CITY, PROVINCE, ZIP_CODE)
     values (3003, 2001, '789 Lois Lane', 'Bedrock', 'MA', 12345)

Statement 26
```
commit
```

Statement 27

select json_serialize(doc pretty) from SALES_DOCUMENTS

Statement 28

insert into SALES_ORDERS(ORDER_ID, CUSTOMER_ID, ORDER_DATE, SHIP_ADDRESS)
     values (9001, 2001, SYSDATE, 3001)

Statement 29

insert into SALES_ORDER_LINES(ORDER_ID, SKU, QUANTITY)
     values (9001, 'TP-1001', 10)

Statement 30

update SALES_ORDERS set ORDER_DATE = SYSDATE where ORDER_ID = 9001

Statement 31
```
commit
```

Statement 32

select json_serialize(doc pretty) from SALES_DOCUMENTS

Statement 33

insert into SALES_ORDERS(ORDER_ID, CUSTOMER_ID, ORDER_DATE, SHIP_ADDRESS)
     values (9002, 2001, SYSDATE, 3001)

Statement 34

insert into SALES_ORDER_LINES(ORDER_ID, SKU, QUANTITY)
     values (9002, 'TP-1001', 20)

Statement 35

update SALES_ORDERS set ORDER_DATE = SYSDATE where ORDER_ID = 9002

Statement 36
```
commit
```

Statement 37

select json_serialize(doc pretty) from SALES_DOCUMENTS

Statement 38

insert into SALES_ORDERS(ORDER_ID, CUSTOMER_ID, ORDER_DATE, SHIP_ADDRESS)
     values (9003, 2001, SYSDATE, 3003)

Statement 39

insert into SALES_ORDER_LINES(ORDER_ID, SKU, QUANTITY)
     values (9003, 'TP-1001', 10)

Statement 40

insert into SALES_ORDER_LINES(ORDER_ID, SKU, QUANTITY)
     values (9003, 'PT-1002', 10)

Statement 41

update SALES_ORDERS set ORDER_DATE = SYSDATE where ORDER_ID = 9003

Statement 42
```
commit
```

Statement 43

select json_serialize(doc pretty) from SALES_DOCUMENTS

Statement 44

insert into SALES_ORDERS(ORDER_ID, CUSTOMER_ID, ORDER_DATE, SHIP_ADDRESS)
     values (9004, 2002, SYSDATE, 3002)

Statement 45

insert into SALES_ORDER_LINES(ORDER_ID, SKU, QUANTITY)
     values (9004, 'PT-1002', 5)

Statement 46

update SALES_ORDERS set ORDER_DATE = SYSDATE where ORDER_ID = 9004

Statement 47
```
commit
```

Statement 48

select json_serialize(doc pretty) from SALES_DOCUMENTS

Statement 49

insert into SALES_ORDERS(ORDER_ID, CUSTOMER_ID, ORDER_DATE, SHIP_ADDRESS)
     values (9005, 2002, SYSDATE, 3002)

Statement 50

insert into SALES_ORDER_LINES(ORDER_ID, SKU, QUANTITY)
     values (9005, 'PT-1002', 7)

Statement 51

update SALES_ORDERS set ORDER_DATE = SYSDATE where ORDER_ID = 9005

Statement 52
```
commit
```

Statement 53

select json_serialize(doc pretty) from SALES_DOCUMENTS

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database