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).
Databases:
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 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 |
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) | User-defined function |
Converting date and time functions:
Oracle | PostgreSQL | |||
1 | FROM_TZ(timestamp, timezone) | Setting timezone for timestamp | timestamp AT TIME ZONE timezone | |
2 | SYSTIMESTAMP | Get the current timestamp | CURRENT_TIMESTAMP |
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) |
Converting SQL queries:
Converting sequences:
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.
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 | |
4 | IS | AS | AS $$ | |
5 | function_body | function_body | |
6 | END proc_name; | END; | |
7 | / | $$ LANGUAGE plpgsql; |
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, …) |
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 ![]() |
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 code block:
Oracle | PostgreSQL | ||
1 | [DECLARE declarations] BEGIN statements END; / | DO $$ [DECLARE declarations] BEGIN statements END; $$; |
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 |
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 |
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 |