Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


OLE OBJECT from MSSQL to ORACLE, or save as image

Posted on 2012-04-10
Medium Priority
Last Modified: 2013-03-01
In MS SQL there is a table with image in it.
1. I need to save that image to disk and write image name instead to the database (ORACLE)
2. I need to save that image into ORACLE database.

We have a new program wich works with ORACLE only. But the datasource as you have understood is MS SQL.
Need least painfull migration to ORACLE.
But, since the other department where I get data will continue using the MS SQL, this process should somehow easier as possible.

No problem if to use access.

Question by:masirof

Author Comment

ID: 37831404
Any comments experts?
LVL 40

Accepted Solution

lcohan earned 672 total points
ID: 37833399
"In MS SQL there is a table with image in it.
1. I need to save that image to disk and write image name instead to the database (ORACLE)
2. I need to save that image into ORACLE database"

Please see how you can setup a Linked Server from SQL to ORACLE below:


Then use full(4) name quelifier queries or OPENQUERRY from SQL to insert/update a ORACLE table CLOB equivalent to image from SQL.

LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 total points
ID: 37833749
>>insert/update a ORACLE table CLOB equivalent to image from SQL.

The Oracle equivalent is a BLOB not a CLOB.

I'm also not sure you can pull an Image/Blob across a linked server but I'm not a SQL Server person.

As a last resort if you can get the images saves to the OS as files you can use SQL Loader or DBMS_LOB.LOADBLOBFROMFILE (images need to be on the database server for this) to load them.
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 664 total points
ID: 37834156
Is there a reason you can't use the SQL Server client tools Import/Export wizard to do this for you? You should be able to connect to an MSSQL source and an Oracle destination and just copy the data over, including copying your IMAGE/VARBINARY field to an Oracle BLOB, which is the equivalent type.

Here's a walk-through of the process:


Author Comment

ID: 37835994
I will check that and come back here.
Thanks yet.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question