-
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 enuff to explain my pridacament.
Any help would be most gracefully appreciated.
Thanks in advance.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|