Data Analytics and AI

Automation: The Answer to PL/SQL Code Analysis, Migration and Validation


Before ETL tools became commonly available in the market, many organizations started developing a business intelligence capability by using PL/SQL, either in whole or in part, to build a data integration solution. Over the years, as the scale of the business has expanded, these organizations found that the PL/SQL based platform was not sufficient or lacked scalability to meet increasingly complex data demands.

Additionally, PL/SQL (procedural language extension to Structured Query Language) is based on 3GL code, which is missing the advanced features of new-age data integration platforms. These advanced features, which are critical to modern data management needs, include:

  • Metadata Management
  • Data Lineage
  • Ease of development, validation, debugging
  • Dealing with disparate source systems
    Many more…

While the limitations of a 3GL code are well known, most organizations do not consider migrating PL/SQL to a modern data integration platform due to a myriad of challenges, including:

  • Analysis of PL/SQL is too complex
  • Timeline is too long
  • Resource intensive
  • Tedious and error-prone
  • Multiple ways to code similar functionality, which is largely programmer driven
  • No structured methodology to implement PL/SQL script
  • Goto labels, Exception handling, etc.

In a nutshell, the cost and time of migration is exceptionally high.

The answer to all these migration challenges is automation. Relying on over eight years of experience in building automated ETL conversion solutions, Bitwise has developed a unique ‘automated PL/SQL conversion’ solution for end-to-end conversion of PL/SQL to any data integration platform. In essence, the solution parses the stored procedures and applies the mapping logic to create the most appropriate ETL transformation.

Let’s take a closer look at how automated PL/SQL conversion works

When converting PL/SQL to a data integration platform, such as Informatica or Talend, Bitwise uses the following steps:

Step 1: Feed PL/SQL script and Database Schema

Step 2: PL/SQL script analyzer parses the script and creates a) Visual Workflow and b) Mapping Document

Step 3: Update the mapping document for optimization as per target ETL tool

Step 4: Feed mapping document to Automated ETL conversion tool

Step 5: Import the converted script in ETL tool and make manual changes (as per non-converted code highlighted in the conversion report)

Step 6: Testing and Validation

Steps for automated PL/SQL conversion

When performing a PL/SQL migration, Bitwise uses a phased approach starting with an Assessment phase to accurately scope the conversion timeline and effort, which is followed by iterations of each phase of conversion.

Phased approach to PL/SQL migration

The level of automated conversion depends on how the PL/SQL code is designed and structured. During the Assessment phase, Bitwise performs a thorough analysis of the code base. The Bitwise team derives the percentage of conversion that can be automated based on the analytics from the Assessment phase. With phased migration, new patterns of conversion are identified at the end of each iteration, which allows the team to recalibrate the tool configuration to increase the level of automation.

The result for the customer is a completely tested code on a new-age ETL tool – within a fixed cost and fixed timeline!

Top 8 reasons to consider an automated PL/SQL conversion service

Organizations running a data integration solution built with PL/SQL have a difficult decision: do nothing and continue running the application as is; or painstakingly migrate the code manually. When compared to these options, it is easy to see the advantage of an automated PL/SQL conversion solution. Here are eight specific benefits to consider:

  • Visual analysis of PL/SQL
  • Automated Mapping Document
  • Flexibility to configure the tool for custom code
  • Detailed conversion report
  • Hassle-free, completely tested target ETL code
  • Improved time-to-market with phased conversion approach
  • Inventory clean up
  • In build, best practice, standardization

To make the PL/SQL migration process even easier, Bitwise partners with leading data integration vendors to offer a seamless automated ETL conversion solution with your platform of choice.

Bitwise partners with leading data integration vendors

Excitement for PL/SQL conversion builds at Informatica World

Earlier this year, we showcased our automated PL/SQL conversion solution to the data community at Informatica World 2017. If you missed us at the event, catch the updates in our Good Bits from Bitwise newsletter article PL/SQL Conversion Excites at Informatica World.


An Automated Approach to Migrating PL/SQL to Informatica

Check out our on-demand webinar An Automated Approach to Migrating PL/SQL to Informatica for a more in-depth look at our PL/SQL conversion process.

Editor's Note: The blog was originally posted on July 2017 and recently updated on December 2023 for accuracy.


Kalpa Sharma

Kalpa, BI Project Manager at Bitwise, leads an innovative automated ETL conversion solutions team. She is responsible for concept assessment, creation of large scale BI roadmaps and managing project deliveries in complex onshore and offshore model with high efficiency and cost effective solutions. With her vast experience in BI projects, Kalpa collaborates with client technical teams to setup the best practices, design innovative solutions and establish guidelines for new Data Integration tool implementation, or migrating or redesigning the existing one.

You Might Also Like


ETL Migration

Data Modernization: eBook Overview for Transforming ETL in the Cloud 
Learn More

ETL Migration

Migrating Legacy ETL to IDMC: What you need to know  
Learn More

ETL Migration

ETL Modernization with PySpark
Learn More