CHAR/NCHAR and VARCHAR2/NVARCHAR2 Conversion Bug?

[darryl_staflund]

Hi there,

I do not know if this is a bug or not but it did catch me offguard so I am posting my observations here. I am using 5.1.6 BTW.

I used PL/SQL Developer yesterday to change CHAR fields to NCHAR and VARCHAR2 fields to NVARCHAR2 in a test database of mine and conversion went smoothly (NB: The database's character set is AL16UTF16.)

I then generated a DDL dump of my schema objects using PL/SQL Developer and went home for the night. After testing yesterday's changes, I decided to switch back to CHAR and VARCHAR2, so I used 'vi' to replace all instances of 'nchar' to 'char' and 'nvarchar2' to 'varchar2'. I then deleted the objects of my schema and then ran the modified DDL script.

After that my code broke, and after investigating the problem for some time, I found that the original DDL dump created by PL/SQL Developer (made after I used PL/SQL Developer to change CHAR to NCHAR and VARCHAR2 to NVARCHAR2) had effectively doubled the lengths of changed columns. Columns that originally looked like this:

field_name char(5)
field_name_2 varchar2(5)

became

field_name nchar(10)
field_name_2 nvarchar2(10)

Is this a normal Oracle operation (NB: I could be as I have not worked with NCHAR and NVARCHAR2 before.)? Or was the DDL generated incorrectly? I know that internally the representation of NCHAR and NVARCHAR2 characters is effectively double that of CHAR and CHAR characters, but I didn't think change in lengths would be reflected in the DDL.

Thanks,
Darryl Staflund

PS: Congratulations of v 6!

 

[Marco Kalter]

This should be fixed in 6.0. Can you verify this?

 

[darryl_staflund]

I can indeed verify this. I tried reproducing it on v. 6.0 and was not able to. Thanks!

 

[Duke Ganote]

I'm using Version 6.0.1.848 with Oracle 9iR2. I don't understand how it exported multi-lingual data using the "SQL inserts" option. I've got NLS_CHAR = AL32UFT8, and the generated INSERT statements for my Russian and Japanese and other non-Latin text appear to truncated my phrases to ASCII -- they look this this:
'