How to get number of rows deleted

[nenkert]

Suppose I want to run this query:
delete from TABLE where id=502

How do I get the number of rows deleted?

I would prefer to get this information from the JDBC driver but any other information is also great.

Thanks!
/Tobias Nenkert

 

[carp]

You can get that from sql%rowcount:

begin
delete from TABLE where id=502;
<return SQL%ROWCOUNT via whatever mechanism you are using>;
end;

 

[nenkert]

Ok, thanks. But can you give an example of how this variable can be returned?

/Tobias

 

[kindus]

Hope following example helps u

set serveroutput on
declare
a number;
begin
delete emp where emp_id=3;
a := sql%rowcount;
dbms_output.put_line(a);
end;
/

~kindus

 

[nenkert]

Great, now I know how to get this information &quot;inside the DB&quot;. Here is a follow-up question: How can I use this information inside a Java program. I guess the value of the ROWCOUNT variable must be returned in a ResultSet in some way, but how? This query does _not_ work:
select SQL%ROWCOUNT from dual

Any ideas?

/Tobias

 

[carp]

As a SQL statement passed through a JDBC driver - I don't know how you could do that. However, a possible workaround might be to have a generic procedure on the database side. You could then put in a call to the procedure and get the number of rows affected passed back to you:

PROCEDURE generic_call(p_command IN VARCHAR2, p_rows IN OUT NUMBER) IS
BEGIN
EXECUTE IMMEDIATE p_command;
p_rows := sql%rowcount;
END;

Now, instead of issuing a sql command, your application would declare a variable (rows_affected), and place a call:

generic_call('delete from TABLE where id=502',rows_affected)

After the call completes, you can examin rows_affected to see how many rows were updated/deleted.

It's a bit klugey (as most work-arounds are!) and it may or may not work for you. But it's about the best I can do today!