Hi,This is Halim,working as a Sr. Oracle DBA and PL/SQL developer in Atlanta, USA. I very much like to play with SQL, PL/SQL and scripting languages along with my DBA job. I got a recognition for PL/SQL. Please see here at 16th position(http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html). I am maintaining this blog for helping myself while in work and sharing my knowledge with others. If you have any question. you can reach me at infohalimdba@gmail.com Thanks.

Sunday, September 20, 2009

History of PL/SQL code changes

One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example:

CREATE OR REPLACE TRIGGER change_hist AFTER CREATE ON SCHEMADECLAREBEGIN IF ora_dict_obj_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE') THEN INSERT INTO source_hist SELECT SYSDATE, user_source.* FROM user_source WHERE TYPE = ora_dict_obj_type AND NAME = ora_dict_obj_name; END IF;EXCEPTION WHEN OTHERS THEN raise_application_error (-20000, SQLERRM);END;