ref cursor to table convertor
521053Jun 30 2006 — edited Jul 3 2006Hello.
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