Oracle to PostgreSQL Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, packages, triggers, queries and SQL scripts from Oracle to PostgreSQL.

We also help convert embedded SQL statements in C/C++ (ODBC, Pro*C, OCI, OCCI), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET, Perl, PHP, Python, Linux shell and other applications.

  • SQLines Data - Data transfer, schema migration and validation tool
  • SQLines SQL Converter - SQL scripts conversion tool

Databases:

  • Oracle 19c, 18c, 12c, 11g, 10g and 9i
  • PostgreSQL 12.x, 11.x, 10.x and 9.x

Migration Reference

SQL Language Elements

Converting SQL and PL/SQL language elements from Oracle to PostgreSQL:

Oracle PostgreSQL
1 column%TYPE Derived data type attribute column%TYPE
2 SYS_REFCURSOR Cursor reference REFCURSOR
3 SQL%ROWCOUNT Number of rows affected GET DIAGNOSTICS var = ROW_COUNT
4 SQL%NOTFOUND No rows affected NOT FOUND
5 SQL%FOUND One or more rows affected FOUND

Data Types

Data type mapping:

Oracle PostgreSQL
1 BINARY_FLOAT 32-bit floating-point number REAL
2 BINARY_DOUBLE 64-bit floating-point number DOUBLE PRECISION
3 BLOB Binary large object, ⇐ 4G BYTEA
4 CHAR(n), CHARACTER(n) Fixed-length string, 1 ⇐ n ⇐ 2000 CHAR(n), CHARACTER(n)
5 CLOB Character large object, ⇐ 4G TEXT
6 DATE Date and time TIMESTAMP(0)
7 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
8 DOUBLE PRECISION Floating-point number DOUBLE PRECISION
9 FLOAT(p) Floating-point number DOUBLE PRECISION
10 INTEGER, INT 38 digits integer DECIMAL(38)
11 INTERVAL YEAR(p) TO MONTH Date interval INTERVAL YEAR TO MONTH
12 INTERVAL DAY(p) TO SECOND(s) Day and time interval INTERVAL DAY TO SECOND(s)
13 LONG Character data, ⇐ 2G TEXT
14 LONG RAW Binary data, ⇐ 2G BYTEA
15 NCHAR(n) Fixed-length UTF-8 string, 1 ⇐ n ⇐ 2000 CHAR(n)
16 NCHAR VARYING(n) Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 VARCHAR(n)
17 NCLOB Variable-length Unicode string, ⇐ 4G TEXT
18 NUMBER(p,0), NUMBER(p) 8-bit integer, 1 <= p < 3 SMALLINT
16-bit integer, 3 <= p < 5 SMALLINT
32-bit integer, 5 <= p < 9 INT
64-bit integer, 9 <= p < 19 BIGINT
Fixed-point number, 19 <= p <= 38 DECIMAL(p)
19 NUMBER(p,s) Fixed-point number, s > 0 DECIMAL(p,s)
20 NUMBER, NUMBER(*) Floating-point number DOUBLE PRECISION
21 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
22 NVARCHAR2(n) Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 VARCHAR(n)
23 RAW(n) Variable-length binary string, 1 ⇐ n ⇐ 2000 BYTEA
24 REAL Floating-point number DOUBLE PRECISION
25 SMALLINT 38 digits integer DECIMAL(38)
26 TIMESTAMP(p) Date and time with fraction TIMESTAMP(p)
27 TIMESTAMP(p) WITH TIME ZONE Date and time with fraction and time zone TIMESTAMP(p) WITH TIME ZONE
28 UROWID(n) Logical row addresses, 1 ⇐ n ⇐ 4000 VARCHAR(n)
29 VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 4000 VARCHAR(n)
30 VARCHAR2(n) Variable-length string, 1 ⇐ n ⇐ 4000 VARCHAR(n)

Other data types:

Oracle PostgreSQL
1 BFILE Pointer to binary file, ⇐ 4G VARCHAR(255)
2 ROWID Physical row address CHAR(10)
3 SYS_REFCURSOR Cursor reference REFCURSOR
4 XMLTYPE XML data XML

CREATE PROCEDURE Statement

Converting CREATE PROCEDURE statement from Oracle to PostgreSQL:

Oracle PostgreSQL
1 CREATE OR REPLACE PROCEDURE CREATE OR REPLACE FUNCTION
2 param IN | OUT | IN OUT datatype DEFAULT default param IN | OUT | INOUT datatype DEFAULT default
param OUT SYS_REFCURSOR param REFCURSOR ... RETURNS REFCURSOR
3 No () if procedure is without parameters Empty () are required
4 IS | AS RETURNS VOID AS $$
5 procedure_body procedure_body
6 END proc_name; END;
7 / $$ LANGUAGE plpgsql;

Procedure calls:

Oracle PostgreSQL
1 proc_name(param, …) PERFORM proc_name(param, …) From a PL/pgSQL block
SELECT proc_name(param, …) Standalone call
2 proc_name(name => value, …) Named parameters proc_name(name := value, …)

Anonymous Block

Anonymous code block:

Oracle PostgreSQL
1 [DECLARE declarations] BEGIN statements END; / DO $$ [DECLARE declarations] BEGIN statements END; $$;

PL/SQL Statements

Cursor operations:

Oracle PostgreSQL
1 cur SYS_REFCURSOR Reference cursor declaration cur REFCURSOR = 'cur'
2 CURSOR cur IS select_stmt Cursor declaration cur CURSOR FOR select_stmt
3 FOR rec IN cur LOOP … END LOOP Fetch data from cursor FOR rec IN cur LOOP … END LOOP
4 FOR rec IN (select_stmt) LOOP … Fetch data from query DECLARE rec RECORD;
FOR rec IN (select_stmt) LOOP …
5 OPEN cur Open a cursor OPEN cur
6 FETCH cur INTO v1, v2, … Fetch data FETCH cur INTO v1, v2, …
7 EXIT WHEN cur%NOTFOUND Terminate loop when no row found EXIT WHEN NOT FOUND
8 CLOSE cur Close a cursor CLOSE cur

Flow-of-control statements:

Oracle PostgreSQL
1 LOOP statements END LOOP A loop statement LOOP statements END LOOP
2 EXIT WHEN condition Exit from loop when condition is true EXIT WHEN condition

Dynamic SQL execution:

Oracle PostgreSQL
1 EXECUTE IMMEDIATE sql_string [USING …] EXECUTE sql_string [USING …]
2 :1, :2, … Referencing parameters in SQL string $1, $2, …

Built-in PL/SQL Packages

Converting built-in PL/SQL packages from Oracle to PostgreSQL:

Oracle PostgreSQL
1 DBMS_OUTPUT.PUT_LINE(text) Output a message RAISE NOTICE '%', text

SQL*Plus Commands

Converting Oracle SQL*Plus commands from Oracle to PostgreSQL:

Oracle PostgreSQL
1 SET SERVEROUTPUT ON | OFF Enable output for DBMS_OUTPUT Commented