Re: ora-600 with table...cast... on / al32utf8 / linux

From: codadilupo <>
Date: Tue, 05 Feb 2008 09:34:00 +0100
Message-ID: <47a81f78$0$16034$>

Vladimir M. Zakharychev wrote:

> I'd consider it a bug-like feature. :) PL/SQL VARCHAR2s have a limit
> of 32K *bytes*, so it may seem logical that declarations of VARCHAR2s
> with CHAR size semantics that may exceed 32Kb limit shouldn't be
> allowed. The problem is - how do you detect such declarations with
> variable-length code point encodings, like UTF-8? In UTF-8, a code
> point can occupy from one to four bytes - should CHAR semantics
> declarations be limited to 8091 chars assuming the worst case?
> Obviously this would be incorrect as you can legally fill a 32767
> bytes long UTF-8 string with 32767 ASCII chars (as ASCII is a subset
> of UTF-8 and each ASCII character is encoded with single byte in
> UTF-8;) let alone the fact that such charset-dependent limitations
> will hinder PL/SQL's general portability as the same declaration could
> be valid in one database and invalid in another, with a different
> charset. Such limit can't be imposed syntactically and thus maximum
> possible length is always allowed to be declared. However, when
> actually allocating memory Oracle seems to be preparing for the worst
> case and overallocates, hitting the limit on size in bytes. Don't
> think this can be [easily] fixed in Oracle code, so you should fix
> yours and plan/test for similar issues whenever you touch Unicode,
> whether explicitly or implicitly. :)

I understand. But there are two strange things related to this "bug-like feature".

  1. this simple piece of code works:

  x varchar2(32767 char) ;
  x := rpad('a',32767,'a') ;
end ;

2. the problem I showed in my first post doesn't reproduce in an Oracle XE (therefore AL32UTF8 instance

Do you have an explaination for that?

Thank you. Kind regards, C. Received on Tue Feb 05 2008 - 02:34:00 CST

Original text of this message