October 7, 2004 at 9:26 am
I'm having the following problem. In our company we are using SQL Server as a main database server, but now is implemeted one package solution (work on Oracle) and we need to execute one of already implemented procedure on Oracle (for some interface issued). Problem is that this procedure have input and output parameters, and we can't find some way to execute this procedure and to get result back to the SQL Server. We succesfully make link to the oracle (sp_addlinkedserver) and working with the tables and views from oracle is not a problem.
Please tell me how to execute this kind of procedure.
October 8, 2004 at 2:14 am
If no other way, you might do this with DTS.
I have managed this with an ODBC connection to Intersystems Cache, using and Execute SQL task. The only problem really was with syntax around parameters.
You might also manage this with an ActiveX DTS task though probably at some performance cost. Should be very simple to use ADO to run the Oracle SP and write the result to a temp table.
I do hope you find a more elegant solution than these.
Bill.
October 8, 2004 at 10:00 am
We had a similar need to execute a parameter-driven Oracle stored proc from SQL server. We do this using linked server, via a small Oracle table with an insert trigger. To run the Oracle proc, we first populate a separate Oracle table with the parameter values. Then we insert a record into the trigger table. The insert trigger reads the parameter table, and loads the parameter values into local variables. Then it calls the Oracle proc, passing it the parameters. The process should also clean up after itself – remove records from the insert trigger table and the parameter table.
Good luck!
January 11, 2008 at 7:40 am
try using someone like this :
SELECT * FROM OPENQUERY( Oracle_Srvr , '{Call scott.WrapPack.WrapPackSP( ''prm1'', ''prmn''}' )
but, be carefully with the quote between parameters
January 15, 2008 at 12:28 pm
Hi
All I need is to call one simple oracle procedure from DTS. Since I am new to DTS I am having hard time to solve this problem. please explain me with some simple example.
January 16, 2008 at 8:02 am
Note that anything you write will need to be using Oracle SQL syntax and/or pl/sql, not sql server-based t-sql. All SQL Server (or the DTS/SSIS package) is doing is pushing the code to Oracle's database engine.
Hope This Helps
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
February 11, 2009 at 12:48 pm
try this one
In Oracle side
create table where fields of this table are parameter of Your procedure
create insert trigger on this table and put there your procedure
if procedure return value put this to table field
In SQlserver
insert row to oracle table
Regards
PJ
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply