Bulk Update in Oracle 12.1.0.2?
I am currently working on a task and has a requirement to update around 2000 to 4000 records by reading values from JSON. I optimize the JSON part but currently, I am updating each record one by one. Can someone suggest the best approach to update all 2000 to 4000 records with just single query instead of running it 2000 to 4000 times? Here is my sample code
APEX_JSON.PARSE(V_OUTPUT_DATA);
plan_count := apex_json.get_count('plan');
IF plan_count > 0 THEN
FOR I in 1..plan_count LOOP
activities_count := APEX_JSON.get_count(p_path => 'plan['||i||'].activities');
IF activities_count > 0 THEN
FOR j in 2..(activities_count-1) LOOP
V_TASK_ID := APEX_JSON.get_varchar2(p_path => 'plan['||i||'].activities['||j||'].task_id');
V_SEQ := APEX_JSON.get_number(p_path => 'plan['||i||'].activities['||j||'].sequence');
UPDATE TABLE_NAME
SET ROUTE_SEQUENCE = V_SEQ, UPDATED_BY = 'SYSTEM',UPDATED_ON = SYSTIMESTAMP
WHERE TASK_ID = V_TASK_ID;
END LOOP;
COMMIT;
END IF;
END LOOP;
END IF;
Should I use a 2D array and use that to bulk update or some other approach can be followed?
Sample JSON added
{
"plan": [{
"vehicle_id": "vehicle_1",
"activities": [{
"sequence": 0,
"timestamp": "2017-11-10T09:48:19Z",
"location_id": "depot"
},
{
"sequence": 1,
"timestamp": "2017-11-10T09:50:07Z",
"task_id": "465427",
"location_id": "465427",
"travel_distance": 1099,
"travel_duration": "00:01:48"
},
{
"sequence": 2,
"timestamp": "2017-11-10T09:50:10Z",
"task_id": "443951",
"location_id": "443951",
"travel_distance": 26,
"travel_duration": "00:00:03"
},
{
"sequence": 3,
"timestamp": "2017-11-10T09:50:25Z",
"task_id": "165760",
"location_id": "165760",
"travel_distance": 152,
"travel_duration": "00:00:15"
},
{
"sequence": 4,
"timestamp": "2017-11-10T09:51:34Z",
"task_id": "459187",
"location_id": "459187",
"travel_distance": 705,
"travel_duration": "00:01:09"
}]
}]
}
Assuming that your table is like this one:
create table table_name
(
id number(12) primary key,
route_sequence number(12),
updated_by varchar2(30),
updated_on timestamp(9)
)
and that the json object is like this one:
{
"activities":
[
{"task_id": 1, "sequence" : 10},
{"task_id": 2, "sequence" : 20},
{"task_id": 3, "sequence" : 30},
{"task_id": 4, "sequence" : 40},
{"task_id": 5, "sequence" : 50},
]
}
You can query directly in SQL the json data using the "JSON_TABLE" sql operator (new to oracle 12 - see https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973)... and then you can take advantage of it, using such a query in a "merge" statement:
this single SQL statement does what you need:
merge into table_name t
using
(
select *
from JSON_TABLE(
'{
"activities":
[
{"task_id": 1, "sequence" : 10},
{"task_id": 2, "sequence" : 20},
{"task_id": 3, "sequence" : 30},
{"task_id": 4, "sequence" : 40},
{"task_id": 5, "sequence" : 50},
]
}',
'$."activities"[*]'
COLUMNS(
V_TASK_ID NUMBER PATH '$.task_id',
V_SEQ NUMBER PATH '$.sequence'
)
)
) json_data
on (json_data.v_task_id = t.id)
when matched then
update set
ROUTE_SEQUENCE = V_SEQ,
UPDATED_BY = 'SYSTEM',
UPDATED_ON = SYSTIMESTAMP
Edit: now that you have posted your actual json example:
to make my example work with your data you just need to replace the
'$."activities"[*]'
line with this one:
'$."plan"[0]."activities"[*]'
thing can get more complicated if the "plan" array item contains more than one element, but it can still be done.
Edit 2: how to handle nested objects (that is: what to do when "plan" contains multiple objects
Let's say that the json string to be processed is this one
'{
"plan":
[
{
"vehicle_id": "vehicle_1",
"activities":
[
{
"sequence": 1,
"task_id": "465427"
},
{
"sequence": 2,
"task_id": "443951"
}
]
}
,
{
"vehicle_id": "vehicle_2",
"activities":
[
{
"sequence": 3,
"task_id": "165760"
},
{
"sequence": 4,
"task_id": "459187"
}
]
}
]
}'
(I won't repeat it in my examples: i will just write in the code
If you are not interested in reading the vehicle_id field and you want a flat view of all the activities details (regardless of which "plan" object contains them, you can just change the root object selector string from this
'$."plan"[0]."activities"[*]'
to this:
'$."plan"[*]."activities"[*]'
so, this query:
select *
from JSON_TABLE
(
<json_string_here>,
'$."plan"[*]."activities"[*]'
COLUMNS(
V_TASK_ID NUMBER PATH '$.task_id',
V_SEQ NUMBER PATH '$.sequence'
)
)
will traverse all the "activities" objects of all plan objects, but it will return you only the "task_id" and "sequence" columns.
if you want instead also the corresponding vehicle id column being repeated on all rows, you must step up on level with your root selector, using this expression
'$."plan"[*]'
and inside the "columnns" clause, you can use the "nested path" syntax to say you want to expand in-line also the columns of a subobject:
select *
from JSON_TABLE
(
<json_string_here>,
'$."plan"[*]'
COLUMNS
(
VEHICLE varchar2(20) PATH '$."vehicle_id"',
NESTED PATH '$."activities"[*]'
COLUMNS
(
V_TASK_ID NUMBER PATH '$.task_id',
V_SEQ NUMBER PATH '$.sequence'
)
)
)
