Forum Stats

  • 3,780,534 Users
  • 2,254,407 Discussions
  • 7,879,374 Comments

Discussions

Categories

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

Soukaina IDRISSI
Soukaina IDRISSI Member Posts: 855 Bronze Badge
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:
· Share on TwitterShare on Facebook

Best Answer

  • Pavel_p
    Pavel_p Member Posts: 2,312 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
    · Share on TwitterShare on Facebook

Answers

  • Pavel_p
    Pavel_p Member Posts: 2,312 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

    · Share on TwitterShare on Facebook
  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 855 Bronze Badge
    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"
      }
    ]
    · Share on TwitterShare on Facebook
  • Pavel_p
    Pavel_p Member Posts: 2,312 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
    · Share on TwitterShare on Facebook
  • Soukaina IDRISSI
    Soukaina IDRISSI Member Posts: 855 Bronze Badge
    edited Feb 26, 2018 11:23AM

    Bravo Pavel_p, and thank you so so much.

    · Share on TwitterShare on Facebook
  • Pavel_p
    Pavel_p Member Posts: 2,312 Gold Trophy
    edited Feb 26, 2018 3:50PM

    Glad to help

    · Share on TwitterShare on Facebook
This discussion has been closed.