0

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?

Kinga
  • 9
  • 4
  • 2
    Files and Got... Versions of files and diffs are automatic. https://www.thatjeffsmith.com/archive/2021/04/ci-cd-with-oracle-database-and-apex/ – thatjeffsmith Apr 28 '21 at 09:08
  • Sigh, Git, not got. – thatjeffsmith Apr 28 '21 at 10:08
  • What happens if pack_a_1.0.1 references table versions that are no longer valid, e.g new NOT NULL columns, new triggers etc? You cannot guarantee that an old version will still work with the latest schema. So, the key question is would running the old version require the database structure to be the same as it was when the old package version was last (successfully) run? – TenG Apr 28 '21 at 10:32
  • Thanks @thatjeffsmith for the recommendation. We are using git but this schema is kept on a secure server which don't allow hooking it up automatically which means you need to manually copy the package. Will try to see how could we connect them but most likely our security guy won't allow that because the schema contains very sensitive information. – Kinga Apr 28 '21 at 15:30
  • @TenG that's a very good question and one that indeed causes a lot of concerns with the decision for me. Package state is only one of the issue when you want to go back potentially years and you don't want to freeze your relevant objects at any time hence looking for alternative options. – Kinga Apr 28 '21 at 15:31

2 Answers2

2

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

Aman Singh Rajpoot
  • 1,451
  • 6
  • 26
  • I didn't know about this so thanks. It seems this is intended for upgrading without downtime, and for a quick means of backing out the changes if any issues surface. How does this handle table definition changes, i.e. if a new column is added to a table for an upgrade, would switching back to a previous edition revert that table change too? – TenG Apr 28 '21 at 10:36
  • EBR is a big topic, have a read on this topic and I am sure you will get the idea behind that. " It seems this is intended for upgrading without downtime" It does not just reduce downtime, you can create editions of your objects like versions and handle them very easily. "How does this handle table definition changes" you can not have editions of tables it is simply because tables have data and it is not feasible to make copies of it. But you can create editable views which will allow you to make changes to your table. – Aman Singh Rajpoot Apr 28 '21 at 10:49
  • This is good blog to understand editioning view and overall EBR http://db-oriented.com/2018/05/21/ebr-part-7-editioning-views/ – Aman Singh Rajpoot Apr 28 '21 at 10:55
  • That is a great idea @AmanSinghRajpoot, I will read up on the links. Much appreciated, might be exactly what we need! – Kinga Apr 28 '21 at 15:32
0

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.

justadeveloper
  • 203
  • 3
  • 12