从SQL Server将字符串参数传递给链接的Oracle Server过程 [英] Passing string parameters to linked Oracle Server procedures from SQL Server

问题描述

我有一个名为P_CREATE_USER的Oracle过程.它只需要一个varchar2参数:

create or replace PROCEDURE 
P_CREATE_USER (P_USERID varchar2)
...

我可以以EXEC P_CREATE_USER('MyUserId')

的形式从Oracle运行此文件

我们已将Oracle服务器链接到我们的SQL Server.

我尝试过:

EXECUTE('P_CREATE_USER(''MyUserId'')') AT ORACLE_SERVER

对于我的麻烦，我得到:

链接服务器"ORACLE_SERVER"的OLE DB访问接口"OraOLEDB.Oracle"返回了消息"ORA-00900:无效的SQL语句".
消息7215，第17级，状态1，第1行
无法在远程服务器"ORACLE_SERVER"上执行语句.

我尝试了多种变体，使用了P_CREATE_USER和参数之间的空格而不是括号.我尝试使用@P_USERID=''MyUserId''.我以这种方式运行无参数程序没有问题，但是我看不出如何传递字符串...

更新:用于链接服务器的UserId有权运行此过程，并且可以直接从Oracle运行它.这不是权限问题.据我所知，参数是唯一导致问题的原因.

解决方案

该解决方案需要检查SQL Server(服务器对象/链接服务器/提供程序/OraOLEDB.Oracle)的OraOLEDB.Oracle驱动程序中的Dynamic Parameters属性. /p>

然后，要调用该过程，我必须执行以下操作:

DECLARE @userid varchar(50)
SET @userid = 'MyUserId'
EXECUTE ('BEGIN P_CREATE_USER(?); END;', @userid) AT ORACLE_SERVER

I have an Oracle procedure called P_CREATE_USER. It takes a single varchar2 parameter:

create or replace PROCEDURE 
P_CREATE_USER (P_USERID varchar2)
...

I can run this from Oracle as EXEC P_CREATE_USER('MyUserId')

We have the Oracle server linked to our SQL Server.

I've tried:

EXECUTE('P_CREATE_USER(''MyUserId'')') AT ORACLE_SERVER

For my trouble, I get:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_SERVER" returned message "ORA-00900: invalid SQL statement".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'ORACLE_SERVER'.

I have tried a number of variants, using a space between P_CREATE_USER and the parameter instead of parenthesis. I've tried using @P_USERID=''MyUserId''. I have no problem running parameterless procedures this way, but I can't see to figure out how to pass a string...

Update: The UserId used for the linked server has permissions to run this procedure and can run it directly from Oracle. This is not a permissions issue. As best I can tell, the parameter is the only thing causing a problem.

解决方案

The solution required checking the Dynamic Parameters property in the OraOLEDB.Oracle driver in SQL Server (Server Objects/Linked Servers/Providers/OraOLEDB.Oracle).

Then, to call the procedure, I had to do the following:

DECLARE @userid varchar(50)
SET @userid = 'MyUserId'
EXECUTE ('BEGIN P_CREATE_USER(?); END;', @userid) AT ORACLE_SERVER

这篇关于从SQL Server将字符串参数传递给链接的Oracle Server过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！