Oracle to PostgreSQL Migration

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

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

Databases:

  • Oracle 21c, 19c, 18c, 12c, 11g, 10g and 9i
  • PostgreSQL 15.x, 14.x, 13.x, 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

Operators:

Oracle PostgreSQL
1 '0' < 1 String and integer comparison '0' < 1 Cast may be required

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

Built-in SQL Functions

Converting string functions:

Oracle PostgreSQL
1 INSTR(str, substr) Get position of substring POSITION(substr IN str) param order
INSTR(str, substr, pos) User-defined function
INSTR(str, substr, pos, num)
2 LISTAGG(exp, delim)... Aggregate concatenation STRING_AGG(exp, delim)
3 TO_CHAR(expr, format) Convert to string TO_CHAR(expr, format)
TO_CHAR(expr) expr::text

Converting date and time functions:

Oracle PostgreSQL
1 FROM_TZ(timestamp, timezone) Setting timezone for timestamp timestamp AT TIME ZONE timezone
2 SYSDATE Get current date and time (up to seconds) CURRENT_TIMESTAMP(0)
3 SYSTIMESTAMP Get the current timestamp CURRENT_TIMESTAMP
4 TRUNC(datetime) Truncate datetime to day DATE_TRUNC('day', datetime)

Converting JSON functions:

Oracle PostgreSQL
1 JSON_TABLE(exp, jpath, columns ...) Extract rows from JSON array LATERAL and JSON Expressions
2 JSON_VALUE(json, jpath) Extract JSON value JSONB_PATH_QUERY(json, jpath)

Converting XML functions:

Oracle PostgreSQL
1 EXTRACT(xml_content, xpath) Extract from XML content EXTRACT(xml_content, xpath)
2 XMLAGG(element [ORDER BY order]) Create XML from elements XMLAGG(element [ORDER BY order])
3 XMLTYPE(content) Get XML value from string content XMLPARSE(CONTENT | DOCUMENT content)

SELECT Statement

Converting SQL queries:

Oracle PostgreSQL
1 DUAL table A single row, single column dummy table FROM clause can be omitted, DUAL removed
2 FROM (SELECT …) Optional alias for subquery FROM (SELECT …) s Alias required
3 SELECT … MINUS SELECT … Exclude rows returned by 2nd query SELECT … EXCEPT SELECT …
4 CONNECT BY PRIOR Hierarchical queries Recursive Common Table Expressions (CTE)

CREATE SEQUENCE Statement

Converting sequences:

Oracle PostgreSQL
1 CREATE SEQUENCE seqname CREATE SEQUENCE [IF NOT EXISTS] seqname
2 INCREMENT BY num Positive or negative increment, default is 1 INCREMENT BY num
3 START WITH num Initial value START [WITH] num
4 MAXVALUE num Maximum value is num MAXVALUE num
NOMAXVALUE System limit NO MAXVALUE
5 MINVALUE num Minimum value is num MINVALUE num
NOMINVALUE System limit NO MINVALUE
6 CYCLE Reuse values after reaching the limit CYCLE
NOCYCLE No reuse, this is default NO CYCLE
7 CACHE num Cache num values, default is 20 CACHE num Default is 1
NOCACHE Values are not preallocated Option not supported, commented
8 ORDER Guarantee numbers in order of requests Option not supported, commented
NOORDER No guarantee, this is default Option not supported, removed as it is default

By default MAXVALUE is 9999999999999999999999999999 in Oracle, and this value is out of range for type BIGINT in PostgreSQL, so MAXVALUE is commented in such case.

CREATE FUNCTION Statement

Converting CREATE FUNCTION statement from Oracle to PostgreSQL:

Oracle PostgreSQL
1 CREATE OR REPLACE FUNCTION name CREATE OR REPLACE FUNCTION name
2 (param IN | OUT | IN OUT datatype DEFAULT default, …) (param IN | OUT | INOUT datatype DEFAULT default, …)
3 RETURN data_type RETURNS data_type
4 DETERMINISTIC Not supported, removed
5 IS | AS AS $$
6 function_body function_body
7 END proc_name; END;
8 / $$ LANGUAGE plpgsql;

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, …)

CREATE TRIGGER Statement

Converting triggers:

Oracle PostgreSQL
1 CREATE TRIGGER Create a trigger CREATE FUNCTION ... RETURNS TRIGGER
CREATE TRIGGER … EXECUTE FUNCTION
2 schema.name Trigger name name Cannot be schema-qualified,
uses table schema
3 REFERENCING NEW AS …
OLD AS …
Column reference predicates REFERENCING NEW TABLE AS …
OLD TABLE AS …
4 :NEW.column New column value reference NEW.column
5 :OLD.column Old column value reference OLD.column
6 INSERTING Conditional predicate for INSERT operation TG_OP = 'INSERT'
7 UPDATING Conditional predicate for UPDATE operation TG_OP = 'UPDATE'

DROP TRIGGER statement:

Oracle PostgreSQL
1 DROP TRIGGER schema.name DROP TRIGGER [IF EXISTS] name ON table

CREATE TYPE Statement

Converting CREATE TYPE statement:

Oracle PostgreSQL
1 CREATE OR REPLACE TYPE name AS OBJECT (col1 type, …) Object type CREATE TYPE name AS (col1 type, …)
2 CREATE OR REPLACE TYPE name IS TABLE OF type Table type CREATE TYPE name AS (row type[])

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, …

Error handling:

Oracle PostgreSQL
1 RAISE_APPLICATION_ERROR(code, message) Raise an user error RAISE EXCEPTION '%s', message
USING ERRCODE = code
2 SQLCODE Error code SQLSTATE
3 SQLERRM Error message SQLERRM

Built-in PL/SQL Packages

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

DBMS_LOB Package:

Oracle PostgreSQL
1 DBMS_LOB.APPEND(dest_lob, src_lob) Append a LOB value dest_lob := dest_lob || src_lob

DBMS_OUTPUT Package:

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

SQL*Plus Commands

Converting Oracle SQL*Plus commands:

Oracle PostgreSQL
1 CALL proc_name(name => value, …) Execute a stored procedure CALL proc_name(name => value, …)
EXECUTE proc_name(name => value, …)
2 SET SERVEROUTPUT ON | OFF Enable output for DBMS_OUTPUT Commented