oracle cursor and ref cursor
I have a cursor in Package A
cursor c1
select * from emp;
now what I want to do is pass this curosr in package A into a function
that will convert the curor into a ref curosor.
function convert_crsto_ref
(y Packagea.c1) return sys_refcursor;
any clues on how i can do this??
the actual cursor is huge and very complex.
cursor c1
select * from emp;
now what I want to do is pass this curosr in package A into a function
that will convert the curor into a ref curosor.
function convert_crsto_ref
(y Packagea.c1) return sys_refcursor;
any clues on how i can do this??
the actual cursor is huge and very complex.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can do it without dbms_sql. I just don't see the need to do it.
You can do it without dbms_sql. I just don't see the need to do it.I agree, but since that's what the author wants...
ASKER
The reason is that different groups are using the cursor ,
The dev group is developing and using he cursor in their procs,
The qa group would use that same cursor in reports and some front end procs
I need the 1st /2nd line support folks to be able to just run the cursor and get the results to determine actions to take to resolve problems that may occur,
So I need to keep the cursor consistent say in package a
The qa would use the generic function to select the appropriate cursor to pass back to their programs
The support folks could just run a simple select functionReturnCursor(cur a)from dual to interperet the results.
The dev team does not want to re code the cursors into strings, because they declare record types based on the cursor by cur/row type for speed.
So I am trying to find a way that would meet everyone's needs ...
Hence my idea of just having a function that would allow you to pass the cursor to a function so you could do a select from dual
The dev group is developing and using he cursor in their procs,
The qa group would use that same cursor in reports and some front end procs
I need the 1st /2nd line support folks to be able to just run the cursor and get the results to determine actions to take to resolve problems that may occur,
So I need to keep the cursor consistent say in package a
The qa would use the generic function to select the appropriate cursor to pass back to their programs
The support folks could just run a simple select functionReturnCursor(cur a)from dual to interperet the results.
The dev team does not want to re code the cursors into strings, because they declare record types based on the cursor by cur/row type for speed.
So I am trying to find a way that would meet everyone's needs ...
Hence my idea of just having a function that would allow you to pass the cursor to a function so you could do a select from dual
Again, a cursor and ref cursor are the same thing. If you have the cursor to pass to a function, just use it. What pass it into a function to just have it passed back.
I'm not understanding what you think this is going to do for you.
From what you posted: you want to open c1, call a function with it that basically returns c1 back to you?