Re: How to link to SQL Server using Oracle without using Oracle Gateway

Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3CF5C00C.E5526ED2_at_exesolutions.com>...
> Brian Li wrote:
>
> > Oracle Transparent Gateway is expensive bundle, how can I link a SQL
> > Server in Oracle without using Oracle Transparent Gateway?
> >
> > Can I call SQL Server's Store Procedure in Oracle Store Procedure?
> >
> > If can, can you explain it detail?
> >
> > Thanks a lot!
>
> Oracle gets a lot of money for the product for a reason.
>
> Primarily the development cost of doing it yourself is greater when you
> look at the total cost rather than just your take-home pay.
>
> Daniel Morgan

You do not necessarily need a Transparent Gateway product to insert and select to MSSQLserver from Oracle. Oracle version 8.1.7 on AIX, SUN, HP, and NT includes Heterogeneous Services, also called generic connectivity, as a part of the rdbms (at least on EE edition). The Transparent Gateways are built on top of generic connectivity. Except on NT where the install disk includes one you do have to obtain an ODBC to MSSQLserver driver for your platform. I have successfully setup and tested the driver from Data Direct on AIX to perform selects and inserts against a single MSSQL table. There are some comments in the documentation about the SQL being limited compared to using a Transparent Gateway, but the documentation I found did not explain the limitations.

See Oracle notes -
115098.1 Quick Start Guide: UNIX Generic Connectivity using ODBC 115992.1 Generic Connectivity: ODBC to MS SQL Server

If appears to me that depending on whose driver you choose and their billing plan, some are by machine size, and some are by number of MS DB you plan to connect to that the cost runs in the $3000 - $10,000 range.

I believe out programmers intend to go with a true JDBC, but I think the ability to reference MSSQL tables in Oracle DML statements via a database link just like a remote Oracle table to be very useful feature.

HTH -- Mark D Powell -- Received on Thu May 30 2002 - 08:06:03 CDT