Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL%ROWCOUNT with EXECUTE IMMEDIATE

reidsterSep 29 2010 — edited Sep 29 2010
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 27 2010
Added on Sep 29 2010
3 comments
14,508 views