Blog Oracle PL/SQL

rowcount on oracle dynamic sqls

A good friend asked me on how to capture the number of affected records on his delete statement. The answer would be as

simple as below:

DELETE FROM my_table
WHERE mycolumn = 'mysearch';

v_rec_count := SQL%ROWCOUNT;

On this case, SQL%ROWCOUNT will return the number of affected records in the immediate preceding statement. It is the

equivalent command for @@rowcount in Sybase/SQL Server.

However in my friend's case, the DELETE statement is part of the dynamic SQL. Obviously, we cannot use the approach

above as it is. Here's how to capture the affected records in dynamic SQLs.

vSQL := 'DELETE FROM ' || v_SchemaName || '.' || v_TableName || ' WHERE ' || v_Conditions || ‘;’;
EXECUTE IMMEDIATE ‘BEGIN ’ || vSQL || ‘ :z := sql%rowcount; END; ’ USING OUT nRecCount ;

Basically, we have to capture the rowcount from within the dynamic sql. That means you have to pass the variable via "OUT" parameter to flow the value out of the execute immediate. You can't just fire nRecCount := sql%rowcount after the execute immediate because with that, the last command that ran is the execute immediate. It will return 1 instead of the actual value from the dynamic sql.

Source :