Inserting log statement inside SQL queries

Question

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

@Tim thanks for the response but the duplicate which you marked will give you the trace option and which queries has been executed on DB.. My requirement is like i need to put the log statement for the query result...Correct me if my understanding is wrong — vinod, Dec 13 '16 at 10:27
Update your question by showing exactly what you want logged. We can't read your mind, and you should be assuming this. — Tim Biegeleisen, Dec 13 '16 at 10:28
@Tim I am sorry but i need a logs like "Row 999 and 666 has been inserted" for the above query — vinod, Dec 13 '16 at 10:29
Why do you need logs? You know the values you inserted, so you know that if there were no exceptions, then they got inserted correctly. If you absolutely must know which rows were added as part of an insert, make sure there's a unique identifier for each insert (some sort of run_number or timestamp or something) and then you can query the table for the rows you inserted. I mean, what happens if you have millions of rows to be inserted in one go; do you really want to have to do that row-by-row just to get the logging info back? — Boneist, Dec 13 '16 at 11:12
@Boneist I am building SQL script from java and script is bigger its just one example.. and those records are dynamic so i need logs for the same also thanks for the idea of unique identifier — vinod, Dec 13 '16 at 11:32
So why don't you log the info in the Java side, rather than in the database side? If the insert is successful, then all of the rows will have been inserted, otherwise all the rows being inserted by that statement will fail to be inserted. — Boneist, Dec 13 '16 at 11:34
My Java output is only building the script.. and scripts get executed from dba ..And there don't have control from java side otherwise i would love to go with java logs — vinod, Dec 13 '16 at 11:35
So you don't know what values are going in the script that Java is putting together? I'm still curious as to why you need logs. Generally, you'd let the fact that the rows existed in the db as evidence of the fact that they were inserted. You can also use dml error logging to handle rows that fail to be inserted (although there are restrictions) so that you don't have to rollback the entire insert statement if one or more rows fail. — Boneist, Dec 13 '16 at 11:45
@Boneist I am performing Select, Insert and Delete operation..Java code will take the input from XML files and Build the SQL queries.. With the the use of Unions i am generating the select statement(Rows) also using minus comparing the DB table and records from XML ... so whatever queries i will execute on DB .. I need customized logs where i can put my own custom messages... — vinod, Dec 13 '16 at 11:57

score 3 · Answer 1 · answered Dec 13 '16 at 11:07

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.

score 2 · Accepted Answer · answered Dec 13 '16 at 10:51

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;

It will work for me. Instead of single query need to build cursor.. Thanks Kacper for the alternative... .Also for confirming that It wont work with SQL — vinod, Dec 13 '16 at 10:57

Inserting log statement inside SQL queries

2 Answers2