This discussion is archived
13 Replies Latest reply: Mar 7, 2013 9:19 AM by Nattu RSS

Issue with printing CLOB using htp.prn

Nattu Explorer
Currently Being Moderated
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
  • 1. Re: Issue with printing CLOB using htp.prn
    riedelme Expert
    Currently Being Moderated
    Nattu wrote:
    However, if the clob size is too small, it is printing. Is there in any restriction in size for printing clob?
    There is probably a physical limit to the number of bytes htp.prn() can use. Find it in the documentation. Use the value to loop through the CLOB contents using DBMS_LOB.SUBSTR() to write the clob in sections, logically something like (untested)
    set byte length
    set byte counter to 1
    get length of clob
    while byte counter + byte length < length of clob
      write line
    write any unwritten bytes
    Beware endless loops!!!!!
  • 2. Re: Issue with printing CLOB using htp.prn
    jkallman Employee ACE
    Currently Being Moderated
    Hi Natarajan,

    Here's a chunk of code I wrote to emit the contents of a CLOB (an AWR report, actually). You should be able to adapt this to your needs.
    declare
        l_amt               integer := 4000;
        l_pos               integer := 1;
        l_buf               varchar2(32000);
    begin
        for c1 in (select *
                     from awr_reports
                    where id = :P5_ID) loop
    
            loop
                begin
                    dbms_lob.read( c1.awr_report, l_amt, l_pos, l_buf );
                    l_pos := l_pos + l_amt;
                    l_amt := 4000;
    
                    sys.htp.prn( l_buf );
    
                exception
                    when no_data_found then
                        exit;
               end;
            end loop;
        end loop;
    end;
    Joel
  • 3. Re: Issue with printing CLOB using htp.prn
    Nattu Explorer
    Currently Being Moderated
    Thank you Joel.

    I find that the issue is not really with the size but with the diacritic characters. The error is thrown when trying to push these characters to htp.pnr. (Example Årsjö, Sören (Ling/Trans. - Konai - 4136). The character set of our DB is AL32UTF8, where as it seems HTP package can only work with UTF-8 character set.

    Not sure how to go about this.

    Any suggestion/help is highly appreciated.

    Regards,

    Natarajan

    Edited by: Nattu on Mar 6, 2013 8:38 AM
  • 4. Re: Issue with printing CLOB using htp.prn
    jkallman Employee ACE
    Currently Being Moderated
    Hi Natarajan,

    +>> The character set of our DB is AL32UTF8, where as it seems HTP package can only work with UTF-8 character set.+

    That's not a true statement. What leads you to believe that? Have you attempted to use my proposed solution?

    Joel
  • 5. Re: Issue with printing CLOB using htp.prn
    Nattu Explorer
    Currently Being Moderated
    Yes, You are correct Joel. I misunderstood from some reference which led me to think wrongly. Yes, I applied your code, it works just fine for the data with normal characters. However it still throws the error if the content has multi byte characters from a different character set. To the strange, the same characters work fine with htp.prn, if they are passed as a simple varchar2 variable. In the real scenario I need to generate the xml that is always pushed into a clob, so I need to use the content from the clob.
    CREATE OR REPLACE PROCEDURE nat_test_lsp_ws_4
    IS
    
       l_amt NUMBER DEFAULT 4000;
       l_pos NUMBER DEFAULT 1;
       l_buf VARCHAR2(32000);
       v_clob CLOB :=  '<?xml version="1.0"?>
    <ROWSET>
     <ROW>
      <ROW_NUM>xyz:162</ROW_NUM>
      <EXA_DATA1>01-MAY-94</EXA_DATA1>
      <EXA_DATA4>5777</EXA_DATA4>
      <EXA_DATA5>PNB</EXA_DATA5>
      <EXA_DATA7>XYZ Branch</EXA_DATA7>
      <EXA_DATA9>Årsjö, Sören (Ling/Trans. Worker - mani - 0000)</EXA_DATA9>
      <EXA_DATA11>OKNOANIAI</EXA_DATA11>
      <EXA_DATA12>Sample program</EXA_DATA12>
      <EXA_DATA14>Unknown Region</EXA_DATA14>
     </ROW>
     <ROW>
    </ROWSET>
    ';
     
    BEGIN
    
    
      htp.htmlopen;
      htp.bodyopen;
      /* This works just fine */
      htp.prints('<EXA_DATA9>Årsjö, Sören (Ling/Trans. Worker - mani - 4136)</EXA_DATA9>');
    
     LOOP 
       BEGIN 
          dbms_lob.read(v_clob, l_amt, l_pos, l_buf);
          l_pos := l_pos + l_amt;
          l_amt := 4000;
          /* here it is giving trouble */
          htp.prints(l_buf);
          EXCEPTION 
             WHEN no_data_found THEN 
             EXIT;
       END;
       
      END LOOP; 
      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;
    If I am running this, I am still getting error. However if I remove the line which has the diacritic characters <EXA_DATA9>... </EXA_DATA9>, it works. Another strange thing, the below line also works.
     /* This works just fine */
      htp.prints('<EXA_DATA9>Årsjö, Sören (Ling/Trans. Worker - mani - 4136)</EXA_DATA9>');
    Regards,

    Natarajan

    Edited by: Nattu on Mar 7, 2013 6:37 AM
    Changed l_buf as Joel wrote.
  • 6. Re: Issue with printing CLOB using htp.prn
    fac586 Guru
    Currently Being Moderated
    Nattu wrote:
    Yes, You are correct Joel. I misunderstood from some reference which led me to think wrongly. Yes, I applied your code, it works just fine for the data with normal characters. However it still throws the error if the content has multi byte characters from a different character set. To the strange, the same characters work fine with htp.prn, if they are passed as a simple varchar2 variable. In the real scenario I need to generate the xml that is always pushed into a clob, so I need to use the content from the clob.
    Note that for CLOBs the <tt>amount</tt> parameter to <tt>dbms_lob.read</tt> specifies the number of characters to be read, not the number of bytes. Spot the difference between Joel's variables:
    declare
        l_amt               integer := 4000;
        l_pos               integer := 1;
        l_buf               varchar2(32000);
    ...
    and yours:
       l_amt NUMBER DEFAULT 4000;
       l_pos NUMBER DEFAULT 1;
       l_buf VARCHAR2(4000);
       l_length NUMBER DEFAULT 0;
    ...
    Joel reads 4000 characters at a time into a buffer that is 32000 bytes long&mdash;4 times bigger. That provides sufficient space to handle up to 4000 characters encoded using a character set that may use up to 4 bytes to store 1 character. You need to increase the size of your buffer to 32000.

    The following code is completely useless and should be removed:
    EXCEPTION
       WHEN OTHERS THEN
       dbms_output.put_line(dbms_utility.format_error_stack);
       dbms_output.put_line(dbms_utility.format_error_backtrace);
       RAISE;
  • 7. Re: Issue with printing CLOB using htp.prn
    jkallman Employee ACE
    Currently Being Moderated
    Perfectly stated, Paul. You beat me to the response.

    joel
  • 8. Re: Issue with printing CLOB using htp.prn
    Nattu Explorer
    Currently Being Moderated
    Well, I tried with various values for the l_buf, l_buf varchar2(32000) also does not work. The exception block is just for me to quickly see what the error and where it exactly coming. Thanks fac586 for your response.

    Regards,

    Natarajan

    Edited by: Nattu on Mar 7, 2013 6:37 AM
  • 9. Re: Issue with printing CLOB using htp.prn
    jkallman Employee ACE
    Currently Being Moderated
    Natarajan,

    Your procedure works fine in the context of Application Express. If you run your procedure from SQL Commands, no error will be encountered. If you run it from SQL*Plus or outside of an APEX context, then you'll encounter the error that you reported.

    To rectify your problem, include the following line as the first executable statement of your procedure:
    sys.htp.htbuf_len := 63;
    Joel
  • 10. Re: Issue with printing CLOB using htp.prn
    ascheffer Expert
    Currently Being Moderated
    If you dont' have an exception handler most Oracle clients will quickly give you the exact error message and locations
    where the error occured. And that will give also an indication if your buffer is to small or that the error occurs inside htp.prn or else where.
  • 11. Re: Issue with printing CLOB using htp.prn
    ascheffer Expert
    Currently Being Moderated
    Or try
    alter package htp COMPILE NLS_LENGTH_SEMANTICS='CHAR' REUSE SETTINGS;
  • 12. Re: Issue with printing CLOB using htp.prn
    Nattu Explorer
    Currently Being Moderated
    Thank you so much Joel, it finally works. That particular line makes the trick. :)

    Regards,

    Natarajan
  • 13. Re: Issue with printing CLOB using htp.prn
    Nattu Explorer
    Currently Being Moderated
    I altered the package as mentioned, but it throws the same error. Thanks for your response.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points