Re: Oracle Transparent Gateways

Steve McDaniels wrote:
>
> I running an Oracle DB on HP UX,
> Oracle gateway on NT
> connecting to MS-SQL 6.5 server.
>
> Although Oracle names the Gateway as "transparent"
> (implying that it's transparent to the Oracle sql/procedures, etc,)
> it's not.
>
> Commits / rollbacks are not under Oracle's control (duh!)
>
> The gateway uses an ODBC connection. So, anything you could do with
> say, MS-Access (via odbc) you can do with your oracle connection.
> (You can't use triggers, for example, but you can execute stored procedures)
>
> You create a database link to the tables on the SQL server via the gateway.
>
> As with any odbc connection, it is very slow. It works, but performance
> is so incredibly bad, to do a complex join over the gateway.
>
> We use the gateway simply as the "interface" to the SQL db.
> We use record posting date/timestamps to determine which SQL records
> have been posting since our last "pull"; we "manually" replicate SQL
> tables on the Oracle side, THEN process the records.
>
> Watch out for treatment of INTs (numbers), varchars (CHARS), etc.
>
> Kenneth C Stahl <BlueSax_at_Unforgettable.com> wrote in message
> news:383180F2.BE0474F_at_Unforgettable.com...
> > Could someone provide a brief description of what a transparent gateway
> > provides?
> >
> > Is there a transparent gateway that will run on a Unixware platform to
> > access a Sql Server 6.5 database running on an NT server?

I think I'm starting to understand.

As I stated earlier, my primary application is running on a Unixware 2.1.2 platform which also has an Oracle 7.3.4 database. The stored procedures that I need to run are located in a Sql Server database running on an NT workstation. From what you are saying I'll deduce, the transparent gateway would be installed on the NT machine and would allow connections from the Unixware platform using normal connection commands in a PRO-C program. At this point I don't know whether there will be a problem with INTs and VARCHARs, that will have to be handled later when we start mapping out exactly what data needsw to pass back and forth.

Despite the speed problems, I'm also wondering whether there is any other reasonable methodology that can be used. We have a lot of experience with PRO-C and if we can write our application using PRO-C that will save us the need to learn new syntax.

Now, let me ask this. Say we set this all up. Is there anything special that has to go into the tnsnames.ora file? Would we be able to run sqlplus on the Unix platform and issue DML just as if the target database were Oracle? From what you say, it sounds like COMMIT is recognized. Does that mean that INSERT, UPDATE and DELETEs cannot be performed (or at least committed) via the gateway?

Ken

............................................................................
............................................................................
............................................................................
............................................................................
............................................................................
............................................................................