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

From: Adam Donahue <adonahue@xxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Tue, 22 Jun 2004 17:44:42 -0700

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) );

-- no key-based relationship, for simplicity

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

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

Other related posts: