Is there any disadvantage doing deletion using execute immediate statement like below vs the ordinary delete. Thank you.
Approach 1
FOR I IN TABNAME.FIRST..TABNAME.LAST
LOOP
begin
i_rowcount:=0;
loop
EXECUTE IMMEDIATE
'DELETE FROM '||TABNAME(I)||' WHERE EXTRACT_DT='''||p_extract_dt||''''
||' AND PROVIDER='''||p_provider||''''
||' AND UNINUM='''||p_uninum||''''
||' AND VER_NUM='||p_ver_num
||' AND rownum<='||i_commit;
i_rowcount := i_rowcount + sql%rowcount;
if sql%rowcount = 0 then
COMMIT;
exit;
end if;
commit;
END loop;
-- dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) || ' rows deleted from LOAN table') ;
INSERT INTO PMERR_CUSTOM(LOGGED_BY,LOGGED_TIME,ERROR_MESSAGE) VALUES('infrmtca',sysdate,i_rowcount||' rows deleted from '||TABNAME(I)||' table');
COMMIT;
END;
END LOOP;
Approach 2: (Regular Delete)
begin
i_rowcount:=0;
loop
DELETE FROM FEED_PROCESS_LOG WHERE EXTRACT_DT=p_extract_dt and PROVIDER=p_provider
AND UNINUM=p_uninum AND VER_NUM=p_ver_num AND rownum <= i_commit;
i_rowcount := i_rowcount + sql%rowcount;
if sql%rowcount = 0 then
commit;
exit;
end if;
commit;
end loop;
-- dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) || ' rows deleted from FEED_PROCESS_LOG table') ;
INSERT INTO PMERR_CUSTOM(LOGGED_BY,LOGGED_TIME,ERROR_MESSAGE) VALUES('infrmtca',sysdate,i_rowcount||' rows deleted from FEED_PROCESS_LOG table');
COMMIT;
end;