Thomas Kyte wrote:
>
> A copy of this was sent to Kenneth C Stahl <BlueSax_at_Unforgettable.com>
> (if that email address didn't require changing)
> On Wed, 17 Nov 1999 07:54:38 -0500, you wrote:
>
> >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.
> >>
>
> it is transparent meaning you use Oracle SQL/PLSQL to access the other database.
> As far as the end user is concerned, the other database is Oracle -- not SQL
> Server. I don't know what Steve was trying to relay here.
>
> the gateways don't do DDL, but as far as DML is concerned, they are transparent.
>
> >> Commits / rollbacks are not under Oracle's control (duh!)
>
> sure they are. You commit in Oracle -- it'll commit in SQL Server. In fact,
> it'll 2phase commit across Oracle and SQL Server if you modify data in both.
>
> >>
> >> 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 would create triggers on the tables in SQL Server -- they will be used. You
> will not code your triggers in plsql for MS SQL server as triggers are executed
> by the database itself. Triggers are used (but they would be written in TSQL,
> not plsql).
>
> >> 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?
> >
>
> The transparent gateway will look like an Oracle database to another Oracle
> database. You will setup configuration files under the transparent gateways
> oracle home telling it how to get to the other database (odbc configuration).
> The transparent gateway will have its own listener to configure.
>
> On the Oracle database side, you will use a database link to point to the
> gateway (so yes, some tnsnames entries will be made). once that is setup, you
> can
>
> SQL> select * from "table_name"@other_database;
> SQL> insert into "table_name"@other_database ....;
> SQL> update "table_name"@other_database set .....;
> SQL> delete from "table_name"@other_database .....;
>
> People commonly use synonyms to hide the fact that a database link is being used
> and that the table names in Sybase/Sql server are case sensitive (hence the
> double quoted identifiers).
>
> Commits are not only fully supported but distributed 2pc is supported as well
> (eg: modify data in Oracle, modify data in sql server, commit and either both
> will commit or neither will commit).
>
> >Ken
> >
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Ok. That helps. I guess this is the direction we need to go with the
project I'm working on. Sounds like there will be a few nuances we'll have
to master but that it will do what we want.
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
Received on Wed Nov 17 1999 - 09:54:43 CST
