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;

kfinity kfinity · Accepted Answer · 2019-03-22T13:21:12

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();
    }

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

3 Answers