Migrating Oracle PL/SQL Applications to AnalyticDB for PostgreSQL

Image for post
Image for post

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.

Image for post
Image for post

System Function Conversion Table

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

Image for post
Image for post
Image for post
Image for post

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 keyword is converted to .
  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 loop with 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

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

Transaction Processing

Transactional control statements such as , , and 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 to package database identifiers and to package values.

For example, convert the following:

To this code below:

Pipe Row

Use table functions in AnalyticDB for PostgreSQL to replace functions.

For example, convert the following:

To this code below:

1. Convert the custom type pair to the composite type pair.
2. The type does not need to be defined. Replace it with 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 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 ;.

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 parameter and the parameter at the same time. Therefore, the parameter to be returned needs to be rewritten as an parameter.

For example, convert the following:

To this code below:

Then run ; 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 is of string type. Assume that the value of is . When the following is directly used in AnalyticDB for PostgreSQL, will be recognized as an operator, causing an error. The statement must be converted by using the 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 , 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 . 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

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 to generate .

For example, convert the following:

To the code below:

DUAL Table

1. Remove .

For example, convert the following:

To this code below:

2. Create a table.

User-Defined Functions in

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 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 to join two tables first, and then perform outer join on the table and the table connected with .

For example, convert the following:

To the code below:

Merge Into

To convert syntax, use in AnalyticDB for PostgreSQL first, and then use the ; statement to obtain the number of updated rows. If the number of updated rows is , use 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 example, convert the following:

To this code below:

Original Source

Written by

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store