SQL%ROWCOUNT with EXECUTE IMMEDIATE
reidsterSep 29 2010 — edited Sep 29 2010I know that SQL%ROWCOUNT works well with straightforward DML statements. However, is there a way to obtain the SQL%ROWCOUNT when running a delete statement with EXECUTE IMMEDIATE?
Maybe I'm missing something, but I keep getting 0 records with the following even though > 0 records are deleted. evt_log is a simple logging proc.
v_sql := 'DELETE FROM ORABPEL.DLV_MESSAGE WHERE state > 1 AND receive_date < TRUNC(SYSDATE) - :1 ';
EXECUTE IMMEDIATE v_sql USING p_days_back;
evt_log(v_proc, 'LOG', 'End of DLV_MESSAGE delete. Rows deleted='||SQL%ROWCOUNT);
Thanks for your insights.
-Reid