Migrate data from a self-managed Oracle application to an AnalyticDB for PostgreSQL instance - AnalyticDB

AnalyticDB for PostgreSQL is compatible with Oracle syntax. This topic describes how to migrate data from an Oracle application to an AnalyticDB for PostgreSQL instance.

Syntax conversion by using Ora2Pg

Ora2Pg is an open source tool. You can use the tool to convert DDL statements for tables, views, and packages in Oracle to statements that are compatible with PostgreSQL syntax. For more information, see the Ora2Pg documentation.

Note

You must manually correct converted SQL scripts because the PostgreSQL syntax version after script conversion is later than the minor engine version of your AnalyticDB for PostgreSQL instance and the rules based on which Ora2Pg converts scripts may be missing or incorrect.

Compatibility with Oracle

Compatibility mode parameter

The behavior of the same function is different and incompatible across different databases. You must configure a compatibility mode parameter. PostgreSQL compatibility mode and Oracle compatibility mode are available.

The following table describes the parameter that is provided by AnalyticDB for PostgreSQL to specify the compatibility mode.

Parameter

Description

adb_compatibility_mode

The compatibility mode. Valid values:

postgres (default): PostgreSQL compatibility mode.
oracle: Oracle compatibility mode.

Before you configure this parameter, we recommend that you execute the SHOW adb_compatibility_mode; statement to query the current value of the parameter.

If you want to modify instance-level parameters, Submit a ticket.

Compatibility mode of string concatenation operators

In a string concatenation expression 'abc' || NULL, 'abc' and NULL can be constants or the data from base tables or calculation results.

In PostgreSQL compatibility mode, the return value of the expression is NULL. In PostgreSQL, a string concatenation with NULL produces NULL.
In Oracle compatibility mode, the return value of the expression is 'abc'. In Oracle, NULL is equivalent to an empty string that is enclosed within single quotation marks (' ').
Important
Before you use the string concatenation feature in Oracle compatibility mode, you must disable the Laser engine by using laser.enable = off.

Data type support

AnalyticDB for PostgreSQL provides data type support for constant strings without the need to configure the compatibility mode parameter.

When you execute statements such as CREATE TABLE AS SELECT, the system automatically recognizes constant strings as the TEXT type rather than the UNKNOWN type.

Use the Orafce extension

AnalyticDB for PostgreSQL provides the Orafce extension. This extension supports functions that are compatible with Oracle. You can use these functions in AnalyticDB for PostgreSQL without the need to modify or convert them.

Before you use Orafce, execute the following statement to install Orafce:

CREATE EXTENSION orafce;

The following table lists the Oracle-compatible functions that are provided by Orafce.

Table 1. Oracle-compatible functions that are provided by Orafce
Function	Description	Example
`nvl(anyelement, anyelement)`	If the value of the first argument is null, this function returns the value of the second argument. If the value of the first argument is not null, this function returns the value of the first argument. Note The data types of the two arguments must be the same.	`SELECT nvl(null,1);` The following information is returned: `nvl ----- 1 (1 row)` `SELECT nvl(0,1);` The following information is returned: `nvl ----- 0 (1 row)` `SELECT nvl(0,null);` The following information is returned: `nvl ----- 0 (1 row)`
`add_months(day date, value int)RETURNS date`	This function adds the number of months that is specified by the second argument to the date that is specified by the first argument and returns a date.	`SELECT add_months(current_date, 2);` The following information is returned: `add_months ------------ 2019-08-31 (1 row)`
`last_day(value date)`	This function returns the last day of the month for the specified date. The return value is a date.	`SELECT last_day('2018-06-01');` The following information is returned: `last_day ------------ 2018-06-30 (1 row)`
`next_day(value date, weekday text)`	The first argument specifies a start date. The second argument specifies a day of a week. Example: Friday. This function returns the date that represents a day of the second week since the start date. Example: the date that represents the second Friday.	`SELECT next_day(current_date, 'FRIDAY');` The following information is returned: `next_day ------------ 2019-07-05 (1 row)`
`next_day(value date, weekday integer)`	The first argument specifies a start date. The second argument specifies a number that represents a day of a week. The number ranges from 1 to 7. The value 1 represents Sunday, and the value 2 represents Monday. In a similar manner, the value 7 represents Saturday. This function returns a date that is a specific number of days later than the start date.	`SELECT next_day('2019-06-22', 1);` The following information is returned: `next_day ------------ 2019-06-23 (1 row)` `SELECT next_day('2019-06-22', 2);` The following information is returned: `next_day ------------ 2019-06-24 (1 row)`
`months_between(date1 date, date2 date)`	This function returns the number of months between date1 and date2. If date1 is later than date2, the return value is positive. If date1 is earlier than date2, the return value is negative.	`SELECT months_between('2019-01-01', '2018-11-01');` The following information is returned: `months_between ---------------- 2 (1 row)` `SELECT months_between('2018-11-01', '2019-01-01');` The following information is returned: `months_between ---------------- -2 (1 row)`
`trunc(value timestamp with time zone, fmt text)`	The first argument specifies the timestamp that you want to truncate. The second argument specifies the precision based on which the timestamp is truncated, such as year, month, day, week, hour, minute, or second. Y: specifies that the timestamp is truncated to the first day of the year that corresponds to the timestamp. Q: specifies that the timestamp is truncated to the first day of the quarter that corresponds to the timestamp.	`SELECT TRUNC(current_date,'Q');` The following information is returned: `trunc ------------ 2019-04-01 (1 row)` `SELECT TRUNC(current_date,'Y');` The following information is returned: `trunc ------------ 2019-01-01 (1 row)`
`trunc(value timestamp with time zone)`	This function truncates a timestamp. The hour, minute, and second values of the specified timestamp are truncated by default.	`SELECT TRUNC('2019-12-11'::timestamp);` The following information is returned: `trunc ------------------------ 2019-12-11 00:00:00+08 (1 row)`
`trunc(value date)`	This function truncates a date.	`SELECT TRUNC('2019-12-11'::timestamp,'Y');` The following information is returned: `trunc ------------------------ 2019-01-01 00:00:00+08`
`round(value timestamp with time zone, fmt text)`	This function rounds a timestamp to the nearest value based on units such as week or day.	`SELECT round('2018-10-06 13:11:11'::timestamp, 'YEAR');` The following information is returned: `round ------------------------ 2019-01-01 00:00:00+08 (1 row)`
`round(value timestamp with time zone)`	This function rounds a timestamp to the nearest value based on the unit day.	`SELECT round('2018-10-06 13:11:11'::timestamp);` The following information is returned: `round ------------------------ 2018-10-07 00:00:00+08 (1 row)`
`round(value date, fmt text)`	This function returns a rounded date.	`SELECT round(TO_DATE('27-OCT-00','DD-MON-YY'), 'YEAR');` The following information is returned: `round ------------ 2001-01-01 (1 row)` `SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'), 'YEAR');` The following information is returned: `round ------------ 2000-01-01 (1 row)`
`round(value date)`	This function returns a rounded date.	`SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'));` The following information is returned: `round ------------ 2000-02-27 (1 row)`
`instr(str text, patt text, start int, nth int)`	This function searches for a substring in a string. If a substring is obtained, the function returns the position of the substring. Otherwise, the function returns 0. start: specifies the start position of the search. nth: specifies the position of the nth occurrence of the substring.	`SELECT instr('Greenplum', 'e',1,2);` The following information is returned: `instr ------- 4 (1 row)` `SELECT instr('Greenplum', 'e',1,1);` The following information is returned: `instr ------- 3 (1 row)`
`instr(str text, patt text, start int)`	The nth argument is not specified. This function returns the position of the first occurrence of the substring.	`SELECT instr('Greenplum', 'e',1);` The following information is returned: `instr ------- 3 (1 row)`
`instr(str text, patt text)`	The start argument is not specified. This function searches for the substring from the start of a string.	`SELECT instr('Greenplum', 'e');` The following information is returned: `instr ------- 3 (1 row)`
`plvstr.rvrs(str text, start int, end int)`	This function reverses the order of characters in a specified string. The str argument specifies the string, and the start and end argument specify the start position and end position of the characters whose order you want to reverse.	`SELECT plvstr.rvrs('adb4pg', 5,6);` The following information is returned: `reverse --------- gp (1 row)`
`plvstr.rvrs(str text, start int)`	This function reverses the order of characters from the character that is specified by the start argument to the end of the string.	`SELECT plvstr.rvrs('adb4pg', 4);` The following information is returned: `reverse --------- gp4 (1 row)`
`plvstr.rvrs(str text)`	This function reverses the order of an entire string.	`SELECT plvstr.rvrs('adb4pg');` The following information is returned: `reverse --------- gp4bda (1 row)`
`concat(text, text)`	This function joins two strings together.	`SELECT concat('adb','4pg');` The following information is returned: `concat -------- adb4pg (1 row)`
`concat(text, anyarray)/concat(anyarray, text)/concat(anyarray, anyarray)`	This function joins data of the same or different data types together.	`SELECT concat('adb4pg', 6666);` The following information is returned: `concat ------------ adb4pg6666 (1 row)` `SELECT concat(6666, 6666);` The following information is returned: `concat ---------- 66666666 (1 row)` `SELECT concat(current_date, 6666);` The following information is returned: `concat ---------------- 2019-06-306666 (1 row)`
`nanvl(float4, float4)/nanvl(float4, float4)/nanvl(numeric, numeric)`	If the first argument is of the NUMERIC data type, this function returns the value of the first argument. Otherwise, this function returns the value of the second argument.	`SELECT nanvl('NaN', 1.1);` The following information is returned: `nanvl ------- 1.1 (1 row)` `SELECT nanvl('1.2', 1.1);` The following information is returned: `nanvl ------- 1.2 (1 row)`
`bitand(bigint, bigint)`	This function performs an AND operation for two binary numbers of the INTEGER data type. Only one row is returned.	`SELECT bitand(1,3);` The following information is returned: `bitand -------- 1 (1 row)` `SELECT bitand(2,6);` The following information is returned: `bitand -------- 2 (1 row)` `SELECT bitand(4,6);` The following information is returned: `bitand -------- 4 (1 row)`
`listagg(text)`	This function returns a clustered string for texts.	`SELECT listagg(t) FROM (VALUES('abc'), ('def')) as l(t);` The following information is returned: `listagg --------- abcdef (1 row)`
`listagg(text, text)`	This function returns a clustered string for texts. The value of the second argument is used as a separator.	`SELECT listagg(t, '.') FROM (VALUES('abc'), ('def')) as l(t);` The following information is returned: `listagg --------- abc.def (1 row)`
`nvl2(anyelement, anyelement, anyelement)`	If the value of the first argument is null, this function returns the value of the third argument. Otherwise, this function returns the value of the second argument.	`SELECT nvl2(null, 1, 2);` The following information is returned: `nvl2 ------ 2 (1 row)` `SELECT nvl2(0, 1, 2);` The following information is returned: `nvl2 ------ 1 (1 row)`
`lnnvl(bool)`	If the value of the argument is null or false, this function returns true. If the value of the argument is true, this function returns false.	`SELECT lnnvl(null);` The following information is returned: `lnnvl ------- t (1 row)` `SELECT lnnvl(false);` The following information is returned: `lnnvl ------- t (1 row)` `SELECT lnnvl(true);` The following information is returned: `lnnvl ------- f (1 row)`
`dump("any")`	This function returns a text that contains the data type code, length in bytes, and the internal representation of the argument.	`SELECT dump('adb4pg');` The following information is returned: `dump --------------------------------------- Typ=705 Len=7: 97,100,98,52,112,103,0 (1 row)`
`dump("any", integer)`	The second argument specifies the format of the return value. The format can be a decimal notation (specified by 10) or a hexadecimal notation (specified by 16).	`SELECT dump('adb4pg', 10);` The following information is returned: `dump --------------------------------------- Typ=705 Len=7: 97,100,98,52,112,103,0 (1 row)` `SELECT dump('adb4pg', 16);` The following information is returned: `dump ------------------------------------ Typ=705 Len=7: 61,64,62,34,70,67,0 (1 row)` `SELECT dump('adb4pg', 2);` The following information is returned: `ERROR: unknown format (others.c:430)`
`nlssort(text, text)`	This function sorts data in a specific order.	Execute the following statements to create a table and insert data: `CREATE TABLE t1 (name text); INSERT INTO t1 VALUES('Anne'), ('anne'), ('Bob'), ('bob');` `SELECT * FROM t1 ORDER BY nlssort(name, 'en_US.UTF-8');` The following information is returned: `name ------ anne Anne bob Bob (4 row)` `SELECT * FROM t1 ORDER BY nlssort(name, 'C');` The following information is returned: `name ------ Anne Bob anne bob (4 row)`
`substr(str text, start int)`	This function retrieves a substring from the string that is specified by the first argument. The second argument specifies the start position of the substring.	`SELECT substr('adb4pg', 1);` The following information is returned: `substr -------- adb4pg (1 row)` `SELECT substr('adb4pg', 4);` The following information is returned: `substr -------- 4pg (1 row)`
`substr(str text, start int, len int)`	The third argument specifies the end position of the substring. The value of this argument must be `greater than or equal to the value of the start argument and less than or equal to the length of the string`.	`SELECT substr('adb4pg', 5,6);` The following information is returned: `substr -------- pg (1 row)`
`pg_catalog.substrb(varchar2, integer, integer)`	This function returns a substring from a string of the VARCHAR2 data type. The second argument specifies the start position of the substring, and the third argument specifies the end position of the substring.	`SELECT substr('adb4pg'::varchar2, 5,6);` The following information is returned: `substr -------- pg (1 row)`
`pg_catalog.substrb(varchar2, integer)`	This function returns a substring from a string of the VARCHAR2 data type. The substring starts from the character that is specified by the second argument and continues until the end of the string.	`SELECT substr('adb4pg'::varchar2, 4) ;` The following information is returned: `substr -------- 4pg (1 row)`
`pg_catalog.lengthb(varchar2)`	This function returns the number of bytes for a string of the VARCHAR2 data type. If null is specified, the function returns null. If an empty string is specified, the function returns 0.	`SELECT lengthb('adb4pg'::varchar2) ;` The following information is returned: `lengthb --------- 6 (1 row)` `SELECT lengthb('analytics'::varchar2);` The following information is returned: `lengthb --------- 9 (1 row)`
`lpad(string char, length int, fill char)`	This function pads a string on the left to a specified length with a sequence of characters. The first argument specifies the string that you want to pad on the left. The second argument specifies the length of the result string after padding. The third argument specifies the string that is used for padding. Note For strings of the CHAR type, PostgreSQL removes trailing spaces and Oracle does not.	`SELECT lpad('abc '::char(4),6,'x');` The following information is returned: `lpad -------- xxabc (1 row)`
`lpad(string char, length int)`	This function pads a string on the left to a specified length with spaces.	`SELECT lpad('abc '::char(4),6);` The following information is returned: `lpad -------- abc (1 row)`
`regexp_count(string text, pattern text, startPos int, flags text)`	This function returns the number of times a pattern occurs in a source string from the start position. The return value must be an integer. If no pattern is found, the function returns 0. The first argument specifies the source string. The second argument specifies the pattern. The third argument specifies the position from which the search starts. It must be a positive integer. You cannot search for the pattern from the end of the source string. The fourth argument specifies the character expression that you can use to change the default matching behavior of the function. It can include one or more of the following characters: `'i'`: case-insensitive matching. By default, case-sensitive and accent-sensitive matching is used. `'c'`: case-sensitive and accent-sensitive matching. `'n'`: allows a period (.) to match the line feed. By default, a period (.) does not match the line feed. `'m'`: treats the source string as multiple lines. By default, the source string is treated as a single line. `'x'`: ignores whitespace characters. By default, whitespace characters match themselves.	`SELECT regexp_count('a'\|\|CHR(10)\|\|'d', 'a.d', 1, 'n');` The following information is returned: `regexp_count -------------- 1 (1 row)` `SELECT regexp_count('a'\|\|CHR(10)\|\|'d', 'a.d', 1, 'n');` The following information is returned: `regexp_count -------------- 1 (1 row)`
`regexp_count(string text, pattern text, startPos int)`	This function returns the number of times a pattern occurs in a source string from the start position of the string. The return value must be an integer. If no pattern is found, the function returns 0.	`SELECT regexp_count('abc', '[a-z]',3);` The following information is returned: `regexp_count -------------- 1 (1 row)`
`regexp_count(string text, pattern text)`	This function returns the number of times a pattern occurs in a source string from the beginning of the string. The return value must be an integer. If no pattern is found, the function returns 0.	`SELECT regexp_count('abc', '[a-z]');` The following information is returned: `regexp_count -------------- 3 (1 row)`
`regexp_instr(string text, pattern text, startPos int, occurence int, return_opt int, flags text, group int)`	This function returns the start or end position of a pattern in a source string. The return value must be an integer. The first argument specifies the source string. The second argument specifies the pattern. The third argument specifies the position from which the search starts. It must be a positive integer. You cannot search for the pattern from the end of the source string. The fourth argument specifies the occurrence of the pattern in the source string from which the search starts. It must be a positive integer. The default value is 1, which indicates that the search starts from the first occurrence of the pattern. The fifth argument specifies the start or end position of the pattern in the source string. Valid values: 0 (default): the position of the first character of the occurrence. 1: the position of the character that follows the occurrence. The sixth argument specifies the character expression that you can use to change the default matching behavior of the function. For more information, see REGEXP_COUNT. The seventh argument specifies the serial number of the capturing group in the pattern. It must be a positive integer. Capturing groups can be nested. Capturing groups are numbered based on the order in which their left parentheses appear in the pattern. If the value is 0, the position of the entire substring that matches the pattern is returned. If the value is greater than the number of capturing groups in the pattern, the function returns 0. Default value: 0.	`SELECT regexp_instr('abxxabc','(a)(b)(c)',1,1,0,'',2);` The following information is returned: `regexp_instr -------------- 6 (1 row)`
`regexp_instr(string text, pattern text, startPos int, occurence int, return_opt int, flags text)`	This function returns the start or end position of the specified occurrence of a pattern in a source string from the start position of the string. The return value must be an integer. return_opt specifies the start or end position of the pattern in the source string. Valid values: 0 (default): the position of the first character of the occurrence. 1: the position of the character that follows the occurrence. flags specifies the character expression that you can use to change the default matching behavior of the function. For more information, see REGEXP_COUNT.	`SELECT regexp_instr('abxxabc','(a)(b)(c)',1,1,0,'');` The following information is returned: `regexp_instr -------------- 5 (1 row)`
`regexp_instr(string text, pattern text, startPos int, occurence int, return_opt int)`	This function returns the start or end position of the specified occurrence of a pattern in a source string from the start position of the string. The return value must be an integer. return_opt specifies the start or end position of the pattern in the source string. Valid values: 0 (default): the position of the first character of the occurrence. 1: the position of the character that follows the occurrence.	`SELECT regexp_instr('abc','[a-z]{3}',1,1,1);` The following information is returned: `regexp_instr -------------- 4 (1 row)`
`regexp_instr(string text, pattern text, startPos int, occurence int)`	This function returns the position of the specified occurrence of a pattern in a source string from the start position of the string. The return value must be an integer.	`SELECT regexp_instr('abcd','[a-z]{2}',1,2);` The following information is returned: `regexp_instr -------------- 3 (1 row)`
`regexp_instr(string text, pattern text, startPos int)`	This function returns the position of a pattern in a source string from the start position of the string. The return value must be an integer.	`SELECT regexp_instr('abc','[a-z]',2);` The following information is returned: `regexp_instr -------------- 2 (1 row)`
`regexp_instr(string text, pattern text)`	This function returns the position of a pattern in a source string from the beginning of the string. The return value must be an integer.	`SELECT regexp_instr('abc','[a-z]');` The following information is returned: `regexp_instr -------------- 1 (1 row)`
`regexp_like(string text, pattern text, flags text)`	If a substring of a source string matches a pattern, this function returns true. Otherwise, this function returns false. The first argument specifies the source string. The second argument specifies the pattern. The third argument specifies the character expression that you can use to change the default matching behavior of the function. For more information, see REGEXP_COUNT.	`SELECT regexp_like('a'\|\|CHR(10)\|\|'d', 'a.d', 'n');` The following information is returned: `regexp_like ------------- t (1 row)`
`regexp_like(string text, pattern text)`	If a substring of a source string matches a pattern, this function returns true. Otherwise, this function returns false.	`SELECT regexp_like('abc', '[a-z]');` The following information is returned: `regexp_like ------------- t (1 row)`
`regexp_substr(string text, pattern text, startPos int, occurence int, flags text)`	This function returns the substring of a source string that matches a pattern. The first argument specifies the source string. The second argument specifies the pattern. The third argument specifies the position from which the search starts. It must be a positive integer. You cannot search for the pattern from the end of the source string. The fourth argument specifies the occurrence of the pattern in the source string from which the search starts. It must be a positive integer. The default value is 1, which indicates that the search starts from the first occurrence of the pattern. The fifth argument specifies the character expression that you can use to change the default matching behavior of the function. For more information, see REGEXP_COUNT.	`SELECT regexp_substr('a,bc,def', '[^,]+',1,2,'');` The following information is returned: `regexp_substr --------------- bc (1 row)`
`regexp_substr(string text, pattern text, startPos int, occurence int)`	This function returns the substring of a source string that matches a pattern for the specified occurrence from the start position of the string.	`SELECT regexp_substr('a,bc,def', '[^,]+',4,2);` The following information is returned: `regexp_substr --------------- def (1 row)`
`regexp_substr(string text, pattern text, startPos int)`	This function returns the substring of a source string that matches a pattern from the start position of the string.	`SELECT regexp_substr('a,bc,def', '[^,]+',4);` The following information is returned: `regexp_substr --------------- c (1 row)`
`regexp_substr(string text, pattern text)`	This function returns the substring of a source string that matches a pattern from the beginning of the string.	`SELECT regexp_substr('a,bc,def', '[^,]+');` The following information is returned: `regexp_substr --------------- a (1 row)`

In addition to the preceding functions, Orafce is compatible with the VARCHAR2 data type in Oracle.

The following table describes the Oracle functions that can be supported by AnalyticDB for PostgreSQL without installing Orafce.

Function	Description	Example
`sinh(float)`	This function returns a hyperbolic sine value.	`SELECT sinh(0.1);` The following information is returned: `sinh ------------------- 0.100166750019844 (1 row)`
`tanh(float)`	This function returns a hyperbolic tangent value.	`SELECT tanh(3);` The following information is returned: `tanh ------------------ 0.99505475368673 (1 row)`
`cosh(float)`	This function returns a hyperbolic cosine value.	`SELECT cosh(0.2);` The following information is returned: `cosh ------------------ 1.02006675561908 (1 row)`
`decode(expression, value, return [,value,return]... [, default])`	This function searches for a value in expressions. If the value is found, the function returns the value. Otherwise, the function returns the default value.	Execute the following statements to create a table and insert data: `CREATE TABLE t1(id int, name varchar(20)); INSERT INTO t1 values(1,'alibaba'); INSERT INTO t1 values(2,'adb4pg');` `SELECT decode(id, 1, 'alibaba', 2, 'adb4pg', 'not found') from t1;` The following information is returned: `case --------- alibaba adb4pg (2 rows)` `SELECT decode(id, 3, 'alibaba', 4, 'adb4pg', 'not found') from t1;` The following information is returned: `case ----------- not found not found (2 rows)`

Mappings between Oracle data types and AnalyticDB for PostgreSQL data types

Oracle	AnalyticDB for PostgreSQL
VARCHAR2	varchar or text
DATE	timestamp
LONG	text
LONG RAW	bytea
CLOB	text
NCLOB	text
BLOB	bytea
RAW	bytea
ROWID	oid
FLOAT	double precision
DEC	decimal
DECIMAL	decimal
DOUBLE PRECISION	double precision
INT	int
INTEGER	integer
REAL	real
SMALLINT	smallint
NUMBER	numeric
BINARY_FLOAT	double precision
BINARY_DOUBLE	double precision
TIMESTAMP	timestamp
XMLTYPE	xml
BINARY_INTEGER	integer
PLS_INTEGER	integer
TIMESTAMP WITH TIME ZONE	timestamp with time zone
TIMESTAMP WITH LOCAL TIME ZONE	timestamp with time zone

Mappings between Oracle functions and AnalyticDB for PostgreSQL functions

Oracle	AnalyticDB for PostgreSQL
sysdate	current timestamp
trunc	trunc or date trunc
dbms_output.put_line	RAISE statement
decode	case when or decode
NVL	coalesce

Conversion of data in PL/SQL

Procedural Language/SQL (PL/SQL) is a procedural language extension to SQL that is provided by Oracle. PL/SQL supports the features of general programming languages for SQL and can be used to implement complex business logic. PL/SQL maps PL/pgSQL in AnalyticDB for PostgreSQL.

Package

PL/pgSQL does not support packages. You must convert packages to schemas. All the procedures and functions in packages must be converted to functions that are supported by AnalyticDB for PostgreSQL.

Example:

CREATE OR REPLACE PACKAGE pkg IS 

...

END;

Conversion result:

CREATE SCHEMA pkg;

Variables that are defined in packages
Local variables of procedures and functions remain unchanged, and global variables can be stored in temporary tables in AnalyticDB for PostgreSQL.
Package initialization blocks
You must remove package initialization blocks. If the blocks cannot be removed, encapsulate them in functions and call the functions when necessary.
Procedures and functions that are defined in packages
Convert procedures and functions that are defined in packages to functions that are supported by AnalyticDB for PostgreSQL. Each function must be defined in the schema that corresponds to the package that is being used.
For example, a package named pkg contains the following function:
```
FUNCTION test_func (args int) RETURN int is
var number := 10;
BEGIN
...
END;
```
The preceding function must be converted to the following function that is supported by AnalyticDB for PostgreSQL:
```
CREATE OR REPLACE FUNCTION pkg. test_func(args int) RETURNS int AS
$$

  ...

$$
 LANGUAGE plpgsql;
```

Procedure/Function

Convert package-specific and global procedures and functions in Oracle to functions that are supported by AnalyticDB for PostgreSQL.

Example:

CREATE OR REPLACE FUNCTION test_func (v_name varchar2, v_version varchar2)
RETURN varchar2 IS
    ret varchar(32);
BEGIN
    IF v_version IS NULL THEN
        ret := v_name;
ELSE
    ret := v_name || '/' || v_version;
    END IF;
    RETURN ret;
END;

Conversion result:

CREATE OR REPLACE FUNCTION test_func (v_name varchar, v_version varchar)
RETURNS varchar AS
$$

DECLARE
    ret varchar(32);
BEGIN
    IF v_version IS NULL THEN
        ret := v_name;
ELSE
    ret := v_name || '/' || v_version;
    END IF;
    RETURN ret;
END;

$$
 LANGUAGE plpgsql;

Take note of the following information before you convert a procedure or function:

Convert the RETURN keyword to RETURNS.
Use &dollar;\$ ... &dollar;\$ to encapsulate a function body.
Pay attention to function language declarations.
Convert a subprocedure to a function that is supported by AnalyticDB for PostgreSQL.

PL statement

FOR statements
In PL/SQL and PL/pgSQL, integer FOR LOOP statements with REVERSE work differently:
- PL/SQL counts down from the second number to the first number.
- PL/pgSQL counts down from the first number to the second number.
Therefore, loop boundaries need to be exchanged during conversion. Example:
```
FOR i IN REVERSE 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
END LOOP;
```
Conversion result:
```
FOR i IN REVERSE 3..1 LOOP
    RAISE '%' ,i;
END LOOP;
```
PRAGMA statements
AnalyticDB for PostgreSQL does not support PRAGMA statements. You must delete PRAGMA statements.
Transaction processing
Functions of AnalyticDB for PostgreSQL do not support transaction control statements such as BEGIN, COMMIT, and ROLLBACK.
These statements must be processed based on the following rules:
- Delete transaction control statements in function bodies, and include them outside the function bodies.
- Split functions based on COMMIT and ROLLBACK statements.
EXECUTE statements
AnalyticDB for PostgreSQL supports dynamic SQL statements that are similar to those provided in Oracle. Take note of the following differences:
- The dynamic SQL statements in AnalyticDB for PostgreSQL do not support the USING syntax. You must join parameters into SQL strings.
- Database identifiers are packaged by using quote_ident, and numeric values are packaged by using quote_literal.
Example:
```
EXECUTE 'UPDATE employees_temp SET commission_pct = :x' USING a_null;
```
Conversion result:
```
EXECUTE 'UPDATE employees_temp SET commission_pct = ' || quote_literal(a_null);
```

PIPE ROW functions

Use the table functions in AnalyticDB for PostgreSQL to replace PIPE ROW functions.

Example:

TYPE pair IS RECORD(a int, b int);
TYPE numset_t IS TABLE OF pair;

FUNCTION f1(x int) RETURN numset_t PIPELINED IS
DECLARE
    v_p pair;
BEGIN
    FOR i IN 1..x LOOP
      v_p.a := i;
      v_p.b := i+10;
      PIPE ROW(v_p);
    END LOOP;
    RETURN;
END;

select * from f1(10);

Conversion result:

CREATE TYPE pair AS (a int, b int);

CREATE OR REPLACE FUNCTION f1(x int) RETURNS SETOF PAIR AS
$$

DECLARE
REC PAIR;
BEGIN
    FOR i IN 1..x loop
        REC := row(i, i+10);
        RETURN NEXT REC;
    END LOOP;
    RETURN ;
END

$$
 language 'plpgsql';

SELECT * FROM f1(10);

Exception handling
- Use the RAISE statement to throw an exception.
- After the exception is caught, the corresponding transaction cannot be rolled back. Rollback is only allowed outside user-defined functions.
- For information about error codes that are supported by AnalyticDB for PostgreSQL, visit the PostgreSQL official website.

Functions that contain return and out arguments

In AnalyticDB for PostgreSQL, a function cannot contain a return argument and an out argument at the same time. You must convert the return argument to an out argument.

Example:

CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int) returns varchar(10)
AS $body$
BEGIN
      out_id := id + 1;
      return name;
end
$body$
LANGUAGE PLPGSQL;

Conversion result:

CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int, out_name out varchar(10))
AS $body$
BEGIN
      out_id := id + 1;
      out_name := name;
end
$body$
LANGUAGE PLPGSQL;

Then, execute the SELECT * FROM test_func(1,'1') INTO rec; statement to obtain the return value of the corresponding field from rec.

Quotation marks (') that are included in variables in string connections
In the following example, the variable param2 is of the STRING data type. For example, the value of this variable is adb'-'pg. If sql_str is directly used in AnalyticDB for PostgreSQL, hyphens (-) are identified as an operator, which causes an error. You must use the quote_literal function to convert the variable.
Example:
```
sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = '''|| param2 || '''AND col3 = 3';
```
Conversion result:
```
sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = '|| quote_literal(param2) || 'AND col3 = 3';
```

Obtain the number of days between two timestamps

Example:

SELECT to_date('2019-06-30 16:16:16') - to_date('2019-06-29 15:15:15') + 1 INTO v_days FROM dual;

Conversion result:

SELECT extract('days' from '2019-06-30 16:16:16'::timestamp - '2019-06-29 15:15:15'::timestamp + '1 days'::interval)::int INTO v_days;

PL data types

Record
Convert the RECORD data type to the composite data type in AnalyticDB for PostgreSQL.
Example:
```
TYPE rec IS RECORD (a int, b int);
```
Conversion result:
```
CREATE TYPE rec AS (a int, b int);
```

Nest table

As a variable in PL, the NESTED TABLE data type can be converted to the ARRAY data type in AnalyticDB for PostgreSQL.

Example:

DECLARE
  TYPE Roster IS TABLE OF VARCHAR2(15);
  names Roster :=
  Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
BEGIN
  FOR i IN names.FIRST .. names.LAST
  LOOP
      IF names(i) = 'J Hamil' THEN
        DBMS_OUTPUT.PUT_LINE(names(i));
      END IF;
  END LOOP;
END;

Conversion result:

CREATE OR REPLACE FUNCTION f1() RETURNS VOID AS
$$

DECLARE
    names varchar(15)[] := '{"D Caruso", "J Hamil", "D Piro", "R Singh"}';
    len int := array_length(names, 1);
BEGIN
    for i in 1..len loop
        if names[i] = 'J Hamil' then
            raise notice '%', names[i];
        end if;
    end loop;
    return ;
END

$$
 language 'plpgsql';

SELECT f();

If a nested table is used as the return value of a function, you can use a table function to replace the nested table.

Associative Array
No replacement is available for this data type.
Variable-Size Arrays
The VARIABLE-SIZE ARRAY data type can be converted to the ARRAY data type, in a similar manner as the NESTED TABLE data type.
Global variables
AnalyticDB for PostgreSQL does not support global variables. You can store all global variables of a package in a temporary table and define functions that are used to obtain the global variables.
Example:
```
CREATE TEMPORARY TABLE global_variables (
        id int,
        g_count int,
        g_set_id varchar(50),
        g_err_code varchar(100)
);

INSERT INTO global_variables VALUES(0, 1, null, null);

CREATE OR REPLACE FUNCTION get_variable() returns setof global_variables AS

$$

DECLARE
    rec global_variables%rowtype;
BEGIN
    execute 'select * from global_variables' into rec;
    return next rec;
END;

$$
 LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION set_variable(in param varchar(50), in value anyelement) returns void AS

$$

BEGIN
    execute 'update global_variables set ' ||  quote_ident(param) || ' = ' || quote_literal(value);
END;

$$
 LANGUAGE plpgsql;
```
In the global_variables temporary table, the ID field is the distribution key of the table. AnalyticDB for PostgreSQL does not allow you to modify a distribution key. You must add the tmp_rec record; field in the table.
To modify a global variable, execute the select * from set_variable('g_error_code', 'error'::varchar) into tmp_rec; statement.
To obtain a global variable, execute the select * from get_variable() into tmp_rec; error_code := tmp_rec.g_error_code; statement.

SQL

CONNECT BY

The CONNECT BY clause can be used for hierarchical queries in Oracle. No SQL statement from AnalyticDB for PostgreSQL can be used in the same manner to replace a CONNECT BY clause. You can use circular traversal by hierarchy to convert a CONNECT BY clause.

Example:

CREATE TABLE employee(
       emp_id numeric(18),
       lead_id numeric(18),
       emp_name varchar(200),
       salary numeric(10,2),
       dept_no varchar(8)
);
INSERT INTO employee values('1',0,'king','1000000.00','001');
INSERT INTO employee values('2',1,'jack','50500.00','002');
INSERT INTO employee values('3',1,'arise','60000.00','003');
INSERT INTO employee values('4',2,'scott','30000.00','002');
INSERT INTO employee values('5',2,'tiger','25000.00','002');
INSERT INTO employee values('6',3,'wudde','23000.00','003');
INSERT INTO employee values('7',3,'joker','21000.00','003');
INSERT INTO employee values('3',7,'joker','21000.00','003');

SELECT emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
     FROM employee
     START WITH  lead_id=0
     CONNECT BY prior emp_id =  lead_id

Conversion result:

CREATE OR REPLACE FUNCTION f1(tablename text, lead_id int, nocycle boolean) RETURNS setof employee AS
$$

DECLARE
    idx int := 0;
    res_tbl varchar(265) := 'result_table';
    prev_tbl varchar(265) := 'tmp_prev';
    curr_tbl varchar(256) := 'tmp_curr';

    current_result_sql varchar(4000);
    tbl_count int;

    rec record;
BEGIN

    execute 'truncate ' || prev_tbl;
    execute 'truncate ' || curr_tbl;
    execute 'truncate ' || res_tbl;
    loop
        -- Query the current hierarchical result and insert the result into the tmp_curr table.
        current_result_sql := 'insert into ' || curr_tbl || ' select t1.* from ' || tablename || ' t1';

        if idx > 0 then
            current_result_sql := current_result_sql || ', ' || prev_tbl || ' t2 where t1.lead_id = t2.emp_id';
        else
            current_result_sql := current_result_sql || ' where t1.lead_id = ' || lead_id;
        end if;
        execute current_result_sql;

        -- If a loop exists, delete the data that is traversed.
        if nocycle is false then
            execute 'delete from ' || curr_tbl || ' where (lead_id, emp_id) in (select lead_id, emp_id from ' || res_tbl || ') ';
        end if;

        -- Exit if no data exists.
        execute 'select count(*) from ' || curr_tbl into tbl_count;
        exit when tbl_count = 0;

        -- Save data from the tmp_curr table to the result table.
        execute 'insert into ' || res_tbl || ' select * from ' || curr_tbl;
        execute 'truncate ' || prev_tbl;
        execute 'insert into ' || prev_tbl || ' select * from ' || curr_tbl;
        execute 'truncate ' || curr_tbl;
        idx := idx + 1;
    end loop;

    -- The following information is returned:
    current_result_sql := 'select * from ' || res_tbl;
    for rec in execute current_result_sql loop
        return next rec;
    end loop;
    return;
END

$$
 language plpgsql;

ROWNUM
1. ROWNUM can be used to limit the size of a result set. You can use a LIMIT clause to replace ROWNUM.
  Example:
```
SELECT * FROM t WHERE rownum < 10;
```
  Conversion result:
```
SELECT * FROM t LIMIT 10;
```
2. Use row_number() over() to generate ROWNUM.
  Example:
```
SELECT rownum, * FROM t;
```
  Conversion result:
```
SELECT row_number() over() AS rownum, * FROM t;
```
DUAL table
1. Remove the DUAL table.
  Example:
```
SELECT sysdate FROM dual;
```
  Conversion result:
```
SELECT current_timestamp;
```
2. Create a table named dual.

User-defined functions in SELECT statements

AnalyticDB for PostgreSQL allows you to call user-defined functions in SELECT statements. These functions cannot contain SQL statements. If a user-defined function contains SQL statements, the following error message is displayed:

ERROR: function cannot execute on segment because it accesses relation "public.t2" (functions.c:155) (seg1 slice1 127.0.0.1:25433 pid=52153) (cdbdisp.c:1326)
DETAIL:
SQL statement "select b from t2 where a = $1 "

To prevent this error, convert the user-defined functions in SELECT statements to SQL expressions or subqueries.

Example:

CREATE OR REPLACE FUNCTION f1(arg int) RETURN int IS
    v int;
BEGIN
    SELECT b INTO v FROM t2 WHERE a = arg;
    RETURN v;
END;

SELECT  a, f1(b) FROM t1;

Conversion result:

SELECT t1.a, t2.b FROM t1, t2 WHERE t1.b = t2.a;

OUTER JOIN (+) for multiple tables
AnalyticDB for PostgreSQL does not support the (+) syntax. You must convert the (+) syntax to the standard OUTER JOIN syntax.
Example:
```
SELECT * FROM a,b WHERE a.id=b.id(+)
```
Conversion result:
```
SELECT * FROM a LEFT JOIN b ON a.id=b.id
```
If the (+) syntax requires a JOIN operation to be performed for three tables, use WTE to join two tables, and then perform an OUTER JOIN operation on the WTE table and the table that is connected with (+).
Example:
```
SELECT * FROM test1 t1, test2 t2, test3 t3 WHERE t1.col1(+) BETWEEN NVL(t2.col1, t3.col1) ADN NVL(t3.col1, t2.col1);
```
Conversion result:
```
WITH cte AS (SELECT t2.col1 AS low, t2.col2, t3.col1 AS high, t3.col2 AS c2 FROM t2, t3)
SELECT * FROM t1 RIGHT OUTER JOIN cte ON t1.col1 BETWEEN coalesce(cte.low, cte.high) AND coalesce(cte.high,cte.low);
```
MERGE INTO
In most cases, the MERGE INTO syntax can be implemented by INSERT ON CONFLICT. However, some features of MERGE INTO can be implemented only by stored procedures.
For more information about INSERT ON CONFLICT, see Use INSERT ON CONFLICT to overwrite data.
For more information about stored procedures, see Stored procedures.

Sequence

Example:

CREATE SEQUENCE seq1;
SELECT seq1.nextval FROM dual;

Conversion result:

CREATE SEQUENCE seq1;
SELECT nextval('seq1');

Cursor

You can use the following statement to traverse cursors in Oracle.

Example:

FUNCTION test_func() IS
    Cursor data_cursor IS SELECT * from test1;
BEGIN
    FOR I IN data_cursor LOOP
        Do something with I;
END LOOP;
END;

Conversion result:

CREATE OR REPLACE FUNCTION test_func()
AS $body$
DECLARE
data_cursor cursor for select * from test1;
I record;
BEGIN
    Open data_cursor;
    LOOP
       Fetch data_cursor INTO I;
      If not found then
            Exit;
      End if;
      Do something with I;
    END LOOP;
    Close data_cursor;
END;
$body$
LANGUAGE PLPGSQL;

Cursors with the same name can be opened in recursive functions. This is not supported in AnalyticDB for PostgreSQL. You must use FOR I IN queries.

Example:

FUNCTION test_func(level IN numer) IS
    Cursor data_cursor IS SELECT * from test1;
BEGIN
If level > 5 then
        return;
   End if;

    FOR I IN data_cursor LOOP
        Do something with I;
        test_func(level + 1);
END LOOP;
END;

Conversion result:

CREATE OR REPLACE FUNCTION test_func(level int) returns void
AS $body$
DECLARE
data_cursor cursor for select * from test1;
I record;
BEGIN
    If level > 5 then
        return;
    End if;
    For I in select * from test1 LOOP
      Do something with I;
       PERFORM test_func(level+1);
    END LOOP;
END;
$body$
LANGUAGE PLPGSQL;