Hi All,
A bit more of theoretical question:-
One Stored Prod has input:
v_id input
JSON_ARRAY coming in as CLOB
Now we merge the data from JSON array to target table generating a primary for target table for insert and updating if it is passed
Below is the pseudo code (normal merge statement, but with source as jason array)
merge into target_tab t
using (select a.col1, a.col2 from json_table(json_obj,'$' columns ( Nested PATH ‘$[*]’ columns col1 varchar2(100) PATH ‘$.column1’,…. ) b
on ( t.colx = b.col1)
when matched then update
when not matched then insert
Now the new requirement which came up is they want to send another column in json object (attachements say for e.g.), which would be type clob (an array of json ) and it is required to be stored in a different table which will refer to primary key target table in above statement.
One row in target_tab can receive multiple attachments in the new json_array column which needs to be stored in a new table.
Sorry i know i was not able to copy SP, or table statements.
Working on creating templates, i will post them as soon as I finish the create table and insert statement.