Start to Version your PL/SQL Application with Git

Many times I participated in different Oracle database development teams there was no clear concept of how the source code should be versioned. There were many dubious practices, e.g. sharing code on file systems level, via e-mail or even directly developing and compiling in the database.

These worse practices has persuaded me to establish some approaches how you can make development in PL/SQL context more reliable.

My Creed

Maybe it seems excessive but during development there is a creed in which I believe before, during and after work time :). These very simple rules ensures that you stay revision safe in your code no matter what happens:

Some brief explanations:

Master = Production

No matter what are your doing  just ensure that your master branch always looks like the application state in your production. It is a very good feeling to be able to check on source level what is the difference between your ongoing development and your production.

In some projects, it depends on how fail-safe your team is, it could help when only a single teammate is able to merge commits back into the master.

Only File-Based Dev

As mentioned on the beginning make sure that development happens always on a corresponding script file to the object which shall be altered. Direct development (compiling) is strictly prohibited!

Some developers are really (really, really) lazy! How often I had to hear that a change can be done so easily directly in the database. My advice: Do not take account of these statements!

Development must happen revision safe. You mast always be able to say why and when someone changed a source. So do your homework properly.

The Truth is in Git

Needless to say: each source which has something to do with the software you are developing will be stored in Git or another version control system.

Avoid any code which has not been placed in Git! Or in other words: what does not exists in Git is non existing for your!

Get your Sources from DB

My above assumptions imply that you have your sources on file system in order to comply with Only File-Based Dev and The Truth is in Git. And this is what we are going to do now.

Scheme2ddl ( qwazer 2018) is a very nice Java application which helps you to get all your sources from your database. It checks which different objects (packages, table definitions, views, procedures, etc.) are stored in your database and extracts these sources as files on our file system.

After extracting these sources you can init a Git repository and bring your development on a higher level.

Configuration (XML)

The Scheme2ddl extractor can be configured. It can load a configuration XML in which you can provide some settings that will be applied during file generation.

I will not show the entire XML structure but only focus on some interesting sections/attributes:

Connection

Set your login details. You can even pass the connection and login information as arguments.

Target Schema

Set which schema you want to extract. The user must have access to these schema! Or you can login a sysdba.

Output Mapping

The source extractor allows you to customize how your files shall look like. I would not to customize to many here. Just use the value toad, lean back and enjoy. With this settings you will get for PL/SQL packages *.pkg (body) and *.pks (specification) extended files. Other types like views and triggers will also get their own extensions.

Run the Extraction

Find here attached my entire scheme2ddl.config.xml configuration. Needless to say that you need a Java runtime in order to run the extraction. Than start the extraction with the following command and check the result:

The output of the extractor:

As you can see the extractor has also put your database objects in sub directories in the output folder. Now you can start to check-in your sources in Git and start to develop (more sophisticatedly)!

qwazer, 2018, ‘Command line util for export oracle schema to set of ddl scripts’, GitHub, accessed February 18, 2018, from <https://github.com/qwazer/scheme2ddl>.

4 Gedanken zu “Start to Version your PL/SQL Application with Git

    1. So for the long lag but had a lot stuff to do.

      I really appreciate your effort in developing the schema2dll. It helps me a lot to get things into version control. Its really embarrassing how many people thinks good development can happen without version control.

      Thanks and best regards,
      Adam L.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.