u can try hetrogenous services(HS) provided by oracle..it comes with the
product...
u have to set the entries in ur oracle machine to talk to sql server box..
i use this method to setup replication between oracle & sql server.....
Thanks
Harvinder
-----Original Message-----
Sent: Wednesday, June 20, 2001 5:52 AM
To: Multiple recipients of list ORACLE-L
Hello everybody,
One of my developers is having the following requirement.
* He has PL/SQL stored proc say proc_1 in Oracle database.
* Now within this proc_1 he runs a loop and gets some values in say
variables v1, v2 and v3
* From the loop itself he needs to pass these variables as parameters
to another procedure proc_2
* the issue is proc_2 is residing in MS SQL Server.
* proc_2 inserts or updates few tables based on the values of
parameters passed.
* If the transaction in proc_2 is committed successfully then only the
transaction in proc_1 should be committed or else shud be rolled back.
* proc_1 is called from Java interface.
Is it possible to make a connection from Oracle Proc to MS SQL Server.
What I suggested him is :
* Split Oracle Procedure proc_1 in two parts say part A and part B.
* Java interface will call part A of proc_1.
* Have a Temporary table to which part A of proc_1 will write all the
values along with the status of transaction. i.e. say "posted"
* Java interface will then read the values from Temp. table and call
SQL Server Procedure with these values.
* The SQL Server proc will complete the transaction and return the
status of the transaction to the Calling Java interface.
* part B of the proc_1 will be called with the status of transaction
from SQL Server proc. and accordingly the rows inserted or updated will be
either deleted or updated back to previous values.
Can somebody suggest me an efficient way of accomplishing the same.
Thanx in advance.
Ravindra Channe
----------------------------------------------------------------------------
-
-----------------------------
Any opinion expressed here is personal and doesn't reflect that of my
Employer
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ravindra P Channe
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
