Search code examples
sqloracle-databaseoracle12c

SQL Obtain total amount of deleted lines in file with multiple DELETE queries

I put an example

In a SQL script with a hundred or more delete queries the output should be like that: Note: The deletes cannot be made using a unique delete query. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. 1 row deleted. Commit complete.

Is it possible to obtain a count of total lines deleted like.

1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.

16 row deleted.
Commit complete.

I could also be valid for update or insert commands.

Solution

  • SQL%ROWCOUNT is what comes in mind. However & unfortunately, I can't make it work at SQL level. Here's an example: it is a .SQL script which sets a variable, deletes some rows from two tables and tries to calculate total number of deleted rows. 

    var total number;

delete from a where deptno = 10;
exec :total := nvl(:total, 0) + sql%rowcount;

delete from b where deptno = 10;
exec :total := nvl(:total, 0) + sql%rowcount;

print total

    When I ran it, I got:

    SQL> @p

3 rows deleted.


PL/SQL procedure successfully completed.


1 row deleted.


PL/SQL procedure successfully completed.


     TOTAL
----------

    Total is NULL because SQL%ROWCOUNT is NULL (I know; I tried with NVL(SQL%ROWCOUNT, -1) and got -2 as a result.

    But, if you switch to PL/SQL - which might be possible, by enclosing your DELETE statements into BEGIN-END - then there's some improvement. A new .SQL script:

    set serveroutput on
declare
  l_total number := 0;
begin
  delete from a where deptno = 10;
  l_total := l_total + sql%rowcount;

  delete from b where deptno = 10;
  l_total := l_total + sql%rowcount;

  dbms_output.put_line('Deleted total of ' || l_total || ' rows');
end;
/

    Testing:

    SQL> @p
Deleted total of 4 rows

PL/SQL procedure successfully completed.

    If that satisfies your needs, good. If not, I'm afraid I wouldn't know how to do what you want.