CODEWITHSUNDEEP
sqloracle-databaseloggingplsql
vinod
vinod

Reputation: 1202

Inserting log statement inside SQL queries

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..

Upvotes: 2

Views: 2901

Answers (2)

Aleksej
Aleksej

Reputation: 22949

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.

Upvotes: 3

Kacper
Kacper

Reputation: 4818

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;

Upvotes: 2

Related Questions