Hashing Packages, procedures, views and triggers

From: Jason Salter <jason_at_seahorse.demon.co.uk>
Date: Mon, 09 Aug 1999 11:00:53 GMT
Message-ID: <37afb27c.7178252@news.demon.co.uk>

All,

I'm trying to find a valid way to audit two (or more) supposedly identical sets of packages, procedures,views etc without having to dump them to UNIX spool files and running diff's on them. Platform is Sun Solaris 2.5.1 running Oracle 7.3.4.0.1.

So far, I've come up with the following hashing code scripts;

For Procedures, Packages & Functions :-

---

set serverout on size 1000000

declare

v_SourceText 	dba_source.text%TYPE;
v_Hash 		INTEGER := 0;

cursor c_SrcText (src_owner_in VARCHAR2, 
		  src_name_in VARCHAR2,
		  src_type_in VARCHAR2) is
 select text
 from	dba_source
 where 	owner = UPPER (src_owner_in)
 and 	name  = UPPER (src_name_in)
 and 	type  = UPPER (src_type_in)

 order by line;

BEGIN
FOR srcname_rec IN (select distinct owner, name, type

		    from dba_source
		    where owner not in ('SYS','SYSTEM'))

 LOOP  v_Hash := 0;

  OPEN c_SrcText
(srcname_rec.owner,srcname_rec.name,srcname_rec.type);   LOOP
   FETCH c_SrcText INTO v_SourceText;
   EXIT WHEN c_SrcText%NOTFOUND;
    v_Hash := v_Hash + dbms_utility.get_hash_value(v_SourceText, 37, 1073741824);
  END LOOP;   CLOSE c_SrcText;

  dbms_output.put_line(rpad(srcname_rec.owner,15)||' '||

		       rpad(srcname_rec.name,30)||' '||
		       rpad(srcname_rec.type,15)||' '||v_Hash);

 END LOOP;
END;
/

For Triggers :-

---

set serverout on size 1000000

declare

v_Hash INTEGER := 0;

begin

FOR srcname_rec IN (select owner,trigger_name, trigger_body

		    from dba_triggers
		    where owner not in ('SYS','SYSTEM'))

  LOOP
   v_Hash := dbms_utility.get_hash_value(srcname_rec.trigger_body, 37, 1073741824);

   dbms_output.put_line(rpad(srcname_rec.owner,15)||' '||

			rpad(srcname_rec.trigger_name,30)||
                        ' TRIGGER         '||v_Hash);

 END LOOP; end;
/

For Views :-

---

set serverout on size 1000000

declare

v_Hash INTEGER := 0;

begin

FOR srcname_rec IN (select owner,view_name,text from dba_views

                        where owner not in ('SYS','SYSTEM'))   LOOP
   v_Hash := dbms_utility.get_hash_value(srcname_rec.text, 37, 1073741824);

   dbms_output.put_line(rpad(srcname_rec.owner,15)||' '||

			rpad(srcname_rec.view_name,30)||
                        ' VIEW            '||v_Hash);

  END LOOP; end;
/

The procs and triggers snippets seem to work quite well. The views snippet will fall over if a view is >32767 bytes long. I can fix that using DBMS_SQL.COLUMN_VALUE_LONG but is there an easier/better way to do all this?

Regards,
Jason. Received on Mon Aug 09 1999 - 06:00:53 CDT