Associative Array Not Populated From Cursor
852343Jul 15 2011 — edited Jul 18 2011I'm trying to load an associative array from a cursor selecting from a View using the following code. The issue is the array seems to be empty and I can't figure out why. I'm trying to display data from the array using the first DBMS output statement and can't get that to work either. The second DBMS output statement doesn't error in the compile and doesn't show a count in the output screen which leads me to believe the array isn't getting populated.
Can anyone help with displaying data from the array using DBMS output? Also, if anyone sees something obvious with why the array doesn't have data from the view that would be great also.
CURSOR cur_OBJ(prod_date_in IN DATE) IS
Select
ev.PROD_DATE,
ev.DELTA_FILE_BYTE,
ev.DATA_SRC_ID,
ev.ACCT_NUM,
ev.ACCT_TYPE,
ev.REC_TYPE,
ev.OBJ_TYPE,
ev.OBJ_CODE,
ev.EFF_START_DATE,
ev.EFF_END_DATE
from VW_LD_ACCOUNT_OBJ_CODE_V2 ev
where
ev.prod_date = prod_date_in
and ev.acct_num = '0000999999'
Order by ev.DELTA_FILE_BYTE;
TYPE type_tab_OBJ IS TABLE OF cur_OBJ%ROWTYPE;
TYPE type_arr_OBJ IS TABLE OF cur_OBJ%ROWTYPE INDEX BY VARCHAR2(160);
l_OBJ_rec cur_OBJ%ROWTYPE := Null;
l_tab_OBJ type_tab_OBJ;
l_arr_OBJ type_arr_OBJ;
OPEN cur_OBJ(p_curr_prod_date);
LOOP
FETCH cur_OBJ BULK COLLECT INTO l_tab_OBJ LIMIT l_fetch_limit;
EXIT WHEN l_tab_OBJ.COUNT = 0;
IF l_tab_OBJ.COUNT > 0 THEN
For l_idx in 1 .. l_tab_OBJ.last
LOOP
l_arr_OBJ(l_tab_OBJ(l_idx).acct_num ) := l_tab_OBJ(l_idx);
dbms_output.put_line('l_arr_OBJ(l_tab_OBJ(l_idx).acct_num ) ' || l_arr_OBJ(l_tab_OBJ(l_idx).acct_num ) );
END LOOP;
END IF;
END LOOP;
CLOSE cur_OBJ;
l_tab_OBJ.delete;
dbms_output.put_line('l_arr_OBJ ' || l_arr_OBJ.count );