Results 1 to 3 of 3

Thread: Collections

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Collections

    Hi,

    When I execute the below procedure, it prints fine when cd = xxx
    but when it checks for both the values(xxx and yyy) it prints a BLANK line

    PROCEDURE proc_aa(
    a_tab IN a_list,
    period IN VARCHAR2,
    pf_tab IN OUT t_list,
    per_tab IN p_list,
    . .
    . .
    )
    IS

    amt number := 0;
    BEGIN
    For rec in (select did, NVL(SUM (expns_amt),0) amt
    from TABLE (CAST (a_tab AS a_list))
    where cd in ('xxx','yyy')
    group by did
    )

    LOOP
    IF rec.amt > 0
    THEN
    FOR i IN
    per_tab.FIRST .. per_tab.LAST

    LOOP
    pf_tab.EXTEND;
    pf_tab (i).did := rec.did;
    pf_tab (i).c_did :=c_did;
    pf_tab (i).m_did :=m_did;
    pf_tab (i).e_amt := per_tab(i).per * rec.amt;
    END LOOP;
    END IF;
    END LOOP;

    if pf_tab.COUNT > 0 then
    for i in pf.First .. pf.Last
    Loop
    dbms_output.put_line('Printing ... '||pf_tab(i).did||' '||
    pf_tab(i).c_did||' '||pf_tab(i).m_did||' '||
    pf_tab(i).e_amt);
    end loop;
    end if;

    end proc_aa;
    Per_tab has 2 rows and the select above returns 2 rows for xxx and yyy, so I'm expecting 4 rows in total in the output.
    But whats happening is, after it prints for XXX(the first set of 2 rows), its looping through the same i(pf_tab(i)) counter and replacing the existing value and hence not printing anything. I need to initilize it and also increment it which will solve the problem. But as pf_tab(i) is a PL/SQL table I'm not able to achieve what I want. Any ideas?

    printing...11 22 33 2329.343
    printing...22 33 44 334.23
    printing...
    printing...

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you should also post your call exactly as you are doing it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    I got it. I initialized a variable j and then used that variable when getting data from pf_tab(J) and incremented that variable before exiting the loop.

    PROCEDURE proc_aa(
    a_tab IN a_list,
    period IN VARCHAR2,
    pf_tab IN OUT t_list,
    per_tab IN p_list,
    . .
    . .
    )
    IS

    amt number := 0;
    j number :=1;
    BEGIN
    For rec in (select did, NVL(SUM (expns_amt),0) amt
    from TABLE (CAST (a_tab AS a_list))
    where cd in ('xxx','yyy')
    group by did
    )

    LOOP
    IF rec.amt > 0
    THEN
    FOR i IN
    per_tab.FIRST .. per_tab.LAST

    LOOP
    pf_tab.EXTEND;
    pf_tab (j).did := rec.did;
    pf_tab (j).c_did :=c_did;
    pf_tab (j).m_did :=m_did;
    pf_tab (j).e_amt := per_tab(i).per * rec.amt;
    j := j+1;
    END LOOP;
    END IF;
    END LOOP;

    if pf_tab.COUNT > 0 then
    for i in pf.First .. pf.Last
    Loop
    dbms_output.put_line('Printing ... '||pf_tab(i).did||' '||
    pf_tab(i).c_did||' '||pf_tab(i).m_did||' '||
    pf_tab(i).e_amt);
    end loop;
    end if;

    end proc_aa;
    Last edited by nandinir; 08-06-08 at 12:26.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •