CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Jorge Vega Sánchez
Jorge Vega Sánchez

Reputation: 7590

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.

Upvotes: 0

Views: 163

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

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.

Upvotes: 1

Related Questions