How to capture Oracle SP INOUT parameter in SQL Server

Question

I have Oracle linked server in my SQL Server. I created procedure which executes a stored procedure in Oracle:

CREATE PROCEDURE ModifySth (
    @var1 nvarchar(255),
    @var2 nvarchar(255)
)
AS
BEGIN
    DECLARE @var3 as NVARCHAR(255);
    EXEC('begin oracleSPname(?, ?, ? ); end;',
            @var1 output,        
            @var2,
            @var3 output,
        )
    at ORACLE
    SELECT @var1 as var1, @Vvar3 as var3;
END

When I execute ModifySth I am passing two parameters and excepting two output vars. var1 is IN OUT. How to capture var1? When I am trying to capture is, I get value which I passed to the stored procedure, not the output value.

John Zoulias · Accepted Answer · 2016-04-26 09:03:16Z

1

Why don't you use an extra variable?

DECLARE @var1_reply as NVARCHAR(255);

EXEC('begin oracleSPname(?, ?, ?, ? ); end;',
        @var1,        
        @var1_reply output,
        @var2,
        @var3 output,
    )
at ORACLE

set @var1= @var1_reply;

answered Apr 26, 2016 at 9:03

John Zoulias

346 bronze badges

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

Collectives™ on Stack Overflow

How to capture Oracle SP INOUT parameter in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related