33858

executed an oracle stored procedure from sql server using the open query function

Can you confirm if you have successfully executed an oracle stored procedure from sql server using the open query function. If yes, How? because I am having problems executing stored procedures with arguments using the OPENQUERY Function.

Answer1:

Finally tracked down a few solutions – the key to the problem (for us) is that by default RPCs are disabled for linked servers. The parameters for Rpc, Rpc Out, and Use Remote Collation need to be set to true. More info:

http://blog.sqlauthority.com/2007/10/18/sql-server-2005-fix-error-msg-7411-level-16-state-1-server-is-not-configured-for-rpc/

The solution you use will depend upon the procedure output requirements. The first example returns an output value. The second example no output values are returned (data is collected in a subsequent query).

Example 1

The procedure T2T_collect_all has two input parameters (start and end dates) and one output parameter (row count).

DECLARE @l_i_parameter1 varchar(10)

DECLARE @l_i_parameter2 varchar(10)

DECLARE @l_i_parameter3 varchar(10)

DECLARE @l_i_parameter4 varchar(10)

DECLARE @l_o_parameter1 integer

SET @l_i_parameter1 = '2009/10/01'

SET @l_i_parameter2 = 'yyyy/mm/dd'

SET @l_i_parameter3 = '2009/12/31'

SET @l_i_parameter4 = 'yyyy/mm/dd'

SET @l_o_parameter1 = 0

EXECUTE ( 'begin T2T_collect_all(to_date(?, ?), to_date(?, ?), ? ); end;',

@l_i_parameter1, @l_i_parameter2, @l_i_parameter3, @l_i_parameter4, @l_o_parameter1 OUTPUT

) AT ORA_DB;

More Info: http://blogs.msdn.com/joaquinv/archive/2008/10/23/execute-oracle-stored-procedure-in-sql-server.aspx

Example 2a

The procedure T2T_collect_allx has only two input parameters (start and end dates).

EXECUTE ('begin T2T_collect_allx (SYSDATE - 40, SYSDATE); end;') ORA_DB;

Example 2b

SELECT * FROM OPENQUERY(ORA_DB, 'begin T2T_collect_allx (SYSDATE - 40, SYSDATE ); end;')

Answer2:

Im no db guy, but what about adding an output parameter on the Oracle proc. Then populate it in the proc and return it to SQL server. Here is an example click here

hth, ~ck

Recommend

  • Which index should I use on binary datatype column mysql
  • Error Code: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC
  • Arabic data transfer between multiple tables in different MySQL databases returning ???????? as new
  • How to retrieve unicode data stored directly in non-unicode field as varchar format in sql server
  • sql server 2008 filter on big list passed in
  • T-SQL: sorting results by year, month
  • Accent insensitive search django sqlite
  • Why can't I use a specific collation in MySQL?
  • Laravel 5.1 - Connecting to MySQL Database (MAMP)
  • mysql-python collation issue: how to force unicode datatype?
  • How to make MySQL handle strings like SQLite does, with regard to Unicode and collation?
  • MySQL with Japanese characters
  • SQL Server - Is there a collation that provides natural order for numbers?
  • limited threads in soapUI free version
  • Send emails through VB6 if no email client
  • New Firebase failed: First argument must be a valid firebase URL and the path can't contain “.”
  • JSON encode and decode on PHP
  • Building Qt project for C++11 standard
  • Rest Services conventions
  • Database structure design with variable amounts of fields
  • NHibernate Validation Localization with S#arp Architecture
  • Highlight one bar in a series in highcharts?
  • How to rebase a series of branches?
  • Spring security and special characters
  • JSON with duplicate key names losing information when parsed
  • XCode can't find symbols for a specific iOS library/framework project
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • bootstrap to use multiple ng-app
  • How to get icons for entities from eclipse?
  • C# - Getting references of reference
  • How to set the response of a form post action to a iframe source?
  • Change div Background jquery
  • Turn off referential integrity in Derby? is it possible?
  • Qt: Run a script BEFORE make
  • sending mail using smtp is too slow
  • JaxB to read class hierarchy
  • Busy indicator not showing up in wpf window [duplicate]
  • reshape alternating columns in less time and using less memory
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?