Monday, January 21, 2008

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

6 Comments:

Blogger csabi said...

and...
the result is:

OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-02005: implicit (-1) length not valid for this bind or define datatype
]

what should I do?

3:57 AM  
Blogger Julien said...

I have the same error:
ORA-02005: implicit (-1) length not valid for this bind or define datatype

Any ideas how to resolve this?

6:24 AM  
Blogger Libby said...

The solution that you have suggested to solve this problem is really very nice. I do find it the most appropriate one of all I have searched so far. I will use this solution in my project to solve this problem. Thanks for suggesting the solution.
sap upgrade

1:12 AM  
Blogger Libby said...

The solution that you have suggested to solve this problem is really very nice. I do find it the most appropriate one of all I have searched so far. I will use this solution in my project to solve this problem. Thanks for suggesting the solution.
sap upgrade

1:12 AM  
Blogger Billsqloracle said...

I know this may not be exactly what you want but I had a similar problem and ended up converting my procedure to a function and then created a view on that function and selected form the view.

8:26 AM  
Blogger Billsqloracle said...

via the linked server...

8:26 AM  

Post a Comment

<< Home