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 |
Character data types:
Oracle | PostgreSQL | |||
1 | CHAR(n), CHARACTER(n) | Fixed-length string, 1 ⇐ n ⇐ 2000 | CHAR(n), CHARACTER(n) | |
2 | CLOB | Character large object, 4 GB | TEXT | |
3 | LONG | Character data, 2 GB | TEXT | |
4 | NCHAR(n) | Fixed-length UTF-8 string, 1 ⇐ n ⇐ 2000 | CHAR(n) | |
5 | NCHAR VARYING(n) | Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
6 | NCLOB | Variable-length Unicode string, ⇐ 4 GB | TEXT | |
7 | NVARCHAR2(n) | Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
8 | VARCHAR(n) | Synonym for VARCHAR2 | VARCHAR(n) | |
9 | VARCHAR2(n) | Variable-length string, 1 ⇐ n ⇐ 32767 | VARCHAR(n) |
Numeric data types:
Oracle | PostgreSQL | |||
1 | BINARY_FLOAT | 32-bit floating-point number | REAL | |
2 | BINARY_DOUBLE | 64-bit floating-point number | DOUBLE PRECISION | |
3 | DECIMAL(p,s), DEC(p,s) | Fixed-point number | DECIMAL(p,s), DEC(p,s) | |
4 | DOUBLE PRECISION | Synonym for FLOAT(126) | DOUBLE PRECISION | |
5 | FLOAT(p) | Floating-point number | DOUBLE PRECISION | |
6 | INTEGER, INT | 38 digits integer | DECIMAL(38) | |
7 | 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 | |||
Large integer, 19 ⇐ p ⇐ 38 | DECIMAL(p) | |||
8 | NUMBER(p,s) | Fixed-point number, s > 0 | DECIMAL(p,s) | |
9 | NUMBER, NUMBER(*) | Exact floating-point number | DECIMAL | DOUBLE PRECISION |
10 | NUMERIC(p,s) | Fixed-point number | NUMERIC(p,s) | |
11 | REAL | Synonym for FLOAT(63) | REAL | |
12 | SMALLINT | 38 digits integer | DECIMAL(38) |
Date and time data types:
Oracle | PostgreSQL | |||
1 | DATE | Date and time | TIMESTAMP(0) | |
2 | INTERVAL YEAR(p) TO MONTH | Date interval | INTERVAL YEAR TO MONTH | |
3 | INTERVAL DAY(p) TO SECOND(s) | Day and time interval | INTERVAL DAY TO SECOND(s) | |
4 | TIMESTAMP(p) | Date and time with fraction | TIMESTAMP(p) | |
5 | TIMESTAMP(p) WITH TIME ZONE | Date and time with fraction and time zone | TIMESTAMP(p) WITH TIME ZONE |
Binary data types:
Oracle | PostgreSQL | |||
1 | BLOB | Binary large object, ⇐ 4G | BYTEA | |
2 | LONG RAW | Binary data, ⇐ 2G | BYTEA | |
3 | RAW(n) | Variable-length binary string, 1 ⇐ n ⇐ 2000 | BYTEA |
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 | UROWID(n) | Logical row addresses, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
5 | 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) | ||||
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) |
Adding constraints:
Oracle | PostgreSQL | |||
1 | ALTER TABLE tab ADD [CONSTRAINT cns] … | Named constraint | ALTER TABLE tab ADD [CONSTRAINT cns] … | |
2 | PRIMARY KEY (col, …) | Primary key | PRIMARY KEY (col, …) | |
3 | UNIQUE (col, …) | Unique key | UNIQUE (col, …) | |
4 | CHECK (condition) | Check condition | CHECK (condition) | |
5 | FOREIGN KEY (col, …) REFERENCES tab2 (col, …) | Foreign key | FOREIGN KEY (col, …) REFERENCES tab2 (col, …) |
Converting comments on objects:
Oracle | PostgreSQL | |||
1 | COMMENT ON COLUMN IS 'text' | Comment on column | COMMENT ON COLUMN IS 'text' |
Converting indexes:
Oracle | PostgreSQL | |||
1 | CREATE [UNIQUE] INDEX name ON tab | Create an index | CREATE [UNIQUE] INDEX name ON tab cols | |
2 | (col [ASC | DESC], …) | Index columns | (col [ASC | DESC], …) |
See also Storage clauses conversion in CREATE TABLE.
Converting table definitions:
Oracle | PostgreSQL | |||
1 | CREATE TABLE name | Create a table | CREATE TABLE [IF NOT EXISTS] name | |
2 | colname data_type NOT NULL | NOT NULL column constraint | colname data_type NOT NULL |
Storage clauses:
Oracle | PostgreSQL | |||
1 | LOGGING | Log operations on table | Removed, logged by default |
For more details, see Data Types conversion.
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; |
Converting stored procedures:
Oracle | PostgreSQL | ||
1 | CREATE OR REPLACE PROCEDURE name | CREATE OR REPLACE PROCEDURE name | |
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 | 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 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 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[]) |
Converting views:
Converting inserting rows:
Oracle | PostgreSQL | |||
1 | INSERT INTO name alias | Insert rows | INSERT INTO name AS alias |
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) |
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 | IF condition THEN statements END IF | IF statement | IF condition THEN statements END IF |
2 | LOOP statements END LOOP | A loop statement | LOOP statements END LOOP |
3 | 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 |