Compare User Objects - should ignore whitespace differences

[Bernhard S]

Hello,

I have a package, in one schema created on Windows client, on the other created from Unix Server. The create file has been moved via ftp in ascii mode. So the difference is just CR+LF from Windows Client and just LF from Unix Server. In PL/SQL Developer - Compare User Objects it's shown as different, when you look at the details with the built-in ExamDiff it shows "no differences found". It should therefore not show up in the list of differences either.

Regards,
Bernhard

 

[Marco Kalter]

I have added this to the list of enhancement requests.

 

[andyh]

I'd also like the ability to have the comparison ignore any spurious formatting differences e.g. someone has used different beautifier rules in each case. Perhaps both sources could be reformatted to a 'common' style before comparison?

 

[rider]

IMHO, following lines should also be treated as equal:
create or replace procedure "PROC1" as...
create or replace procedure PROC1 as...
create or replace procedure OWNER.PROC1 as...
create or replace procedure "OWNER"."PROC1" as...

Of course its true only for very same owner and name - "proc1" and "PROC1" is different

 

[Gustavo]

Sorry, but PROC1 is totally different from OWNER.PROC1. No matter who generated the script and no matter who will be running it, these should be considered as different anyway.

 

[Bernhard S]

Hello,

data pump export/import for some strange reason puts the whole first line before the "is" of packages, procedures, functions, triggers into all uppercase and puts double quotes around their names. i.e.
create or replace package pkg_test is
after data pump export/import becomes:
CREATE OR REPLACE PACKAGE "PKG_TEST" is

Those two should not be shown as different by pl-sql-developer - "Compare User Objects".

Another oddity I found is the following. In a DDL for a package there are lines that contain nothing else than a couple of blanks. When I create that package with pl-sql-developer - "program window", the generated package contains those lines including the blanks. If I do the same in a pl-sql-developer command window or in sql*plus, those blanks are all gone!! That behavior seems to be the case with all stored objects: package, procedure, function, trigger, ...
Stored Objects created with the "program window" should really be stored the same way as created via "command window" or via sql*plus.
When I do a comparison in pl-sql-developer of the same package in one schema generated the first way with the same package in another schema generated the second way, of course it shows that as differences. That should not be shown as difference either.
Here is a small testcase:

drop procedure prc_blank_test;

create or replace procedure prc_blank_test is
--this procedure has the blanks in blank lines when created using pl-sql-developer "Program Window"
--when you use pl-sql-developer "Command window" or sql*plus the blanks in blank lines are removed!
begin

--line before contains three blanks, line after contains two blanks

  null;
end prc_blank_test;
/

set linesize 120
column text_with_blank_as_# format a80
select line, replace(text,' ','#') text_with_blank_as_#
 from dba_source s where name='PRC_BLANK_TEST' order by line;

 

[Bernhard S]

I had an SR opened with Oracle regarding this issue and they found there's an open Bug 8471428 about this behaviour. Just SQL*Plus does that, which certainly is used by the pl-sql-developer command window too. As a workaround the recommended to use Oracle's SQL Developer.
I guess no matter what method is used to create stored pl/sql objects, pl-sql-developer compare should not show any of those differences.
Here's the info from my SR:

The cause is a bug filed for SQL*PLUS 10.1.0.5:
Bug 8471428 BLANK SPACES ARE TRIMMED FROM SQL SCRIPTS WHEN EXECUTED THROUGH SQLPLUS
--> Status: 32,Not a Bug
Development statement was that this is not a bug, this is the way SQL*PLUS works.

Therefore an enhancement request was filed for this:
BUG 8522334 : NEED TO HAVE THE BLANK SPACES IN THE SCRIPTS NOT TO BE TRIMMED OUT
and this is now under Internal (Oracle) Review. The proposal was to add another SET parameter (i.e. TRIM_LINES_IN_SCRIPT) that when set to FALSE
will bypass this behavior.

 

[Bernhard S]

Version 8.0.1.1502

This is still an issue. When I compare objects using PSD "Compare User Objects" utility, I get those objects, created as described above and therefore only differing in tailing whitespace, in the resulting "Differences" tab. When I click on "Show Differences" and I have previously set the options of "ExamDiff" to unchecked "Ignore changes in amount of white space in lines", I get a message window telling me: Files xxx and yyy are identical. Press OK to show the files, or Cancel to quit.
I think PSD "Compare User Objects" utility should have an option to ignore such whitespaces as well in order not to show such pseudo-differences. It could be a setting in options.
It would also be useful in order to cope with this behaviur to have a functionality in the (Program)-editor that removes all those tailing whitespaces in one sweep.

We cannot rely on Oracle changing this behavior anytime soon ( bug 8471428 was last changed in May last year ) and should rather enable PSD to easily work around it.

 

[jking]

+1

I would also love to have the Datapump issue (added quotes to object name) ignored during comparison.

Thanks.

 

[Bernhard S]

What's the status on this issue? Is this going to be dealt with in an upcoming PSD version?
To get trailing blank lines removed from packages/package bodies, that later on would result in the above mentioned pseudo-differences, I now use PSPad editor menu option:
Edit - Lines Manipulation - Remove Redundant Spaces...
In there I check only "From End of Lines" checkbox.

Would be great, sure saving us a lot of trouble and should be easy enough to get this functionality implemented as well into PSD editor.

 

[Marco Kalter]

It's still on the list. My guess is that it will make it into the next version.