select more than 4000 characters in Report Oracle Apex

Question

I have a function in Oracle 21 version that return a CLOB variable, let's call it x (more than 4000 bytes) and I'm selecting this variable to display it in a classic report in Oracle Apex 21

But I always got the error: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 7258, maximum: 4000)

I tried also dbms.lob.substr but it won't work.

Is there any solution to this ?

Answer 1

Perhaps you should think it over - who wants to read CLOB contents on the screen? That's just too much text. A better option might be to let users download CLOB contents, if they want (and display e.g. the first 100 or 200 characters).

Otherwise, see if switching to a PL/SQL Dynamic Content region type helps. It means that you'd actually "draw" the report manually, reading CLOB contents in a loop and create the region using htp.p calls. Sample code is available on Universal theme pages. In case it becomes unavailable, here it is:

declare    
 cursor c_tasks is
   select task_name, assigned_to
     from eba_ut_chart_tasks
    where rownum < 5;     
begin
  sys.htp.p('');
  for a in c_tasks
  loop
    sys.htp.p('' || apex_escape.html(a.task_name) || ' (' || apex_escape.html(a.assigned_to) || ')' );
  end loop;
  sys.htp.p('');
end;

---

It's up to you, but ... I'd go for the 1st option.