Re: Hashing Packages, procedures, views and triggers

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 09 Aug 1999 22:03:25 +0800
Message-ID: <37AEDFAD.72C3@yahoo.com>

Jason Salter wrote:
>
> 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.

If you're just looking for a "yes they're the same, no they're different" response, why not just use 'minus'

select * from
 ( ( select line, text

     from   user_source
     where  name = 'PROC1'
     and    type = 'PROCEDURE'
     minus
     select line, text
     from   user_source
     where  name = 'PROC2'
     and    type = 'PROCEDURE'

   )
   union all
 ( select line, text

     from   user_source
     where  name = 'PROC2'
     and    type = 'PROCEDURE'
     minus
     select line, text
     from   user_source
     where  name = 'PROC1'
     and    type = 'PROCEDURE'

)
)
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Mon Aug 09 1999 - 09:03:25 CDT