Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Transparent Gateways

Re: Oracle Transparent Gateways

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 17 Nov 1999 08:44:06 -0500
Message-ID: <aq8yOJEzGwc463X0PveDTyv+rm4D@4ax.com>


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 Received on Wed Nov 17 1999 - 07:44:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US