Forum Stats

  • 3,837,426 Users
  • 2,262,257 Discussions
  • 7,900,279 Comments

Discussions

How can i get the return variables of GET method in PL/SQL procedure

13»

Answers

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 880 Bronze Badge
    edited Feb 21, 2018 8:24AM

    The result of WS looks like;

    resWS.PNG

    but when i parse values , i get null like Column2, and column3

  • Pavel_p
    Pavel_p Member Posts: 2,314 Gold Trophy
    edited Feb 21, 2018 9:53AM Answer ✓

    I don't even see it in threaded view, so let's start from the beginning.

    Your current service is something different than the previous one. While the previous service returned a single JSON object, this modified one returns an array of items, so paths are different and also you need to loop over array elements. Let's suppose you have a service defined like

    select * from dept

    that returns an array of JSONs, you could process it this way

    declare  l_resp_clob  clob := '  {    "next": {        "$ref": "http://server_addr/apex/testing/emprest/getAllDepts?page=1"    },    "items": [{            "deptno": 10,            "dname": "ACCOUNTING",            "loc": "NEW YORK"        }, {            "deptno": 20,            "dname": "RESEARCH",            "loc": "DALLAS"        }, {            "deptno": 30,            "dname": "SALES",            "loc": "CHICAGO"        }, {            "deptno": 40,            "dname": "OPERATIONS",            "loc": "BOSTON"        }    ]}';  l_values    apex_json.t_values;  l_js_deptno  dept.deptno % type;  l_js_dname  dept.dname % type;  l_js_loc    dept.loc % type;  l_item_count number;begin  --parse the returned JSON     apex_json.parse(  p_values => l_values,  p_source => l_resp_clob);  l_item_count := apex_json.get_count(p_path => 'items', p_values => l_values);  dbms_output.put_line('Item count: ' || l_item_count);  dbms_output.put_line('-----------looping over all items------------------');  for i in 1 .. l_item_count loop    dbms_output.put_line('Item no: ' || i);    l_js_deptno := apex_json.get_number(    p_values => l_values,    p_path => 'items[' || i || '].deptno');    l_js_dname := apex_json.get_varchar2(    p_values => l_values,    p_path => 'items[' || i || '].dname');    l_js_loc := apex_json.get_varchar2(    p_values => l_values,    p_path => 'items[' || i || '].loc');    dbms_output.put_line('Dept no: ' || l_js_deptno || ', Dname: ' || l_js_dname || ', Loc: ' || l_js_loc);  end loop;end;

    Please, modify your code accordingly.

    Soukaina IDRISSISoukaina IDRISSI
  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 880 Bronze Badge
    edited Feb 22, 2018 5:32AM

    Thank you so so much Pavel_p for your patience and understanding .
    You saved my life, it was very useful your post, it's really touching to see people who take their time to assist others .

    Oracle apex is the best forum in the world, for ever.

    Pavel_p
This discussion has been closed.