Hello ,i need help with apex oracle. This task is not typical for me, so I ask for help. I have a rest point (get). As a result, I have the answer:
{
"DT_СREATE": "2020-02-05T10:38:11Z",
"DT_UPDATE": "2020-02-05T10:38:11Z",
"ID": 12015,
"sensors": [
{
"name": "SENSOR1",
"temp": "11.91"
},
{
"name": "SENSOR2",
"temp": "9.23"
}
]
}
I also have a TEMPR_SILO table. In which I want to write this answer!
CREATE TABLE "TEMPR_SILO"
( "ID" NUMBER,
"NAME" VARCHAR2(500),
"TEMP" VARCHAR2(500),
"ID_TRANS" NUMBER,
CONSTRAINT "TEMPR_SILO_PK" PRIMARY KEY ("ID")
USING INDEX ENABLE
)
but all this should be the result of a dynamic action after pushing a button on the weed. That is, when the button is pressed, I have to record the data from the GET. I wrote a POST for this. here he is.
Declare
l_clob CLOB;
v_url varchar2(1000);
V_RES number;
tv apex_json.t_values;
sCount int;
v_id VARCHAR2(500);
----------------------
q_temp VARCHAR2(500);
q_name VARCHAR2(500);
BEGIN
v_url := 'My API';
l_clob := apex_web_service.make_rest_request(p_url => v_url, p_http_method=> 'GET');
apex_json.parse(tv, l_clob);
v_id := apex_json.get_varchar2(p_path => 'id', p_values => tv);
sCount := APEX_JSON.get_count(p_path => 'sensors' , p_values => tv);
IF sCount > 0 THEN
FOR i in 1 .. sCount LOOP
q_temp := apex_json.get_varchar2(p_path => 'sensors['|| i ||'].temp', p_values => tv);
q_name := apex_json.get_varchar2(p_path => 'sensors['|| i ||'].name', p_values => tv);
INSERT INTO TEMPR_SILO ( NAME, TEMP,ID_TRANS)
VALUES ( q_name , q_temp ,v_id);
commit;
END LOOP;
END IF;
END ;
But I don't know how to use it in my task! I would appreciate an example or help in this question!