Re: BLOBs from Oracle to MS Sql server

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Fri, 13 Sep 2002 09:30:43 +0200
Message-ID: <als488$on0$1@ctb-nnrp2.saix.net>

Zbrk wrote:

>> > The easiest way I can think of is to write a client app that does this
>> > - pushing and pulling the blobs.

>
> Do you know is it possible to do this using PL/SQL stored procedures.

Off the cuff, yes. Using something like the utility file package to read the blob file from disk and putting it into a CLOB column in a table. Another option could be using BFILEs (file handle stored in an Oracle column that allows you to via a package read the contents of the file for that handle).

This requires the BLOB files to be on the Oracle server.

PL/SQL can not pull the BLOB from SQL-Server. You will need either an external stored proc for it (similar to extended procs on SQL-Server) that allows you to get access to SQL-Server , or Oracle's Transparant Gateway for SQL-Server.

The opposite is also possible - write a C++ extended proc for SQL-Server that uses Oracle's OCI to provide you with an Oracle connection.

>>>  I would write it in Delphi (a few minutes
>> > effort and likely less than 10 lines of code) and run it on the same
>> > platform as SQL-Server to prevent doubling the network traffic with the
>> > pull from SQL-Server and push to Oracle.
>> >

> Please keep in mind that BLOB column can take about 1M, there is
> limitation in PL/SQL that a variable can not exceed 32k. Is there such
> limitation in Delphi, and which version of Delphi you suggesting.

No limitation in Delphi (it has "proper" BLOB support in its TTable class). Version - Delphi 6 Enterprise Edition. You could also likely do it with Delphi Client-Server or even Delphi Professional (using ODBC). Visual Basic is another option.

Essentially you develop a small app that can open two connections - one to Oracle and the other to SQL-Server. Then you open a cursor on the SQL-Server BLOB table and (in pseudo code): while not SQLServer.cursor.end-of-cursor   read SQL-Server.cursor.row
  set Oracle.table.row = SQL-Server.cursor.row   Oracle.table.update
  SQL-Server.cursor.next
end while

> There is an option in Oracle for passthrough SQL called
> Transparent gateway but it is licenced seperaterly and price is $15000.

Yes. But that is pretty expensive to use for a once-off data export from SQL-Server to Oracle.

How does bcp treat BLOBs? (been many years since I last use SQL-Server).

Another possibility could be to bcp the data from SQL-Server and use Oracle's SQL*Loader to load it.

Personally, I would use Delphi - am very familiar with the language and to write a quick and dirty application to pull and push the data, will be very quick and easy to do. I suggest that you think along similar lines - choose the option that is the easiest to do and not necassarily technically the best designed. For a once-off exercise, cutting corners and just getting the job done is IMO okay. :-)

--
Billy

Received on Fri Sep 13 2002 - 02:30:43 CDT