Call PL/SQL Procedure in MS SQL Server

[Database Administrator]

I have one procedure in Oracle that receives two in/out parameters,
that are
PL/SQL structures. The parameters are changed by the procedure and the
results must be read from them, that were updated by the procedure.


Here are the PL/SQL declaration of the package that contains the
procedure:
=================================================
Package EXTRACT
IS
SUBTYPE row_table1 IS table1%Rowtype;

TYPE array_table2 IS TABLE OF table2%ROWTYPE
INDEX BY BINARY_INTEGER;

Procedure run( p1 IN OUT row_table1, p2 IN OUT array_table2);
END;
=================================================

I need to:
1. Populate p1 (only 1 row) and p2 ("n" rows) from my system in T-SQL;
2. Call the Oracle procedure "extract.run", passing the two in/out
parameters;
3. Read the return in the same p1 and p2 variables, updated by the
procedure.

I have read how to call an stored procedure in Oracle through linked
server,
receiving a recordset as return.
Unfortunately I could not find things like the example above, where I
need
to send complex parameters to Oracle, not native datatypes.

Can someone help me?

Thanks in advance,
Rodolfo Rodrigues

[Database Administrator]

Even if the (OLEDB) Driver used with the linked server allows complex
datatypes they won't be mapped to TSQL. The only option for non
supported (PL)SQL constructs is a pass through query using OPENROWSET
or OPENQUERY. You'll need to construct a literal sql statement for
this. It will however not allow you to pass in several rows/recordsets.
For this you'll probably require a seperate temporary table in your
ORACLE database into which the records from your SQL Server instance
can be put.

DM Unseen

[radovan.cisar]

call oracle procedure (function) via linked server and get its result (return value)

--input parameters p1, p2, p3
declare @SQL nvarchar(1000)
declare @param nvarchar(100)
declare @result varchar(20) -- numbers may cause data type error

-- (...custom actions, i.e. insert to local table)

set @SQL = N'set @result = (select * from openquery(myLinkedServer, ''select OwnerName.Function_Insert(' + cast(@p1 as varchar) + ', ' + cast(@p1 as varchar) + ', ' + cast(@p3 as varchar) + ') from dual''))'
set @param = '@result varchar output'
EXEC sp_executesql @SQL, @param, @result output 

if @result <> '0' goto ERROR

-- (...custom actions)

return 1 -- OK

ERROR:
-- (...custom actions, i.e. delete from local table with(rowlock))
return 0 -- KO