Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Hashing Packages, procedures, views and triggers

Re: Hashing Packages, procedures, views and triggers

From: Jason Salter <jason_at_seahorse.demon.co.uk>
Date: Tue, 10 Aug 1999 12:39:00 GMT
Message-ID: <37b31d31.15425134@news.demon.co.uk>


On Mon, 09 Aug 1999 22:03:25 +0800, Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:

>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'
>)
>)

Conner,

Thanks for that! Unfortunately the packages live inside different DB's on differents servers on isolated networks! That's why I needed something I could run against both DB's and paste the 'checksums' into a spreadsheet for comparison.

But I'll keep your code for next time :)

Regards,
Jason. Received on Tue Aug 10 1999 - 07:39:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US