CODEWITHSUNDEEP
sqlstored-proceduressql-server-2012oracle12cstored-functions
J.Cart
J.Cart

Reputation: 572

Get return value from stored procedure on linked server in SQL Server

I have a stored function on an Oracle DB, that I'm executing from a procedure in SQL Server. The function in Oracle returns a boolean value. The trouble I'm having is getting that value once it's executed.

Here's the call:

EXEC  ('DECLARE
            x boolean;
        BEGIN
            x := dba.ws_pack.truncate_table;
        END;'
) AT DBLINK;

Which works for executing the function and truncating the table.

So any idea how I get the value of x, or how I can execute this and get the return value directly?

Upvotes: 0

Views: 1478

Answers (2)

J.Cart
J.Cart

Reputation: 572

Here's what I got to work:

select @return_value = val from openquery(DBLINK, 'select dba.ws_pack.truncate_table val from dual');

Upvotes: 1

OjtamOjtam
OjtamOjtam

Reputation: 280

EXEC ('BEGIN ? := dba.ws_pack.truncate_table; END;', @ReturnValue ) AT DBLINK;

Upvotes: 1

Related Questions