Learn more about SQL Server tools


Transfer of image data from Oracle to SQL Server 2008

Asked 5/29/2010 12:32:23 AM by md.aamir.hussain@gmail.com

Dear Sir,

Please tell me how to transfer image data from Oracle to SQL Server 2008. I have tried to transfer image data through SSIS packages but getting error. so please give me suggestion  to resolve the issue.



Answered 7/23/2010 7:10:13 AM by Devart (0) vote answer is useful vote answer is NOT useful

 There can be two ways of solving this problem:

1) Load data of the BLOB fields of the Oracle table to external files (i.e. restore the picture from data as a file). Then with the help of the SQL Server OPENROWSET(BULK N'img.file', SINGLE_BLOB) function load it into a SQL Server table. Such operation may be performed for one picture or for several pictures if this action is performed in a cycle.
The most difficult part in the realization of this method is that there are no standard methods for loading BLOB fields in Oracle. A way of performing such loading on one's own is described in the book "Expert One-on-One Oracle" by Thomas Kyte in Chapter 18: C-Based External Procedures -> LOB to File External Procedure (LOB_IO) p 821. http://www.amazon.com/exec/obidos/ASIN/1861004826/

2) Create a connection with linked server Oracle and create a SQL Server table basing on the selection of data from the Oracle table. In this case an error "The provider ran out of memory" may occur. This means that there is not enough memory for sending data through the OLEDB provider. But if the size of data does not exceed 5 MB you can use this method.

Post an Answer

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
*Enter Code refresh code

Sponsor Information