Re: Oracle ODBC and SQL Server 7

In article <8ubqok$lvj$1_at_nnrp1.deja.com>,   roffster_at_my-deja.com wrote:
> I am trying to set up a system whereby I keep 2 tables (one under SQL
> Server 7 and the other in Oracle 8.0.6) in synch, so that
> inserts,updates and deletes in the SQL Server tables are communicated
> to the Oracle tables.
>
> I have set up a linked server in SQL Server using the Oracle OLE ODBC
> driver and I can insert,update and delete through this linked server
 to
> the Oracle tables...however the Oracle tables are holding upwards of
> 500K rows. I discovered that when the Oracle tables are empty the
> inserts etc take very little time to complete however when the Oracle
> tables are full of the appropriate data an update could take upwards
 of
> 3 minutes, making the whole system unworkable!
>
> Ive subsequently discovered that the calls via the ODBC link first do
 a
> SELECT * FROM ... before inserting,updating or deleting, which
 explains
> why the whole process took so long to complete.
>
> Does anyone have any suggestions (or encountered this before)?

Can you clarify what exactly you mean by "Oracle OLE ODBC driver"? If you're connecting via OLE DB, you'll be using one of

- Oracle OLE DB Provider
- Microsoft OLE DB Provider for Oracle
- Microsoft OLE DB Provider for ODBC

If you're using the third option, which ODBC driver are you using (Oracle ODBC driver, Microsoft ODBC driver for Oracle, or a third-party ODBC driver for Oracle).

I'm rather clueless about how one links to an Oracle table from SQL*Server, so I'll assume that you use some sort of adaptor with limited config options. If this isn't the case, let me know. I'll also assume that it's this adaptor which is generating the obnoxious "select *" statement.

You may also want to try going the opposite way (linking the SQL*Server table from Oracle). In my (admittedly limited) experience with the Oracle Transparent Gateways, I haven't seen them generate this sort of really obnoxious SQL.

>
> To get over this Ive decided to write an Oracle procedure that does
 the
> updates etc and only call this procedure via the ODBC link.
>
> However when I try and call the procedure from SQL Server (by EXECUTE
> <linked server name>..<user>.<procedure name>) I get the following
> error:-
>
> [OLE/DB provider returned message: [Oracle][ODBC][Ora]ORA-06550: line
> 1, column 30:
> PLS-00103: Encountered the symbol "1" when expecting one of the
> following:
>
> begin declare end exception exit for goto if loop mod null
> pragma raise return select update while <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql commit <a single-quoted SQL string>
> The symbol "return" was substituted for "1" to continue.
> ]

I assume you're able to call this procedure correctly from within Oracle. Generally, ORA-06550 errors indicate that the procedure failed to compile correctly.

--
Justin Cave - Oracle ODBC Development

Opinions expressed herein are my own and may not reflect those of
Oracle Corporation.

--
Justin Cave - Oracle ODBC Development

Opinions expressed herein are my own and may not reflect those of
Oracle Corporation.


Sent via Deja.com http://www.deja.com/
Before you buy.