Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ref cursor to table convertor

521053Jun 30 2006 — edited Jul 3 2006
Hello.
I ask you a favor and I hope you could help me out.
I have a lot of legacy functions that return REF CURSOR.
I'd like to have a generic method that convert a cursor variable to something what I could query with SELECT statement.

select * from TABLE(mypkg.CONVERT(LEGACYFUNCTION()));

Here I give you an example how I can deal with the problem.
But It's easy to see that "mypkg" depends on "ordr" table.
I would like the "mypkg" to be unaware of "ordr" table - it must be generic.

A legacy function looks like this one:
CREATE OR REPLACE FUNCTION LEGACYFUNCTION RETURN SYS_REFCURSOR AS
c SYS_REFCURSOR;
begin
OPEN c FOR SELECT * from ordr;
return c;
end;


Here my package that converts REF CURSOR to something good:
CREATE OR REPLACE PACKAGE mypkg AS
TYPE TabTyp IS TABLE OF ordr%ROWTYPE;
FUNCTION CONVERT(c IN SYS_REFCURSOR) RETURN TabTyp PIPELINED;
end;

CREATE OR REPLACE PACKAGE BODY mypkg AS

FUNCTION CONVERT(c IN SYS_REFCURSOR) RETURN TabTyp PIPELINED AS
data ordr%ROWTYPE;
begin
LOOP
FETCH c INTO data;
PIPE ROW(data);
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
end;

end;

I'm looking for something like SELET * FROM CONVERT(LEGACYFUNCTION());
Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 31 2006
Added on Jun 30 2006
8 comments
5,185 views