CODEWITHSUNDEEP
oracle-apex
Android
Android

Reputation: 169

Oracle Apex Getting JSON.WRITER.NOT_OPEN Error

I tried the following code in SQL workshop and it ran. I tried the same code in an application after removing DBMS_OUT_PUT using it in a set value action and I get the error message: Ajax call returned server error ORA-20987: APEX - JSON.WRITER.NOT_OPEN - Contact your application administrator. Details about this incident are available via debug id "354017". for Set Value. However, the debug logs don't provide any additional details.

What am I missing? It worked in the application a few days ago.

 declare
  l_request_url varchar2(32767);
  l_content_type varchar2(32767);
  l_content_length varchar2(32767);
  l_response varchar2(10000);
  l_body_clob clob;
  l_name varchar2(200);
  download_failed_exception exception;
  j apex_json.t_values;
  n_customer_id number;
begin
  l_request_url := 'https://mysterysite.com/api/v3/customer';
  APEX_JSON.initialize_clob_output;
  APEX_JSON.open_object;
  APEX_JSON.write('company', :P5_COMPANY);
  APEX_JSON.write('bill_addr1', :P5_BILL_ADDR1);
  APEX_JSON.write('bill_addr2', :P5_BILL_ADDR2);
  APEX_JSON.write('bill_city', :P5_BILL_CITY);
  APEX_JSON.write('bill_postcode', :P5_BILL_POSTCODE);
  APEX_JSON.write('superuser_email', :P5__YOUR_EMAIL);
  APEX_JSON.close_object;
  l_body_clob :=APEX_JSON.get_clob_output;
  APEX_JSON.free_output;
     apex_web_service.g_request_headers.delete();
      apex_web_service.g_request_headers(1).name := 'Content-Type';  
      apex_web_service.g_request_headers(1).value := 'application/json';
  l_response := apex_web_service.make_rest_request(
    p_url => l_request_url
    , p_http_method => 'POST'
    , p_username => 'junk username'
    , p_password => 'junkpassword'
    , p_body => l_body_clob
  );
   if apex_web_service.g_status_code != 201 then
   DBMS_OUTPUT.PUT_LINE('ERROR');
   else 
   apex_json.Parse (j, l_response);
   n_customer_id := apex_json.get_varchar2 (p_values => j, p_path => 'response.id');
   DBMS_OUTPUT.PUT_LINE(n_customer_id);
   end if;
end;




Product Build   22.2.1
Schema Compatibility    2022.10.07
Patch Version   1
Last Patch Time 12/26/2022 05:32:28 PM
Last DDL Time   12/26/2022 04:37:11 PM
Host Schema ORDS_PLSQL_GATEWAY
Application Owner   APEX_220200

Upvotes: 0

Views: 621

Answers (2)

Alan Arentsen
Alan Arentsen

Reputation: 1

Try to set the preserve parameter to true in the call to initialize_clob_output. In what context are you running this code? Another process probably also uses apex_json in the same code.

Upvotes: 0

Android
Android

Reputation: 169

Not a complete solution, just a work around.

Replace the set value action with execute server side code action. In the code replace RETURN with apex_util.set_session_state ('item_name', value);

Upvotes: 1

Related Questions