Strings in UTF8 Database

From: <Number.One.Mike_at_gmail.com>
Date: 24 May 2007 03:54:43 -0700
Message-ID: <1180004083.873561.144100@w5g2000hsg.googlegroups.com>

Hello,

I wonder if anyone could explain why I get the following behaviour. This is the same query run on two databases where as far as we know the only difference is that the first database is a UTF8 database and the second an old school ASCII7.

It's not terribly important, beyond that I think I should understand why the answer is different on a UTF8 database.

Thanks in advance,

Mike.

SQL> select * from v$version
  2 /

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production

SQL>
SQL> -- How long is a 40 charecter string?
SQL> select length(
  2          chr(64)||chr(59)||chr(36)||chr(59)||chr(129)||chr(96)||
chr(158)||chr(225)||chr(116)||chr(59)||
  3          chr(115)||chr(171)||chr(215)||chr(29)||chr(174)||chr(76)||
chr(22)||chr(94)||chr(28)||chr(35)||
  4          chr(132)||chr(52)||chr(89)||chr(171)||chr(191)||chr(93)||
chr(50)||chr(150)||chr(199)||chr(52)||
  5          chr(4)||chr(127)||chr(144)||chr(21)||chr(215)||chr(238)||
chr(64)||chr(59)||chr(36)||chr(59)
  6          ) the_length

  7 from dual
  8 /

THE_LENGTH

---

        26

SQL> conn mijones_at_oradev10
Enter password: *******
ERROR:
ORA-28002: the password will expire within 1 days

Connected.
SQL> select * from v$version
  2 /

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production

SQL>
SQL> -- How long is a 40 charecter string?
SQL> select length(
  2          chr(64)||chr(59)||chr(36)||chr(59)||chr(129)||chr(96)||
chr(158)||chr(225)||chr(116)||chr(59)||
  3          chr(115)||chr(171)||chr(215)||chr(29)||chr(174)||chr(76)||
chr(22)||chr(94)||chr(28)||chr(35)||
  4          chr(132)||chr(52)||chr(89)||chr(171)||chr(191)||chr(93)||
chr(50)||chr(150)||chr(199)||chr(52)||
  5          chr(4)||chr(127)||chr(144)||chr(21)||chr(215)||chr(238)||
chr(64)||chr(59)||chr(36)||chr(59)
  6          ) the_length

  7 from dual
  8 /

THE_LENGTH

---

        40

SQL> Received on Thu May 24 2007 - 05:54:43 CDT