Calling an Oracle Stored Procedure and Function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » Calling an Oracle Stored Procedure and...

Calling an Oracle Stored Procedure and Function

Rate Topic

Display Mode

Topic Options

Author

Message

Brian Connolly

Posted Thursday, October 11, 2007 8:34 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2008 12:30 PM
Points: 11, Visits: 14

Hi,

I'm trying to call an oracle stored procedure and function via linkedServer but I seem to be getting syntax errors from SQL server. The stored procedure passes two parameters and should get four back. This is the layout of the command I am using:

Select * from openquery([linked_server_name],'
{ Call oracle_pkg_name.oracle_stored_proc_name(20,25,{OUT_MSG_STATUS_CD,OUT_MSG_STATUS,OUT_ACK_MSG,OUT_ERROR_MSG}) }
')

The function takes two parameters as input, returns a numbers, and also has one output:

select * from openquery([linked_server_name], 'select oracle_pkg_name.oracle_function_name(''CLR'', ''AAAM'',{OUT_ERROR_MSG})
as proc_status from dual')

Thanks,
Brian

Post #409561

SJanki

Posted Friday, July 29, 2011 1:55 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:44 PM
Points: 70, Visits: 194

Hi,
Were you able to call the oracle funtion from SQL Server?
I have the same issue.
I have to call Oracle function with 1 input and 1 output parameter, but it always gives some or other syntax error. If you know the exact syntax, please let me know.
I tried these ways-->
declare @InputPara varchar(100)
set @InputPara=’Hello’
declare @OutputPara bigint
EXECUTE ( ‘BEGIN ? := packagename.functionname(?,?); END;’, @InputPara, @OutputPara OUTPUT )at linkedservername;

select * from linkedservername..packagename.functionname(‘Hello)

select * from openquery
(
LinkedServerName,
‘SELECT * FROM packagename.functionname(”Hello”);’
)

Thank you so much -Janki

Post #1151291

« Prev Topic | Next Topic »

Permissions