INSERT Through Pro*C in UTF 8 DB

[Anoop]

Hi,

I have a table that contains a huge number of fields, out of which
3 are VARCHAR2(2000). I am using a Orcale 9i with UTF8 char set. The
data I am trying to insert is ASCII (English). When I try using a
Pro*C program to insert the data, it fails, giving the error
ORA-01461.
My data is not longer than 2000 char. It neither contains any
multibyte character. Why does this happen?

If I try to insert one of the 3 VARCHAR2 columns only, it works fine.

If I use the same Pro*C program to insert the same data into the same
table in non-UTF8 database, it inserts perfectly.

Incidently, I tried changing the columns from VARCHAR2(2000) to CLOB.
Then the program works fine.

Do I need to use CLOB? As long as my data contains single byte
characters, why should I increase my column length?

Thanks in advance.

- Anoop

[Frank van Bortel]

Anoop wrote:

Hi,

I have a table that contains a huge number of fields, out of which
3 are VARCHAR2(2000). I am using a Orcale 9i with UTF8 char set. The
data I am trying to insert is ASCII (English). When I try using a
Pro*C program to insert the data, it fails, giving the error
ORA-01461.
My data is not longer than 2000 char. It neither contains any
multibyte character.
Are you sure about that? Any non-7 bit ASCII character could be
interpreted as multibyte character.

BTW - use varchar2(2000) CHAR as definition. The default is BYTE.
If this change makes your problem disappear, you had "extended ASCII"
characters, that are actually multibyte in UTF.

Why does this happen?
If I try to insert one of the 3 VARCHAR2 columns only, it works fine.

If I use the same Pro*C program to insert the same data into the same
table in non-UTF8 database, it inserts perfectly.

Incidently, I tried changing the columns from VARCHAR2(2000) to CLOB.
Then the program works fine.

Do I need to use CLOB? As long as my data contains single byte
characters, why should I increase my column length?

All points to the fact you may have multibyte characters (which
cold be single byte in, say WE8MSWIN1252)
--

Regards,
Frank van Bortel