Calling an Oracle Stored Procedure from a Sql Server 2000 Stored P

Calling an Oracle Stored Procedure from a Sql Server 2000 Stored P

Post by amF5bG9 » Sat, 19 Aug 2006 22:09:02


I am very sorry to hear about this Richard!
I had the same issue...

The way I got around this was to create a trigger on an input table in
Oracle to fire off the SP. the table should hold all variables the SP needs
and the trigger should pass those values. on the Oracle side you need to
have the SP load a results table and you need to use openquery to retrieve
the data from the results table.

Real PITA but it works well.

I can't help you with the Oracle side but I can help you with the SQL piece.

HTH,
Joe
 
 
 

Calling an Oracle Stored Procedure from a Sql Server 2000 Stored P

Post by Richard Ur » Sat, 19 Aug 2006 23:45:38

I found another way to get this result.
I created an Oracle function that call the Oracle SP an return "ok".
I call the function with :
SELECT *
FROM OPENQUERY(ORA_LINEKED, 'SELECT DATABASE.FUNCTION FROM DUAL')
the result is ok.