CODEWITHSUNDEEP
stored-proceduresplsql
rgautam11
rgautam11

Reputation: 3

How to return ref_cursor from for loop from pl/sql procedure

i want to get back ref cursor for the below sp. But it's not working.. not getting any records.. So please help me for this.

DATA_INPUTLIST - it's collection type for two variables (mtn, mtnEffDate) DATA_RESULTLIST - It's also a collection type for 3 variables (id, effDate, mtn)

 CREATE OR REPLACE PROCEDURE proc1 (
       pCustId  IN  NUMBER,
       pAcctNo    IN    NUMBER,
       pSearchCriteria    IN    DATA_INPUTLIST,
       pRecordSet OUT SYS_REFCURSOR,
       out_error_code   OUT   NUMBER,
       out_error_message   OUT   VARCHAR2
    ) AS

        --Variables
        v_SUN_DATE DATE := TO_DATE('01/15/2011', 'mm/dd/yyyy');
        vCount    NUMBER := 0;
        v_mtn NUM

BER;
    v_mtn_eff_date DATE;
    cMtnPricePlanInfo DATA_RESULTLIST;

BEGIN

    SELECT COUNT(*) INTO vCount FROM TABLE (pSearchCriteria);

    FOR i IN 1..vCount LOOP

        SELECT MTN, TO_DATE(MTN_EFF_DATE, 'mm/dd/yyyy') into v_mtn, v_mtn_eff_date 
        FROM TABLE (pSearchCriteria) 
        WHERE 
        ROWNUM = i; 

        SELECT 
            A.PPLAN_ID, A.EFF_DATE, A.MTN INTO cMtnPricePlanInfo(i).PPLAN_ID, cMtnPricePlanInfo(i).EFF_DATE, cMtnPricePlanInfo(i).MTN
        FROM CUST_ACCT_LINE_PPLAN A, CUST_ACCT_LINE_PPLAN_HIST B
        WHERE
            A.CUST_ID  = pCustId
            AND A.ACCT_NO    = pAcctNo
            AND A.MTN = v_mtn
            AND A.MTN_EFF_DATE = v_mtn_eff_date
            AND A.EFF_DATE >= 
                (SELECT MAX(EFF_DATE) FROM CUST_ACCT_LINE_PPLAN_HIST C
                WHERE
                    C.CUST_ID  = pCustId
                    AND C.ACCT_NO    = pAcctNo
                    AND C.MTN = v_mtn
                    AND C.MTN_EFF_DATE = v_mtn_eff_date
                    AND C.EFF_DATE <= v_SUN_DATE)
            AND A.CUST_ID = B.CUST_ID
            AND A.ACCT_NO = B.ACCT_NO
            AND A.MTN = B.MTN
            AND A.MTN_EFF_DATE = B.MTN_EFF_DATE
            AND A.PPLAN_ID = B.PPLAN_ID;

    END LOOP;
       OPEN pRecordSet FOR
            SELECT * FROM TABLE (cMtnPricePlanInfo);

END;
/

Upvotes: 0

Views: 145

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64959

The first things that stands out to me is the following:

    SELECT MTN, TO_DATE(MTN_EFF_DATE, 'mm/dd/yyyy') into v_mtn, v_mtn_eff_date 
    FROM TABLE (pSearchCriteria) 
    WHERE 
    ROWNUM = i;

For i greater than 1 this will not return anything. For the reason why, see this question. In fact, because you're using SELECT ... INTO ..., you'll get a no data found error if i is greater than or equal to 2.

You don't need an SQL query just to fetch some values out of PL/SQL collections. Try replacing this query with

    v_mtn := pSearchCriteria(i).MTN;
    v_mtn_eff_date := TO_DATE(pSearchCriteria(i).MTN_EFF_DATE, 'mm/dd/yyyy');

I don't have your tables nor the data in them so I can't be sure why you're getting no data. I can only hazard at some obvious suggestions: is vCount zero? If you put suitable values for v_mtn and v_mtn_eff_date and run your query on its own, separate from this stored procedure, does it return any rows?

Upvotes: 1

Related Questions