Search code examples
oracleplsqloracle12cbulkupdate

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"
    }]
  }]
}
Solution

  • 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.

    traversing only the leaf details

    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'
                )
        )
   )

    expanding subobject columns