DTS or Import Data into SQL Server 7/2000 from Oracle Stored Proc returning resultset

Dear Brains of the Universe.
Here is my challenge or I think it is that I am challenged.

The scenario is, after days of searching I have finally managed to find how to return resultsets from an Oracle stored procedure. What I want to do is get this resultset into an SQL Server table using T/SQL or DTS.

1. Question 1. Is this possible? (I bet there is a way to do it, but just dunno how..)
2. Question 2. Hmmm, Is there any other way I can achieve what I want to achieve?

Ok here is the SQL Script for the Oracle Package, function and statement to view the resultset in oracle. (remember to use SQL* Plus).

/*

||Package Creation
*/
create or replace package Types
as
type cursorType is ref cursor;
end;
/

/*

||Function Creation
*/
create or replace function sp_ListEmp return types.cursorType as
l_cursor Types.cursorType;
begin
open l_cursor for select ename, empno from emp_order by ename; return l_cursor;
end;
/

/*

||View Resultset
*/

variable c refcursor
exec :c := sp_ListEmp
print c

I hope this is enuff to explain my pridacament. Any help would be most gracefully appreciated.

Thanks in advance.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 30 2000 - 20:02:59 CST