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 Procedural Language (PL), you can use the open-source tool to implement preliminary conversion. You can also use Ora2Pg to convert DDL, view, and packages in -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 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

, 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

(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:

create or replace package pkg is 

end;

To this code below:

create schema pkg;

1. Variables defined in the package:

procedure/function The local variables remain unchanged, and global variables can be saved in ADB for PG using temporary tables. See section 1.4.5 for more information.block for the package

2. The initialization block for the package:

If you can delete it, delete it, if you can't delete it, you can use function wrapper and call the function actively when needed.

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:

FUNCTION test_func (args int) RETURN int is 
var number := 10;
BEGIN
… …
END;
Converted to the function of ADB for PG:
CREATE OR REPLACE FUNCTION pkg. test_func(args int) RETURNS int AS
$$
… …
$$ LANGUAGE plpgsql;

Procedure/Function

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

For example, convert the following:

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;

To this code below:

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;

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:

FOR i IN REVERSE 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
END LOOP;

To the following code:

FOR i IN REVERSE 3..1 LOOP
RAISE '%' ,i;
END LOOP;

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:

EXECUTE 'UPDATE employees_temp SET commission_pct = :x' USING a_null;

To this code below:

EXECUTE 'UPDATE employees_temp SET commission_pct = ' || quote_literal(a_null);

Pipe Row

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

For example, convert the following:

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);

To this code below:

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);

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:

rec := row(i);
return next rec;

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

create or replace function f1(x int) returns setof record as $$    
declare
rec record;
begin
for i in 1..x loop
rec := row(i, i+10);
return next rec;
end loop;
return ;
end
$$language 'plpgsql';

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 .

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:

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;

To this code below:

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 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:

sql_str := 'select * from test1 where col1 = ' || param1 || ' and col2 = '''|| param2 || '''and col3 = 3';

To this code below:

sql_str := 'select * from test1 where col1 = ' || param1 || ' and col2 = '|| quote_literal(param2) || 'and col3 = 3';

Obtain the Days between Two Timestamps

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

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

To this code below:

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

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

For example, convert the following:

TYPE rec IS RECORD (a int, b int);

To this code below:

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

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:

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;

To this code below:

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();

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:

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;

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.

tmp_rec record;

To modify a global variable, use:

select * from set_variable('g_error_code', 'error'::varchar) into tmp_rec;

To obtain a global variable, use:

select * from get_variable() into tmp_rec; error_code := tmp_rec.g_error_code;

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:

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

To this code below:

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 level result and insert it 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 there is a ring, delete the data that has been 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 there is no data
execute 'select count(*) from ' || curr_tbl into tbl_count;
exit when tbl_count = 0;
-- Save the tmp_curr data 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;
-- Return result
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. This is used to limit the size of a result set. This can be replaced by limit.

For example, convert the following:

select * from t where rownum < 10;

To the code below:

select * from t limit 10;

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

For example, convert the following:

select rownum, * from t;

To the code below:

select row_number() over() as rownum, * from t;

DUAL Table

1. Remove DUAL.

For example, convert the following:

select sysdate from dual;

To this code below:

select current_timestamp;

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:

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 solve this problem, convert the user-defined functions in SELECT to SQL expressions or subqueries.

For example, convert the following:

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;

To this code below:

select t1.a, t2.b from t1, t2 where t1.b = t2.a;

(+) 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:

oracle
select * from a,b where a.id=b.id(+)

To this code below:

select * from a left join b on a.id=b.id

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:

Select * from test1 t1, test2 t2, test3 t3 where t1.col1(+) between NVL(t2.col1, t3.col1) and NVL(t3.col1, t2.col1);

To the code below:

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

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.

MERGE INTO test1 t1
USING (SELECT t2.col1 col1, t3.col2 col2,
FROM test2 t2, test3 t3) S
ON S.col1 = 1 and S.col2 = 2
WHEN MATCHED THEN
UPDATE
SET test1.col1 = S.col1+1,
test1.col2 = S.col2+2
WHEN NOT MATCHED THEN
INSERT (col1, col2)
VALUES
(S.col1+1, S.col2+2);

The preceding is converted to following:

Update test1 t1 SET t1.col1 = test2.col1+1, test3.col2 = S.col2+2 where test2.col1 = 1 and test2.col2 = 2;
GET DIAGNOSTICS rowcount := ROW_COUNT;
if rowcount = 0 then
insert into test1 values(test2.col1+1, test3.col2+2);
end if;

Sequence

For example, convert the following:

create sequence seq1; 
select seq1.nextval from dual;

To this code below:

create SEQUENCE seq1;
select nextval('seq1');

Cursor

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

For example, convert the following:

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;

To this code below:

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;
  • 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:

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;

To this code below:

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;

Original Source

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