HI

I'm trying to insert into

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 ?

Thanks to read me!

boutreau.adrien@gmail.com wrote:
> HI
>
> I'm trying to insert into
>
> 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 ?
>
> Thanks to read me!

The problem most probably is that your french characters take more
than one byte i.e. these are multibyte characters. You have to either
1) choose another characterset of the db so that your french
characters are not multi-byte (which actually means rebuild the DB,
actually just a theoretical solution
2) change VARCHAR2(20 BYTE) to VARCHAR2(20 CHAR), so that oracle takes
more space for this particular column, but you definitely can store 20
characters not only 20 bytes.

Gints Plivna
http://www.gplivna.eu

Gints Plivna schreef:
> boutreau.adrien@gmail.com wrote:
>> HI
>>
>> I'm trying to insert into
>>
>> 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 ?
>>
>> Thanks to read me!
>
> The problem most probably is that your french characters take more
> than one byte i.e. these are multibyte characters. You have to either

Assuming a multi byte character set is used. An assumption I would have
made, too, but I have had long discussions, based on wrong assumptions.

> 1) choose another characterset of the db so that your french
> characters are not multi-byte (which actually means rebuild the DB,
> actually just a theoretical solution

Hmmm - there is a program called csscan (Character Set Scan), that will
show you whether you can switch from one charset to another.
So, this is not theoretical in all situations, but very likely (based
on earlier made assumptions) in this case.

> 2) change VARCHAR2(20 BYTE) to VARCHAR2(20 CHAR), so that oracle takes
> more space for this particular column, but you definitely can store 20
> characters not only 20 bytes.

Agree: that would be the "standard": change to character
semantics, instead of byte, for multi byte character sets.
Seems this is not the case here.
>
> Gints Plivna
> http://www.gplivna.eu
>


--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

On 24 Apr., 11:04, boutreau.adr...@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>')
)
)

On 24 Apr., 11:04, boutreau.adr...@gmail.com wrote:
> 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 ?

If you are looking for a way to remove special chars, try this:

select translate('aábcd', 'áàéè', 'aaee') from dual

However 'æ' would have to result in 'ae', right? For that you would
need the replace function. And you would have one char more then the
original string holds.

select replace('abcæ', 'æ', 'ae') from dual

> Assuming a multi byte character set is used. An assumption I would have
> made, too, but I have had long discussions, based on wrong assumptions.

That's why I said "most probably"
Also he inserted 20 chars in 20 byte. If this would be single byte
charset then there wouldn't be any problem at least connected with
value too big, yea?

>
> > 1) choose another characterset of the db so that your french
> > characters are not multi-byte (which actually means rebuild the DB,
> > actually just a theoretical solution
>
> Hmmm - there is a program called csscan (Character Set Scan), that will
> show you whether you can switch from one charset to another.
> So, this is not theoretical in all situations, but very likely (based
> on earlier made assumptions) in this case.

OK csscan is something new for me.

Gints Plivna
http://www.gplivna.eu