AnalyticDB:Oracle Application Migration to AnalyticDB for PostgreSQL

FOR LOOP with REVERSE

PL/SQL and PL/pgSQL handle the REVERSE keyword differently:

• PL/SQL: FOR i IN REVERSE 1..3 counts down from 3 to 1

• PL/pgSQL: FOR i IN REVERSE 1..3 counts down from 1 to 3 (the opposite direction)

Swap the loop boundaries when converting:

Oracle:

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

AnalyticDB for PostgreSQL:

-- Boundaries swapped: 1..3 -> 3..1; DBMS_OUTPUT.PUT_LINE -> RAISE
FOR i IN REVERSE 3..1 LOOP
    RAISE '%', i;
END LOOP;

PRAGMA statements

PRAGMA statements are not supported. Delete all PRAGMA statements.

Transaction control

Functions in AnalyticDB for PostgreSQL do not support BEGIN, COMMIT, or ROLLBACK inside function bodies. Apply one of these strategies:

• Delete the transaction control statements from the function body and include them outside the function call.

• Split the function at each COMMIT or ROLLBACK boundary into separate functions.

EXECUTE (dynamic SQL)

AnalyticDB for PostgreSQL supports dynamic SQL, but with these differences from Oracle:

• The USING syntax is not supported. Concatenate parameters directly into SQL strings.

• Wrap database identifiers with quote_ident and string values with quote_literal.

Oracle:

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

AnalyticDB for PostgreSQL:

-- USING replaced with string concatenation using quote_literal
EXECUTE 'UPDATE employees_temp SET commission_pct = ' || quote_literal(a_null);

PIPE ROW functions

Replace PIPE ROW functions with table functions using RETURNS SETOF <type> and RETURN NEXT.

Oracle:

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

AnalyticDB for PostgreSQL:

-- RECORD type -> CREATE TYPE; PIPE ROW -> RETURN NEXT; PIPELINED -> RETURNS SETOF
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 exceptions.

• After an exception is caught, the transaction cannot be rolled back inside the function. Rollback is only allowed outside user-defined functions.

For supported error codes, see the PostgreSQL error codes reference.

Functions with RETURN and OUT arguments

A function cannot use both a RETURN argument and an OUT argument simultaneously. Convert the RETURN argument to an additional OUT argument, then retrieve the return values using SELECT * FROM test_func(...) INTO rec.

Oracle:

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;

AnalyticDB for PostgreSQL:

-- RETURN value converted to an OUT argument (out_name)
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;

-- Retrieve both output values
SELECT * FROM test_func(1, '1') INTO rec;

Single quotes in dynamic SQL string concatenation

When a string variable contains single quotes (e.g., adb'-'pg), direct concatenation causes a parse error because hyphens are interpreted as operators. Use quote_literal to safely embed the value.

Oracle:

sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = ''' || param2 || ''' AND col3 = 3';

AnalyticDB for PostgreSQL:

-- quote_literal wraps param2 safely, handling embedded quotes
sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = ' || quote_literal(param2) || ' AND col3 = 3';

Days between two timestamps

Oracle:

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

AnalyticDB for PostgreSQL:

-- Use extract() to get integer day count from an interval
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;

RECORD type

Convert Oracle RECORD types to composite types:

Oracle:

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

AnalyticDB for PostgreSQL:

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

Nested tables and variable-size arrays

As PL variables, both NESTED TABLE and VARRAY map to the PostgreSQL ARRAY type.

Oracle:

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;

AnalyticDB for PostgreSQL:

-- TABLE OF -> varchar[]; array indexing: names(i) -> names[i]; FIRST..LAST -> 1..array_length
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 f1();

If the nested table is used as a function return value, use a table function (RETURNS SETOF <type>) instead.

Associative arrays

There is no replacement for Oracle associative arrays in AnalyticDB for PostgreSQL. This construct requires redesign.

Global variables

AnalyticDB for PostgreSQL does not support global variables. Store package-level global variables in a temporary table and define accessor functions.

-- Store global variables in a temporary table
-- Note: The id column is the distribution key and cannot be modified
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);

-- Getter function
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;

-- Setter function
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;

To modify a global variable:

-- Declare tmp_rec record; in the calling function
SELECT * FROM set_variable('g_err_code', 'error'::varchar) INTO tmp_rec;

To read a global variable:

SELECT * FROM get_variable() INTO tmp_rec;
error_code := tmp_rec.g_err_code;

CONNECT BY

The CONNECT BY clause for hierarchical queries has no direct SQL equivalent in AnalyticDB for PostgreSQL. Rewrite it as a PL/pgSQL function that traverses the hierarchy iteratively.

Oracle:

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;

AnalyticDB for PostgreSQL (iterative traversal function):

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 hierarchy level and insert into tmp_curr
        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 nocycle is false, remove already-traversed rows
        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 when no more rows
        EXECUTE 'SELECT count(*) FROM ' || curr_tbl INTO tbl_count;
        EXIT WHEN tbl_count = 0;

        -- Promote current results to result and prev tables
        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;

    FOR rec IN EXECUTE 'SELECT * FROM ' || res_tbl LOOP
        RETURN NEXT rec;
    END LOOP;
    RETURN;
END
$$
LANGUAGE plpgsql;

ROWNUM

Limit result size:

Oracle:

SELECT * FROM t WHERE rownum < 10;

AnalyticDB for PostgreSQL:

SELECT * FROM t LIMIT 10;

Generate row numbers:

Oracle:

SELECT rownum, * FROM t;

AnalyticDB for PostgreSQL:

SELECT row_number() OVER() AS rownum, * FROM t;

DUAL table

Option 1 — Remove `FROM DUAL`:

Oracle:

SELECT sysdate FROM dual;

AnalyticDB for PostgreSQL:

SELECT current_timestamp;

Option 2 — Create a `dual` table:

CREATE TABLE dual (dummy varchar(1));
INSERT INTO dual VALUES ('X');

User-defined functions in SELECT statements

AnalyticDB for PostgreSQL does not allow user-defined functions that contain SQL statements to be called inside SELECT. Doing so causes a segment error:

ERROR: function cannot execute on segment because it accesses relation "public.t2"

Convert such functions to SQL expressions or subqueries.

Oracle:

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;

AnalyticDB for PostgreSQL:

-- Inline the function logic as a join
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.b = t2.a;

OUTER JOIN (+)

Two-table outer join:

Oracle:

SELECT * FROM a, b WHERE a.id = b.id(+);

AnalyticDB for PostgreSQL:

SELECT * FROM a LEFT JOIN b ON a.id = b.id;

Three-table outer join:

Use a Common Table Expression (CTE) to join two tables first, then apply RIGHT OUTER JOIN with coalesce.

Oracle:

SELECT * FROM test1 t1, test2 t2, test3 t3
WHERE t1.col1(+) BETWEEN NVL(t2.col1, t3.col1) AND NVL(t3.col1, t2.col1);

AnalyticDB for PostgreSQL:

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, replace MERGE INTO with INSERT ON CONFLICT. For cases that INSERT ON CONFLICT cannot cover, use stored procedures.

• INSERT ON CONFLICT documentation

• Stored procedures documentation

Sequences

Oracle:

CREATE SEQUENCE seq1;
SELECT seq1.nextval FROM dual;

AnalyticDB for PostgreSQL:

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

Cursors

Basic cursor traversal is supported. Use the OPEN/FETCH/CLOSE pattern:

Oracle:

FUNCTION test_func() IS
    CURSOR data_cursor IS SELECT * FROM test1;
BEGIN
    FOR i IN data_cursor LOOP
        -- process i
    END LOOP;
END;

AnalyticDB for PostgreSQL:

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;
        -- process i
    END LOOP;
    CLOSE data_cursor;
END;
$body$
LANGUAGE PLPGSQL;

Cursors with the same name in recursive functions are not supported. Replace with FOR i IN <query>:

Oracle:

FUNCTION test_func(level IN number) IS
    CURSOR data_cursor IS SELECT * FROM test1;
BEGIN
    IF level > 5 THEN RETURN; END IF;
    FOR i IN data_cursor LOOP
        -- process i
        test_func(level + 1);
    END LOOP;
END;

AnalyticDB for PostgreSQL:

-- Named cursor removed; inline query used instead to support recursion
CREATE OR REPLACE FUNCTION test_func(level int) RETURNS void
AS $body$
DECLARE
    i record;
BEGIN
    IF level > 5 THEN
        RETURN;
    END IF;
    FOR i IN SELECT * FROM test1 LOOP
        -- process i
        PERFORM test_func(level + 1);
    END LOOP;
END;
$body$
LANGUAGE PLPGSQL;