CODEWITHSUNDEEP
jsonoracle-databaseplsqlpljson
Lucas Mouta
Lucas Mouta

Reputation: 1

Oracle - Retrive data from json object with json list inside

So, I have this api that ansawer me a json_list, and my program put it inside a json object that i can't change. I need to retrive information from it but i can't.

My json file:

[{ "idSolic" : "1234",
    "Status" : {
                 "code" : "502",
                 "description" : "i cant get the data" 
                }
 }]

If only i could remove the [], i would able to get it.

I'm woking with pl/sql using the json objects from GitHub Documentation

I'm working with PL/JSON V1_0_5 and oracle 12c.

My code is:

FUNCTION GET_INVOICE_STATUS(  p_chave_tabela   apps.json
     , p_company        VARCHAR2
     , p_invoice        VARCHAR2
     , p_resp_json      OUT json
     , p_id_solicitacao OUT NUMBER)
    RETURN VARCHAR2
    IS
        PRAGMA               AUTONOMOUS_TRANSACTION;
    --   
        l_authorization      VARCHAR2(4096);
        l_status_solicitacao VARCHAR2(2048);
        l_nome_processo      VARCHAR2(100) := 'invoices/sefaz';
        l_request            utl_http.req;
        l_response           utl_http.resp; 
        l_host               VARCHAR2(1024);
        l_chave_tabela_clob  CLOB := empty_clob();
        l_clob_raw           CLOB := empty_clob();
        l_id_solicitacao     NUMBER;
        l_resp_list          apps.json_list;
        --
    BEGIN
    --
    xxavl_util_pkg.inicia_host_token(p_host => l_host, p_token => l_authorization);
        --
        --l_authorization := 'Bearer ' || l_authorization;
        l_host          := l_host    || l_nome_processo || '/' || trim(p_invoice)   ;
        --l_host    || l_nome_processo || '/' ||p_invoice  ;
        --
        dbms_output.put_line(l_host);
        --
        dbms_output.put_line(xxavl_util_pkg.g_autenticacao_padrao);
        utl_http.set_wallet('file:/home/oracle/wallet','NULL');
        --
        l_request := utl_http.begin_request(l_host, 'GET', 'HTTP/1.1');
        --
      --utl_http.set_header(l_request, 'authorization', l_authorization);                                -- 02/07/2018
        utl_http.set_header(l_request, 'authorization', xxavl_util_pkg.g_autenticacao_padrao);           -- 02/07/2018
        --
        utl_http.set_header(l_request, 'User-agent', 'Apache-HttpClient/4.1.1');
        utl_http.set_header(l_request, 'accept', 'application/json');
        utl_http.set_header(l_request, 'Content-Type', 'application/json');
    --
    --
    l_response := utl_http.get_response(l_request);
    --
    BEGIN
    --
    l_status_solicitacao := xxavl_util_pkg.trata_resposta(l_response, p_resp_json, l_clob_raw); --treats the http request and give me back it in a json variable p_resp_json
    --
    --l_resp_list := apps.json_list(p_resp_json.to_char).to_json_value;
    --p_resp_json := apps.json(l_resp_list);
    --dbms_output.put_line(p_resp_json.to_char);
    --
    dbms_output.put_line(apps.json_ext.get_string(p_resp_json, 'status.protocol') || '-----------------' );

I need to get the "status.protocol" but as my json is inside a "[ ]" i'm not able to do it, i've tried to convert it to a json_list then get the last list object and put it inside a json object but i got errors like, buffer out of space.

Upvotes: 0

Views: 3244

Answers (2)

archimede
archimede

Reputation: 736

I'm afraid I don't understand your code, but if you have a string variable containing your json, parsing it should be pretty easy. Try something like this:

set serveroutput on
declare
   v_resp  clob := '[{ "idSolic": "1234", "Status": { "code": "502", "description": "i cant get the data" } }]';
   v_jsonl json_list := json_list(v_resp);
   v_json  json;
begin
   for i in 1..v_jsonl.count loop
      v_json := json(v_jsonl.get(i));
      dbms_output.put_line('idSolic=' || v_json.get('idSolic').get_string);
      v_json := json(v_json.get('Status'));
      dbms_output.put_line('Status=' || v_json.get('description').get_string);
   end loop;
end;

Upvotes: 0

TomLan
TomLan

Reputation: 13

First of all, your OUT parameter p_resp_json in your code has type json but data which you have pasted as content of this parameter isn't compatible with json type but with json_list type. It is an array and it is impossible to parse such string into object with type json - you will get:

ORA-20101: JSON Parser exception - no { start found

You can store json_list inside json but it has to be named, for example:

{
  "myArray": [
    {
        "idSolic": "1234",
        "Status": {
            "code": "502",
            "description": "i cant get the data"
        }
    }
  ]
}

Second, you can't pass path as 'status.protocol' to retrieve data from array because it is unknown which one array element you want to get. You have to add index to your path. To retrieve "code" from json which I have pasted above you can use:

json_ext.get_string( p_resp_json, 'myArray[1].Status.code' )

You can check what you got inside p_resp_json by p_resp_json.print() method - it will parse it into string and write result in output.

Upvotes: 0

Related Questions