DATA MIGRATION FROM NTEXT(SQLSERVER) TO NCLOB(ORACLE)
Problem: Migrate data from SQL Server (2000) to Oracle 10g.
There are 3 tables with 30000 - 160000 rows of data. The problem I have now is for columns of data type ntext in SQL Server, which need to be converted to NCLOB in Oracle. The max length of the actual ntext content is > 4000.
The solutions we have tried:
1. Use DTS package from SQL Server.
I was able to connect the Oracle Database using Microsoft OLE DB for Oracle. But it errored out when I tried to access the table with NCLOB column, because the data type is not supported.
2. Create a linked server to Oracle from SQL Server Enterprise manager.
The linked server was created. I was able to query tables without NCLOB column, but had problem accessing the table with NCLOB column.
3. Use SQL Server "Export Data" tool to export the table directly to Oracle.
It worked for tables without NCLOB column, but failed for table with NCLOB column for the same reason as 1.
4. Use Oracle SQL Developer.
It errored out due to some Oracle JDBC error (could be the setup on my machine), but it does not allow to specify tables, which may not be a good choice because I have lots of other tables that I don't want to migrate.