Sample JSON:
[
{
"term-id": "BA000000",
"bank-id": "111",
"location": "Poslovalnica banke",
"address": "Cankarjeva ulica 2",
"city": "Ljubljana",
"post-code": "1000",
"printer": "true",
"deposit": "false",
"accessible": "true",
"cards": "DECPVR",
"special-payments": "false",
"BNA": "false",
"transaction-receipt": "true",
"latitude": 46.051671,
"longitude": 14.505122
}
]I am trying to utilize pljson:
declare
w_req t_http_request := t_http_request();
w_res t_http_response;
w_vrni clob;
w_json pljson;
w_jsonValue pljson_value;
w_jsonList pljson_list;
w_test varchar2(100);
begin
w_req.url := 'https://api.bankart.si/psd2/hub/v1/' || 'ATMList';
w_req.add_header('x-ibm-client-id', 'client-id');
w_res := https_client.doGet (w_req, 'DB');
w_vrni := hibis_util.convertBlobToClob(w_res.content_blob,'AL32UTF8');
w_jsonList := pljson_list(w_vrni);
if w_jsonList is not null and w_jsonList.count > 0 then
for i in 1..w_jsonList.count loop
w_json := pljson(w_jsonList.get(i));
w_jsonValue := w_json.get('term-id');
w_test := w_jsonValue.get_string;
dopl(w_test);
end loop;
end if;
end;I'm able to extract, I don't know maybe location or address values from this JSON, but when I want to extract elements term-id or bank-id I get error PL/SQL ORA-30625: method dispatch on NULL SELF argument is disallowed. Maybe because of "-" sign between?
Thx!
I'm not familiar with PL/JSON, but I suspect the issue lies here:
for i in 1..w_jsonList.count loopJSON arrays are zero-indexed, so the first item is at location zero. This starts searching from location 1. You need to change the start and end values for the loop:
for i in 0..w_jsonList.count - 1 loopHere are examples using the PL/SQL object types added in 12.2:
declare
doc json_array_t;
item json_element_t;
begin
doc := json_array_t.parse ( '[
{
"term-id": "BA000000",
"bank-id": "111",
"location": "Poslovalnica banke",
"address": "Cankarjeva ulica 2",
"city": "Ljubljana",
"post-code": "1000",
"printer": "true",
"deposit": "false",
"accessible": "true",
"cards": "DECPVR",
"special-payments": "false",
"BNA": "false",
"transaction-receipt": "true",
"latitude": 46.051671,
"longitude": 14.505122
}
]' );
for i in 1 .. doc.get_size loop
item := doc.get ( i );
dbms_output.put_line ( treat ( item as json_object_t ).get ( 'term-id' ).to_String );
end loop;
end;
/
ORA-30625: method dispatch on NULL SELF argument is disallowed
declare
doc json_array_t;
item json_element_t;
begin
doc := json_array_t.parse ( '[
{
"term-id": "BA000000",
"bank-id": "111",
"location": "Poslovalnica banke",
"address": "Cankarjeva ulica 2",
"city": "Ljubljana",
"post-code": "1000",
"printer": "true",
"deposit": "false",
"accessible": "true",
"cards": "DECPVR",
"special-payments": "false",
"BNA": "false",
"transaction-receipt": "true",
"latitude": 46.051671,
"longitude": 14.505122
}
]' );
for i in 0 .. doc.get_size - 1 loop
item := doc.get ( i );
dbms_output.put_line ( treat ( item as json_object_t ).get ( 'term-id' ).to_String );
end loop;
end;
/
"BA000000"