Hi : I need to compare all the PL/SQL source code (ie, Procedures, Functions, packages, and Triggers) between two databases (let´s say, Homo and Prod) to get the diffs.
Point is, I want to get only functional diffs, ie, only meaningful source code diffs, discarding differences caused by linefeeds, upper/lowercase, comments, and alike . So , both source code extracts belowe must be considered non-different :
=> source code in PROD database
....
-- Query entered in PROD @ 2017/12/31
select cola,
colB, colC FROM mytable
where colA=2;
==> source code in HOMO database
....
-- Query homologated in
-- HOMO @ 2017/12/10
--
select cola, colB, colC FROM mytable where COLA=2;
/*
yet
another
comment...
*/
I was thinking about comment/linefeeds/whitespace removal via string functions, but I was unable to cook something ... And just to add, some source code will be small BUT some could be very large...
Regards,
J. Laurindo Chiappa
You can cleanse the data with some PLSQL. After that you could spool it to file and use normal compare tools (diff etc), or you could store it as a clob and use dbms_lob, etc. Here is something to get you going
SQL> create or replace
2 procedure scott.my_proc(
3 p_string in varchar2 default null,
4 tag in varchar2 default null,
5 p_debug in varchar2 default 'NO',
6 p_trace in varchar2 default 'NO',
7 p_session in varchar2 default null)
8 is
9 b int := dbms_utility.get_time;
10 l_question_id int;
11 begin
12 /*
13 some multiline comments
14 that end in the middle */ null;
15 if tag is null then
16 wwv_flow.show (
17 p_flow_id => '100',
18 p_flow_step_id => '1',
19 p_debug => p_debug, -- some stuff
20 p_arg_names => wwv_flow_utilities.string_to_table2('P1_SEARCH_CRITERIA'),
21 p_arg_values => wwv_flow_utilities.string_to_table2(p_string),
22 p_instance => p_session,
23 -- some more stuff
24 p_trace => p_trace
25 );
26 htp.p('<font size=-2>'||to_char((dbms_utility.get_time - b) * .01,'999,990.000')||'</font>');
27 end if;
28 /* last little bit */
29 end;
30 /
Procedure created.
SQL>
SQL>
SQL> variable c clob
SQL> declare
2 l_in_comment boolean default FALSE;
3 l_comment_pos int;
4 l_text varchar2(4000);
5 begin
6 dbms_lob.createtemporary(:c,true);
7 for i in ( select text
8 from dba_source
9 where owner = 'SCOTT'
10 and name = 'MY_PROC'
11 and type = 'PROCEDURE'
12 order by line
13 )
14 loop
15 -- santise case
16 l_text := lower(i.text);
17
18 -- repeated spaces becomes single space
19 while l_text like '% %'
20 loop
21 l_text := replace(l_text,' ',' ');
22 end loop;
23
24 -- single line comments removed
25 l_comment_pos := instr(l_text,'--');
26 if l_comment_pos > 1 then
27 l_text := substr(l_text,1,l_comment_pos-1);
28 elsif l_comment_pos > 0 then
29 l_text := null;
30 end if;
31
32
33 -- multi line comments
34 l_comment_pos := instr(l_text,'/*');
35 if l_comment_pos > 0 then
36 l_in_comment := true;
37 if l_comment_pos > 1 then
38 l_text := substr(l_text,1,l_comment_pos-1);
39 else
40 l_text := null;
41 end if;
42 end if;
43
44 l_comment_pos := instr(l_text,'*/');
45 if l_in_comment then
46 if l_comment_pos > 0 then
47 l_text := substr(l_text,l_comment_pos+3);
48 l_in_comment := false;
49 else
50 l_text := null;
51 end if;
52 end if;
53
54 -- l_text := replace(replace(l_text,chr(10)),chr(13));
55 if l_text is not null then
56 dbms_lob.writeappend(:c,length(l_text),l_text);
57 end if;
58 end loop;
59 end;
60 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> print c
C
--------------------------------------------------------------------------------
procedure my_proc(
p_string in varchar2 default null,
tag in varchar2 default null,
p_debug in varchar2 default 'no',
p_trace in varchar2 default 'no',
p_session in varchar2 default null)
is
b int := dbms_utility.get_time;
l_question_id int;
begin
null;
if tag is null then
wwv_flow.show (
p_flow_id => '100',
p_flow_step_id => '1',
p_debug => p_debug, p_arg_names => wwv_flow_utilities.string_to_table2('p1_sea
rch_criteria'),
p_arg_values => wwv_flow_utilities.string_to_table2(p_string),
p_instance => p_session,
p_trace => p_trace
);
htp.p('<font size=-2>'||to_char((dbms_utility.get_time - b) * .01,'999,990.000'
)||'</font>');
end if;
SQL>
If you comment back in line 54, you'll remove the carriage returns as well