File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Copying BLOB from SQL Server 2000 to Oracle 8 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Copying BLOB from SQL Server 2000 to Oracle 8" Watch "Copying BLOB from SQL Server 2000 to Oracle 8" New topic

Copying BLOB from SQL Server 2000 to Oracle 8

Uday Kumar

Joined: Jan 22, 2002
Posts: 24
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.
martin samm
Ranch Hand

Joined: Nov 09, 2000
Posts: 31
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, there's plenty of Oracle based JDBC help there

martin samm<BR>
I agree. Here's the link:
subject: Copying BLOB from SQL Server 2000 to Oracle 8
It's not a secret anymore!