Migrating Oracle PL/SQL Applications to AnalyticDB for PostgreSQL

By Lu Feng.

AnalyticDB for PostgreSQL is compatible with Oracle syntax, and therefore you can easily migrate oracle applications to AnalyticDB for PostgreSQL. In this blog, you will learn how you can specifically migrate Oracle PL/SQL applications to AnalyticDB for PostgreSQL. This blog can also be a good point reference when you need to do various migration operations.

The Open-source Ora2Pg Tool

Before starting to convert Oracle Procedural Language (PL), you can use the open-source tool Ora2Pg to implement preliminary conversion. You can also use Ora2Pg to convert DDL, view, and packages in Oracle to PostgreSQL-compatible syntax. However, you may need to manually correct converted SQL scripts because the PostgreSQL syntax version after scripts are converted is often of a later version than the PostegreSQL kernel version used by AnalyticDB for PostgreSQL and rules that Ora2Pg depends on for conversion may be missing or incorrect. For this, generally the best thing to do is to run converted SQL scripts on ADB for PG first to see whether syntax errors or unexpected results are reported, and then, after this, you can manually make corrections.

Data Type Conversion Table

Below is a conversion comparison of Oracle and AnalyticDB for PostgreSQL data types.

System Function Conversion Table

Below is a comparison of Oracle and AnalyticDB for PostgreSQL system functions.

PL/SQL and PL/pgSQL

PL/SQL, which stands for Procedural Language/SQL, is a procedural SQL language extension. It is an extension of SQL statements implemented by Oracle and allows SQL to have some programming language features. Therefore, PL/SQL can be used to implement complex business logic. PL/SQL is equivalent to PL/pgSQL, which stands for Procedural Language/PostgreSQL, in AnalyticDB for PostgreSQL.

Package

PL/pgSQL (Procedural Language/PostgreSQL) in AnalyticDB for PostgreSQL does not support packages. As a result, packages need to be converted to schemas, and all the procedures and functions in packages need to be converted functions in AnalyticDB for PostgreSQL.

For example, consider the following. You’ll want to convert the following:

To this code below:

1. Variables defined in the package:

2. The initialization block for the package:

3. The procedure/function defined in the package:

4. The procedure and function defined in the package are converted into the function of adb for pg, and the function is defined into the schema corresponding to the package.

For example, there is a package named pkg with the following functions:

Procedure/Function

Convert Oracle procedures and functions either in the package or globally to functions in AnalyticDB for PostgreSQL.

For example, convert the following:

To this code below:

Some key points to take consideration of when in procedure/function conversions:

  1. The RETURN keyword is converted to RETURNS.
  2. Use $\$ ... $\$ to package a function body.
  3. You should pay special attention to function language declarations.
  4. A subprocedure also needs to be converted to a function in AnalyticDB for PostgreSQL.

PL Statement

For Statement

The FOR loop with REVERSE statements work differently: The reversion order in PL/SQL is from the second number to the first number, whereas in PL/pgSQL the order is from the first number to the second number. So, what does that mean anyway? Well, in the conversion process, when the FOR loop is ported, the loop boundaries must be exchanged.

To make it clearer, consider the following example. You’ll want to convert the following:

To the following code:

PRAGMA Statement

PRAGMA statements do not exist in AnalyticDB for PostgreSQL, and therefore they need to be deleted.

Transaction Processing

Transactional control statements such as begin, commit, and rollback cannot be used within functions in AnalyticDB for PostgreSQL.

Modification method:

  1. Delete transactional control statements in the function body and put them outside the function body.
  2. Split the function into several functions based on commit/rollback.

EXECUTE Statement

AnalyticDB for PostgreSQL supports dynamic SQL statements that are similar to statements in Oracle. However, AnalyticDB for PostgreSQL differs from Oracle in the following aspects:

  1. The using syntax is not supported. The corresponding solution is to join parameters into SQL strings.
  2. Use quote_ident to package database identifiers and quote_literal to package values.

For example, convert the following:

To this code below:

Pipe Row

Use table functions in AnalyticDB for PostgreSQL to replace Pipe Row functions.

For example, convert the following:

To this code below:

1. Convert the custom type pair to the composite type pair.
2. The Table Of type does not need to be defined. Replace it with Set Of in AnalyticDB for PostgreSQL.
3. The Pipe Row statement is converted to the two following statements:

4. The preceding Oracle function can also be converted to:

The difference from the first option is that the data type numset_t is not required to be pre-defined. This is exactly why a return type needs to be specified when a query is performed, for example, by using select * from f1(10) as (a int, b int);.

Exception Handling

  1. Use Raise to throw an exception.
  2. After the exception is caught, the transaction cannot be rolled back internally. Rollback is only allowed outside user-defined functions.
  3. For error codes supported in AnalyticDB for PostgreSQL, see this page.

When Both Return and Out Parameters Are Included in a Function

In AnalyticDB for PostgreSQL, a function cannot contain the Return parameter and the Out parameter at the same time. Therefore, the parameter to be returned needs to be rewritten as an Out parameter.

For example, convert the following:

To this code below:

Then run select * from test_func(1,'1') into rec; to obtain the return value of the corresponding field from rec.

When a Variable in a String Connection Contains an Apostrophe

In the following example, the variable param2 is of string type. Assume that the value of param2 is adb'-'pg. When the following sql_str is directly used in AnalyticDB for PostgreSQL, - will be recognized as an operator, causing an error. The statement must be converted by using the quote_literal function.

For example, convert the following:

To this code below:

Obtain the Days between Two Timestamps

To obtain the days between two timestamps, you can do the following conversion:

To this code below:

PL Data Types

Record

For Record, convert it to a composite type in AnalyticDB for PostgreSQL.

For example, convert the following:

To this code below:

Nested Table

1. As a variable in PL/SQL, a nested table can be converted to the array type in AnalyticDB for PostgreSQL.

For example, convert the following:

To this code below:

2. When a nested table is used as the return value, replace it with the table function (see Section 1.3.5).

Associative Array

Currently, no conversion type is available for this type.

Variable-Size Arrays

Like with a nested table, for variable-size arrays, simply convert to the array type.

Global Variables

Currently, AnalyticDB for PostgreSQL does not support global variables. One solution is to store all the global variables into one temporary table and define the function to modify and obtain global variables.

For example, consider the following:

The ID field is the distribution column of the temporary table global_variables. Next, because it is not allowed to modify the distribution column in AnalyticDB for PostgreSQL, this field is additionally needed.

To modify a global variable, use:

To obtain a global variable, use:

SQL

Connect By

Connect By is used to process hierarchical queries in Oracle. No equivalent SQL statement can be found in AnalyticDB for PostgreSQL. An conversion option is to use circular iteration by hierarchy.

For example, convert the following:

To this code below:

Rownum

1. This is used to limit the size of a result set. This can be replaced by limit.

For example, convert the following:

To the code below:

2. Use row_number() over() to generate rownum.

For example, convert the following:

To the code below:

DUAL Table

1. Remove DUAL.

For example, convert the following:

To this code below:

2. Create a DUAL table.

User-Defined Functions in SELECT

AnalyticDB for PostgreSQL supports invoking user-defined functions in SELECT. However, user-defined functions cannot contain SQL statements. Otherwise the following error will occur:

To solve this problem, convert the user-defined functions in SELECT to SQL expressions or subqueries.

For example, convert the following:

To this code below:

(+) Multi-table Outer Join

AnalyticDB for PostgreSQL does not support (+) syntax format. To achieve the same effect, convert this syntax to standard Outer Join syntax.

For example, convert the following:

To this code below:

If the (+) syntax involves joining three tables, use wte to join two tables first, and then perform outer join on the wte table and the table connected with +.

For example, convert the following:

To the code below:

Merge Into

To convert Merge Into syntax, use Update in AnalyticDB for PostgreSQL first, and then use the GET DIAGNOSTICS rowcount := ROW_COUNT; statement to obtain the number of updated rows. If the number of updated rows is 0, use Insert statement to insert data.

The preceding is converted to following:

Sequence

For example, convert the following:

To this code below:

Cursor

  • In Oracle, the following statement can be used to iterate through cursors.

For example, convert the following:

To this code below:

  • In Oracle, cursors with the same name can be opened in recursively called functions. However, this is not allowed in AnalyticDB for PostgreSQL. Equivalent statements in AnalyticDB for PostgreSQL must be in the format of for i in query.

For example, convert the following:

To this code below:

Original Source

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.