SQL%ROWCOUNT and Logger

Logger (by Tyler Muth) is a free open source logging tool for PL/SQL. Tyler initially launched it in 2009 and now it has become a staple tool for many Oracle development teams. If you've never heard of it go check it out.

When using logger I like to log the number of rows an update statement made after the update was performed. In order to do that I use the SQL%ROWCOUNT variable. The thing to be aware of is if you log SQL%ROWCOUNT using logger it will be "reset" by the implicit insert that logger does.  In the example below you'll notice that after calling logger the SQL%ROWCOUNT now has a value of 0 since it does an insert:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> BEGIN   2    UPDATE emp   3    set sal = sal;   4   5    dbms_output.put_line('Rows updated: ' || SQL%rowcount);   6    dbms_output.put_line('Rows updated: ' || SQL%rowcount);   7    logger.log('Rows updated: ' || SQL%rowcount);   8    dbms_output.put_line('Rows updated: ' || SQL%rowcount);   9  END;  10  / Rows updated: 14 Rows updated: 14 Rows updated: 0   PL/SQL procedure successfully completed.   SQL> SELECT text   2  FROM logger_logs   3  WHERE ROWNUM = 1   4  ORDER BY ID DESC;   TEXT ------------------ Rows updated: 14

This is important to know because sometimes you may do some additional work after an update statement depending on how many records were updated in the previous statement. You should change your code from:

1 2 3 4 5 6 7

UPDATE emp SET sal = sal; logger.log('Rows updated: ' || SQL%rowcount);   IF SQL%rowcount > 0 THEN   ... END IF;

To:

1 2 3 4 5 6 7 8

UPDATE emp SET sal = sal; l_row_count := SQL%rowcount; logger.LOG('Rows updated: ' || l_row_count);   IF l_row_count > 0 THEN   ... END IF;