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:
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.
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
Lino
p.s. example of the code can be found on https://codeshare.io/K8OZ1E
Dear SLino, could you please share to me this code? Thanks so much.
ReplyDeleteWhen 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.
ReplyDelete/***************** 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;
If i am understanding your problem correctly, you seem to have an issue with passing the JSON payload to the HandsOnTable initializatior?
DeleteThis article offers excellent guidance on handling APEX_SERVER_PROCESS and CLOB processing—crucial for developers working with large data! If you're also a gamer, rancher desktop is a fantastic tool to help you manage your game library while you tackle these advanced database tasks.
ReplyDelete