Home » SQL & PL/SQL » SQL & PL/SQL » Audit history of pl/sql code..
Audit history of pl/sql code.. [message #262133] Fri, 24 August 2007 12:28 Go to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
i want to create a audit history of my stored procs, and triggers code in my database.. i know that USER_SOURCE stores the code..
i found on oracle:
CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;

CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
But when i create the trigger i got the error PL/SQL: ORA-00904: "DICTIONARY_OBJ_NAME": invalid identifier??

i don't want to use CVS nor any other source control system i need it to be in the database.. we have remote sites that change the code and never check in changes.. So i want a way to track it from the database by a trigger..
Re: Audit history of pl/sql code.. [message #262152 is a reply to message #262133] Fri, 24 August 2007 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Then,
Just a look to the documentation would show that variable names are wrong: ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type

Regards
Michel
Re: Audit history of pl/sql code.. [message #262174 is a reply to message #262152] Fri, 24 August 2007 15:23 Go to previous messageGo to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
i shall work on fixing my post next time.. thank you for your help.. i was more looking at other ways of getting the information outside of this trigger.. it only picks up the add to packages, triggers, etc.. it does not pick up table changes.. columns and index etc.. but it was the best example of what i found to do soo far.. i am looking for a total way to track changes..
Re: Audit history of pl/sql code.. [message #262202 is a reply to message #262174] Sat, 25 August 2007 01:33 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it only picks up the add to packages, triggers, etc.. it does not pick up table changes.. columns and index

The title of your topic is "Audit history of pl/sql code" and your first sentence was:
Quote:
i want to create a audit history of my stored procs, and triggers code in my database

So we answered for this.
If you want to record other changes, use an "AFTER DDL" trigger and scan the event using the Oracle variables.

Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers
Section Responding to System Events through Triggers

Regards
Michel
Previous Topic: range from comma separated values
Next Topic: how to add new procedures to existing package
Goto Forum:
  


Current Time: Sun Dec 04 02:41:25 CST 2016

Total time taken to generate the page: 0.09095 seconds