Passing string parameters to linked Oracle Server procedures from SQL Server

Question

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.

Does the DB link on oracle has the grant permissions to execute this procedure? — Jorge Campos
– Jorge Campos, Commented Nov 19, 2013 at 16:56
Also, see if it helps you: dba-oracle.com/sf_ora_00900_invalid_sql_statement.htm — Jorge Campos
– Jorge Campos, Commented Nov 19, 2013 at 16:58
@JorgeCampos Yes, the userid used for the linked server has the necessary permissions. — Pete
– Pete, Commented Nov 19, 2013 at 19:14
So, it must be something related with the double quotes as mentioned on the link I provided you. Just to test. Create a procedure on oracle like this: create or replace procedure P_CREATE_USER_test as puserid varchar2(30):='MyUserId'; begin P_CREATE_USER(puserid); end; and call it from ms sql and see what happens. — Jorge Campos
– Jorge Campos, Commented Nov 19, 2013 at 19:19
@JorgeCampos I'm unsure what this is testing. As I already said in my post, I have no problem calling parameterless procedures. If this is merely to test whether or not I can call a parameterless procedure, I can. — Pete
– Pete, Commented Nov 19, 2013 at 19:38

Pete · Accepted Answer · 2013-11-19 19:53:13Z

3

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

answered Nov 19, 2013 at 19:53

Pete

6,7716 gold badges48 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Baodad Over a year ago

You should mark this as the answer. FWIW, I've also been able to call Oracle procedures using OUTPUT parameters. For instance,

DECLARE @ReturnMessage varchar(255); EXECUTE ('BEGIN ORACLE_PROC(?);END;',@ReturnMessage OUTPUT) AT ORACLE_SERVER; PRINT @ReturnMessage

Collectives™ on Stack Overflow

Passing string parameters to linked Oracle Server procedures from SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related