Can I get the resulting message from an UPDATE in Oracle?

Question

When I run an UPDATE statement in Oracle, is there a way to catch the returning message from the update that is displayed in the console (should be something like "15 rows updated" for example)?

If not, is there a way to catch the number of rows that were updated and store this value into a variable in PL/SQL?

The message is generated by the client (sql*plus for example) so you can't access that in PL/SQL, but you probably want the `SQL%ROWCOUNT` attribute. https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1273 — kfinity, Jun 12 '18 at 14:23
This may be what I was looking for but, in this case, I need to store this value into a variable in PL/SQL I declared before. Is there a way to do that? — Rexam, Jun 12 '18 at 14:33

score 4 · Accepted Answer · answered Jun 12 '18 at 14:37

4

You can store the output of SQL%ROWCOUNT in a variable then it can be displayed as:

declare
v_count number;
begin
update t1 set val = 'B' where id = 1;
v_count := sql%rowcount;
dbms_output.put_line(v_count||' rows updated');
end;

answered Jun 12 '18 at 14:37

Vivek

783
5
11

Perfect, I didn't know you could directly do something like `v_count := sql%rowcount` – Rexam Jun 12 '18 at 14:45

Can I get the resulting message from an UPDATE in Oracle?

1 Answers1