ThatJeffSmith

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

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?