How to call Oracle stored procedure from MS SQL

It is really a pain if you need to call Oracle stored procedure from MSSQL.
MSSQL throws an error 7212 if remote procedure does not return any recordset. Oracle procedures usually don't return recordset.
One way was to create a dummy table, create trigger on it, from where stored proc is called. Then to update this table from MSSQL.

Matt Meleski has published really excellent way of calling oracle stored procedures from MS SQL! It does not require the trigger at all!
He's placed quite thorow example on his BLOG http://objectsharp.com/cs/blogs/matt/archive/2005/06/13/2221.aspx

By the way, the example can be simplified to very short code. Main thing - the Oracle stored procedure must have at least one output parameter type of indexed PL/SQL table. Indexed - it is important. Instead of creating a custom type in PL/SQL package, one can use readily available DBMS_OUTPUT.CHARARR.

Here is an example.

Make stored proc in Oracle:

create or replace procedure SCOTT.PROC4MS(OUTPUT OUT dbms_output.chararr) is
begin
-- you do not have to assign anything to OUTPUT if you don't need it.
OUTPUT(1):='one';
OUTPUT(2):='two';
OUTPUT(3):='three';
end;
/

Then call it from MS SQL. And here is the trick - use curly braces and a magic "resultset 25" parameter.

select * from openquery(MYORADB, '{CALL SCOTT.PROC4MS({resultset 25, OUTPUT})}');

Enjoy!

Other helpful links:
- How To Retrieve a Recordset from an Oracle Stored Procedure Using ADO on ASP http://support.microsoft.com/kb/229919