Differences

This shows you the differences between two versions of the page.

oracle-to-postgresql [December 21, 2017 2:33 pm]
sqlines
oracle-to-postgresql [January 11, 2021 12:26 am] (current)
sqlines
Line 1: Line 1:
====== Oracle to PostgreSQL Migration ====== ====== Oracle to PostgreSQL Migration ======
-SQLines open source tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, packages, triggers, queries and SQL scripts from Oracle to PostgreSQL:+SQLines provides tools and services 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.
  * [[/sqldata/oracle-to-postgresql|SQLines Data]] - Data transfer, schema migration and validation tool   * [[/sqldata/oracle-to-postgresql|SQLines Data]] - Data transfer, schema migration and validation tool
Line 8: Line 8:
**Databases:** **Databases:**
-  * Oracle 12c, 11g, 10g and 9i +  * Oracle 19c, 18c, 12c, 11g, 10g and 9i 
-  * PostgreSQL 10.x and 9.x  +  * PostgreSQL 13.x, 12.x, 11.x, 10.x and 9.x  
=====  Migration Reference ===== =====  Migration Reference =====
-  * [[/oracle-to-postgresql#data-types|Data Types]] +  * [[/oracle-to-postgresql#sql-language-elements|SQL Language Elements]] 
 +  * [[/oracle-to-postgresql#data-types|Data Types]] 
 +  * [[/oracle-to-postgresql#create-sequence-statement|CREATE SEQUENCE]] 
 +  * [[/oracle-to-postgresql#create-function-statement|CREATE FUNCTION]]  
 +  * [[/oracle-to-postgresql#create-procedure-statement|CREATE PROCEDURE]] 
 +  * [[/oracle-to-postgresql#anonymous-block|Anonymous Block]] 
 +  * [[/oracle-to-postgresql#plsql-statements|PL/SQL Statements]] 
 +  * [[/oracle-to-postgresql#built-in-plsql-packages|Built-in PL/SQL Packages]] 
 +  * [[/oracle-to-postgresql#sql-plus-commands|SQL*Plus Commands]] 
 + 
 +===== 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 |
===== Data Types ===== ===== Data Types =====
Line 21: Line 40:
| | **Oracle** || **PostgreSQL** || | | **Oracle** || **PostgreSQL** ||
-| 1 | BFILE | Pointer to binary file, <= 4G | VARCHAR(255) || +| 1 | BINARY_FLOAT | 32-bit floating-point number | REAL || 
-| 2 | BINARY_FLOAT | 32-bit floating-point number | REAL || +| 2 | BINARY_DOUBLE | 64-bit floating-point number | DOUBLE PRECISION || 
-| 3 | BINARY_DOUBLE | 64-bit floating-point number | DOUBLE PRECISION || +| 3 | BLOB | Binary large object, <= 4G {{:exclamation.png|}} | BYTEA || 
-| 4 | BLOB | Binary large object, <= 4G {{:exclamation.png|}} | BYTEA || +| 4 | CHAR(//n//), CHARACTER(//n//)  | Fixed-length string, 1 <= //n// <= 2000 | CHAR(//n//), CHARACTER(//n//) || 
-| 5 | CHAR(//n//), CHARACTER(//n//)  | Fixed-length string, 1 <= //n// <= 2000 | CHAR(//n//), CHARACTER(//n//) || +| 5 | [[oracle/datatypes/clob|CLOB]] | Character large object, <=  4G {{:exclamation.png|}} | TEXT || 
-| 6 | [[oracle/datatypes/clob|CLOB]] | Character large object, <=  4G {{:exclamation.png|}} | TEXT || +| 6 | [[oracle/datatypes/date|DATE]]  | Date and time | TIMESTAMP(0) || 
-| 7 | [[oracle/datatypes/date|DATE]]  | Date and time | TIMESTAMP(0) || +| 7 | DECIMAL(//p,s//), DEC(//p,s//) | Fixed-point number | DECIMAL(//p,s//), DEC(//p,s//) || 
-| 8 | DECIMAL(//p,s//), DEC(//p,s//) | Fixed-point number | DECIMAL(//p,s//), DEC(//p,s//) || +| 8 | DOUBLE PRECISION | Floating-point number | DOUBLE PRECISION || 
-| 9 | DOUBLE PRECISION | Floating-point number | DOUBLE PRECISION || +| 9 | FLOAT(//p//) | Floating-point number | DOUBLE PRECISION || 
-| 10 | FLOAT(//p//) | Floating-point number | DOUBLE PRECISION || +| 10 | INTEGER, INT | 38 digits integer | DECIMAL(38) || 
-| 11 | INTEGER, INT | 38 digits integer | DECIMAL(38) || +| 11 | INTERVAL YEAR(//p//) TO MONTH | Date interval | INTERVAL YEAR TO MONTH || 
-| 12 | 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 | INTERVAL DAY(//p//) TO SECOND(//s//) | Day and time interval | INTERVAL DAY TO SECOND(//s//) || +| 13 | LONG | Character data, <= 2G {{:exclamation.png|}} | TEXT || 
-| 14 | LONG | Character data, <= 2G {{:exclamation.png|}} | TEXT || +| 14 | LONG RAW | Binary data, <= 2G | BYTEA || 
-| 15 | LONG RAW | Binary data, <= 2G | BYTEA || +| 15 | NCHAR(//n//) | Fixed-length UTF-8 string, 1 <= //n// <= 2000 | CHAR(//n//) {{:exclamation.png|}} || 
-| 16 | NCHAR(//n//) | Fixed-length UTF-8 string, 1 <= //n// <= 2000 | CHAR(//n//) {{:exclamation.png|}} || +| 16 | NCHAR VARYING(//n//) | Varying-length UTF-8 string, 1 <= //n// <= 4000 | VARCHAR(//n//) {{:exclamation.png|}} || 
-| 17 | NCHAR VARYING(//n//) | Varying-length UTF-8 string, 1 <= //n// <= 4000 | VARCHAR(//n//) {{:exclamation.png|}} || +| 17 | NCLOB | Variable-length Unicode string, <= 4G {{:exclamation.png|}} | TEXT || 
-| 18 | NCLOB | Variable-length Unicode string, <= 4G {{:exclamation.png|}} | TEXT || +| 18 | NUMBER(//p//,0),  NUMBER(//p//) | 8-bit integer, 1 %%<=%% //p// < 3  | SMALLINT ||
-| 19 | NUMBER(//p//,0),  NUMBER(//p//) | 8-bit integer, 1 %%<=%% //p// < 3  | SMALLINT ||+
|:::|:::| 16-bit integer, 3 %%<=%% //p// < 5 | SMALLINT || |:::|:::| 16-bit integer, 3 %%<=%% //p// < 5 | SMALLINT ||
|:::|:::| 32-bit integer, 5 %%<=%% //p// < 9 | INT || |:::|:::| 32-bit integer, 5 %%<=%% //p// < 9 | INT ||
|:::|:::| 64-bit integer, 9 %%<=%% //p// < 19 | BIGINT || |:::|:::| 64-bit integer, 9 %%<=%% //p// < 19 | BIGINT ||
|:::|:::| Fixed-point number, 19 %%<=%% //p// %%<=%% 38 | DECIMAL(//p//) || |:::|:::| Fixed-point number, 19 %%<=%% //p// %%<=%% 38 | DECIMAL(//p//) ||
-| 20 | NUMBER(//p,s//) | Fixed-point number, s > 0 | DECIMAL(//p,s//) || +| 19 | NUMBER(//p,s//) | Fixed-point number, s > 0 | DECIMAL(//p,s//) || 
-| 21 | NUMBER, NUMBER(*) | Floating-point number | DOUBLE PRECISION || +| 20 | NUMBER, NUMBER(*) | Floating-point number | DOUBLE PRECISION || 
-| 22 | NUMERIC(//p,s//) | Fixed-point number | NUMERIC(//p,s//) || +| 21 | NUMERIC(//p,s//) | Fixed-point number | NUMERIC(//p,s//) || 
-| 23 | NVARCHAR2(//n//) | Varying-length UTF-8 string, 1 <= //n// <= 4000 | VARCHAR(//n//) {{:exclamation.png|}} || +| 22 | NVARCHAR2(//n//) | Varying-length UTF-8 string, 1 <= //n// <= 4000 | VARCHAR(//n//) {{:exclamation.png|}} || 
-| 24 | [[oracle/datatypes/raw|RAW(n)]] | Variable-length binary string, 1 <= n <= 2000 | BYTEA || +| 23 | [[oracle/datatypes/raw|RAW(n)]] | Variable-length binary string, 1 <= n <= 2000 | BYTEA || 
-| 25 | REAL | Floating-point number | DOUBLE PRECISION || +| 24 | REAL | Floating-point number | DOUBLE PRECISION || 
-| 26 | ROWID | Physical row address | CHAR(10) || +| 25 | SMALLINT | 38 digits integer | DECIMAL(38) || 
-| 27 | SMALLINT | 38 digits integer | DECIMAL(38) || +| 26 | TIMESTAMP(//p//) | Date and time with fraction | TIMESTAMP(//p//) || 
-| 28 | 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 || 
-| 29 | 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//) || 
-| 30 | UROWID(//n//) | Logical row addresses, 1 <= //n// <= 4000 | VARCHAR(//n//) || +| 29 | VARCHAR(//n//) | Variable-length string, 1 <= //n// <= 4000 | VARCHAR(//n//) || 
-| 31 | VARCHAR(//n//) | Variable-length string, 1 <= //n// <= 4000 | VARCHAR(//n//) || +| 30 | VARCHAR2(//n//) | Variable-length string, 1 <= //n// <= 4000 | VARCHAR(//n//) || 
-| 32 | VARCHAR2(//n//) | Variable-length string, 1 <= //n// <= 4000 | VARCHAR(//n//) || + 
-| 33 | XMLTYPE | XML data | XML ||+ 
 +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 || 
 + 
 + 
 +===== 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  {{:exclamation.png|}} || 
 +| 8 | ORDER | Guarantee numbers in order of requests | Option not supported, commented  {{:exclamation.png|}} || 
 +|:::| 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// || RETURN**S** //data_type// |  
 +| 4 | DETERMINISTIC || Not supported, removed |  
 +| 4 | IS %%|%% AS || AS $$ | 
 +| 5 | //function_body// || //function_body// | 
 +| 6 | END //proc_name//; || END; | 
 +| 7 | / || $$ LANGUAGE plpgsql; | 
 + 
 +===== CREATE PROCEDURE Statement ===== 
 + 
 +Converting CREATE PROCEDURE statement from Oracle to PostgreSQL: 
 + 
 +| | **Oracle** || **PostgreSQL** | 
 +| 1 | CREATE OR REPLACE PROCEDURE || CREATE OR REPLACE FUNCTION {{:exclamation.png|}} | 
 +| 2 | //param// IN %%|%% OUT %%|%% IN OUT //datatype// DEFAULT //default// || //param// IN %%|%% OUT %%|%% INOUT //datatype// DEFAULT //default// |  
 +|:::| [[oracle-to-postgresql/return_sys_refcursor|param OUT SYS_REFCURSOR]] || [[oracle-to-postgresql/return_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//, ...)  || 
 + 
 +===== 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, ... | 
 + 
 + 
 + 
 +===== Built-in PL/SQL Packages ===== 
 + 
 +Converting built-in PL/SQL packages from Oracle to PostgreSQL: 
 + 
 +| | **Oracle** || **PostgreSQL** | 
 +| 1 | DBMS_OUTPUT.PUT_LINE(//text//) | Output a message | RAISE NOTICE '%', //text// |  
 +===== SQL*Plus Commands =====
 +Converting Oracle SQL*Plus commands from Oracle to PostgreSQL:
 +| | **Oracle** || **PostgreSQL** |
 +| 1 | SET SERVEROUTPUT ON %%|%% OFF | Enable output for DBMS_OUTPUT | Commented |