Search code examples
sql-serveroracle-databasestored-procedureslinked-server

Passing string parameters to linked Oracle Server procedures from SQL 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.

Solution

  • 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