Convert ntext to clob

Question

I have to copy data from one table to another which one table is in Oracle and one is in MSSQL Server. I want to copy the data from MSSQL Server table to Oracle table. The problem is that the MSSQL Server table has one column which is of data type ntext and the destination column in Oracle table is clob.

When I use the query insert into oracle.table select * from sqlserver.table@mssql; I get the following error:

SQL Error: ORA-00997: illegal use of LONG datatype

Can anyone advice on this please?

Answer 1

I tried it through a PL/SQL Procedure and it worked. I created a cursor, passed in the values to my variables declared in VARCHAR2 and then run an EXECUTE IMMEDIATE for the INSERT INTO....SELECT * FROM <TABLE_NAME>@MSSQL.