Subtle Art of Code Conversion in Oracle to PostgreSQL Migration.

In any database migration project, code conversion plays a critical role in achieving overall success and instilling confidence. During an Oracle to PostgreSQL migration, I am aware of conversion tools such as AWS Schema Conversion Tool and Ora2pg. These tools generally perform well in converting the dialect of the code. However, there are instances when we come across complex proprietary features or intricate code that prove to be challenging for automated conversion tools. In such cases, manual conversion becomes necessary to tackle these complexities effectively.

Code conversion is the subtle art of understanding the source engine and dissecting it with knowledge of the target engine.

Code conversion is about understanding how a code functions in the source and transforming it into the target dialect to achieve a similar result or functionality. This process involves dissecting and analyzing the intricacies of the source engine and applying that knowledge to effectively adapt the code to the target engine.

Let’s consider a sample code that I encountered during an Oracle to PostgreSQL migration. This code includes hierarchical queries and string functions, making it complex with multiple layers. Such code can pose a challenge for conversion tools.

create or replace function  fun_check_string_common(
                pi_str1 IN VARCHAR2,
                pi_delimiter IN VARCHAR2)
RETURN PLS_INTEGER
IS
   n_cnt PLS_INTEGER := 0;
BEGIN
  SELECT COUNT(1)
   INTO n_cnt
FROM(
SELECT u
  FROM
    (SELECT TRIM( SUBSTR ( txt , INSTR (txt,pi_delimiter, 1, LEVEL ) + 1 , INSTR (txt, pi_delimiter, 1, LEVEL+1 ) - INSTR (txt, pi_delimiter, 1, LEVEL) -1 ) ) AS u
        FROM
          ( SELECT pi_delimiter||pi_str1||pi_delimiter AS txt FROM dual
          )
          CONNECT BY LEVEL <= LENGTH(txt)-LENGTH(REPLACE(txt,pi_delimiter,''))-1
     ) );
RETURN n_cnt;
  EXCEPTION
    WHEN OTHERS THEN
                  RETURN -1;
END fun_check_string_common;
/

As a conversion engineer, if I were to approach it literally and try to convert the code line by line, it could become a tedious task. I’m not saying it’s impossible, but it would likely require a significant amount of time. One crucial aspect of the conversion process is working backwards. This means that sometimes we need to consider why the code was originally built and what functionality it was intended to achieve. By understanding the original intent, we can approach the conversion more strategically.

When examining the sample Oracle code, it appears to involve string manipulation and serves as a utility within the database. Conducting a quick evaluation with sample data can provide us with some insights about its functionality.

select fun_check_string_common('abc_123_abc','_') from dual;
select fun_check_string_common('abc_123','_') from dual;
select fun_check_string_common('ab','_') from dual;

FUN_CHECK_STRING_COMMON('ABC_123_ABC','_')
------------------------------------------
                                         3


FUN_CHECK_STRING_COMMON('ABC_123','_')
--------------------------------------
                                     2


FUN_CHECK_STRING_COMMON('AB','_')
---------------------------------
                                1

The initial test provides a fair understanding of the expected functionality of the source Oracle function.

Return the count of splits that can be created based on the input delimiter.

By leveraging our understanding of the expected code functionality, we can reconstruct it by utilizing the capabilities of the target engine instead of converting the code line by line. In PostgreSQL, we can achieve similar functionality by using functions like regexp_split_to_array and unnest.

[ora_migrate] > select count(1) from (select unnest(a) from (SELECT regexp_split_to_array('ABC_123_ABC','_') ) as dt(a)) alias1;
 count 
-------
     3
(1 row)

[ora_migrate] > select count(1) from (select unnest(a) from (SELECT regexp_split_to_array('ABC_123','_') ) as dt(a)) alias1;
 count 
-------
     2
(1 row)

[ora_migrate] > select count(1) from (select unnest(a) from (SELECT regexp_split_to_array('AB','_') ) as dt(a)) alias1;
 count 
-------
     1
(1 row)

Overall, code conversion requires a understanding of both the source and target engines, careful analysis of the code intricacies, and strategic thinking to achieve successful migrations and maintain functionality.