This question has come up a few times recently. One person asked how to compare 2 SQL statements at Open World. In fact, they claimed if I could show them how to do it, it would make the trip to San Francisco worth every penny. Don’t be surprised they left extremely satisfied 🙂
Then just yesterday, someone else asked how to see the differences between their code objects in the database and their file system.
Thankfully it’s pretty straightforward for both scenarios and I’ll take a second now to demonstrate.
Comparing DB Object to File
Of course it always starts with a click. Find your PL/SQL object in the navigation tree and right click.
Pick your file and, ta-da!
Comparing Two SQL Statements
If you have your SQL statement(s) in a file, then you’re good to go.
Load up file 1.
Use the File menu to start the compare.
Then voila.
We can do more than just compare the SQL code of course. You know we can also compare Explain Plans and Auto Trace results?
43 Comments
That’s nice, but I believe we need something extra, like comparing 2 different result set from 2 (or more) different instances/connections. For example query patches applied from 2 tables in instance 1, and comparing the results/differences with same 2 tables in instance 2, 3, 4….
You want to compare query results?
yes, but patch results from different connections.
what are ‘patch results’ ?
Comparing data is a HUGE feature. It’s not tackled lightly. It’s not on our road map, but that could change.
Have you used/seen DBMS_COMPARISON? Most folks don’t even know about it.
I do believe all the ideas you have introduced for your post.
They are very convincing and will certainly work. Nonetheless,
the posts are very short for novices. May just you please
lengthen them a little from next time? Thanks for the post.
In Sql Developer 17.2 the File / Compare With / Other File is still always grayed out. Please repair.
The workaround using View / Files does work however.
Yeah, we’ve got that logged. Thanks for the report though.
My company has shut off our access to SQL Navigator and we are now forced to user Oracle SQL Developer and my coworkers along with myself are severely hampered by the fact we cannot perform a simple code move from one compare file to the other compared file
Is there a way to move a line(s) of code from one of the compare files to the other compare file?
Thank you
Did you look at the Generate Patch feature?
Hi Jeff,
Is there any option to Ignore case while comparing as its showing many differences with case changes which i don’t want.
Thanks,
Krishna.
upper or lower everything before you compare is the only way I know
Why are my options under Compare with (File on Disk, Other File, Each Other) always grayed out – meaning I cannot due a compare? I’m under the impression to compare PL/SQL I should be using the Compare With command under the File menu but it never works. Any suggestions on how to properly get a compare done would be great. thank you.
Make sure you’re on a code editor or SQL Worksheet when you access the menu item.
Just had the same issue while viewing a package body in SQLDev 4.2; couldn’t compare it to a File on Disk.
Just modifying it a little bit (adding a whitespace) made the option available.
IMHO it shouldn’t be necessary for me to modify the text of the package to be able to compare it to a file on disk.
Also, when I finally select “File on Disk”, what I get is an “Items are identical” modal window message.
That’s with 4.2.0.16.260, build 16.260.1303
If I can help further with this just let me by answering.
I just recently updated to 4.2, and appear to have had some of the Compare functionality stop working. I can no longer compare from a file/editor window to “Other file”. I have used this functionality many times prior to my update, so I am certain of how I was using it before (Open stored procedure or regular file into an editor, go to “File->Compare With->Other file…). I even tried completely removing all SQL Developer installation and preference information and installing a new copy, Compare “Other file” still is not enabled.
Version 4.2.0.17.089
Build 17.089.1709
It’s a bug.
Workaround, find the file in the view > Files panel. right click, compare with other file
Hi. I wanto to compare 2 packages.
I chose compare with other file (other options are disabled). It show me a strange path “C:\Users\sa0067\AppData\Roaming\SQL Developer\system4.0.0.13.80\o.sqldeveloper.12.2.0.13.80\projects”, i don’t know what to choose. It shows me list of the connection, not the list of the package. If i click ‘OK’ he says me “directory not accessible, choose another directory”. Can you help me? Thank you in advance. Andrea
it’s looking for a file, not for a database object – that’s why you’re on the File menu
do you not use source control for your PL/SQL?
you can also use the Tools > DB Diff wizard, but that’s a lot of clicks to just do a simple text compare
Tools > DB Diff wizard it’ ok, thank you.
Jeff,
I have to say that I started using SQL Developer 4 full time just because of this feature that I missed from PL/SQL Developer!
Anyway there is still a minor issue in SQLDev, that is it’s not possible to start the diff functionality by right clicking on a package body in the results of the Find DB Objects window.
Do you think it’s possible to add it also there? It would make my work way easier!
Thanks a lot,
Paolo
Anything’s possible – and I personally like this idea.
But to get it on ‘the list’ – we need to make sure it’s something that enough people would use to justify the development time. I would post this suggestion to our Exchange, and encourage others to vote it up.
Got it,
anyway I just found out a quick workaround, that is I can click on the search results to open the object and then I can right click on the new plsql editor window and select the Compare with… option.
That will do fine for now.
Thanks again,
Paolo
Very Useful
I have upgraded (fresh installed) my system yesterday.
I had some issues starting sqldeveloper itself, so I have been tinkering with nearly everything. By some “miracle” it’s working again 🙂
>>I had some issues starting sqldeveloper itself
Sorry to hear that – what did you run into?
Don’t exactly know. Perhaps some libraries or services needed to reload or, may be, purging ~/.sqldeveloper directory did the trick…
I’ve been using this nice feature for a long time, but since v. 4EA2 it doesn’t work any longer; on any stored object or .sql file either.
Do I need to enable/disable some setting?
Nope, nothing to enable/disable – what happens when you go to do the compare?
Wow, thanks for reply. The situation:
When I right-click object’s (or pl/sql editor’s) context menu or File > Compare with… menuitems are inactive (and, of course, not clickable).
Had no idea the compare was there! But then I mostly compare data models…
Why not comparing worksheet to worksheet it would be great to have such option too.
Kind Regards
Tomasz
I agree and it’s on the list for version 4.NEXT
Thanks Jeff, it always amazes me the stuff I don’t know that is in SQLD! This is very useful – however (there is always a however, right!) when I right click on a schema object and select Compare – all the options are grayed out. If I have an open file and use the File menu, I can select another file to compare to but can’t from a schema object. Am I missing a step somewhere? I’m on 4EA2 on a mac.
What type of schema object? This will only work on PL/SQL type containers like Packages, procedures, etc.
Yes, I’m trying on functions, packages and procedures. I tried from both the connections panel and the schema browser.
No idea, and I just tried it from version 3.2 as well.
You could try running sqldev with an open cmd window and see if any exceptions get raised when you do the right click.
Looks like EA3 fixed it!
When I right-click on an object in the Navigator (say a package) I only ever get the option to compare with “Other File…”. However, I’d like to compare two package bodies (or specs) in the same, or even different, schemas.
How can I do this in SQL Developer? I have tried highlighting both objects I want to compare but I still get just the option to compare with “Other File…”. There is an “Each Other” option but it is always greyed-out.
do you source control your pl/sql? if so, it’s easy, just point to the file where you keep your package body.
To use the ‘compare with each other’ feature, you need to select 2 items in the View > Files panel.
Hi Jeff, Trying to compare 2 packages from different schemas but can never get the File Compare with option to work. After selecting the file compare, the options (file on disk, other file, each other) are always grayed out. I’m thinking I need to use the “each other” option but part of my problem I think is I can not select both files before selecting the compare. I toggle between the two files trying to select them but only the last one selecting stays highlighted. Is there an obvious problem or does my needed not match what is showing above and requires more instructions for comparing. Main goal is to compare same package that is in different schemas – like test and prod.
Open the files dialog under the View menu, select both files, then check the compare with each other menu operation.