SQL%ROWCOUNT yielding ORA-00911: invalid character

Question

I'm trying to log how many rows affected from an DELETE statement, so

delete MyTbl where MyCondition = 1;
insert into MyLog(MyTotal) values(SQL%ROWCOUNT);

getting this:

10 rows deleted.

insert into MyLog(MyTotal) values(SQL%ROWCOUNT)

Error at line 2 ORA-00911: invalid character

Cause:

Nothing to do with PL/SQL or not. SQL%ROWCOUNT is special.

Can't use SQL%ROWCOUNT as is in an INSERT statement even in a PL/SQL block (whether stored procedure or anonymous procedure); you have to create a variable, assign the count to it, and use that variable in INSERT

Are you running this in a pl/sql block, because if you are not, that won't work. — OldProgrammer
Possible duplicate of Converting SQL Select Query to PL/SQL Anonymous Block (ORACLE) — Jorge Campos

mathguy mathguy · Accepted Answer · 2018-02-28T02:04:37

You are probably trying to do something like this. Note that you can't use SQL%ROWCOUNT as is in an INSERT statement even in a PL/SQL block (whether stored procedure or anonymous procedure); you have to create a variable, assign the count to it, and use that variable in INSERT.

create table mytbl (id number, mycondition number);

insert into mytbl
  select 101, 1 from dual union all
  select 102, 2 from dual union all
  select 105, 1 from dual union all
  select 110, 9 from dual
;

commit;

create table mylog(mytotal number);

Anonymous block (procedure we didn't name, so we can use it only once, in place; otherwise give it a name, it becomes a "stored procedure"):

declare
  cnt number;
begin
  delete mytbl where mycondition = 1;
  cnt := sql%rowcount;
  insert into mylog(mytotal) values (cnt);
end;
/

Execute it (select and press F5? I don't use Toad but that is how it would work in SQL Developer).

Then check the results:

select * from mytbl;

 ID MYCONDITION
--- -----------
102           2
110           9

select * from mylog;

MYTOTAL
-------
      2

Don't forget to COMMIT when you are done! And, I hope this is a significant over-simplification of what you are actually doing; a row in the log table, just showing how many rows were deleted, with no other information (such as a timestamp), is useless.

SQL%ROWCOUNT yielding ORA-00911: invalid character

1 Answers