HI GURUS,

HOW TO WRITE THE GIVEN FUNCTION THAT RETURN REF CURSOR WITH MORE
THAN ONE ROW?

IN THE GIVEN FUNCTION THE INPUT PARAMETER IS BRAND_ID THAT WILL
RETURN ORDER NO(MULTIPLE ROW).
I M PASSING THE ORDER NO TO C1 CURSOR BUT IT IS RETURNING ONLY
THE LAST RECORD.
HOW CAN I GET MULTIPLE RECORD?
SHOULD I INSERT THE VALUE INTO TEMP TABLE AND THAN RETRIEVE IT
OR
IS THERE ANY OPTION??

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




Thanks & Regards
Rajeev kumar singh
___________________________________________________
Meet your old school or college friends from
1 Million + database...
Click here to reunite www.batchmates.com/rediff.asp


Oracle LazyDBA home page