ZDS

Kintana Standards

Requests are starting to fail in production at a step that used to behave perfectly.
Almost the first thing you want to know is if anyone changed the source code and
if so what was changed.

Yes I know you have a very well controlled procedure for documenting changes
to objects in the production image, but it could'nt hurt surely to record them
automatically...

This article describes a trigger you can add to your schema to track changes to your
production image. It provides a very basic form of version control.

However it does ensure that you can easily revert to previous code if someone (else)
makes changes that turn out to be ... not totally tested.

The basic code is taken from How can one keep a history of PL/SQL code changes?
on orafaq.com.
I could'nt make that work so I've re-written it extensively to make sure it works on
an Oracle Kintana server and extended it to track os user names.

First we need to create a table to track changes to schema objects

-- Create History table
-- Change MY_SOURCE_HISTORY to your own table name
CREATE TABLE MY_SOURCE_HISTORY (
     change_date timestamp,
     login_user  VARCHAR2(30),
     os_user     VARCHAR2(30),
     object_type VARCHAR2(12),  -- user_source.type
     object_name VARCHAR2(30),  -- user_source.name
     line        NUMBER,        -- user_source.line
     text        VARCHAR2(4000) -- user_source.text
);
/

Kintana Administrators are all likely to use the same database login when using
DBArtisan or Toad to to alter procedures. That's not going to help much in figuring
out who made that change...

However, displaying the os_user will let us hunt down the true identity of the
evil-doer who inadvisedly changed that previously working procedure...

To make this work you'll need to get your dba to

grant select on v_$session to SCOTT;

grant select on v_$process to SCOTT;

on your production database (and test database while you try this out).

-- Store code in history table
CREATE OR REPLACE TRIGGER my_source_change_history    -- Change to your table name
AFTER CREATE ON SCOTT.SCHEMA                          -- Change SCOTT to your schema name
DECLARE
BEGIN
IF sys.DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
   THEN
      -- Store old code as it was BEFORE the latest alteration
      -- Change to your table name
      INSERT INTO my_source_history (change_date,
                                       login_user, os_user,
                                       object_type, object_name, line, text )
      ( SELECT CDT, LGU, OSU, OTP, ONM, LIN, TXT
        FROM
          (SELECT sysdate CDT from dual),
          ( SELECT s.username LGU,
                   s.osuser OSU
            FROM v$session s,  v$process p
            WHERE s.paddr = p.addr
              AND s.username is not null
              AND s.audsid=sys_context('userenv','SESSIONID')
          ),
          ( SELECT us.type OTP,
                   us.name ONM,
                   us.line LIN,
                   us.text TXT
            FROM USER_SOURCE us
            WHERE us.type = sys.DICTIONARY_OBJ_TYPE
              AND us.name = sys.dictionary_obj_name
          )
      );
END IF;
EXCEPTION
  WHEN OTHERS THEN
         raise_application_error(-20000, SQLERRM);
END;
/

CHANGE_DATE                LOGIN_USER  OS_USER     OBJECT_TYPE  OBJECT_NAME            LINE  TEXT
7/15/2005 18:12:04.000 AM  SCOTT      davegoodall PACKAGE BODY MAININVENTORYUPDATEV24 1     PACKAGE BODY MainInventoryUpdatev24 AS
...
7/15/2005 18:12:04.000 AM  SCOTT      davegoodall PACKAGE BODY MAININVENTORYUPDATEV24 1          select inv.bin inv.cell, inv.rack
...
7/16/2005 20:12:34.000 AM  SCOTT      evildooer   PACKAGE BODY MAININVENTORYUPDATEV24 1     PACKAGE BODY MainInventoryUpdatev24 AS
...
7/16/2005 20:12:34.000 AM  SCOTT      evildooer   PACKAGE BODY MAININVENTORYUPDATEV24 1          select inv.bin inv.cell

You'll need to test this using SQL*PLUS. These commands will come in handy...

-- Compile
show errors trigger my_source_change_history;
alter trigger my_source_change_history COMPILE;

-- Toggle
alter trigger my_source_change_history DISABLE;
alter trigger my_source_change_history ENABLE;

-- Cleanup
drop trigger my_source_change_history;
drop table my_source_history

-- test!
select * from my_source_history;

Kintana™ and 'HP PPM (Project and Portfolio Management)'™ are trademarks of Hewlett Packard Corporation.