AnalyticDB for PostgreSQL:Migrate data from a self-managed 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.

Use the Orafce extension

AnalyticDB for PostgreSQL provides the Orafce extension, which 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.

SELECT add_months(current_date, 2);

add_months
------------
2019-08-31
(1 row)

SELECT last_day('2018-06-01');

 last_day
------------
2018-06-30
(1 row)

SELECT next_day(current_date, 'FRIDAY');

 next_day
------------
2019-07-05
(1 row)

SELECT TRUNC('2019-12-11'::timestamp);

        trunc
------------------------
2019-12-11 00:00:00+08
(1 row)

SELECT TRUNC('2019-12-11'::timestamp,'Y');

        trunc
------------------------
2019-01-01 00:00:00+08

SELECT round('2018-10-06 13:11:11'::timestamp, 'YEAR');

        round
------------------------
2019-01-01 00:00:00+08
(1 row)

SELECT round('2018-10-06 13:11:11'::timestamp);

        round
------------------------
2018-10-07 00:00:00+08
(1 row)

SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'));

  round
------------
2000-02-27
(1 row)

SELECT instr('Greenplum', 'e',1);

instr
-------
    3
(1 row)

SELECT instr('Greenplum', 'e');

instr
-------
    3
(1 row)

SELECT plvstr.rvrs('adb4pg', 5,6);

reverse
---------
gp
(1 row)

SELECT plvstr.rvrs('adb4pg', 4);

reverse
---------
gp4
(1 row)

SELECT plvstr.rvrs('adb4pg');

reverse
---------
gp4bda
(1 row)

SELECT concat('adb','4pg');

concat
--------
adb4pg
(1 row)

SELECT listagg(t) FROM (VALUES('abc'), ('def')) as l(t);

listagg
---------
abcdef
(1 row)

SELECT listagg(t, '.') FROM (VALUES('abc'), ('def')) as l(t);

listagg
---------
abc.def
(1 row)

SELECT  dump('adb4pg');

                dump
---------------------------------------
Typ=705 Len=7: 97,100,98,52,112,103,0
(1 row)

CREATE TABLE t1 (name text);
INSERT INTO t1 VALUES('Anne'), ('anne'), ('Bob'), ('bob');

SELECT substr('adb4pg', 5,6);

substr
--------
pg
(1 row)

SELECT  substr('adb4pg'::varchar2, 5,6);

substr
--------
pg
(1 row)

SELECT substr('adb4pg'::varchar2, 4) ;

substr
--------
4pg
(1 row)

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

The following functions of Oracle are supported by AnalyticDB for PostgreSQL by default. You do not need to install Orafce to use these functions.

SELECT sinh(0.1);

      sinh
-------------------
0.100166750019844
(1 row)

SELECT  tanh(3);

      tanh
------------------
0.99505475368673
(1 row)

SELECT cosh(0.2);

      cosh
------------------
1.02006675561908
(1 row)

CREATE TABLE t1(id int, name varchar(20));
INSERT INTO t1 values(1,'alibaba');
INSERT INTO t1 values(2,'adb4pg');

Mappings between Oracle data types and AnalyticDB for PostgreSQL data types

Mappings between Oracle functions and AnalyticDB for PostgreSQL functions

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 $\$ ... $\$ 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, or 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

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

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

• Handle exceptions
  • 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 more information about error codes that are supported by AnalyticDB for PostgreSQL, visit the official website of PostgreSQL.
• Functions that contain return and out parameters

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

  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, - is 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';
    
    SLECT 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 select * from set_variable('g_error_code', 'error'::varchar) into tmp_rec;.

  To obtain a global variable, execute select * from get_variable() into tmp_rec; error_code := tmp_rec.g_error_code;.

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;
  
      -- Return results.
      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

  To convert the MERGE INTO syntax, execute an UPDATE statement in AnalyticDB for PostgreSQL, and use the GET DIAGNOSTICS rowcount := ROW_COUNT; statement to obtain the number of updated rows. If the number of updated rows is 0, execute an 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);

  Conversion result:

  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

  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;