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 enuff to explain my pridacament.
Any help would be most gracefully appreciated.

Thanks in advance.