Re: HELP - Ideas for Oracle / MS Sql Interface.

Johne_uk wrote:
> Hi,
>
> I've been asked to look at developing a new interface between Oracle
> and MS Sql Server. The setup is as follows.
>
> Oracle 9i installation on Solaris 9. Various standalone tables are
> populated with data via an application (periodically).
>
> The data in these tables needs to be copied to identical tables on a MS
> Sql Server DB running on Windows and accessed via a VPN. The data will
> only ever flow from Oracle to SQL.
>
> The tables are identical on both sides (compatible filetypes) and the
> update process will be carried out every 30 minutes (realtime not
> required).
>
> I need to develop an interface that will copy the data from Oracle to
> MS Sql. The interface will be located on the Oracle unix server.
> Ideally, I would want to use shell scripts etc. to write the interface.
>
> I'm looking at initial ideas of the best way to to achieve this i.e. an
> effective mechanism.
>
> Thanks in advance
> John

I would suggest forget about the shell scripts and use Oracle's own facilities, ie Heterogenous Gateways (aka HS ODBC). Or ( I shouldn't advocate this, as the overhead in this solution is enormous): dump the data to interface tables, and have sqlserver by means of ODBC connect to Oracle and collect the data from the interface tables.
In any case: just forget about the shell scripts. When you buy a car, you don't replace the engine by a less powerful one, do you?

-- 
Sybrand Bakker
Senior Oracle DBA