Copying BLOB from SQL Server 2000 to Oracle 8

[Uday Kumar]

I need some help in migrating data (about 120,000 records) from Sql Server to Oracle. I have downloaded Weblogics JDBC driver for sql server but when I try to read the BLOB field, I get windows error. I did good research on this topic on internet but didnt find any help.
Anyone have any ideas as to how to read BLOB field from sql server and write to Oracle db.
Any help will be highly appreciated.
Thanks.

[martin samm]

i havent done this on SQL Server, only Oracle, but basically to get a BLOB you need to open a stream to it, so use the resultSet's getBlob() method into a java.sql.Blob, then use the Blob's getBinaryStream and read from it into a buffer.
Then perform the reverse - there is a catch however. If you're updating an existing row, the select it's Blob column (remembering the 'for update' clause) get a binary stream to it a read the buffer from above into it. If your inserting a new row, then create it with an empty_blob() in the Blob field (Oracle function) and the do as the previous case, select it (for update), get it's Blob, get a binary stream and read the buffer into it.
Hope that helps - check out technet.oracle.com, there's plenty of Oracle based JDBC help there