Hi,
I need help in handle exception inside for loop
I wrote pl/sql bock to execute deletes dynamically, in the above sample data Temp1 table is not in tables,but still it is updating as success - in this case it update as table doesn't exists or if there is any error it has to update in status.
declare
v_command varchar2(4000);
c_count NUMBER:=0;
v_sqlerrm VARCHAR2(1000);
v_seq_no NUMBER;
cursor c1 is SELECT * FROM ETL_STG_PURGE_DATA where FLAG='Y' ORDER BY SEQ_NO;
begin
select count(*) into c_count from ETL_STG_PURGE_DATA where FLAG='Y';
for x in c1
loop
v_command := 'delete from '|| x.TABLE_NAME|| ' where TRUNC(' ||x.COLUMN_NAME||') < TRUNC(sysdate)-'||x.NO_OF_DAYS;
execute immediate v_command;
UPDATE ETL_STG_PURGE_DATA SET STATUS='SUCCESS', LST_UPD_DATE=SYSDATE WHERE seq_no=x.seq_no;
commit;
v_seq_no:=x.seq_no;
end loop;
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := sqlerrm;
UPDATE ETL_STG_PURGE_DATA SET STATUS=v_sqlerrm, LST_UPD_DATE=SYSDATE WHERE seq_no=v_seq_no;
commit;
END;
Could you please let me know how to address this issue.
Thanks in advance.