[hemz]

Hi,
I am trying to create a varray of strings using elements returned from a nested table, but getting ora-06533 error, can someone pls help me over come this
I am using a script as below:

Code: [Select all] [Show/ hide]
DECLARE
time_start NUMBER;
time_end NUMBER;
TYPE text_nt
IS TABLE OF cpw_account_info%ROWTYPE;
v_ename_nt TEXT_NT;
TYPE term_acc IS VARRAY(5000) OF VARCHAR2(5000);
term_acc_stmnt TERM_ACC; --:=term_acc();
CURSOR c_emp IS
SELECT *
FROM cpw_account_info;
BEGIN
OPEN c_emp;

time_start := dbms_utility.get_time;

LOOP
FETCH c_emp BULK COLLECT INTO v_ename_nt LIMIT 500;

dbms_output.Put_line('v_eName_nt.count: '
||v_ename_nt.COUNT);

dbms_output.Put_line('=====================================');

FOR i IN 1..v_ename_nt.COUNT LOOP
dbms_output.Put_line(V_ename_nt(i).account_num
||','
||V_ename_nt(i).end_dat
||','
||V_ename_nt(i).termination_reason_id
||','
||V_ename_nt (i).erly_term_chrg_boo);

term_acc_stmnt := Term_acc();

term_acc_stmnt.Extend(v_ename_nt.COUNT);

-- create array of strings using varray
term_acc_stmnt := Term_acc('ACC.TERMACC('
|| V_ename_nt(i).account_num
||','
|| V_ename_nt(i).end_dat
||','
|| V_ename_nt(i).termination_reason_id
||','
|| V_ename_nt(i).erly_term_chrg_boo
|| ');');

dbms_output.Put_line('Terminate API String: '
|| Term_acc_stmnt(i));
END LOOP;

exit WHEN c_emp%notfound;
END LOOP;

time_end := dbms_utility.get_time;

dbms_output.Put_line('time_start: '
||time_start);

dbms_output.Put_line('time_end: '
||time_end);

dbms_output.Put_line('time elapsed in ms: '
||( time_end - time_start ) / 100 * 1000);

CLOSE c_emp;
END;

/


DBMS_OUTPUT:
v_eName_nt.count: 5
=====================================
123455,09-JUL-11,1,F
Terminate API String: ACC.TERMACC(123455,09-JUL-11,1,F);
123456,09-JUL-11,1,F
**ora-06533 subscript beyond count**
/ the script errors out with this error when i'm expecting the script to proceed and produce strings like*
Terminate API String: ACC.TERMACC(99999,09-JUL-11,1,F);
Terminate API String: ACC.TERMACC(11111,09-JUL-11,1,F);
*Terminate API String: ACC.TERMACC(22222,09-JUL-11,1,F); /

I guess I am makin a mistak of accessin varray within a nested table loop, your suggestions please

My table has data in the below format
ACCOUNT_NUM END_DAT TERMINATION_REASON_ID ERLY_TERM_CHRG_BOO
123455 09/07/2011 1 F
123456 09/07/2011 1 F
123457 09/07/2011 1 F
123458 09/07/2011 1 F
123459 09/07/2011 1 F

What am i trying to do?
I am trying to fetch this data through a nested table and prepare a set of strings(api calls) thru varray
Execute the prepared varray thru execute immediate statement