I am loading / inserting data into oracle table , in case of special characters like chinese language characters, i am getting an error like row rejected because maximum size of column was exceeded, i am not getting this error for rows which have english characters which appear to be of same length for same column. I am using SUBSTR and TRIM function but it is not working, how can i determine weather the length of a string which is in chinese language exceeds column size or not.
+1
A:
Hi MANISH,
if your columns are defined as VARCHAR2(XX) [for example VARCHAR2(20)], you will receive an error if you try to insert a string that is more than XX bytes long.
The function SUBSTR calculates length in number of characters, not bytes. To select a substring in bytes, use the function SUBSTRB.
SQL> select substr('ЙЖ', 1, 2) from dual;
SUBSTR('ЙЖ',1,2)
------------------
ЙЖ
SQL> select substrb('ЙЖ', 1, 2) from dual;
SUBSTRB('ЙЖ',1,2)
-------------------
Й
Edit: As suggested by Adam, you can use character arithmetics if you define your columns and variables as VARCHAR2 (XX CHAR). In that case your columns will be able to store XX characters, in all character sets (up to a maximum of 4000 bytes if you store it in a table).
Vincent Malgrat
2009-11-25 13:36:43
You might want to suggest changing the schema definitions from `VARCHAR2(XX)` to `VARCHAR2(XX CHAR)` to have the column lengths be in characters instead of bytes.
Adam Hawkes
2009-11-25 14:01:31
Thanks Adam, I updated my answer
Vincent Malgrat
2009-11-25 14:41:48
after i changed the column schema definition using VARCHAR2(XX CHAR), the problem of row rejecttion got eliminated, but i have to load this value from staging to a main table where i can not change the schema definition, so , i did a substr which will restrict it to not to be more than column size, but now i am getting a new error while doing a SELECT * FROM TABLE, ORA-29275: partial multibyte character.
Manish
2009-12-02 05:28:53