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 <terminate string> in the nested table
The block is executing successfully for about 2000 rows and failing there after with the error
Error terminating account 429023-187 ERROR > SQLERROR = <-6500> 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 = <'|| SQLCODE || '> ' || 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 = <'|| SQLCODE || '> -- ' || SQLERRM);
ROLLBACK;
utl_file.fclose(v_file_handle);
END;