Oracle and GitLab

Admin 1
Viewed 1649

I am looking for a tool, script or method to transfer code - once its development process is done - from the Git Remote Repository back to my Oracle database. Currently, I only know a way of writing a yaml-file that lists every single file in the repository that I would like to push back into the database.

My actual code looks like this at the moment (database connection names are examples):

stages: - build

build: stage: build

  script: 
    - "sqlplus abc/cde@//OracleDevelop@abc_packages/WAREHOUSE1.plsql"
    - "sqlplus abc/cde@//OracleDevelop@abc_functions/WAREHOUSE1_NG.plsql"
    - "sqlplus abc/cde@//OracleDevelop@abc_procedures/COPY_PACKAGE.plsql"
    - "sqlplus abc/cde@//OracleDevelop@abc_types/NUMLIST.plsql"

Now, I don't want to list every single file that needs to get back into the database. I want some mechanism to automate it. The best case would be a GUI, so that the developer could run a script in which he could via drag and drop decide on which files he wants to give back into the database.

There obviously is a way of buying commercial version control systems that already have these things implemented but my company wants to develop a GUI based on shell scripts themselves...

Is there anyone who probably has a functioning script or interface or knows how to make this simpler?

gitlab jenkins deployment continuous-integration oracle
1 Answers

I asked this question several months ago, when I was in a position to introduce versioning with Git into a database development company that didn't have versioning 'till this point. Due to the fact that I have since solved the problem, I thought I'd share my steps for people having the same issues...

At the beginning, I had no idea of how to find a way to create a fluent development circle between an Oracle database, different editors (SQL Developer, VS Code, etc.) and GitLab.

The ultimate purpose was to make it as easy as possible for developers to version, document and store the data during their development process. Therefore, the code needs to be extracted/exported from the database, be brought to GitLab, be compiled and built and then be brought back to (different) databases, e.g. a dev and a prod database.

Basically, my steps were

  1. Extract the files from the database using a filedump function/ export function in database browsers (e.g. SQL developer) or in my case, writing a specific python script for this task. I replaced all critical information with xyz or example...

Python script to export specific data from Oracle database

  1. Create a Remote Repository in GitLab and upload all the files (keeping the database hierarchy) to the GitLab repo.
  2. Clone the Repository and create a local repo.
  3. Write a yaml file that brings files back into the Oracle database and is triggered whenever a new commit is pushed into the repo.

The most important line in the yaml file is the command to built the code that has been pushed in the last 5 minutes and bring it back into the database.

-"git diff --stat ('git diff --stat @{5.minutes.ago} --name-only') do sqlplus xyz"

xyz needs to be replaced by your Oracle database credentials, e.g. SCHEMA/password@//instance/name

  1. Setup a Jenkins server that takes certain files from the repository and brings them into a different database. This is fairly simple, everything in Jenkins has a GUI.

As you can see, we use yaml and Jenkins to address two different databases. This is intentional as we have both a development database and a production database. Therefore, we wanted to be able to address them individually. The yaml script is supposed to trigger whenever a commit is pushed and to immediatly update the development database. Later, when a file is seen as done (needs no more development), a senior engineer can go ahead and use Jenkins to transfer this file into the production database.

Admin1
Related