How to return data in a TABLE type via a REF CURSOR

Hi,

I hope someone has done this or could point me in the right direction or give sample code.

Here is my situation:
currently I can return a REF CURSOR from a stored procedure as an OUT param. I accomplish this by using this line:

      Select V_cType1, V_cType2 FROM DUAL;

( I set V_cType1 and 2 to data that I previously selected and manipulated from the database)

NOW, here is the problem:
I can only select one row from dual. I need to select many rows from dual, (i.e. return a result set with multiple rows, not just ONE). I am trying to do this by using a table type, like this:

...
...
...

        V_FeeIDCursor   pkg_user_types.CursorType;
	TYPE FeeTable 		IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
	TYPE FeeTypeTable 	IS TABLE OF VARCHAR2 INDEX BY BINARY_INTEGER;

   	V_tFeeID 	FeeTable;
   	V_tFeeType 	FeeTypeTable;

	V_nFeeID	ASSOC_FEES.fee_id%TYPE;
	V_cFeeType	ASSOC_FEES.fee_type%TYPE;

	V_nFeeCount	NUMBER;
	V_nFeeIndx	NUMBER;

	CURSOR  V_curAllFees IS
    		SELECT 	f.fee_id, f.fee_type
		INTO	V_nFeeID, V_cFeeType
    		FROM 	assoc_fees f
    		WHERE  (f.assoc_id = P_nNationalID
		OR	f.assoc_id = P_nAssocID
		OR	f.assoc_id = P_nPrimaryStateAssocID);

BEGIN

	V_nFeeIndx  := 1
	FOR CUR_ALLFEE_REC IN V_curAllFees LOOP

		V_tFeeID(V_nFeeIndx) := CUR_ALLFEE_REC.fee_id;
		V_nFeeIndx  := V_nFeeIndx + 1;
	END LOOP;


	V_nFeeCount := V_tFeeID.COUNT;
	V_nFeeIndx  := 1

	WHILE V_nFeeIndx <= V_nFeeCount LOOP

		--7.1.7.2.5
		IF P_cFeeType6 != '' THEN
			IF P_cFeeType1 = V_tFeeType(V_nFeeIndx)

		--7.1.7.2.6
		IF V_cFeeType = 'Dues' OR V_cFeeType = 'Assessment' THEN
			IF P_cDuesWaivedNational = '1' THEN
			IF P_cDuesWaivedState = '1' THEN
			IF P_cDuesWaivedLocal = '1' THEN

	END LOOP;
-- This is where the problem is:
	OPEN V_FeeIDCursor FOR

	SELECT 	V_nFeeID,
		V_cFeeType
	FROM 	dual;

	P_Cursor := V_FeeIDCursor;

--HOW CAN I return the data in V_tFeeID to the calling application.

FYI. I am using JDBC to go against Oracle 8.

THANY YOU VERY MUCH IN ADVANCE
Larry

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Feb 08 1999 - 16:54:27 CST