Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

APEX - apex.server.process pass clob to client

PJFeb 18 2022 — edited Feb 23 2022

ISSUE: I am using javascript (jsPdf) to produce a PDF report on the client - and the data is passed from the DB as a json string. All works fine - unless the data exceeds 32k. How do I pass the clob to the client?

Here is the relevent code:
Within the js file ..

apex.server.process(
   'Generate', {
       x01: apex.item("P9001_SUBSCRIBER_ID").getValue(),
       x02: apex.item("P9001_REQUESTER_NAME").getValue(),
       x03: apex.item("P9001_SUBJECT_FILE_ID").getValue(),
   }, {
       success: function(result) {
         displayLog('start', 'result', result);
         console.log(result)
         printPdf(result);
       },
       error: function(error) {
           console.log('error');
           console.log(error);
       },
       dataType: 'json',
       async: false
   });
};

The process Generate (simplified):

declare
  l_data clob;
  l_subscriber_id integer             := APEX_APPLICATION.G_X01;
  l_requestor_username varchar2(4000) := APEX_APPLICATION.G_X02;
  l_subject_file_id  integer          := APEX_APPLICATION.G_X03;
  l_subject_file_type_code   varchar2(16);
  l_report_name              varchar2(64);

begin
  plj_lg.push('APEX-1203:9001','PDFReport');
  l_report_name := 'TestReport';
  plj_lg.d('Report running: '|| l_report_name);
    
  l_data := APX_CDD.REPORT_DATA_AS_JSON (
                              p_report_name        => l_report_name,
                              p_subscriber_id      => :G_SUBSCRIBER_ID,
                              p_requestor_username => lower(plj_auth.get_current_user), 
                              p_subject_file_id    => :P0_SUBJECT_FILE_ID,
                              p_module_id          => :P0_MODULE_ID,
                              p_report_version     => '1.0');
  
  htp.p(l_data);
  
  plj_lg.d('Report complete.  Clob size: '|| dbms_lob.getlength(l_data) );
  plj_lg.pop;
  
exception
    when others then
      htp.p(l_data);
      plj_err.handle();
end;

HTP.P has a limit of 32K -1 bytes
How do in out a clob to js?
The error from the console:

promise: function promise(e)​
readyState: 4
​
responseText: "sqlerrm:ORA-06502: PL/SQL: numeric or value error\n"

Thank you
This post has been answered by PJ on Feb 23 2022
Jump to Answer

Comments

PJ
Answer

No suggestions. Oh well.
This approach helped and then worked it out. (from Oracle and a collegue)
APEX: Fetch CLOB data via AJAX callback. – Christoph's 2 Cents (0 Bytes)I created an Application process and then got the data with this:

const getJson = async () => {

   console.log('getJson');

   const url = 'f?p=' + $v(pFlowId) + ':0:' + $v(pInstance) + ':APPLICATION_PROCESS=GET_REPORT_DATA:NO:RP:::';
   const response = await fetch(url);
   const text = await response.text();

   const gJSON = JSON.parse(text);
   return gJSON;
}

const initReport = async () => {

    const data = await getJson();
    ....
}

Then do not use apex.server.process at all
I hope that helps someone out there
paul

Marked as Answer by PJ · Feb 24 2022
1 person found this helpful
1 - 1

Post Details

Added on Feb 18 2022
#apex-discussions
1 comment
1,140 views