CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
K.Andersson
K.Andersson

Reputation: 45

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

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;

Upvotes: 1

Views: 2316

Answers (3)

Saad Ahmad
Saad Ahmad

Reputation: 403

PL/SQL provides a dbms_lob package to manipulate this data type. The way I had addressed in a different technology (zope/python) similar problem was to create a framework: To read from db, it returned data as multipe rows To write to db, it would send data as multipel calls and server eventually combined it.

You can see that here Blob Journey from Database To Browser

Upvotes: 1

K.Andersson
K.Andersson

Reputation: 45

PL/SQL in APEX are limited to 32k, pl/sql treats clobs as varchar and thats it. My problem cannot be solved within APEX

Upvotes: 0

kfinity
kfinity

Reputation: 9091

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

Upvotes: 0

Related Questions