Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to get number of rows deleted

Status
Not open for further replies.

nenkert

Programmer
Sep 20, 2001
4
SE
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
 
You can get that from sql%rowcount:

begin
delete from TABLE where id=502;
<return SQL%ROWCOUNT via whatever mechanism you are using>;
end;
 
Ok, thanks. But can you give an example of how this variable can be returned?

/Tobias
 
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
 
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
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top