How to Compare PL/SQL and SQL in Oracle SQL Developer

thatjeffsmith SQL Developer 32 Comments

Tell Others About This Story:

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.

I don't always right-click, but when I...just kidding, I ALWAYS right click.

I don’t always right-click, but when I…just kidding, I ALWAYS right click.

Pick your file and, ta-da!

Line by line compare of your code with differences marked in the gutter for easy ID for large files

Line by line compare of your code with differences marked in the gutter for easy ID for large files

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.

Did you know this was there?

Did you know this was there?

Then voila.

I've created 3 document tab groups so I can see the editor for each file and the compare results all at once!

I’ve created 3 document tab groups so I can see the editor for each file and the compare results all at once!

We can do more than just compare the SQL code of course. You know we can also compare Explain Plans and Auto Trace results?

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 32

  1. 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.

    1. thatjeffsmith Post
      Author
  2. 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.

    1. thatjeffsmith Post
      Author
      1. 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.

        1. 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.

  3. 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

    1. thatjeffsmith Post
      Author

      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

  4. 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

    1. thatjeffsmith Post
      Author

      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.

      1. 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

    1. 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 🙂

      1. thatjeffsmith Post
        Author
        1. Don’t exactly know. Perhaps some libraries or services needed to reload or, may be, purging ~/.sqldeveloper directory did the trick…

  5. 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?

    1. thatjeffsmith Post
      Author
      1. 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).

    1. thatjeffsmith Post
      Author
  6. 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.

    1. thatjeffsmith Post
      Author
      1. Yes, I’m trying on functions, packages and procedures. I tried from both the connections panel and the schema browser.

        1. thatjeffsmith Post
          Author
      2. 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.

        1. thatjeffsmith Post
          Author
        2. thatjeffsmith Post
          Author
          1. 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.

          2. thatjeffsmith Post
            Author

Leave a Reply

Your email address will not be published. Required fields are marked *