Thursday 1 July 2021

APEX_SERVER_PROCESS and clob processing

Fetch CLOB size text on page load and submit page

Oracle APEX 32k size limitation workarounds


Since I had to do this few times around already this is for reference and reminders
 
Ask is to load and process the same text like JSON data on your APEX page that exceeds typical 32K in size. 
 
Let's first tackle getting the JSON data from a database to be parsed with JavaScript. 
 
In example of processing a payload on HansOnTable example I wrote earlier about. 
In my previous post I used APEX page item to store and process the data which obviously came with limitations.
 
Alternative approach getting the data into the initialization code would be to have  JS code: 
Where not so common method of fetch(url) is used to read the JSON data from application process app_get_clob:
This is then used in JS initialization code. 
 
Notice that we are here downloading the clob as a blob to bypass size issues.
 
Sweet, now that we changed the data we want to update our database table in a similar fashion. 
 
In the save process we will use apex.server.process and p_clob_01 parameter option.
where we then access this ajax passed parameter using
Similarly here we are using g_clob_01 vs g_x01..10 which typically get used.
 
This way we should be more than capable of loading large data loads and process them when data gets submitted back by the end users. 
 
Happy APEXing,
Lino 
 
p.s. example of the code can be found on https://codeshare.io/K8OZ1E

3 comments:

  1. Dear SLino, could you please share to me this code? Thanks so much.

    ReplyDelete
  2. When i just add code get data into the initialization code(at 'Func&Global Variable' and at 'Before header ApplicationProcess'), Json is alway return in a popup with content is data from my database. Data is get successfully, but not pass value to Interactive report.
    /***************** JS in GLOBAL FUNCTIONS******************************************/
    gJSON = ""
    async function getJson() {
    const url = "f?p=&APP_ID.:245:&SESSION.:APPLICATION_PROCESS=get_excel_data:NO:RP:::"
    const response = await fetch(url);
    const text = await response.text();
    gJSON = text;
    alert(gJSON);
    dataObject = JSON.parse(gJSON);
    //dataObject = JSON.parse(apex.item("P245_EXCEL_DATA").getValue());
    var hotElement = document.querySelector('#excel_region');
    var hotElementContainer = hotElement.parentNode;
    var hotSettings = {
    data: dataObject,
    licenseKey: 'non-commercial-and-evaluation',
    columns: [
    {
    data: 'REASON_ID',
    type: 'numeric'
    },
    {
    data: 'PROJECT_NUMBER',
    type: 'text'
    },
    {
    data: 'AMOUNT',
    type: 'numeric'
    }
    ],
    stretchH: 'all',
    width: 1500,
    height: 487,
    autoWrapRow: true,
    maxRows: 50000,
    manualRowResize: true,
    manualColumnResize: true,
    rowHeaders: true,
    colHeaders: [
    'REASON_ID',
    'PROJECT_NUMBER',
    'AMOUNT'
    ],
    manualRowMove: true,
    manualColumnMove: true,
    contextMenu: true,
    filters: true,
    dropdownMenu: true,
    language: 'en-US'
    };
    var hot = new Handsontable(hotElement, hotSettings);
    }

    /*processes name: 'get_excel_data' */
    DECLARE
    l_cursor SYS_REFCURSOR;
    l_file BLOB ;--DEFAULT EMPTY_BLOB();
    l_dest_offset INTEGER:=1;
    l_src_offset INTEGER:=1;
    l_blob_warn INTEGER:=0;
    l_lang_ctx INTEGER:=0;
    l_csid NUMBER :=0;
    BEGIN
    OPEN l_cursor FOR
    SELECT REASON_ID, PROJECT_NUMBER, AMOUNT
    FROM import_reasons
    ORDER BY PROJECT_NUMBER;
    APEX_JSON.initialize_clob_output;
    APEX_JSON.write( l_cursor);
    --Large data--
    DBMS_LOB.CREATETEMPORARY(lob_loc => l_file, cache => true, dur => DBMS_LOB.CALL);
    DBMS_LOB.CONVERTTOBLOB(
    l_file,--destination
    APEX_JSON.get_clob_output,--source
    DBMS_LOB.lobmaxsize,
    l_dest_offset,
    l_src_offset,
    l_csid,
    l_lang_ctx,
    l_blob_warn);
    --download blob--
    OWA_UTIL.MIME_HEADER('text/html',false);
    HTP.P('Content-Lenght: ' || DBMS_LOB.getlength(l_file));
    HTP.P('Content-Disposition: attachment; filename="content.html"');
    OWA_UTIL.HTTP_HEADER_CLOSE();
    WPG_DOCLOAD.DOWNLOAD_FILE(l_file);
    END;

    ReplyDelete
    Replies
    1. If i am understanding your problem correctly, you seem to have an issue with passing the JSON payload to the HandsOnTable initializatior?

      Delete