Below Query will insert two rows which are 999,666 and Oracle will give you logs as 2 Rows Inserted.
insert into departments(department_id, department_name, manager_id,location_id)
select 999,'Administration',200,1700 from dual
union all select 666,'Marketing' ,201,1800 from dual
How do i get logs like "Rows 999 and 666 has been inserted".. by putting logs statement in query itself..(User defined logs)..Like in java we are using log4j for the same..
If you don't want to modify the code you use to insert, you could define a trigger on the table to log your operations.
For example:
create or replace trigger departmentsAfterInsert
after insert
on departments
for each row
begin
dbms_output.put_line('Inserted department ' || :new.department_id);
end;
This will log the insert operations, no matter how you do them:
SQL> insert into departments(department_id, department_name, manager_id,location_id)
2 select 999,'Administration',200,1700 from dual
3 union all select 666,'Marketing' ,201,1800 from dual;
Inserted department 999
Inserted department 666
2 rows created.
I think it is not possible from SQL without looking on trace files. It is possible if you're going to insert row by row from PL/SQL:
declare
cursor c is select 999 a, 'Administration' b, 200 c, 1700 d from dual union all
select 666 a, 'Marketing' b , 201 c, 1800 d from dual;
begin
for x in c loop
insert into departments(department_id, department_name, manager_id,location_id) values (x.a, x.b, x.c, x.d);
dbms_output.put_line(x.a || ' '); --or insert to log table here
end loop;
dbms_output.put_line('has been inserted');
end;
