Hi Gurus,

Can any one give me some hints that how to write the given
function
that return ref cursor with more than one records?I m calling this
function in JAVA.

In the given function the input paramerter is BRAND_ID that will
return ORDER NO(multiple records).

I m passing this ORDER NO to C1 CURSOR that will return one
record for
each ORDER NO.But my function is returning only the last record.
How can i get multiple records?

Should i insert the records in temp table and than retrieve it
or
is there any other options?

FUNCTION FN_ORD_NO_BRAND(BND_ID BRAND.BRAND_NAME%TYPE)
RETURN REF_CURSOR AS

TYPE REF_CURSOR IS REF CURSOR;
C1 REF_CURSOR;

TYPE REPORT IS REF CURSOR;
C2 REPORT;

ORDNO REGBSK_TBL.SB_ORDERNO%TYPE;

BEGIN
OPEN C2 FOR
SELECT R.SB_ORDERNO
FROM PRD_TBL P,BRAND B,REGBSK_TBL R,CARDDETAIL C
WHERE C.ORDERID=R.SB_ORDERNO
AND B.BRAND_ID=P.PRD_NAME
AND B.BRAND_ID=BND_ID
AND C.PRODUCTNAME=P.PRD_ID
GROUP BY R.SB_ORDERNO,B.BRAND_NAME,R.SB_DATE
ORDER BY B.BRAND_NAME;

BEGIN
LOOP
FETCH C2 INTO ORDNO;
EXIT WHEN C2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ORDNO);//RETURNING FIVE RECORD;

/* 0408182329
0408365172
2408376660
2408401466
2408958159 */

OPEN C1 FOR
SELECT
R.SB_ORDERNO,SUM(P.DENOMINATION),SUM(P.DENOMINATION)/P.DENOMINATION
QTY,

B.BRAND_NAME,P.DENOMINATION,U.EMAILADDRESS,U.PAYPALEMAILADDRESS,R.SB_DATE
FROM BRAND B, CARDDETAIL C, PRD_TBL P , REGBSK_TBL
R,USERREGISTRATION U
WHERE ( (C.ORDERID = R.SB_ORDERNO)
AND (P.PRD_ID = C.PRODUCTNAME)
AND (B.BRAND_ID = P.PRD_NAME)
AND (R.SBUSR_ID = U.USERID)
AND (R.SB_ORDERNO=ORDNO) )
GROUP BY R.SB_ORDERNO,B.BRAND_NAME
,P.DENOMINATION,U.EMAILADDRESS,
U.PAYPALEMAILADDRESS,R.SB_DATE;

/*should i insert the above returned value in temp table*/
END LOOP;
/*retrieve the values here in ref cursor*/
RETURN C1;
CLOSE C1;

END;
END FN_ORD_NO_BRAND;

SQL>EXEC :RES:=ADDCON.PKG_REPORT.FN_ORD_NO_BRAND('207');
RETURNING RECORD FOR ONLY 2408958159 ORDER NO;

THANKS IN ADVANCE

___________________________________________________
Art meets Army ; Swapna Weds Capt. Rajsekhar.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?2


Oracle LazyDBA home page