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:
Technical information on migration from PostgreSQL to Oracle.
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 |
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 |
Converting SQL language elements:
PostgreSQL | Oracle | ||
1 | SQLERRM | Error message | SQLERRM |
2 | SQLSTATE | Error code | SQLCODE |
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 |
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) |
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 |
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.
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 |
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 |