PL/SQL RPC call from Sql Server linked server (OraOLEDB)

I'ma having trouble calling a remote pl/sql proc from SQL server 2000 using
a
linked server provider=OraOLEDB.Oracle
Here's the call:
EXEC PS..TESTDTA.esp_F0101Z2_EDSP_set
'C',
'MCHAMBERS',
'23577',
'107399',
2000.0
This is the proc, straight forward...
CREATE PROCEDURE TESTDTA.esp_F0101Z2_EDSP_set (
flag in varchar2,
p_szedus in nchar,
p_szedbt in nchar,
p_szedtn nchar,
p_szedln number
)
AS
BEGIN
UPDATE TESTDTA.F0101Z2 SET
SZEDSP = flag
WHERE
szedus = p_szedus AND
szedbt = p_szedbt AND
szedtn = p_szedtn AND
szedln = p_szedln;
END;
Linked Server Properties:
Collation Compatible = true
Data Access = true
RPC = true
RPC Out = true
Use Remote Collation = true
Provider Properties:
AllowInProcess = 1
DisallowAdhocAccess = 0
DynamicParameters = 1
IndexAsAccessPath = 0
LevelZeroOnly = 0
NestedQueries = 0
NonTransactedUpdates = 0"Alien2_51" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:A0EB9CA3-F18A-4777-8F5B-46CE8F1856B4@.microsoft.com...
> I'ma having trouble calling a remote pl/sql proc from SQL server 2000
> using a
> linked server provider=OraOLEDB.Oracle
> Here's the call:
> EXEC PS..TESTDTA.esp_F0101Z2_EDSP_set
> 'C',
> 'MCHAMBERS',
> '23577',
> '107399',
> 2000.0
>
> This is the proc, straight forward...
> CREATE PROCEDURE TESTDTA.esp_F0101Z2_EDSP_set (
> flag in varchar2,
> p_szedus in nchar,
> p_szedbt in nchar,
> p_szedtn nchar,
> p_szedln number
> )
> AS
> BEGIN
> UPDATE TESTDTA.F0101Z2 SET
> SZEDSP = flag
> WHERE
> szedus = p_szedus AND
> szedbt = p_szedbt AND
> szedtn = p_szedtn AND
> szedln = p_szedln;
> END;
>
In SQL 2000 you have to use OPENQUERY and jump through hoops:
http://groups.google.com/group/micr.../>
ce8a?hl=en&
SQL 2005 makes this much easier a couple of enhancements to the EXEC
statement, you can specify a linked server to run remotely, and you can pass
parameters in and out of a dynamic SQL query.
--Oracle SQL---
CREATE OR REPLACE PROCEDURE scott.esp_F0101Z2_EDSP_set (
flag in varchar2,
p_szedus in nchar,
p_szedbt in nchar,
p_szedtn nchar,
p_szedln number,
p_result out varchar2
)
AS
BEGIN
p_result := 'it worked';
END;
--end Oracle SQL---
EXEC sp_addlinkedserver @.server = 'ORACLE',
@.srvproduct='Oracle',
@.provider='OraOLEDB.Oracle',
@.datasrc='//localhost/xe'
EXEC sp_serveroption @.server='ORACLE', @.optname='rpc out', @.optvalue='true'
EXEC sp_addlinkedsrvlogin 'ORACLE', 'false', NULL, 'Scott', 'tiger'
GO
EXEC (
'
BEGIN
SCOTT.esp_F0101Z2_EDSP_set(
:p_flag,
:p_szedus,
:p_szedbt,
:p_szedtn,
:p_szedln,
:p_result);
END;
',
'C',
'MCHAMBERS',
'23577',
'107399',
2000.0,
@.result output
) at ORACLE
print @.result