Calling an Oracle Stored Procedure and Function

• Brian Connolly

  SSC Journeyman

  Points: 91

  More actions

  October 11, 2007 at 8:34 am

  #178925

  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

• SJanki

  SSCrazy

  Points: 2847

  More actions

  July 29, 2011 at 1:55 pm

  #1361110

  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