Oracle Apex 22.21 - REST data source - trigger to convert string to_number and add into a column

This question is a follow up to another SO question.

I have an app with a REST Data Source taking in JSON responses from an API. There are two tables ORDERS and LINES. The ORDERS table contains a column LINES which is a JSON Document type.

I created a trigger on my ORDERS table which runs AFTER INSERT, UPDATE or DELETE on my ORDERS table, and which maintains the LINES table (Extracts the JSON array from the ORDERS table ORDER_ITEMS column and inserts into ORDER_ITEMS table and each column).

SAMPLE JSON RESPONSE FROM API
[ {
  "order_number": "so1223",
  "order_date": "2022-07-01",
  "full_name": "Carny Coulter",
  "email": "ccoulter2@ovh.net",
  "credit_card": "3545556133694494",
  "city": "Myhiya",
  "state": "CA",
  "zip_code": "12345",
  "lines": [
    {
      "product": "Beans - Fava, Canned",
      "quantity": 1,
      "price": $1.99
    },
    {
      "product": "Edible Flower - Mixed",
      "quantity": 1,
      "price": $1.50
    }
  ]
},
{
  "order_number": "so2244",
  "order_date": "2022-12-28",
  "full_name": "Liam Shawcross",
  "email": "lshawcross5@exblog.jp",
  "credit_card": "6331104669953298",
  "city": "Humaitá",
  "state": "NY",
  "zip_code": "98670",
  "lines": [
    {
      "order_id": 5,
      "product": "Beans - Green",
      "quantity": 2,
      "price": $4.33
    },
    {
      "order_id": 1,
      "product": "Grapefruit - Pink",
      "quantity": 5,
      "price": $5.00
    }
  ]
},
]

create or replace trigger "TR_MAINTAIN_LINES"
AFTER
insert or update on "ORDERS"
for each row
begin
    if inserting or updating then
        if updating then
          delete LINES
          where order_number = :old.order_number;
        end if;
        insert into LINES ( order_number, product, quantity, price, item_image) 
        ( select :new.order_number,
                 j.product,
                 j.quantity,
                 j.price,
                 j.item_image
            from json_table( 
                     :new.lines,
                     '$[*]' columns (
                         product  varchar2(4000) path '$.product',
                         quantity number        path '$.quantity',
                         price    varchar2(4000) path '$.price', 
                         item_image varchar2(4000) path '$.item_image' ) ) j );
    end if;
end;

So this works as expected right now. However, the PRICE column is a varchar type due to the '$' in the JSON response. (Ex: $1.99). I'm able to change this to a number type in a view by using TO_NUMBER(price,'$999,999,999.99').

How can I do this within the trigger?

• Have the trigger after insert or update, convert the PRICE column (varchar) to a another column TOTAL_PRICE (number).
• I also want to add another column UNIT_PRICE which takes TOTAL_PRICE/QUANTITY.

Are these possible within the given trigger or would I have to create a separate trigger?

For column TOTAL_PRICE

Yes this can be done in the trigger. Add the column to the lines table and modify the insert statement

...
       insert into LINES ( order_number, product, quantity, price, total_price, item_image) 
        ( select :new.order_number,
                 j.product,
                 j.quantity,
                 j.price,
                 TO_NUMBER(j.price,'$999.99'),
                 j.item_image
            from json_table( 
                     :new.lines,
                     '$[*]' columns (
                         product  varchar2(4000) path '$.product',
                         quantity number        path '$.quantity',
                         price    varchar2(4000) path '$.price', 
                         item_image varchar2(4000) path '$.item_image' ) ) j );
...

For column UNIT_PRICE

This can be done in the trigger as well but... it makes more sense to add a "virtual column" here since unit price is always TOTAL_PRICE/QUANTITY. A virtual column cannot be updated/inserted, only selected - the value is always up to date

alter table lines add unit_price number generated always as ( total_price / quantity ) virtual;