Hello,
I'm using Oracle 18c and Apex 19.1 on a Windows 10 platform.
I have the following code which works fine in SQL Developer, but fails in Apex 19.1. It fails on the statement l_js_obj0 := json_object_t(l_js_arr.get(0).to_string);
If I delete everything from that statement forward, it works fine in Apex. If I View Debug I see a message: Hiding error additional_info, as it contains ORA error message: ORA-40478: output value too large (maximum: )
Why would this fail in Apex 19.1?
DECLARE
l_param_names apex_application_global.vc_arr2;
l_param_values apex_application_global.vc_arr2;
l_resp clob;
l_json json_object_t;
l_js_arr json_array_t;
l_js_obj0 json_object_t;
l_kind varchar2(512);
l_title varchar2(512);
l_publisher varchar2(512);
BEGIN
--specify parameter names and values
l_param_names(1) := 'q';
l_param_values(1) := '9781484204856';
--invoke the service
l_resp := apex_web_service.make_rest_request(
p_http_method => 'GET',
p_url => 'https://www.googleapis.com/books/v1/volumes',
p_parm_name => l_param_names,
p_parm_value => l_param_values,
p_wallet_path => 'file:C:\app\product\18.0.0\dbhomeXE\bin\owm\wallets',
p_wallet_pwd => '(my_password)'
);
-- Place the l_resp CLOB into the JSON object l_json
l_json := json_object_t(l_resp);
--get some values either using json_object_t
dbms_output.put_line('Kind using object type: ' ||
l_json.get_string('kind'));
dbms_output.put_line('totalItems using object type: ' ||
l_json.get_number('totalItems'));
l_kind := l_json.get_string('kind');
-- Place the l_json "items" part of the payload into the JSON array l_js_arr
l_js_arr := l_json.get_array(key => 'items');
-- Place the first response (0) and place it into the JSON object l_js_obj0
-- In this app, we should receive only one response per submitted ISBN.
l_js_obj0 := json_object_t(l_js_arr.get(0).to_string);
-- Obtain l_title from the l_js_obj0 object.
l_title := l_js_obj0.get_object('volumeInfo').get_string('title');
dbms_output.put_line('Title: ' || l_title);
--or json_table
for r in (select *
from json_table(l_resp columns kind varchar2(50) path kind,
tot varchar2(50) path "totalItems")) loop
dbms_output.put_line('Using json_table : ' || r.kind || ', ' || r.tot);
end loop;
l_publisher := l_js_obj0.get_object('volumeInfo').get_string('publisher');
dbms_output.put_line('Publisher: ' || l_publisher);
for r in (select *
from json_table(l_resp , '$.items[0].volumeInfo.authors[*]'
columns author varchar2(50) path '$' )) loop
dbms_output.put_line( r.author);
end loop;
END;