CODEWITHSUNDEEP
oracle-databaseoracle11g
JCD70
JCD70

Reputation: 33

Oracle DB : get the query that triggered a particular update

I would like to monitor some field in my database ; when an SQL query updates that field with a certain value, I would like to log the query that triggered the update.

How can I do that ?

Thank you in advance!

Upvotes: 2

Views: 1068

Answers (2)

steve
steve

Reputation: 6020

Why don't you use the audit statement? It allows you to monitor for instance updates against a table.

Upvotes: 0

winkbrace
winkbrace

Reputation: 2711

Good question. Made me curious. I found an answer here. A LOGMINER utility is also mentioned there. Maybe worth looking into?

SQL> CREATE OR REPLACE FUNCTION cur_sql_txt
  2    RETURN CLOB
  3  AS
  4    v_cnt BINARY_INTEGER;
  5    v_sql ORA_NAME_LIST_T;
  6    v_rtn CLOB;
  7  BEGIN
  8    v_cnt := ora_sql_txt (v_sql);
  9    FOR l_bit IN 1..v_cnt LOOP
 10      v_rtn := v_rtn || RTRIM (v_sql (l_bit), CHR (0));
 11    END LOOP;
 12    RETURN RTRIM (v_rtn, CHR (10)) || ';';
 13  END;
 14  /

Function created.

SQL> CREATE OR REPLACE TRIGGER trigger_name
  2    BEFORE UPDATE ON emp
  3    FOR EACH ROW
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE (cur_sql_txt);
  6  END;
  7  /

Trigger created.

SQL> SET SERVEROUTPUT ON;
SQL> UPDATE emp
  2  SET    empno = empno,
  3         ename = ename
  4  WHERE  ROWNUM = 1;
UPDATE emp
SET    empno = empno,
       ename = ename
WHERE  ROWNUM =
:"SYS_B_0";

1 row updated.

SQL>

Upvotes: 1

Related Questions