Why does Char(1) change to Char(3) when copying over an Oracle DBLINK?

Question

I have 2 databases, and I want to transport an existing table containing a CHAR column from database A to database B.

Database A is Oracle 9i, has encoding WE8ISO8859P1, and contains a table "foo" with at least 1 column of type CHAR(1 char). I can not change the table on database A because it is part of a third party setup.

Database B is my own Oracle 10g database, using encoding AL32UTF8 for all kinds of reasons, and I want to copy foo into this database.

I setup a database link from database B to database A. Then I issue the following command:

*create table bar as select * from #link#.foo;*

The data gets copied over nicely, but when I check the types of the columns, I notice that CHAR(1 char) has been converted into CHAR(3 char), and when querying the data in database B, it is all padded with spaces.

I think somewhere underwater, Oracle confuses it's own bytes and chars. CHAR(1 byte) is different from CHAR(1 char) etc. I've read about all that.

Why does the datatype change into a padded CHAR(3 char) and how do I stop Oracle from doing this?

Edit: It seems to have to do with transfering CHAR's between two specific patchlevels of Oracle 9 and 10. It looks like it is really a bug. as soon as I find out I'll post an update. Meanwhile: don't try to move CHAR's between databases like I described. VARCHAR2 works fine (tested).

Edit 2: I found the answer and posted it here: http://stackoverflow.com/questions/253971/why-does-char1-change-to-char3-when-copying-over-an-oracle-dblink#263467 Too bad I can not accept my own answer, because my problem is solved.

Answer 1

YOu need to learn the difference between the WE8ISO8859P1 NLS (which stores characters in one byte) and the AL32UTF8 which stores characters in up to four bytes. You will need to spend some quality time with the Oracle National Language Support (NLS) Documentation. Oracle automatically does the conversion through the database link, in an attempt to be helpful.

Try the following from your SQL prompt:

ALTER SESSION NLS_NCHAR WE8ISO8859P1 
create table bar as select * from #link#.foo;

Answer 2

The first thing I would try is Creating the table NOT as a CTAS but with a list of column definitions and try to perform an insert of the first few thousand rows. If that didn't succeed then it would be very clear why... and you'd have quick confirmation that Thomas Low is dead on accurate.

Answer 3

This problem is caused by the way Oracle (mis)handles character conversions between different character sets based on the original column length definition. When you define the size of a character type column in bytes, Oracle does not know how to do a conversion and bodges it. The solution is to always define the length of a character type in characters.

For a more in-depth explanation of the problem and how I figured this out have a look at http://www.rolfje.com/2008/11/04/transporting-oracle-chars-over-a-dblink/