Oracle ODBC and SQL Server 7

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)?

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. ]

If anyone has any ideas, please email me on chris.roff_at_wgsn.com

many thanks

Chris Roff

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 08 2000 - 09:16:43 CST