Jayesh Acharya
asked on
create a cursor for a record pl/sql
I am trying to write a wrapper code around a number of procedures that return a record as a data type, the wrapper needs to be able to return the record as a cursor
example
declare
TYPE l_REC1 IS RECORD
( TODAY_DATE DATE
, number_col NUMBER(9)
, varchar2_col VARCHAR2(30) );
l_sqlstr VARCHAR2(32000) ;
l_REC l_REC1;
l_cur_1 sys_refcursor;
l_cur_2 sys_refcursor;
begin
l_sqlstr :=
' select sysdate today_date, 12345 number_col , ''ABC'' varchar2_col
from from dual';
l_REC := NULL;
OPEN l_cur_1 FOR l_sqlstr;
loop
FETCH l_cur_1INTO l_REC;
EXIT WHEN l_cur_%notfound;
end loop;
CLOSE l_cur_1;
-- at this point i have the data in l_REC
--but how do i do the reverse, assmume i have a l_REC data populated
--how do i create a sys_refcursor for the data in l_REC?
OPEN l_cur_2 for l_REC;
End;
I would really return just the cursor l_cur_2, but hwo do i do that?
example
declare
TYPE l_REC1 IS RECORD
( TODAY_DATE DATE
, number_col NUMBER(9)
, varchar2_col VARCHAR2(30) );
l_sqlstr VARCHAR2(32000) ;
l_REC l_REC1;
l_cur_1 sys_refcursor;
l_cur_2 sys_refcursor;
begin
l_sqlstr :=
' select sysdate today_date, 12345 number_col , ''ABC'' varchar2_col
from from dual';
l_REC := NULL;
OPEN l_cur_1 FOR l_sqlstr;
loop
FETCH l_cur_1INTO l_REC;
EXIT WHEN l_cur_%notfound;
end loop;
CLOSE l_cur_1;
-- at this point i have the data in l_REC
--but how do i do the reverse, assmume i have a l_REC data populated
--how do i create a sys_refcursor for the data in l_REC?
OPEN l_cur_2 for l_REC;
End;
I would really return just the cursor l_cur_2, but hwo do i do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
actually i have a work aournd, I am creating a different level of packages to just retrun the cursors, and this package is either called by the pl/sql program to return a record, or the vb program to return the cursor ...
it is a change but it should be fairly strait forward ...
it is a change but it should be fairly strait forward ...
ASKER
stopped me going down a path that was futile
ASKER
But now I am trying to create a wrapper around those function to retrun a cursor, becasue I need to pass a cursor to a vb program. I could look at re-writing the entire application, but I was looking for a way around the problem
If this cant be done then at least that one door that is shut