Calling Oracle Stored Procedure using Linked Serve

Twinklestar · July 1st, 2004, 11:14 AM

I have a linked server to oracle, I will have to call an oracle stored procedure from SQL Server.
Any Ideas on this??

Regards
Twinkle

Hal Levy · July 1st, 2004, 11:39 AM

Unlink them.
Create the SP in Oracle.
Call the SP through the ADO.NET driver to Oracle.
the oracle client must be installed (and configured) on the machine you wish to talk to oracle with.

That's all I know- my Oracle DBA actually set it up for me when I needed it.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

happygv · July 2nd, 2004, 12:50 AM

I am not good at that, just took that from a site on searching for that info. See if that helps.

How do you execute stored procedures in Oracle9i from MS SQL Server 2000? Is it possible?

It is possible, but you have a little bit of work to do. First, you'll probably want to visit SQL Server's documentation. For SQL Server 2000, fire up Books Online and choose the Index tab. In the search box, search for the 'heterogeneous data sources, distributed queries' entry. This will give you all the information, or links to the information, on how to set up SQL Server to access data in a non-SQL server (i.e. Oracle) environment. What this doesn't tell you is how to execute a stored procedure. The easiest way I know how to execute an Oracle stored procedure from SQL Server is to change the stored procedure in Oracle to a function. This is pretty easy to do. Use the same PL/SQL block that defines the stored procedure and create a function that returns TRUE at the very end. You can have the function return other values depending on the success of the stored procedure code. Or, have the function just call the stored procedure and return some value. Once you have it in a function, then you'll just have SQL Server run a distributed query which does the following:
SELECT my_function FROM dual

In running this query, the function will execute the stored procedure's code and the output value will be returned to SQL Server. You may or may not care what this value is. Just performing the above SELECT is enough to execute the code.

Also check this URL, if that helps too.
Linked Severs on MS SQL Part 4, Oracle

Cheers!

_________________________
- Vijay G
Strive for Perfection

Twinklestar · July 6th, 2004, 04:02 AM

Yeah, we have called the stored procedure as a function, It is working fine now.Thanks Everyone

radovan.cisar · September 23rd, 2009, 04:02 AM

call oracle procedure (function) via linked server and get its result (return value)

Code:

--input parameters p1, p2, p3
declare @SQL nvarchar(1000)
declare @param nvarchar(100)
declare @result varchar(20) -- numbers may cause data type error

-- (...custom actions, i.e. insert to local table)

set @SQL = N'set @result = (select * from openquery(myLinkedServer, ''select OwnerName.Function_Insert(' + cast(@p1 as varchar) + ', ' + cast(@p1 as varchar) + ', ' + cast(@p3 as varchar) + ') from dual''))'
set @param = '@result varchar output'
EXEC sp_executesql @SQL, @param, @result output 

if @result <> '0' goto ERROR

-- (...custom actions)

return 1 -- OK

ERROR:
-- (...custom actions, i.e. delete from local table with(rowlock))
return 0 -- KO