APEX

Hi,

Oracel 11g R2
Apex 4.1

I got a reference from this forum on how to print CLOB data using htp.prn.

However when I run my procedure I get the error

 ORA-06502: PL/SQL: numeric or value error: character string buffer too small 

The clob length is 195734.

The code is

 

CREATE OR REPLACE PROCEDURE nat_test_lsp_ws_3
IS

   l_amt NUMBER DEFAULT 8191;
   l_offset NUMBER DEFAULT 1;
   l_length NUMBER DEFAULT 0;
   v_clob CLOB;
   
 
BEGIN

SELECT dbms_xmlgen.getxml('select * from table(pkg_bp_eisa.lsp_exa_ws(7))') into v_clob FROM dual;
 htp.htmlopen;
  htp.bodyopen;
  l_length := dbms_lob.getlength(v_clob);
  dbms_output.put_line('Length of Clob:='||l_length);
  IF l_length > 0 THEN 
  WHILE (l_offset < l_length)
  LOOP
   htp.prn(dbms_lob.substr(v_clob, l_amt, l_offset));
   l_offset := l_offset + l_amt;
  END LOOP;
  END IF;
  htp.bodyclose;
  htp.htmlclose;
  

EXCEPTION
   WHEN OTHERS THEN
   dbms_output.put_line(dbms_utility.format_error_stack);
   dbms_output.put_line(dbms_utility.format_error_backtrace);
   RAISE;

END;

I get the error on the line htp.prn...

However, if the clob size is too small, it is printing. Is there in any restriction in size for printing clob?

Thanks in advance.

Regards,

Natarajan