13 Replies Latest reply: Mar 7, 2013 11:19 AM by Nattu RSS

    Issue with printing CLOB using htp.prn

    Nattu
      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
          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
            joelkallman-Oracle
            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
              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
                joelkallman-Oracle
                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
                  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
                    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
                      joelkallman-Oracle
                      Perfectly stated, Paul. You beat me to the response.

                      joel
                      • 8. Re: Issue with printing CLOB using htp.prn
                        Nattu
                        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
                          joelkallman-Oracle
                          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
                            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
                              Or try
                              alter package htp COMPILE NLS_LENGTH_SEMANTICS='CHAR' REUSE SETTINGS;
                              • 12. Re: Issue with printing CLOB using htp.prn
                                Nattu
                                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
                                  I altered the package as mentioned, but it throws the same error. Thanks for your response.