top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

How does one keep a history of PL/SQL code changes?

+1 vote
100 views
How does one keep a history of PL/SQL code changes?
posted Apr 6, 2014 by Neeraj Pandey

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

0 votes

Its one of the FAQ and found the similar problem at http://www.orafaq.com/wiki/PL/SQL_FAQ#How_does_one_keep_a_history_of_PL.2FSQL_code_changes.3F

Here is the detail
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 will allow you to easily revert to previous code should someone make any catastrophic changes. Look at this example:

CREATE TABLE SOURCE_HIST                    -- Create history table
  AS SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*
  FROM   ALL_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 ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
                           'PACKAGE',   'PACKAGE BODY',
                           'TYPE',      'TYPE BODY')
  THEN
     -- Store old code in SOURCE_HIST table
     INSERT INTO SOURCE_HIST
            SELECT sysdate, all_source.* FROM ALL_SOURCE
             WHERE  TYPE = ORA_DICT_OBJ_TYPE  -- DICTIONARY_OBJ_TYPE IN 8i
               AND  NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME IN 8i
  END IF;
EXCEPTION
  WHEN OTHERS THEN
       raise_application_error(-20000, SQLERRM);
END;

/
show errors
answer Apr 6, 2014 by Salil Agrawal
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...