Calling Stored Proc in SQL Server from Stored Proc in Oracle

From: Ravindra P Channe <ravindrapc_at_mindtree.com>
Date: Wed, 20 Jun 2001 01:54:06 -0700
Message-ID: <F001.0032FA5F.20010620015153@fatcity.com>

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: ravindrapc_at_mindtree.com

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: ListGuru_at_fatcity.com (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).

Received on Wed Jun 20 2001 - 03:54:06 CDT