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

Soukaina IDRISSI

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:

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.

Pavel_p

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

Pavel_p

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

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

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

Bravo Pavel_p, and thank you so so much.

Pavel_p

Glad to help