APEX: Fetch CLOB data via AJAX callback.

April 6, 2020 cruepprich

In my use case, I needed to fetch data (JSON) larger than 32k into a JavaScript variable. In APEX versions five and before, I was able to use the apex.ajax.clob function to read clob data from a special collection called CLOB_DATA. In the new versions of APEX (18 and above) this function no longer exists.

After doing some research I found this blog post from Vinish Kapoor, that got me on the right track.

To get this to work I used the following:

A global variable to hold the JSON data (gJSON)
This is to hold my JSON data for later processing with JavaScript
An Ajax Callback Application function (app_get_clob)
This PL/SQL process fetches the CLOB JSON data from the database.
An asynchronous JavaScript function (getJson()) on the page I’m working on.
This function invokes the Ajax Callback and stores the CLOB JSON data in the gJSON variable.
A page with:
1. A page item to hold a variable to pass to the Ajax Callback.
2. A static region with a div to display the output.
3. A page button to fire a dynamic action.
4. A dynamic action that sets the session state of the page item, and calls the javascript function.

Global Variable and JavaScript Function

Define the global variable and asynchronous JavaScript function in the JavaScript Function and Global Variable Declaration of the APEX page. This function calls the Ajax Callback Application function, parses the output as JSON and assigns it to the global gJSON variable.

gJSON = "" //gobal variable for later processing

// Async examples: https://javascript.info/fetch

async function getJson() {
   const url = "f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=app_get_clob:NO:RP:::"

   const response = await fetch(url);

   const text = await response.text();
    
   gJSON = JSON.parse(text)

   $('#output').html(text.slice(0, 80) + '...<hr> Length: '+gJSON.data.length)

}

Ajax Callback Function

The application process Ajax callback function gets the CLOB data from a table named cmr_clob. It converts the CLOB to a BLOB and invokes the browser to download the BLOB. This download is caught by the fetch in the JavaScript above.

-- Code thanks to Vinish Kapoor @FoxInfoTech
declare
  l_clob           clob;
  l_blob           blob;
  l_mime_type      varchar2(30) := 'text/html';
  l_dest_offset    integer := 1;
  l_src_offset     integer := 1;
  l_lang_context   integer := dbms_lob.default_lang_ctx;
  l_warning        integer;
  l_length         integer;
begin

    dbms_lob.createtemporary(lob_loc  => l_clob,
                             cache    => true,
                             dur      => dbms_lob.call);
                                    
    
    dbms_lob.createtemporary(lob_loc => l_blob,
                             cache   => true,
                             dur     => dbms_lob.call);                                   
    
    -- Fetching clob data from my test_table
    select clob_data  
      into l_clob
      from cmr_clob
     where id = :P10_INPUT;

    -- tranform the input clob into a blob of the desired charset
    begin
        dbms_lob.converttoblob( dest_lob     => l_blob,
                                src_clob     => l_clob,
                                amount       => dbms_lob.lobmaxsize,
                                dest_offset  => l_dest_offset,
                                src_offset   => l_src_offset,
                                blob_csid    => nls_charset_id('WE8MSWIN1252'),
                                lang_context => l_lang_context,
                                warning      => l_warning
                                );
    end;                          
                
    owa_util.mime_header(l_mime_type,false);
    htp.p('content-length: ' || dbms_lob.getlength(l_blob)); 
    owa_util.http_header_close;  
    wpg_docload.download_file(l_blob);

exception 
when no_data_found then
    null;
 when others then 
   null;
end;