Re: Oracle accent

From: Thorsten Kettner <thorsten.kettner_at_web.de>
Date: 26 Apr 2007 01:06:34 -0700
Message-ID: <1177574794.526388.173240@n35g2000prd.googlegroups.com>

On 24 Apr., 11:04, boutreau.adr..._at_gmail.com wrote:
> ACC_REF VARCHAR2(20 BYTE),
>
> this value : "0123456789012346éaé" (french accent) and i've got this
> error :
> 'ORA-01401: ' value is too big. I know the problem with oracle, and if
> I modify the number of byte the problem is resolved. But, is it
> possible to format the string before inserting into oracle and not
> modifying the number of byte ?

What do you mean by modifying? Cut it? Then, well, you could do it somehow like this:

insert into <tablename>
(ACC_REF)
values (substrb(:the_string, 1, 20))

Unlike SubStr, SubStrB cuts bytes instead of chars. If you want it more generic, you'd ask the database for the columns length in bytes:

insert into <tablename>
(ACC_REF)
values
(
substrb
(:the_string, 1,
(select data_length from all_tab_columns

     where table_name='<tablename>'
       and column_name ='ACC_REF'
       and owner='<ownername>')

)
) Received on Thu Apr 26 2007 - 03:06:34 CDT