oracle 06500,SQLERROR = <-6500> ORA-06500: PL/SQL: storage error — oracle-tech

Hi,

I am required to execute the block below but facing the above error, I have been looking for a way around this but unable to, please suggest a way to resolve the same

Operation performed:

Bulk collect a list of accounts from a table (account_info) into a collection type nested table (v_cpw_acc)

For each row in the nested table above, prepare a string of terminate statement and store it into another nested table (term_acc_stmnt)

execute immediate each of the in the nested tableThe block is executing successfully for about 2000 rows and failing there after with the error

Error terminating account 429023-187 ERROR > SQLERROR = ORA-06500: PL/SQL: storage error

BLOCK OF CODE

DECLARE

-- Collection to hold list of accounts impacted

TYPE cpw_accs IS TABLE OF account_info%ROWTYPE;

v_cpw_acc cpw_accs;

-- Collection to hold terminate api strings for each account to be terminated

TYPE term_acc IS TABLE OF VARCHAR2(5000);

term_acc_stmnt term_acc :=term_acc();

-- Cursor to fetch impacted accounts

CURSOR cpw_acc IS SELECT * FROM account_info WHERE termination_status IS NULL;

-- Set up the variables

v_current_action VARCHAR2(255);

v_file_handle utl_file.file_type;

v_Tracking NUMBER :=0;

v_Tracking_on NUMBER :=1;

v_accnt_sts NUMBER :=0;

v_sysdate VARCHAR2(20);

BEGIN

dbms_output.enable(1000000);

OPEN cpw_acc;

LOOP

FETCH cpw_acc BULK COLLECT INTO v_cpw_acc;

FOR i IN v_cpw_acc.FIRST..v_cpw_acc.LAST

LOOP

term_acc_stmnt.EXTEND;

term_acc_stmnt(term_acc_stmnt.COUNT) := 'TERMINATACC1NC('||''''||

v_cpw_acc(i).CPW_ACCOUNT_ID||''''||','||

+'TO_DATE('||''''||v_sysdate||''''||','||''''||'dd/mm/yyyy'||''''||')'||','||+

v_cpw_acc(i).TERMINATION_REASON_ID||','||''''||

v_cpw_acc(i).P_EARLYTERMINATIONCHARGEBOO||''''||');';

v_current_action := 'Terminate string for account '||v_cpw_acc(i).CPW_ACCOUNT_ID||' is '||term_acc_stmnt(i);

log_message('T',3,v_current_action);

This seems to be the point of error

BEGIN

EXECUTE IMMEDIATE ('BEGIN '||term_acc_stmnt(i)||' END;');

COMMIT;

EXCEPTION

WHEN others THEN

v_current_action := 'Error terminating account '||v_cpw_acc(i).CPW_ACCOUNT_ID;

log_message('E',1,v_current_action);

log_message('E',1,'SQLERROR = ' || SQLERRM);

UPDATE account_info SET termination_status ='Error', error_message= v_current_action || ' Check Log'

WHERE CPW_ACCOUNT_ID=v_cpw_acc(i).CPW_ACCOUNT_ID;

COMMIT;

END;

END LOOP;

EXIT WHEN cpw_acc%NOTFOUND;

END LOOP;

CLOSE cpw_acc;

EXCEPTION

WHEN no_data_found THEN

log_message('E',1,'No Data Found whilst '||v_current_action);

ROLLBACK;

WHEN others THEN

log_message ('E',1,'Error whilst '||v_current_action);

log_message ('E',1,'ERROR SQLCODE = -- ' || SQLERRM);

ROLLBACK;

utl_file.fclose(v_file_handle);

END;