I have a table in MySQL database. One of the fields of the table is of "TEXT" data type (in MySQL).
I need to copy the table to an Oracle 11gR2 database. I have a cron process to dump the above table content to a tab separated text file. Then I run sqlldr to populate the table in Oracle. THis worked fine so far with other tables where there was no "TEXT" data type in the source MySQL database table.
If I create the Oracle target table field VARCHAR2(4000), half the record does not get inserted -- error shows that input value is too big for this VARCHAR2(4000) Oracle field. If I change the target table (Oracle) field to CLOB (jnstead of VARCHAR2(4000), still I get the same error.
I looked at the content of the failed rows -- none has more than few hundred characters in it.
If I use Oracle Developer IDE (that has the Source MySQL to target Oracle data conversion wizard) -- the wizard creates the target table field in Oracle as CLOB and all source rows get imported!! But I cannot use this IDE as I need to schedule a process to keep the target Oracle table in sync.
How can I make it work...