ref cursors in a package, Urgent!

[mkey]

Hi all,
I have a procedure which has 2 output parameters.

create procedure pro_A(la out ref_cur, lb out ref_cur)
is
begin
....

la := v_one;
lb := v_two;
....

I need to use ref cursors because both la and lb might return multiple records when called.
So I wrote a package something like this:

CREATE OR REPLACE PACKAGE Cross_RefCur_pkg
AS
TYPE crosscur IS REF CURSOR;
PROCEDURE pro_A(la out crosscur , lb out crosscur);
END Cross_RefCur_pkg;
/


CREATE OR REPLACE PACKAGE BODY Cross_RefCur_pkg
AS
PROCEDURE pro_A(la out crosscur , lb out crosscur)
IS
v_cur crosscur;
BEGIN
OPEN v_cur FOR SELECT .....
END pro_A;
END Cross_RefCur_pkg;
/

What I don't understand is the FOR SELECT part of it. I am not populating the results of la and lb to a table. So I'm not sure how I should write the package body.

Any help will be greatly appreciated.

Thank you!

 

[vgg]

Hi,
Your approach is correct.The FOR SELECT is used to dynamically associate you cursor variable v_cur to different select statements.
The only thing your need to do is to declare variables,
in your package body,so that you can fetch the column values in to them.
The best suggestion(if you are using oracle8i) would be to use the bulk collect clause(to improve performance of fetch)
or else you can loop through the cursor to fetch your results.

An ex:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE NameList IS TABLE OF emp.ename%TYPE;
TYPE SalList IS TABLE OF emp.sal%TYPE;
emp_cv EmpCurTyp;
names NameList;
sals SalList;
BEGIN
OPEN emp_cv FOR SELECT ename, sal FROM emp;
FETCH emp_cv BULK COLLECT INTO names, sals;
...
...
END;

 

[jee]

here is a quote from the PL/SQL manual:

"You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set. When all the rows are processed, you CLOSE the cursor variable. "

This implies that queries have to be used to get the data. You can define a query that returns data not found in tables. Since I don't know where you are getting your data, lets assume you just want to return some constant employee data every time - let's say 2 rows like this:

badge name
12345 emp1
54321 emp2

Your code could look like this:

CREATE OR REPLACE PACKAGE BODY Cross_RefCur_pkg
AS
PROCEDURE pro_A(la out crosscur)
IS
BEGIN
OPEN la FOR
SELECT '12345' badge, 'emp1' employee
FROM dual
UNION
SELECT '54321' badge, 'emp2' employee
FROM DUAL;
END pro_A;
END Cross_RefCur_pkg;
/

Another option is to write a function that returns the data and call this function from a query.

 

[mkey]

Hi all,
I'm still not sure about how to retrive my records since I'm not selecting from a table as suggested in the FOR SELECT clause.

 

[sem]

If you're not selecting from the table you need no ref cursor at all. You may use any collection type, e.g. pl/sql table, varray to return a set of values.

 

[mkey]

Hi sem,
I never used pl/sql table or varray before.
Can you able to give an example please?

Thank you!

 

[vgg]

Hi mkey,

In my example I have stored the result in a variable of type TABLE,the same you can try with VARRAY as well.

TYPE empnumtyp is VARRAY(100) OF NUMBER(4);

VGG