Forum Stats

  • 3,757,090 Users
  • 2,251,195 Discussions
  • 7,869,721 Comments

Discussions

How to get the return variables of WS Json format in GET method

Soukaina IDRISSI
Soukaina IDRISSI Member Posts: 844 Blue Ribbon
edited Feb 26, 2018 3:50PM in APEX Discussions

Hi everyone,

Following this post How can i get the return variables of GET method in PL/SQL procedure.

In my case, I get result without items like this:

ws_result.png

So doing this code:

l_item_count := apex_json.get_count(p_path => 'items', p_values => l_values);  dbms_output.put_line('Item count: ' || l_item_count);  

the value of Item count = 0

Suddently, the looping over all items.

Thank you in advance for help.

Tagged:

Best Answer

  • Pavel_p
    Pavel_p Member Posts: 2,305 Gold Trophy
    edited Feb 24, 2018 4:27PM Accepted Answer

    This is way better

    declare  l_resp_clob     clob := '[{        "entite": "TSF",        "id_CLIENT1": "8XXXX",        "nom_CLIENT": "XXX XXX",        "numero_DOSSIER": "XX10XXX",        "date_OCTROI": "2017XXXX",        "montant_FIN": "00000X00XXX00",        "krd": "00000XXXXXXXX",        "montant_IMP": "0000000XXXXXX",        "creance_CLIENT": "00000X0XXXX0X",        "nombre_IMP": "00000X",        "statut_DOSSIER": "ACT",        "nombre_INCIDENT": "001",        "declare_FRAUDE": "N",        "qualite_TIERS": "EMP",        "montant_MAX_ENG": "00000X0XXXX0X",        "date_STATUT_DOSSIER": "20180XXX",        "montant_INCIDENT": "0"    }]';  l_values        apex_json.t_values;  l_js_entite     varchar2(50);  l_js_id_client1 varchar2(50);  l_js_nom_client varchar2(50);  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 => '.', 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_entite := apex_json.get_varchar2(    p_values => l_values,    p_path => '[' || i || '].entite');    l_js_id_client1 := apex_json.get_varchar2(    p_values => l_values,    p_path => '[' || i || '].id_CLIENT1');    l_js_nom_client := apex_json.get_varchar2(    p_values => l_values,    p_path => '[' || i || '].nom_CLIENT');    --get other values accordingly...    dbms_output.put_line('entite: ' || l_js_entite || ', id_CLIENT1: ' || l_js_id_client1 || ', nom_CLIENT: ' || l_js_nom_client);  end loop;end;

    Item count: 1-----------looping over all items------------------Item no: 1entite: TSF, id_CLIENT1: 8XXXX, nom_CLIENT: XXX XXX

Answers

  • Pavel_p
    Pavel_p Member Posts: 2,305 Gold Trophy
    edited Feb 24, 2018 10:24AM

    Hi,

    the tool you use for viewing JSONs quite successfully hides how the array looks like, so unfortunately this image is quite useless. It should be possible to show the "raw" JSON (without "nice" formatting and making it more human-readable), so please, try to paste here a sample JSON response (just 2 or 3 "entite" records), ideally in a text form, not an image.

    Thanks,

    Pavel

  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 844 Blue Ribbon
    edited Feb 24, 2018 2:59PM

    this is one example of result:

    [
      {
        "entite": "TSF",
        "id_CLIENT1": "8XXXX",
        "nom_CLIENT": "XXX XXX",
        "numero_DOSSIER": "XX10XXX",
        "date_OCTROI": "2017XXXX",
        "montant_FIN": "00000X00XXX00",
        "krd": "00000XXXXXXXX",
        "montant_IMP": "0000000XXXXXX",
        "creance_CLIENT": "00000X0XXXX0X",
        "nombre_IMP": "00000X",
        "statut_DOSSIER": "ACT",
        "nombre_INCIDENT": "001",
        "declare_FRAUDE": "N",
        "qualite_TIERS": "EMP",
        "montant_MAX_ENG": "00000X0XXXX0X",
        "date_STATUT_DOSSIER": "20180XXX",
        "montant_INCIDENT": "0"
      }
    ]
  • Pavel_p
    Pavel_p Member Posts: 2,305 Gold Trophy
    edited Feb 24, 2018 4:27PM Accepted Answer

    This is way better

    declare  l_resp_clob     clob := '[{        "entite": "TSF",        "id_CLIENT1": "8XXXX",        "nom_CLIENT": "XXX XXX",        "numero_DOSSIER": "XX10XXX",        "date_OCTROI": "2017XXXX",        "montant_FIN": "00000X00XXX00",        "krd": "00000XXXXXXXX",        "montant_IMP": "0000000XXXXXX",        "creance_CLIENT": "00000X0XXXX0X",        "nombre_IMP": "00000X",        "statut_DOSSIER": "ACT",        "nombre_INCIDENT": "001",        "declare_FRAUDE": "N",        "qualite_TIERS": "EMP",        "montant_MAX_ENG": "00000X0XXXX0X",        "date_STATUT_DOSSIER": "20180XXX",        "montant_INCIDENT": "0"    }]';  l_values        apex_json.t_values;  l_js_entite     varchar2(50);  l_js_id_client1 varchar2(50);  l_js_nom_client varchar2(50);  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 => '.', 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_entite := apex_json.get_varchar2(    p_values => l_values,    p_path => '[' || i || '].entite');    l_js_id_client1 := apex_json.get_varchar2(    p_values => l_values,    p_path => '[' || i || '].id_CLIENT1');    l_js_nom_client := apex_json.get_varchar2(    p_values => l_values,    p_path => '[' || i || '].nom_CLIENT');    --get other values accordingly...    dbms_output.put_line('entite: ' || l_js_entite || ', id_CLIENT1: ' || l_js_id_client1 || ', nom_CLIENT: ' || l_js_nom_client);  end loop;end;

    Item count: 1-----------looping over all items------------------Item no: 1entite: TSF, id_CLIENT1: 8XXXX, nom_CLIENT: XXX XXX
  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 844 Blue Ribbon
    edited Feb 26, 2018 11:23AM

    Bravo Pavel_p, and thank you so so much.

  • Pavel_p
    Pavel_p Member Posts: 2,305 Gold Trophy
    edited Feb 26, 2018 3:50PM
This discussion has been closed.