Thursday, 15 May 2008

Offline Blob Data Move

**Update: This is now set "on" by default in the preferences of SQL Developer 1.5.1**
In SQL Developer 1.5 we have a nifty solution for moving BLOBs (Images in SQL Server and Sybase) to Oracle offline.

The problem in the past was SQL Server and Sybase BCP tool dumps out binary values in HEX.
HEX cant be loaded easily into a BLOB in Oracle using SQL*Loader, there are some limitations to the size of the BLOB.

In the past we had a manual solution to create an extra CLOB column to load the HEX as a string/clob to . Then we had a procedure which was run after the datamove to convert the HEX to RAW piece by piece and insert it into the BLOB column. This is automated now but you have to set a preference first.

Select "Generate Stored Procedure to Migration Blobs Offline". In 1.5 by efault its off. Ill change this for 1.5.1 .


Now when you move your data offline, BLOBS will be handled automatically.