Delete row with confirmation from Oracle table

Question

I have this SQL query which is used to delete users.

DELETE FROM USERS WHERE USERNAME = ?

The problem is that I don't know is there any row success row removal or not. I always get success at the end.

Is there any way to get for example some confirmation from Oracle that row is deleted in my Java code?

If the command completes without an error, then the row(s) (if any) is/are deleted. Check for error codes. — Gordon Linoff, May 15 '15 at 12:23
Not sure but PreparedStatement.executeUpdate() returns effected row count for DML. You can try. — Subir Kumar Sao, May 15 '15 at 12:27
This applies to deletes as well: http://stackoverflow.com/questions/2571915/return-number-of-rows-affected-by-sql-update-statement-in-java — Mark Leiber, May 15 '15 at 12:30

score 3 · Accepted Answer · answered May 15 '15 at 12:46

executeUpdate() method of PreparedStatement gives You the number of rows deleted.If no rows have been deleted by the query You get 0.I think that's the easiest solution.

If You need to know which rows have been deleted You can user "Returning" clause, that will give You rows deleted.

Regards

score 0 · Answer 2 · answered May 15 '15 at 12:32

You can use SQL%ROWCOUNT. It is an implicit cursor that gives the number of rows affected by SQL statement

declare
begin
DELETE FROM USERS WHERE USERNAME = ?;
dbms_output.put_line('Total Deleted:' ||sql%rowcount);
end;

This will give you the count of the number of rows deleted.

Delete row with confirmation from Oracle table

2 Answers2