0

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

Jeb50
  • 6,272
  • 6
  • 49
  • 87
  • 2
    Are you running this in a pl/sql block, because if you are not, that won't work. – OldProgrammer Feb 28 '18 at 01:24
  • yes, in TOAD, F5. column `[MyTotal]` is integer type. – Jeb50 Feb 28 '18 at 01:27
  • Toad is not a pl/sql block. – Jorge Campos Feb 28 '18 at 01:33
  • Possible duplicate of [Converting SQL Select Query to PL/SQL Anonymous Block (ORACLE)](https://stackoverflow.com/questions/48687792/converting-sql-select-query-to-pl-sql-anonymous-block-oracle) – Jorge Campos Feb 28 '18 at 01:35
  • @JorgeCampos is procedure a PL/SQL block? – Jeb50 Feb 28 '18 at 01:52
  • It is kind of but not it is not. A PL/SQL block is an anonymous block that you can run and it will not be saved anywhere in the database so you can use it again (unless you save it in a file or whatever). A Procedure is a defined block to the database that will be saved and can be called many times as you want. If you program in any language such as C, Java a PL/SQL block is like the main function and A procedure is a method that does not have a return statement (although you can get things out of it). There are better technical explanation for then, I just used my own words. Hope it helps – Jorge Campos Feb 28 '18 at 02:14
  • @JorgeCampos not PL/SQL, it's because sql%rowcount has its own limitation. – Jeb50 Feb 28 '18 at 18:22

1 Answers1

5

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.