In Oracle APEX I cannot append data more than 32kb in CLOB

Question

I have text stored in a database table, many short rows about 70-90 characters long fields. (of historical reasons). I want to append these fields (rows) into an CLOB in an APEX (CKEditor) and it does exeed 32k in many cases. I have tried in many ways but it seems to be some limit. My code works fine as long the text is less than 32k! My plan is to save it in a new table and there use a clob instead. I have APEX 5.01. I get 'ORA-06502: PL/SQL: numeric or value error' when it is over 32k. declare l_clob CLOB; l_seq number; cursor textrader_cur is SELECT F1NR,FHTTYP,RADNR,FHTEXT,DATUM,UPPTAGEN,NUSER FROM DATATXT WHERE DATATXT.F1NR = :P10_F1NR ORDER BY F1NR,FHTTYP,RADNR; TYPE datatext_typ IS TABLE OF DATATXT%ROWTYPE INDEX BY PLS_INTEGER; l_datatext datatext_typ; begin l_clob := empty_clob(); DBMS_LOB.CREATETEMPORARY(l_clob,true); apex_collection.create_or_truncate_collection(p_collection_name => 'TEXT'); select count(1) into x from DATATXT@HUMANAUTV WHERE DATATXT.F1NR = :P10_F1NR; if x > 0 then open textrader_cur; loop fetch textrader_cur bulk collect into l_datatext LIMIT 200; for indx in 1..l_datatext.COUNT loop y := length(l_datatext(indx).fhtext); dbms_lob.writeappend (l_clob,y,l_datatext(indx).fhtext); --l_clob := l_clob || l_datatext(indx).fhtext; -- This causes same error end loop; EXIT WHEN l_datatext.COUNT = 0; end loop; close textrader_cur; l_seq := apex_collection.add_member(p_collection_name => 'TEXT', p_d001 => sysdate, p_d002 => sysdate, p_n001 => dbms_lob.getlength(l_clob), p_clob001 => l_clob); -- :P10_WP := l_clob; SELECT clob001 into :P10_WP FROM APEX_COLLECTIONS WHERE SEQ_ID = l_seq AND COLLECTION_NAME='TEXT'; end if; end; JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

The problem is the last line in your code. Session state variables (e.g. P10_WP) are all VARCHAR2, and limited to 32767 characters. You can see that in the APEX functions that call them (example). So you can't assign more than 32k characters to a PL/SQL page item. But obviously you can put more than 32k characters in an HTML form item! So it's an awkward workaround - you have to get the clob data in and out of the HTML form item without using APEX page items. Typically this is done by writing the clob to a Collection, then using AJAX calls to an Application Process to retrieve it, since JavaScript has no problem with character limits. It looks like you've gotten partway there on your own, with your TEXT collection, but you'll still have to write your own On-Demand Application Process so you can load the collection into JavaScript, and put it in the HTML form item from there. It'll be easier if you use the built-in apex.ajax.clob functionality with the CLOB_CONTENT collection. I looked over a few articles about this, and this one is pretty well written and straightforward. The short version is to change your collection name in your code to CLOB_CONTENT, then put this JavaScript function on your page and call it. function clob_get(){ var clob_ob = new apex.ajax.clob( function(){ var rs = p.readyState if(rs == 1||rs == 2||rs == 3){ $x_Show('AjaxLoading'); }else if(rs == 4){ $s('P10_WP',p.responseText); $x_Hide('AjaxLoading'); }else{return false;} } ); clob_ob._get(); }