diff local package with database package

Question

I need somehow compare local version of pl sql package with the one that is stored in database. Are there any "easy" way to do it? Currently I download package from database, save it in some file and perform diff using some diff tool. That is a bit cumbersome, so I would like to have such feature in ide (pl sql developer is preferable).

Answer 1

It's fairly easy to automate the procedure you described with a sqlplus command file. Select from all_source, spool it to a file. Invoke the diff command as "host diff ..." or "!diff ..." within the command file. The -b and -B options to diff will ignore whitespace and blank lines respectively.

Answer 2

Typically developers use versioning software to check differences, maintain version history, and help coordinate team development of code. PL/SQL code should be no different. There's many ways to handle code releases, the following is just what I've seen done, not necessarily the "best" way.

In the Oracle environments I've seen, CVS or SVN is used. Most approaches involve checking out latest code from repository, editing (tagging/branching), and checking in. When code is tested (development instance) and release is ready, the DBAs either grab the release scripts from repository and apply, or one individual is tasked with handing over the correct release scripts to DBAs (more common from my experience). Note that the database here is typically a user acceptance instance that mirrors the production instance. If app testing passes, the code is promoted to production.

If you want to sync directly between the database and your IDE, the one option I've seen is Toad Team Coding. Toad is not free, and this option will require additional objects installed on the database (metadata/tracking tables, etc). A good overview is found here, and good setup article is found here.

Team coding is very cool, but I would only install on a development environment. How you promote the code from there through your system is up to you.