How to move images data from SQL Server to Oracle

[gary]

Hello, everyone,

I am working on migrating data from SQL Server 2000 to Oracle 10g for millions of records of data in some tables in SQL Server.
I am using “BULK COLLECT…FOR ALL” store procedure to do it (using either explicit/implicit cursor to fetch data record by record is too slow). Some of the data type in SQL Server tables are "images" type.
The corresponding data type for "images" in Oracle is LOB/BLOB. First, I create a table with BLOB data types in Oracle, then, I execute the store procedure, But I got the error message: "ora-22992 cannot use LOB locators selected from remote tables"

I checked the error message in Oracle documentation, and got the following information:
Cause: A remote LOB column cannot be referenced.
Action: Remove references to LOBs in remote tables.

However, I am not allowed to do any changes in the remote database (in SQL Server), I can do anything in Oracle.
What I did is: first, I changed the data type from BLOB to RAW(2000) in Oracle, it doesn’t work. Then, I change the data type to LONG RAW, Still doesn’t work. Can anybody tell that changing the data type in Oracle is the right track? If it is, what type of data type I need to change to?
If not, what is the right direction I need to go? At the same time, I checked Migration workbench, this tool can't move BLOB data.

Another approach probably is: move all of the data except images type, and then try to move the images column individual, but it is possible for cursor, not practical for “BULK COLLECT…FORALL”. And I was asked to write store procedure code to do the migration.

I really appreciate any suggestion.

Best Regards,

Gary

[tamilselvan]

The corresponding data type for "images" in Oracle is LOB/BLOB. First, I create a table with BLOB data types in Oracle, then, I execute the store procedure, But I got the error message: "ora-22992 cannot use LOB locators selected from remote tables"

You need to use either JAVA or some OCI to load LOB data from SQL Server to Oracle. PL/SQL proc will fail if the size of LOB > 32K.

Tamil

[dbasan]

Try InterMedia.