Home » SQL & PL/SQL » SQL & PL/SQL » Tracking which procedure updated the record (Oracle 9.2.0.1)
Tracking which procedure updated the record [message #313759] Mon, 14 April 2008 08:11 Go to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

Hello,

i am looking for an elegant way to track/log which stored procedure did the last update on a column/table and am open to any suggestions.

The DB contains too many procedures to manually add a debug line in every one of them. So what i am looking for is trigger based or something i do not know of yet (which is a lot).

Thanks in advance.

[Updated on: Mon, 14 April 2008 08:13]

Report message to a moderator

Re: Tracking which procedure updated the record [message #313760 is a reply to message #313759] Mon, 14 April 2008 08:15 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
You might want to have a look at "auditing" and "logminer," but what if it wasn't a procedure that made the update?
Re: Tracking which procedure updated the record [message #313761 is a reply to message #313760] Mon, 14 April 2008 08:17 Go to previous messageGo to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

To solve my problem finding the procedure will suffice.
Re: Tracking which procedure updated the record [message #313763 is a reply to message #313761] Mon, 14 April 2008 08:21 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Same answer. What if it wasn't a procedure that did the update?
Re: Tracking which procedure updated the record [message #313769 is a reply to message #313763] Mon, 14 April 2008 08:38 Go to previous messageGo to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

The table in this case stores data received via telegrams over the smq protocol. The whole insert/update process is done by procedures. I am trying to establish a timeline in connectino to the telegrams and the resulting values in the db.
So if there was any other update on that table something went wrong.

Auditing seems not to be able to show more than the user that did the update. I will look into the logminer now.

[Updated on: Mon, 14 April 2008 08:39]

Report message to a moderator

Re: Tracking which procedure updated the record [message #313818 is a reply to message #313769] Mon, 14 April 2008 13:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following demonstration uses Tom Kyte's who_called_me procedure:

http://asktom.oracle.com/tkyte/who_called_me/index.html

Here is my demo that you can easily modify to include primary key information and other columns or whatever you like:

-- test environment (table and 2 procedures):
SCOTT@orcl_11g> CREATE TABLE your_table
  2    (your_column  VARCHAR2 (24))
  3  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE proc1
  2  AS
  3  BEGIN
  4    INSERT INTO your_table (your_column) VALUES ('test proc1');
  5  END proc1;
  6  /

Procedure created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE proc2
  2  AS
  3  BEGIN
  4    INSERT INTO your_table (your_column) VALUES ('test proc2');
  5  END proc2;
  6  /

Procedure created.


-- audit table and trigger:
SCOTT@orcl_11g> CREATE TABLE audit_table
  2    (proc	 VARCHAR2 (40),
  3  	when	 DATE,
  4  	col	 VARCHAR2 (30),
  5  	old_val  VARCHAR2 (30),
  6  	new_val  VARCHAR2 (30))
  7  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER your_table_ai
  2    AFTER INSERT OR UPDATE OF your_column ON your_table
  3    FOR EACH ROW
  4  DECLARE
  5    v_owner	VARCHAR2 (30);
  6    v_name	VARCHAR2 (30);
  7    v_line	NUMBER;
  8    v_type	VARCHAR2 (30);
  9  BEGIN
 10    who_called_me (v_owner, v_name, v_line, v_type);
 11    INSERT INTO audit_table (proc, when, col, old_val, new_val)
 12    VALUES
 13  	 (NVL (v_type, 'SQL') ||' ' || v_owner || '.' || v_name,
 14  	  SYSDATE,
 15  	  'YOUR_COLUMN',
 16  	  :OLD.your_column,
 17  	  :NEW.your_column);
 18  END your_table_ai;
 19  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- test:
SCOTT@orcl_11g> EXEC proc1

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC proc2

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> BEGIN
  2    UPDATE your_table
  3    SET    your_column = your_column || ' again';
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> UPDATE your_table
  2  SET    your_column = REPLACE (your_column, 'again', 'one more time')
  3  /

2 rows updated.


-- results:
SCOTT@orcl_11g> SELECT * FROM your_table
  2  /

YOUR_COLUMN
------------------------
test proc1 one more time
test proc2 one more time

SCOTT@orcl_11g> SELECT proc || ' changed ' || col || ' from "' || old_val || '" to "' || new_val || '" on '
  2  	    || TO_CHAR (when, 'dd-Mon-yyyy hh24:mi:ss') AS inserts_and_updates
  3  FROM   audit_table
  4  /

INSERTS_AND_UPDATES
------------------------------------------------------------------------------------------------------------------------
PROCEDURE SCOTT.PROC1 changed YOUR_COLUMN from "" to "test proc1" on 14-Apr-2008 11:41:28
PROCEDURE SCOTT.PROC2 changed YOUR_COLUMN from "" to "test proc2" on 14-Apr-2008 11:41:28
ANONYMOUS BLOCK . changed YOUR_COLUMN from "test proc1" to "test proc1 again" on 14-Apr-2008 11:41:28
ANONYMOUS BLOCK . changed YOUR_COLUMN from "test proc2" to "test proc2 again" on 14-Apr-2008 11:41:28
SQL . changed YOUR_COLUMN from "test proc1 again" to "test proc1 one more time" on 14-Apr-2008 11:41:28
SQL . changed YOUR_COLUMN from "test proc2 again" to "test proc2 one more time" on 14-Apr-2008 11:41:28

6 rows selected.

SCOTT@orcl_11g> 

[Updated on: Mon, 14 April 2008 13:50]

Report message to a moderator

Re: Tracking which procedure updated the record [message #314657 is a reply to message #313759] Thu, 17 April 2008 07:16 Go to previous messageGo to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

It took me some time to adapt the funtcions but now it is working.
Thank you a lot. Especially for the step by step example.

The stack used in the who_called_me function contains only calls for this table? so each table has its own calls stack? is there some overview, some website you can recommend so i can get familiar with the existing stacks?

Thank you!

[Updated on: Thu, 17 April 2008 07:16]

Report message to a moderator

Re: Tracking which procedure updated the record [message #314778 is a reply to message #314657] Thu, 17 April 2008 14:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
isthisadagger wrote on Thu, 17 April 2008 05:16


The stack used in the who_called_me function contains only calls for this table? so each table has its own calls stack?



No. The call stack is associated with the chain of execution of procedures, functions, anonymous pl/sql blocks, and sql statements, not any particular table. Since a trigger was created on the table that executes who_called_me, it extracts the most recent information from the current call stack, in order to determine the procedure or pl/sql block or sql statement that did the insert or update that caused the trigger to fire. This information can be extracted at that point. The call stack only applies to the currently executing chain.

isthisadagger wrote on Thu, 17 April 2008 05:16


is there some overview, some website you can recommend so i can get familiar with the existing stacks?




Who_called_me uses the Oracle built-in function dbms_utility.format_call_stack which formats the call stack into readable lines. There isn't much in the documentation about it, but if you search http:\\asktom.oracle.com you should find some info. I have added a dbms_output of the call stack to the previous demo below, so that you can see the call stack that who_called_me is extracting the information from. When an update statement updates two rows, the row trigger is fired twice, so you will see the call stack twice.

SCOTT@orcl_11g> -- test environment (table and 2 procedures):
SCOTT@orcl_11g> CREATE TABLE your_table
  2    (your_column  VARCHAR2 (24))
  3  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE proc1
  2  AS
  3  BEGIN
  4    INSERT INTO your_table (your_column) VALUES ('test proc1');
  5  END proc1;
  6  /

Procedure created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE proc2
  2  AS
  3  BEGIN
  4    INSERT INTO your_table (your_column) VALUES ('test proc2');
  5  END proc2;
  6  /

Procedure created.

SCOTT@orcl_11g> -- audit table and trigger:
SCOTT@orcl_11g> CREATE TABLE audit_table
  2    (proc	 VARCHAR2 (40),
  3  	when	 DATE,
  4  	col	 VARCHAR2 (30),
  5  	old_val  VARCHAR2 (30),
  6  	new_val  VARCHAR2 (30))
  7  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER your_table_ai
  2    AFTER INSERT OR UPDATE OF your_column ON your_table
  3    FOR EACH ROW
  4  DECLARE
  5    v_owner	VARCHAR2 (30);
  6    v_name	VARCHAR2 (30);
  7    v_line	NUMBER;
  8    v_type	VARCHAR2 (30);
  9  BEGIN
 10    DBMS_OUTPUT.PUT_LINE ('call stack:  ' || chr(10) || DBMS_UTILITY.FORMAT_CALL_STACK);
 11    who_called_me (v_owner, v_name, v_line, v_type);
 12    INSERT INTO audit_table (proc, when, col, old_val, new_val)
 13    VALUES
 14  	 (NVL (v_type, 'SQL') ||' ' || v_owner || '.' || v_name,
 15  	  SYSDATE,
 16  	  'YOUR_COLUMN',
 17  	  :OLD.your_column,
 18  	  :NEW.your_column);
 19  END your_table_ai;
 20  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- test:
SCOTT@orcl_11g> EXEC proc1
call stack:  
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
21DB864C         7
SCOTT.YOUR_TABLE_AI
290EF440         4  procedure SCOTT.PROC1
21CE9BB8         1  anonymous block


PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC proc2
call stack:  
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
21DB864C         7
SCOTT.YOUR_TABLE_AI
291B71AC         4  procedure SCOTT.PROC2
290DAA9C         1  anonymous block


PL/SQL procedure successfully completed.

SCOTT@orcl_11g> BEGIN
  2    UPDATE your_table
  3    SET    your_column = your_column || ' again';
  4  END;
  5  /
call stack:  
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
21DB864C         7
SCOTT.YOUR_TABLE_AI
21DEAC68         2  anonymous block

call stack:  
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
21DB864C         7
SCOTT.YOUR_TABLE_AI
21DEAC68         2  anonymous block


PL/SQL procedure successfully completed.

SCOTT@orcl_11g> UPDATE your_table
  2  SET    your_column = REPLACE (your_column, 'again', 'one more time')
  3  /
call stack:  
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
21DB864C         7
SCOTT.YOUR_TABLE_AI

call stack:  
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
21DB864C         7
SCOTT.YOUR_TABLE_AI


2 rows updated.

SCOTT@orcl_11g> -- results:
SCOTT@orcl_11g> SELECT * FROM your_table
  2  /

YOUR_COLUMN
------------------------
test proc1 one more time
test proc2 one more time

SCOTT@orcl_11g> SELECT proc || ' changed ' || col || ' from "' || old_val || '" to "' || new_val || '" on '
  2  	    || TO_CHAR (when, 'dd-Mon-yyyy hh24:mi:ss') AS inserts_and_delets
  3  FROM   audit_table
  4  /

INSERTS_AND_DELETS
------------------------------------------------------------------------------------------------------------------------
PROCEDURE SCOTT.PROC1 changed YOUR_COLUMN from "" to "test proc1" on 17-Apr-2008 12:02:06
PROCEDURE SCOTT.PROC2 changed YOUR_COLUMN from "" to "test proc2" on 17-Apr-2008 12:02:06
ANONYMOUS BLOCK . changed YOUR_COLUMN from "test proc1" to "test proc1 again" on 17-Apr-2008 12:02:06
ANONYMOUS BLOCK . changed YOUR_COLUMN from "test proc2" to "test proc2 again" on 17-Apr-2008 12:02:06
SQL . changed YOUR_COLUMN from "test proc1 again" to "test proc1 one more time" on 17-Apr-2008 12:02:06
SQL . changed YOUR_COLUMN from "test proc2 again" to "test proc2 one more time" on 17-Apr-2008 12:02:06

6 rows selected.

SCOTT@orcl_11g> 


Re: Tracking which procedure updated the record [message #314783 is a reply to message #314657] Thu, 17 April 2008 14:42 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following, which does not involve any tables, might make the call stack of the chain of execution a little clearer. Proc1 outputs the call stack, showing that it was called by proc2, which was called by proc3, which was called by proc4, which was called by an anonymous pl/sql block. This should help eliminate any confusion caused by tables and triggers and demonstrate that it is just the chain of execution.


SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE proc1
  2  AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);
  5  END proc1;
  6  /

Procedure created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE proc2
  2  AS
  3  BEGIN
  4    proc1;
  5  END proc2;
  6  /

Procedure created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE proc3
  2  AS
  3  BEGIN
  4    proc2;
  5  END proc3;
  6  /

Procedure created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE proc4
  2  AS
  3  BEGIN
  4    proc3;
  5  END proc4;
  6  /

Procedure created.

SCOTT@orcl_11g> BEGIN
  2    proc4;
  3  END;
  4  /
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
290EF440         4  procedure
SCOTT.PROC1
291B71AC         4  procedure SCOTT.PROC2
25346AB8         4  procedure SCOTT.PROC3
1FBD4F90         4
procedure SCOTT.PROC4
252BCD6C         2  anonymous block


PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Previous Topic: Error at line 26 (no matter what)
Next Topic: Finding code changes from source table
Goto Forum:
  


Current Time: Thu Dec 08 03:58:32 CST 2016

Total time taken to generate the page: 0.06801 seconds