SQL & PL/SQL

Some of the table columns of mysql database has character set UTF-8 and latin1. When I create table from MySQL database using database link those columns are getting converted to NCHAR/NVARCHAR.

When I query the copied data in oracle database . I see NCHAR/NVARCHAR values as space in between the characters


MySQL Database:

mysql> desc XXX;
-------------------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------------+
| XX | int(11) | NO | PRI | NULL | auto_increment |
| date_time | datetime | YES | MUL | NULL | |
| XT | int(11) | YES | | NULL | |
| ID_ | int(11) | YES | | NULL | |
| value_ | varchar(200) | YES | | NULL | |
-------------------------------------------------------------+


mysql> SELECT character_set_name FROM information_schema.`COLUMNS`
-> WHERE table_schema ='xxx' and table_name='xxx';
--------------------
| character_set_name |
--------------------
| NULL |
| NULL |
| NULL |
| NULL |
| utf8 |
--------------------

mysql> select <value_> from <xx>;
-------
| value_ |
-------
| Inv |
| BMS |



Create the table using database link

SQL> create table <xx> as select * from <xx>@mysqldblink;

SQL> desc <xx>;
Name Null? Type
----------------------------------------- -------- ---------------

xx NUMBER(10)
date_time DATE
xt NUMBER(10)
ID_ NUMBER(10)
value_ NVARCHAR2(200)

SQL> select <value_> from <xx>;
value_
----------------------------------------
I n v
D M S


I see spaces in the NVARCHAR2 data.

Please help me in creating the tables with columns as VARCHAR in oracle instead of NVARCHAR using the dblink.

Thanks in advance.