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 you are doing, ensure that your master branch always reflects the application state in production. It is a very good feeling to be able to check at source level what differs between ongoing development and 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 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 an XML configuration file in which you can provide settings to apply 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.

<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">
 <property name="URL" value="jdbc:oracle:thin:@192.168.0.33:1521:xe" />
 <property name="user" value="hr" />
 <property name="password" value="hr" />
 <property name="connectionCachingEnabled" value="true" />
</bean>

Target Schema

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

<util:list id="schemaList">
 <value>HR</value>
</util:list>

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.

<property name="extensionMap" ref="toad" />

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:

java -jar scheme2ddl.jar --config scheme2ddl.config.xml

The output of the extractor:

Start getting of user object list in schema HR for processing

Found 34 items for processing in schema HR
Saved sequence hr.departments_seq to file /home/adam/bin/output/sequences/departments_seq.sql
Saved procedure hr.add_job_history to file /home/adam/bin/output/procedures/add_job_history.prc
Saved sequence hr.employees_seq to file /home/adam/bin/output/sequences/employees_seq.sql
Saved table hr.departments to file /home/adam/bin/output/tables/departments.sql
Saved table hr.employees to file /home/adam/bin/output/tables/employees.sql
Saved view hr.emp_details_view to file /home/adam/bin/output/views/emp_details_view.vw
Saved table hr.jobs to file /home/adam/bin/output/tables/jobs.sql
Saved sequence hr.locations_seq to file /home/adam/bin/output/sequences/locations_seq.sql
Saved table hr.job_history to file /home/adam/bin/output/tables/job_history.sql
Saved table hr.regions to file /home/adam/bin/output/tables/regions.sql
Saved procedure hr.secure_dml to file /home/adam/bin/output/procedures/secure_dml.prc
Saved table hr.locations to file /home/adam/bin/output/tables/locations.sql
Saved trigger hr.update_job_history to file /home/adam/bin/output/triggers/update_job_history.trg
Saved trigger hr.secure_employees to file /home/adam/bin/output/triggers/secure_employees.trg
Saved table hr.countries to file /home/adam/bin/output/tables/countries.sql

-------------------------------------------------------
   R E P O R T     S K I P P E D     O B J E C T S
-------------------------------------------------------
| skip rule |  object type              |    count    |
-------------------------------------------------------
|  config   |  INDEX                    |      19     |

Written 15 ddls with user objects from total 34 in schema HR
Skip processing 19 user objects from total 34 in schema HR
scheme2ddl of schema HR completed in 8 seconds
Processing schema  [HR] completed

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)!