Home

The htp.p procedure in Oracle RDBMS does not work (well) with multibyte charactersets

Wed, 2012.09.05 - 16:26 — müzso
http://www.orafaq.com/forum/t/101943/2/
A long time ago I wrote my own framework for PL/SQL projects and one of the first things I did was to create a proper alternative to the builtin htp package. The Oracle supplied variant has lots of limitations and a few bugs/problems too. Since I worked for years on systems that had only English speaking users, I never faced the problem of htp.p (or htp.prn or htp.print) with multibyte characterset databases/strings. The code snippet written by amber.jah demonstrates the problem quite well.

begin
  -- Add padding to get a total number of rows >= pack_after (60)
  -- See the source of SYS.htp for details on pack_after.
  for n in 1 .. 58 loop
    htp.p('x');
  end loop;

  -- Now whatever we prn() goes into htp.htcurline, which is defined as
  -- VARCHAR2(255)
  -- Note: This means 255 BYTES by default, since NLS_LENGTH_SEMANTICS
  -- has a default value of 'BYTE'.

  -- Start with 242 characters/bytes.
  htp.prn(rpad('x',242,'x'));

  -- There is now room left for 13 bytes.
  -- Add 4 characters / 12 bytes.
  -- Note: characters are 4 UTF-8 encoded Tamil language characters.
  htp.prn(utl_raw.cast_to_varchar2('E0AEA4E0AEAEE0AEBFE0AEB4'));

  -- There is now room for (13-12=) 1 more byte. But htp thinks there is
  -- room for (13-4=) 9 more, since it used length().
  -- Adding 3 characters/bytes causes ORA-06502: PL/SQL: numeric or value
  -- error: character string buffer too small
  htp.prn('xxx');
end;

The above code throws an exception at the last line (htp.prn('xxx');) in an AL32UTF8 charactetset database (and the RDBMS is 11g, aka. 11.2.0.1.0).

My workaround was (even though I did not know about this bug at the time Smile ) to keep my own buffer of HTTP output and push it to the client via wpg_docload.download_file in a BLOB. This way I could produce all sorts of HTTP responses (eg. gzip compressed response or any other binary data Smile ) since I had total control over the output buffer.