Home

Bug in CHR() if used in PL/SQL? (9iR2)

2006, May 11 - 12:01 — müzso
I suspect I've found a bug (quite serious ... at least to me) in the PL/SQL implementation of CHR().

Let's assume you use Oracle RDBMS 9.2.0.1.0 on an x86 server.
Your database has the following NLS parameters:
  • NLS_CHARACTERSET = EE8ISO8859P2
  • NLS_NCHAR_CHARACTERSET = AL16UTF16
  • NLS_SORT = BINARY
If you change the NLS_SORT to XHUNGARIAN by running ALTER SESSION SET NLS_SORT=XHUNGARIAN, then CHR() in PL/SQL gets crazy and starts returning garbage. Shocked The effect is that it starts to return a character equal to a CHR(0), regardless of its ascii code parameter.
I've to admit that I'm not sure about how strings are stored by the PL/SQL engine, but it seems to me as if it'd store the length of the strings and data (and not the C-like zero-termination). However sometimes it seems as if some string-related functions would stop processing at the first zero-byte that they find in the string. Shocked So I'm a bit buffled about this ... but nevertheless: the bug is there for sure.

If I set NLS_SORT to plain old "HUNGARIAN", then CHR() works OK! But this is not an option since I need XHUNGARIAN for correct sorts in "ORDER BY" clauses of SQL statements and a simple "HUNGARIAN" NLS_SORT wont do it (it sorts the Hungarian double letters -eg. "sz", "gy", "ny", "ty", ...- in a wrong way). Sad

I checked this "bug" also on a different servers/databases:
HPUX 11i, same Oracle version (and checked also on an x86 with same Oracle version)
NLS parameters:
  • NLS_CHARACTERSET = AL32UTF8
  • NLS_NCHAR_CHARACTERSET = AL16UTF16
  • NLS_SORT=HUNGARIAN
Here even an NLS_SORT of "XHUNGARIAN" did not mess up the outputs of CHR(). Shocked

I've written a small test to check whether the bug is present on a system or not:
SET SERVEROUTPUT ON
SET DEFINE OFF
BEGIN
  dbms_output.enable(100000);
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=XHUNGARIAN';
  FOR i IN 1..255 LOOP
    dbms_output.put_line('idx: ' || TO_CHAR(i, 'FM000'));
    dbms_output.put_line('chr: _' || CHR(i) || '_');
    dbms_output.put_line('');
  END LOOP;
END;
/

The symptom of the bug with the above test code is that nothing appears behind the "chr: _" string ... not even the closing "_" character.

Syndicate content