Oracle PL/SQL package version control

Question

We are building an automation process which needs to be version controlled so we can rerun it with certain parameters potentially even years later. We have been discussing ways to achieve a level of "version control". We would like to keep the versions in Oracle in some form so you can relatively easily get them running again.

The decision was that we will need to use different names for the different versions like *_1, *_2, etc.

I have a couple of concerns with this. Firstly it requires diligence to always make a new version of the package, no matter how small the change would be, so the number of versions can easily get out of hand. Secondly you have no way of knowing if somebody has touched that version since it has been created and ran against a particular set of parameters. (You can get the last updated dates in Oracle tables and compare to the original run time but that's not particularly efficient and you don't know if it was just deleting a space or actual code change.)

Is there a tried and tested good alternative to this?

Answer 1

You may use EBR (Edition-Based Redefinition). It is likely to solve all your problems.

Edition-based redefinition allows multiple versions of PL/SQL objects, views, and synonyms in a single schema, which makes it possible to perform upgrades of database applications with zero downtime.

• If changes are small you can recompile your objects in the same edition.
• You can create editions and actualize only those objects you want to change.
• Changes will be isolated until you make your new edition available for other users and applications.
• You can change edition using ALTER DATABASE DEFAULT EDITION = edition-name; statement

You can read more about EBR here https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#ADFNS020

https://oracle-base.com/articles/11g/edition-based-redefinition-11gr2

Answer 2

You may want to take a look at Gitora www.gitora.com. It helps managing Oracle PL/SQL packages with Git. Full disclosure: I am the developer.