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.

Regards

Aamir



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
Comments
*Enter Code refresh code


 
Sponsor Information






 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.