Blog: Automating Oracle Forms and Reports conversion with a DevOps process

The DB Best Oracle Forms and Reports Converter automates the conversion of your forms to a modern Web 2.0 architecture. We use the Oracle provided FormModules, ObjectLibraries, or MenuModules to XML command-line tool called Forms2XML along with the corresponding Oracle Reports convert to XML tools to start the process. Our converter then processes the source XML files to JavaScript files and SQL code fragments. In this blog post, we will show our best practices for incorporating our Oracle Forms and Reports Converter into a Continous Integration (CI) / Continous Deployment (CD) DevOps process to shorten developer lead time and reduce change failure rates.

CI-CD solution out of the box

Using different environments

First, we started by creating three separate environments: Dev, Test, and Autotest.

Each environment includes a copy of the source database with original forms and reports. This is basically a copy of the customer’s infrastructure and we use it as a reference when comparing the converted forms and original forms.

For scenarios where we migrate the Oracle database to another platform like SQL Server, we also deploy the converted database to each environment. Otherwise, this is the original Oracle database that includes the code from the source Oracle Forms in the shape of stored procedures.

Multiple environments

All these environments include a copy of converted forms and SQL Server Reporting Services (SSRS) reports, including UI files and Web API. Finally, we utilize one CI/CD system to manage all these environments. Usually, we opt for Jenkins as the orchestration tool.

We can implement this infrastructure in the cloud or in the on-premises data center. The first option is rather expensive. So, we usually leverage the second one for the conversion phase. The trick is that you can easily switch to the cloud after releasing the converted forms and reports to production. The changes to the established DevOps scripts will be minimal.

Building the CI/CD process

We then automated the following process:

1. Revert the databases used for testing to initial state.

In this step, we roll back the database data to the golden copy, which we received from the customer. We also remove any locks by our QA engineers. One of the best practices for this step is creating a restore point. For huge databases, we can then use a Jenkins job to revert changes from a previously created restore point instead of a full revert.

/* The following part of code from Jenkinsfile that allows to revert changes from a restore point for the database.
1) node-for-tests - the machine where we will execute restore changes from a restore point;
2) git-domain-name - the domain name of git or gitlab;
3) dbbestdevops/devopsformsreports.git - the git repository, where we store Jenkinsfiles and some configs needed for CI/CD;
4) DB_RESTORE_TO_RESTOREPOINT\\flashbackToProcedures - the folder in this git repository, where we have settings for this Jenkins -job.*/


stage('Flashback procedures on TEST environment') {
           when {
                expression { ACTION == 'TEST' }
            }  
            agent {
                label 'node-for-tests'
            }
            stages {
                stage('Cleaning WS on TEST environment') {
                    steps {
                        echo '> Cleaning WS ...'
                        timeout(time: 3, unit: 'MINUTES') {
                            retry(5) {
                                cleanWs()
                            }
                        }
                        bat("dir")
                    }
                }
                stage('Checkout devopsformsreports') {
                    steps {
                        echo '> Checking out the source control ...'
                        dir ('devopsformsreports') {
                            timeout(time: 60, unit: 'MINUTES') {
                                git branch: "master", url: '[email protected]:dbbestdevops/devopsformsreports.git', credentialsId: 'GITLAB_GET_SSH'
                            }
                        }
                    }
                }
                stage('Flashback procedures on TEST database') {
                    steps {
                        bat(
                            script:'''
                                sqlplus %DB_CREDENTIALS_SYS_USR%/%DB_CREDENTIALS_SYS_PSW% as sysdba "@%WORKSPACE%\\devopsformsreports\\DB_RESTORE_TO_RESTOREPOINT\\flashbackToProcedures"
                            '''

                        )
                    }
                }
            }
        }

The settings for this job in our Git repository look as follows:

/*
The content of flashbackToProcedures  (the settings folder in our git repository)
name-of-restorepoint is the name of restore point, from which we want to make a flashback
*/


spool flashbackToProcedures.log;
select sysdate from dual;
shutdown immediate;
startup mount;
flashback database to restore point name-of-restorepoint;
alter database open resetlogs;
spool off;
exit

2. Convert forms and reports and push the converted code to the repository.

Now, we can take advantage of the command-line interface to launch the DB Best Oracle Forms and Reports Converter. Our converter uses the XML files of the source forms and reports as an input. It extracts the SQL code and converts it if needed (when we migrate from one database platform to another). Also, the Oracle Forms and Reports Converter converts forms, libraries, menus, and reports from XML to .js files.

Oracle Forms Reports Conversion

Every time we convert all forms and reports, even if some of them have already passed the testing phase successfully. That is because we constantly update the back end of our converter and we need to follow the QA process every time. This means that if we discover an issue with one of the already approved forms or reports, the latest changes done with the converter app should be reverted.

We also take care of the code versions and store all changes in the Git repository.

3. Create builds of Web API and UI and deploy them to IIS sites.

Converted code of the source Oracle Forms and Reports will not work as-is. We still need to create builds from it.

So, we use the output of our Oracle Forms and Reports Converter as the source for this build process. We take the converted .js files to build the application back end based on React. We also build the Web API based on .NET. Our web application uses this Web API to interact with the database.

We utilize Jenkins to orchestrate the execution of these operations. Then we run another Jenkins job to push these builds to all 3 environments.

Oracle Forms Reports Deployment

4. Add converted SQL code fragments to the database.

Oracle Forms interact with an Oracle Database using the embedded SQL code. You can’t execute the SQL code from the web, so, we need to add the extracted SQL code to the database in the shape of stored procedures. Our established DevOps process uses the output of the Oracle Forms and Reports Converter to upload the SQL code fragments to the database.

We store all connection credentials using environment variables. So, this process is easily customizable as our Jenkins jobs don’t recognize anything about the environment where the changes are applied.

Once again, we create a restore point after applying changes to the database.

5. Start testing.

We use the test environment for manual tests. Our QA engineers run all the necessary tests after converting new forms or reports. These tests are recorded, and we create automated tests based on this input. Then we run these tests in the Autotest environment nightly. And we get an explicit report with conversion issues — if any – the next day.

Automated delivery

Adding a new Oracle Form or Report to the source repository is the trigger to start the automated conversion process. However, we can start it manually after making changes to the Oracle Forms and Reports Converter application. And, we make these changes every time we need to address any of the detected conversion issues. As a result, we constantly improve the converter, tending the conversion rate to 100 percent.

Because we overwrite all previously converted forms every time, the process implies we can’t make any manual changes to the code of converted forms and reports. This means that at the end of the project all source Oracle Forms and Reports are converted automatically with a push of a button. If any issues occur during testing, we make changes to the back end of the Oracle Forms and Reports Converter application.

After the successful conversion of all forms and reports, we execute another Jenkins job to deliver the results to the customer. This fully automated process allows for the seamless and precise conversion of any number of Oracle Forms.

Start building your DevOps process!

This is how the DB Best team established the DevOps process for a complex automated system from scratch. This process helps us fully automate the conversion and testing processes, as well as the deployment of web applications.

This is just one of many successful DevOps stories accomplished by the DB Best team. We can optimize and automate any non-standard process to build a DevOps pipeline for you. You can rely on our expertise in DevOps practices to integrate the DevOps practices in your infrastructure. Contact us to get started today with development and DevOps services for enterprise-grade systems!