Friday, May 12, 2006

Oracle DB - difference in chars and charset 8i - 9i?!

First time I see this behavior of the Oracle Database... Although when you think about it, I can understand the 8i -> 9i behavior, but the other way around?!

Scenario:
- Two databases: DB 9.2.0.6 with charset UTF8 ; DB 8.1.7 with charset WE8ISO8859P1
- I want to do in the 9i database: create table x91 as select * from x81@db8_link;
- I want to do in the 8i database: create table x82 as select * from x92@db9_link;
- Table structure of x81: c1 VARCHAR2(10), c2 CHAR(5), ...
- Table structure of x92: c1 VARCHAR2(10 CHAR), c2 CHAR(5 CHAR), ...

What happens when you do the create table?
- Table x91 will become c1 VARCHAR2(30), c2 CHAR(15), ...
- Table x82 will become c1 VARCHAR2(30), c2 CHAR(15), ...

So you can see that the initial data/char length is multiplied by 3 in both cases!
I suppose because charset UTF8 can have 3 bytes per character and WE8ISO8859P1 has only 1 byte per character.
When you look into user_tab_columns for table x92 in the 9i db you've data_length/char_col_decl_length 30 and for char_length 10. In 8i you don't have char_length...
--
SELECT table_name, column_name, data_type, data_length, char_col_decl_length,
char_length -- only 9i
FROM user_tab_columns
WHERE table_name IN ('X92', 'X91')
AND data_type IN ('CHAR', 'VARCHAR2')
ORDER BY column_name;
--

The Solution to copy the table?
- Create the table manually with the right length for the columns
- Do the insert of the data afterwards

I can understand the behavior when I'm in the 8i database (because he doesn't know char_length), but when I'm in the 9i database, I thought it would work "correctly"...
(btw: I've in my init.ora parameter nls_length_semantics = CHAR)

You may try yourselves... Download my test script here.

No comments: