CODEWITHSUNDEEP
jsonoracle-databasepostplsqloracle-apex
Apex_MAN
Apex_MAN

Reputation: 127

POST Request (array ) in Oracle APEX

I have an array in json, I want to write it in the oracle method post.But I have somewhere the error I can not find it. I try to write it all in a loop, the code compiles but does not write. The first thing I do is look for the first object using a loop. And then I use another loop in the middle of the object to record the data inside it!

    v_clob := iot_general.blob_to_clob(p_blob); 
    apex_json.parse(tv, v_clob); 
    sCount := APEX_JSON.get_count(p_path => 'GroupSensor' , p_values => tv); 
    IF sCount > 0 THEN 
      FOR i in 1 .. sCount LOOP 
      v_id := apex_json.get_varchar2(p_path => 'GroupSensor.SerialNumber['|| i ||']', p_values => tv);  
                  cCount := APEX_JSON.get_count(p_path => 'GroupSensor.GroupBob['|| i ||']' , p_values => tv); 
                  IF cCount > 0 THEN 
                              FOR q in 1 .. cCount LOOP 
                              q_temp   := apex_json.get_varchar2(p_path => 'GroupSensor.GroupBob['|| i ||']['|| q ||']', p_values => tv);  
                                INSERT INTO SILO_SENSOR( NAME,  DEVICES_ID)  
                                VALUES (q_temp,v_id  ); 
                                commit; 
                                END LOOP;    
                 END IF; 
      END LOOP; 
    END IF;

Here is my json

 {
  "GroupSensor": [
    {
      "silos": 1,
      "GroupBob": [
        "SENSOR0001",
        "SENSOR0002",
        "SENSOR0003",
        "SENSOR0004",
        "SENSOR0005",
        "SENSOR0006",
        "SENSOR0007",
        "SENSOR0008",
        "SENSOR0009",
        "SENSOR0010"
      ],
      "SerialNumber": "1701"
    },
    {
      "silos": 1,
      "GroupBob": [
        "SENSOR0011",
        "SENSOR0012",
        "SENSOR0013",
        "SENSOR0014",
        "SENSOR0015",
        "SENSOR0016",
        "SENSOR0017",
        "SENSOR0018",
        "SENSOR0019"
      ],
      "SerialNumber": "1702"
    },
    {
      "silos": 1,
      "GroupBob": [
        "SENSOR0020",
        "SENSOR0021",
        "SENSOR0022",
        "SENSOR0023",
        "SENSOR0024",
        "SENSOR0025",
        "SENSOR0026",
        "SENSOR0027",
        "SENSOR0028"
      ],
      "SerialNumber": "1703"
    }
]

}

I want to write the data to the SILO_SENSOR table, that's it

NAME            DEVICES_ID
SENSOR0001      1701
SENSOR0002      1701
SENSOR0003      1701
SENSOR0004      1701
SENSOR0005      1701
SENSOR0006      1701
SENSOR0007      1701
SENSOR0008      1701
SENSOR0009      1701
SENSOR0010      1701

Upvotes: 0

Views: 594

Answers (3)

Dan McGhan
Dan McGhan

Reputation: 4659

Given this table:

create table silo_sensor (
  name      varchar2(255),
  devices_id varchar2(255)
);

The following should work:

declare

  tv     apex_json.t_values;
  v_clob clob;
  scount number;
  ccount number;
  q_temp varchar2(255);
  v_id   varchar2(255);

begin

  v_clob := q'-
    {
      "GroupSensor": [
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0001",
            "SENSOR0002",
            "SENSOR0003",
            "SENSOR0004",
            "SENSOR0005",
            "SENSOR0006",
            "SENSOR0007",
            "SENSOR0008",
            "SENSOR0009",
            "SENSOR0010"
          ],
          "SerialNumber": "1701"
        },
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0011",
            "SENSOR0012",
            "SENSOR0013",
            "SENSOR0014",
            "SENSOR0015",
            "SENSOR0016",
            "SENSOR0017",
            "SENSOR0018",
            "SENSOR0019"
          ],
          "SerialNumber": "1702"
        },
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0020",
            "SENSOR0021",
            "SENSOR0022",
            "SENSOR0023",
            "SENSOR0024",
            "SENSOR0025",
            "SENSOR0026",
            "SENSOR0027",
            "SENSOR0028"
          ],
          "SerialNumber": "1703"
        }
      ]
    }
  -';

  apex_json.parse(tv, v_clob); 

  sCount := APEX_JSON.get_count(p_path => 'GroupSensor' , p_values => tv); 

  for i in 1 .. sCount loop 
    v_id := apex_json.get_varchar2(p_path => 'GroupSensor[%d].SerialNumber', p_values => tv, p0 => i);  

    cCount := APEX_JSON.get_count(p_path => 'GroupSensor[%d].GroupBob' , p_values => tv, p0 => i); 

    for q in 1 .. cCount loop 
      q_temp := apex_json.get_varchar2(p_path => 'GroupSensor[%d].GroupBob[%d]', p_values => tv, p0 => i, p1 => q);  
      insert into silo_sensor(name, devices_id)  
      values (q_temp, v_id); 
    end loop;    
  end loop;

  commit;

end;

A couple of things to note:

  • You don't need the IF sCount > 0 THEN or IF cCount > 0 THEN checks. Just go into the loop, if the count is 0 then you will not go into it. The only reason you might need the if check is if it's possible the property will not exist. In that case, your check should be if sCount is not null because get_varchar2 will return null if the property doesn't exist.

  • Don't commit inside of a loop. If you get an error while looping, you'll have committed part of the transaction and it will be more difficult to fix. Either commit at the end or not at all (let the caller commit when they want).

Because you're running on 18c, you could use the built in PL/SQL types for JSON. These should perform better than APEX_JSON.

declare

  top_obj json_object_t;
  gs_arr  json_array_t;
  gs_obj  json_object_t;
  gb_arr  json_array_t;
  v_clob  clob;
  q_temp  varchar2(255);
  v_id    varchar2(255);

begin

  v_clob := q'-
    {
      "GroupSensor": [
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0001",
            "SENSOR0002",
            "SENSOR0003",
            "SENSOR0004",
            "SENSOR0005",
            "SENSOR0006",
            "SENSOR0007",
            "SENSOR0008",
            "SENSOR0009",
            "SENSOR0010"
          ],
          "SerialNumber": "1701"
        },
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0011",
            "SENSOR0012",
            "SENSOR0013",
            "SENSOR0014",
            "SENSOR0015",
            "SENSOR0016",
            "SENSOR0017",
            "SENSOR0018",
            "SENSOR0019"
          ],
          "SerialNumber": "1702"
        },
        {
          "silos": 1,
          "GroupBob": [
            "SENSOR0020",
            "SENSOR0021",
            "SENSOR0022",
            "SENSOR0023",
            "SENSOR0024",
            "SENSOR0025",
            "SENSOR0026",
            "SENSOR0027",
            "SENSOR0028"
          ],
          "SerialNumber": "1703"
        }
      ]
    }
  -';

  top_obj := json_object_t.parse(v_clob);
  gs_arr := top_obj.get_array('GroupSensor');

  for i in 0 .. gs_arr.get_size - 1 loop 
    gs_obj := json_object_t(gs_arr.get(i));
    v_id := gs_obj.get_string('SerialNumber');
    gb_arr := gs_obj.get_array('GroupBob');

    for q in 0 .. gb_arr.get_size - 1 loop 
      q_temp := gb_arr.get_string(i);
      insert into silo_sensor(name, devices_id)  
      values (q_temp, v_id); 
    end loop;    
  end loop;

  commit;

end;

Upvotes: 1

Apex_MAN
Apex_MAN

Reputation: 127

I did

    v_clob := iot_general.blob_to_clob(p_blob); 
apex_json.parse(tv, v_clob); 
sCount := APEX_JSON.get_count(p_path => 'GroupSensor' , p_values => tv); 
IF sCount > 0 THEN 
          FOR i in 1 .. sCount LOOP 
          v_id := apex_json.get_varchar2(p_path => 'GroupSensor['|| i ||'].SerialNumber', p_values => tv);  
          cCount := APEX_JSON.get_count(p_path => 'GroupSensor['|| i ||'].GroupBob' , p_values => tv); 
          IF cCount > 0 THEN 
                          FOR q in 1 .. cCount LOOP 
                                  q_temp   := apex_json.get_varchar2(p_path => 'GroupSensor['|| i ||'].GroupBob['|| q ||']', p_values => tv);  
                                  INSERT INTO SILO_SENSOR( NAME,  DEVICES_ID)  
                                  VALUES (q_temp,v_id  ); 
                                 commit; 
                         END LOOP;    
             END IF; 
  END LOOP; 
END IF;

Upvotes: 0

Ergi Nushi
Ergi Nushi

Reputation: 863

You are not accessing the array right:

Try the following:

BEGIN
    apex_json.parse(tv, v_clob);
    scount := apex_json.get_count(p_path => 'GroupSensor', p_values => tv);
    IF scount > 0 THEN
        FOR i IN 1..scount LOOP
            v_id := apex_json.get_varchar2('GroupSensor[%d].SerialNumber', i);

            ccount := apex_json.get_count(p_path => 'GroupSensor[%d].GroupBob', p0 => i, p_values => tv);

            IF ccount > 0 THEN
                FOR q IN 1..ccount LOOP
                    q_temp := apex_json.get_varchar2('GroupSensor[%d].GroupBob[%d]', i, q);
                    INSERT INTO silo_sensor (
                        name,
                        devices_id
                    ) VALUES (
                        q_temp,
                        v_id
                    );

                    COMMIT;
                END LOOP;
            END IF;
        END LOOP;
    END IF;
END;

Upvotes: 0

Related Questions