• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

OLE OBJECT from MSSQL to ORACLE, or save as image

Hello,
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)
or
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.

Thanks!!!!
0
masirof
Asked:
masirof
3 Solutions
 
masirofAuthor Commented:
Any comments experts?
0
 
lcohanDatabase AnalystCommented:
"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)
or
2. I need to save that image into ORACLE database"


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

http://support.microsoft.com/kb/280106
http://www.mssqltips.com/sqlservertip/1433/how-to-setup-linked-servers-for-sql-server-and-oracle-64-bit-client/

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

http://msdn.microsoft.com/en-us/library/ms188427(v=sql.100).aspx
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
Ryan McCauleyData and Analytics ManagerCommented:
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:

http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm
0
 
masirofAuthor Commented:
I will check that and come back here.
Thanks yet.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now