PostgreSQL to Oracle Migration

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

Databases:

  • PostgreSQL 15.x, 14.x, 13.x, 12.x, 11.x, 10.x and 9.x
  • Oracle 21c, 19c, 18c, 12c and 11g

Tools

Migration Reference

Most Complex Migration Issues

PostgreSQL features that may require significant re-design when migrating to Oracle:

PostgreSQL Oracle
1 Export data to .csv file from a stored procedure using COPY command Using UTL_FILE, DBMS_SQL or cursor

Most Popular Expressions and Queries

Converting most popular expressions and queries:

PostgreSQL Oracle
1 CAST(TIMEOFDAY() AS TIMESTAMP) Get the current date and time SYSTIMESTAMP
2 EXTRACT YEAR * 12 + EXTRACT MONTH Get interval in months MONTHS_BETWEEN function

SQL Language Elements

Converting SQL language elements:

PostgreSQL Oracle
1 SQLERRM Error message SQLERRM
2 SQLSTATE Error code SQLCODE

Data Types

Converting built-in SQL data types:

PostgreSQL Oracle
1 BIGINT 64-bit integer NUMBER(19)
2 BIGSERIAL 64-bit autoincrementing integer Sequence and trigger
3 BIT(n) Fixed-length bit string RAW(n/8)
4 BIT VARYING(n) Variable-length bit string RAW(n/8)
5 BOOLEAN, BOOL True, false or NULL CHAR(1)
6 BYTEA Variable-length binary data, ⇐ 2G BLOB
7 CHARACTER(n), CHAR(n) Fixed-length string, 1 ⇐ n ⇐ 1G CHARACTER(n), CHAR(n)
8 CHARACTER VARYING(n) Variable-length string, 1 ⇐ n ⇐ 1G VARCHAR2(n)
9 DATE Date (year, month and day) DATE Includes time part
10 DECIMAL(p,s), DEC(p,s) Fixed-point number NUMBER(p,s)
11 DOUBLE PRECISION Double-precision floating-point BINARY_DOUBLE
12 FLOAT4 Single-precision floating-point BINARY_FLOAT
13 FLOAT8 Double-precision floating-point BINARY_DOUBLE
14 INTEGER, INT 32-bit integer NUMBER(10)
15 INT2 16-bit integer NUMBER(5)
16 INT4 32-bit integer NUMBER(10)
17 INT8 64-bit integer NUMBER(20)
18 INTERVAL Date and time interval INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
19 INTERVAL unit Date and time interval NUMBER(5)
20 INTERVAL YEAR TO MONTH Date interval INTERVAL YEAR TO MONTH
21 INTERVAL DAY TO HOUR Day and time interval INTERVAL DAY(5) TO SECOND
22 INTERVAL DAY TO MINUTE Day and time interval INTERVAL DAY(5) TO SECOND
23 INTERVAL DAY TO SECOND[(p)] Day and time interval INTERVAL DAY(5) TO SECOND[(p)]
24 INTERVAL HOUR TO MINUTE Time interval INTERVAL DAY(5) TO SECOND
25 INTERVAL HOUR TO SECOND[(p)] Time interval INTERVAL DAY(5) TO SECOND[(p)]
26 INTERVAL MINUTE TO SECOND[(p)] Time interval INTERVAL DAY(5) TO SECOND[(p)]
27 MONEY Currency amount NUMBER(17,2)
28 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
29 REAL Single-precision floating-point BINARY_FLOAT
30 SERIAL 32-bit autoincrementing integer Sequence and trigger
31 SERIAL2 16-bit autoincrementing integer Sequence and trigger
32 SERIAL4 32-bit autoincrementing integer Sequence and trigger
33 SERIAL8 64-bit autoincrementing integer Sequence and trigger
34 SMALLINT 16-bit integer NUMBER(5)
35 SMALLSERIAL 16-bit autoincrementing integer Sequence and trigger
36 TEXT Variable-length character data, ⇐ 1G CLOB
37 TIME(p) Time with fraction TIMESTAMP(p)
38 TIME(p) WITH TIME ZONE Time with fraction and time zone TIMESTAMP(p) WITH TIME ZONE
39 TIMETZ(p)
40 TIMESTAMP(p) Date and time with fraction TIMESTAMP(p)
41 TIMESTAMP(p) WITH TIME ZONE Date and time with time zone TIMESTAMP(p) WITH TIME ZONE
42 TIMESTAMPTZ(p)
43 UUID Universally unique identifier CHAR(36)
44 VARBIT(n) Variable-length bit string RAW(n/8)
45 VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 1G VARCHAR2(n)
46 XML XML data XMLTYPE

Built-in SQL Functions

Converting functions:

PostgreSQL Oracle
1 CAST(number AS VARCHAR) Convert number to string TO_CHAR(number)
2 CAST(datetime AS VARCHAR) Convert datetime to string TO_CHAR(datetime)
3 CAST(string AS INTERVAL) Convert string to interval TO_DSINTERVAL(string)
4 COALESCE(exp, …) Return first non-NULL expression COALESCE(exp, …)
5 CURRENT_TIMESTAMP Get start date and time of the transaction CURRENT_TIMESTAMP
6 DATE('literal') Convert string to date TO_DATE('literal', format)
7 DATE(timestamp) Convert to date, set zero time TRUNC(timestamp)
8 NOW() Get start date and time of the transaction SYSTIMESTAMP
9 OVERLAY(str PLACING sub FROM f) Replace substring User-defined function
10 POSITION(substring IN string) Get position of substring INSTR(string, substring)
11 SPLIT_PART(string, delimiter, n) Get n-th item from delimited string User-defined function
12 SUBSTRING(string, start, len) Get a substring of string SUBSTR(string, start, len)
13 TIMEOFDAY() Get current date and time as string TO_CHAR(SYSTIMESTAMP)

SELECT Statement

Converting SQL SELECT statement and its clauses:

PostgreSQL Oracle
1 FROM table AS alias Optional AS keyword in FROM FROM table alias AS not allowed, removed

CREATE FUNCTION Statement

Converting CREATE FUNCTION statement that defines a user-defined function or stored procedure in PostgreSQL:

PostgreSQL Oracle
1 CREATE OR REPLACE FUNCTION CREATE OR REPLACE FUNCTION | PROCEDURE
2 param datatype(length) Parameter definition param datatype No length and precision
3 () Function with empty parameters Not allowed, () is removed
4 RETURNS datatype Returned data type RETURN datatype
5 RETURNS void No value returned Converted to CREATE PROCEDURE
6 $$ or $body$ Start of the body Removed
7 DECLARE keyword Before variables declaration Removed
8 SELECT exp INTO var Assign a value SELECT exp INTO var FROM dual
9 TRUNCATE TABLE name Truncate a table EXECUTE IMMEDIATE 'TRUNCATE TABLE name'
10 $$ LANGUAGE plpgsql End of the body /
11 VOLATILE Function attribute Removed
12 COST num Execution cost Removed

For more information, see CREATE FUNCTION Conversion Examples and Conversion of SQL and PL/pgSQL Statements.

SQL Statements

Converting SQL statements:

PostgreSQL Oracle
1 CREATE SCHEMA name Create schema CREATE USER name IDENTIFIED BY pwd
2 EXECUTE (sql) Execute dynamic SQL EXECUTE IMMEDIATE sql
3 PERFORM name(params) Execute a function name(params)
4 RAISE NOTICE 'format', params Output a message DBMS_OUTPUT.PUT_LINE(concatenated_params)
5 UPDATE table FROM table2, ... Update-from UPDATE table WHERE EXISTS

PL/pgSQL Statements

Converting PL/pgSQL statements and clauses:

PostgreSQL Oracle
1 var datatype = value Variable initialization var datatype := value
2 var = value Variable assignment var := value
3 FETCH [FROM] cursor INTO Fetch a cursor FETCH cursor INTO FROM keyword removed
4 GET DIAGNOSTICS var = ROW_COUNT Get affected rows var := SQL%ROWCOUNT
5 IF NOT FOUND THEN EXIT Exit a cursor loop EXIT WHEN cursor%NOTFOUND