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

On Feb 4, 1:08 pm, codadilupo <codadil..._at_operamail.com> wrote:
> Vladimir M. Zakharychev wrote:
> > It looks like you stretched the database beyond its limits: one of the
> > possible causes of ORA-600 [15201] is when allocation of a memory
> > chunk larger than 32k is attempted, which is most probably the case
> > here as 32767 CHAR in AL32UTF8 (or any other multibyte charset)
> > consumes more than 32k. You can check if this is the case by
> > inspecting the call stack trace in the error trace file: if the call
> > stack includes kksmal0 then it's memory allocation error indeed.
>
> Actually, the call stack includes kksmal0. How should I consider that, a
> bug or a feature?
>
> Thank you. Kind regards, C.

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. :)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Mon Feb 04 2008 - 13:20:39 CST