POST Request (array ) in Oracle APEX

Question

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

From my understanding, there is a problem with your JSON. It starts as an object and it ends as array. — Ergi Nushi
– Ergi Nushi, Commented Feb 13, 2020 at 13:04
@ErgiNushi I edited, for example, I did not copy my last sweetheart — Apex_MAN
– Apex_MAN, Commented Feb 13, 2020 at 13:07

Dan McGhan · Accepted Answer · 2020-02-16 16:54:35Z

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;

Ergi Nushi · Accepted Answer · 2020-02-13 13:27:31Z

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;

Apex_MAN · Accepted Answer · 2020-02-13 14:59:10Z

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;

Collectives™ on Stack Overflow

POST Request (array ) in Oracle APEX

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related