ORA-06502 when referencing trigger var in UTF8 db with char semantics

From: Adam Donahue <adonahue_at_opsware.com>
Date: Tue, 22 Jun 2004 17:44:42 -0700
Message-ID: <40D8D27A.4010804@opsware.com>

Folks,

I'm seeing odd behavior in the following situation on an Oracle 9.2.0.5 installation (SunOS 5.8).

Assume database character set of UTF8, and nls_length_semantics=char during all creation statements.

create table t1 ( f1 varchar2(4000) );
create table t2 ( f1 varchar2(4000) );

create trigger t1_trg
before insert or update
on t1
for each row
begin

insert into t2 values (:new.f1);
end;

When inserting up to 3,999 characters, this works fine. When inserting 4000 characters, I get

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error

on the trigger.

This also fails:

create trigger t1_trg
before insert or update
on t1
for each row
declare

myVar varchar2(4000);
begin

myVar := :new.f1;
end;

(No insert here, but the deref fails. Again, only for inserts of 4000 characters into a varchar2(4000) field with character semantics.)

However, this works:

create trigger t1_trg
before insert or update
on t1
for each row
declare

myVar varchar2(4000);
begin

select :new.f1 into myVar;
insert into t2 values (myVar);
end;

All inserts are US7ASCII data and codepoints. I've tried combinations of NLS_LANG to no good effect.

I couldn't find anything related on Metalink, AskTom, or TechNet, nor via a Google search.

Any ideas? I'm wondering if I've encountered a bug.

Thanks,

Adam

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Tue Jun 22 2004 - 19:41:42 CDT