Differences

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

oracle-to-mariadb-compatibility [June 25, 2019 1:39 am]
sqlines
oracle-to-mariadb-compatibility [May 24, 2021 1:08 pm] (current)
sqlines
Line 1: Line 1:
====== Oracle to MariaDB Migration - Oracle Compatibility Mode ====== ====== Oracle to MariaDB Migration - Oracle Compatibility Mode ======
-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 MariaDB:+SQLines provides tools to help you transfer data, convert database schema (DDL), views, stored procedures, functions, packages, triggers, queries and SQL scripts from Oracle to MariaDB:
  * [[/sqldata/oracle-to-mariadb|SQLines Data]] - Data transfer, schema migration and validation tool   * [[/sqldata/oracle-to-mariadb|SQLines Data]] - Data transfer, schema migration and validation tool
-  * SQLines SQL Converter - Migration assessment and SQL scripts conversion tool+  * SQLines SQL Converter - SQL scripts assessment and conversion tool
**Databases:** **Databases:**
-  * Oracle 12c, 11g, 10g and 9i +  * Oracle 19c, 18c, 12c, 11g, 10g and 9i 
-  * MariaDB 10.x and 5.x   +  * MariaDB 10.x
-Starting from MariaDB 10.3 there is the Oracle compatibility mode that allows MariaDB to understand a subset of Oracle's PL/SQL language. To enable this mode, run: +Starting from MariaDB 10.3 there is the Oracle compatibility mode that allows MariaDB to understand a subset of Oracle's PL/SQL language. To enable this mode, use:
| set sql_mode=oracle; | | set sql_mode=oracle; |
Line 19: Line 19:
===== Migration Reference ===== ===== Migration Reference =====
 +  * [[/oracle-to-mariadb-compatibility#sql-language-elements|SQL Language Elements]]
 +  * [[/oracle-to-mariadb-compatibility#data-types|Data Types]]
 +  * [[/oracle-to-mariadb-compatibility#built-in-sql-functions|Built-in SQL Functions]]
  * [[/oracle-to-mariadb-compatibility#views|Views]]   * [[/oracle-to-mariadb-compatibility#views|Views]]
  * [[/oracle-to-mariadb-compatibility#sequences|Sequences]]   * [[/oracle-to-mariadb-compatibility#sequences|Sequences]]
 +  * [[/oracle-to-mariadb-compatibility#create-index-statement|CREATE INDEX]]
 +  * [[/oracle-to-mariadb-compatibility#select-statement|SELECT Statement]]
 +  * [[/oracle-to-mariadb-compatibility#insert-statement|INSERT Statement]]
 +  * [[/oracle-to-mariadb-compatibility#create-function-statement|CREATE FUNCTION Statement]]
  * [[/oracle-to-mariadb-compatibility#plsql-statements|PL/SQL Statements]]   * [[/oracle-to-mariadb-compatibility#plsql-statements|PL/SQL Statements]]
-    * [[/oracle-to-mariadb-compatibility#exception-block|Exception Block]] 
  * [[/oracle-to-mariadb-compatibility#sql-statements|SQL Statements]]   * [[/oracle-to-mariadb-compatibility#sql-statements|SQL Statements]]
 +
 +===== SQL Language Elements =====
 +
 +Converting SQL language elements:
 +
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | %%--%% //comment// | Single line comment | %%--%% //comment// | A blank is required after %%--%% {{:exclamation.png|}} |
 +
 +Oracle DATE arithmetic:
 +
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | SYSDATE + 1 | Add 1 day | DATE_ADD(SYSDATE(), INTERVAL 1  DAY) |
 +| 2 | SYSDATE + 1/24 | Add 1 hour | DATE_ADD(SYSDATE(), INTERVAL 1  HOUR) |
 +| 3 | SYSDATE + 1/1440 | Add 1 minute | DATE_ADD(SYSDATE(), INTERVAL 1  MINUTE) |
 +| 4 | SYSDATE + 1/86400 | Add 1 second | DATE_ADD(SYSDATE(), INTERVAL 1  SECOND) |
 +
 +Operators and predicates:
 +
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | (//start1, end1//) OVERLAPS (//start2, end2//) | Check if two datetime \\ intervals overlap | (//start2 < end1// AND //end2 > start1//) |
 +
 +
 +
 +===== Data Types =====
 +
 +Character data types:
 +
 +|  | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | CLOB | Character large object, %%<=%% 4G | LONGTEXT ||
 +| 2 | LONG | Character data, %%<=%% 2G | LONGTEXT ||
 +| 3 | VARCHAR2(//n// [BYTE %%|%% CHAR]) | Variable-length string \\ 1 %%<=%% //n// %%<=%% 4000 \\ (MAX_STRING_SIZE = STANDARD) \\ \\ 1 %%<=%% //n// %%<=%% 32767 \\ (MAX_STRING_SIZE = EXTENDED) \\ since Oracle 12c | VARCHAR2(//n//) | BYTE and CHAR semantics \\ cannot be specified {{:exclamation.png|}} |
 +
 +Unicode character data types:
 +
 +|  | **Oracle** || **MariaDB** - Oracle Compatibility Mode  |||
 +| 1 | NCHAR(//n//) | Fixed-length Unicode string, //n// %%<=%% 2000 | NCHAR(//n//) | n %%<=%% 255 |
 +|:::|:::|:::| NVARCHAR(//n//) {{:exclamation.png|}} | //n// > 255 |
 +| 2 | NVARCHAR2(//n//) | Variable-length Unicode string, //n// %%<=%% 4000 | NVARCHAR(//n//) | n %%<=%% 65,535 |
 +| 3 | NCLOB | Unicode large character object | LONGTEXT ||
 +
 +Numeric data types:
 +
 +|  | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | BINARY_INTEGER | 32-bit signed integer number, PL/SQL only | INTEGER ||
 +| 2 | NUMBER(//p,s//) | Fixed-point number, s > 0 | NUMBER(//p,s//) ||
 +|:::| NUMBER(//*,s//) | Fixed-point number | DECIMAL(//38,s//) {{:exclamation.png|}} ||
 +|:::| NUMBER | Floating-point number | NUMBER ||
 +| 3 | PLS_INTEGER | 32-bit signed integer number, PL/SQL only | INTEGER ||
 +
 +
 +Binary data types:
 +
 +|  | **Oracle** || **MariaDB** - Oracle Compatibility Mode  ||
 +| 1 | BLOB | Binary large object, <= 4G | LONGBLOB |
 +| 2 | RAW(//n//) | Variable-length binary data, 1 <= //n// <= 2000 | RAW(//n//) ||
 +| 3 | LONG RAW | Binary data, %%<%% 2G | LONGBLOB ||
 +
 +Other data types:
 +
 +|  | **Oracle** || **MariaDB**  - Oracle Compatibility Mode ||
 +| 1 | ROWID | Physical row address {{:exclamation.png|}} | CHAR(10) |
 +| 2 | XMLTYPE | XML data | LONGTEXT |
 +
 +
 +
 +===== Built-in SQL Functions =====
 +
 +Converting string functions:
 +
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | [[/oracle-to-mariadb-compatibility/replace|REPLACE(str, search)]] | Remove //search//-string | [[/oracle-to-mariadb-compatibility/replace|REPLACE(str, search, ")]] |
 +|:::| [[/oracle-to-mariadb-compatibility/replace|REPLACE(str, search, replace)]] | Replace //search//-string | [[/oracle-to-mariadb-compatibility/replace|REPLACE(str, search, replace) ]] |
 +
 +Converting date and time functions:
 +
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | [[/oracle-to-mariadb-compatibility/add_months|ADD_MONTHS(date, num)]] | Add //num// months to //date// | [[/oracle-to-mariadb-compatibility/add_months|TIMESTAMPADD(MONTH, num, date)]] ||
 +| 2 | [[/oracle-to-mariadb-compatibility/last_day|LAST_DAY(datetime)]] | Get last day of the month, \\ includes time | [[/oracle-to-mariadb-compatibility/last_day|LAST_DAY(datetime)]] | Returns date only {{:exclamation.png|}} |
 +| 3 | SYSDATE | Get current date and time | SYSDATE() | Returns time, but () are required |
 +| 4 | [[/oracle-to-mariadb-compatibility/to_char_datetime|TO_CHAR(datetime, format)]] | Convert datetime to string | [[oracle-to-mariadb-compatibility/to_char_datetime|DATE_FORMAT(datetime, format)]] {{:exclamation.png|}} ||
 +| 5 | [[/oracle-to-mariadb-compatibility/to_date|TO_DATE(string, format)]] | Convert string to datetime | [[oracle-to-mariadb-compatibility/to_date|STR_TO_DATE(string, format)]] {{:exclamation.png|}} ||
 +| 6 | [[/oracle-to-mariadb-compatibility/trunc_datetime|TRUNC(datetime)]] | Truncate datetime value | [[oracle-to-mariadb-compatibility/trunc_datetime|CAST and DATE_FORMAT]] ||
 +
 +Converting case functions:
 +
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | DECODE(//exp, when, then, …//) | Evaluate conditions | DECODE(//exp, when, then, …//) ||
===== Views ===== ===== Views =====
Line 30: Line 123:
|  | **Oracle** || **MariaDB** - Oracle Compatibility Mode || |  | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
-| 1 | CREATE MATERIAZED VIEW //name// | Create a materiazed view | Not supported {{:red_cross.png|}} ||+| 1 | CREATE OR REPLACE VIEW //name// | Create a view | CREATE OR REPLACE VIEW //name// || 
 +| 2 | CREATE FORCE VIEW //name// | Create a view even if base tables \\ do not exist or no privileges on them | FORCE removed, not supported {{:red_cross.png|}} | 
 +| 3 | CREATE MATERIAZED VIEW //name// | Create a materiazed view | Not supported {{:red_cross.png|}} || 
 +| 4 | EDITIONABLE | Editioning view keyword | Not supported {{:red_cross.png|}} ||
===== Sequences ===== ===== Sequences =====
Line 51: Line 147:
| 9 | [[/oracle-to-mariadb/sequence_order|ORDER]] | Guarantee numbers in order of requests | [[/oracle-to-mariadb-compatibility/sequence_order|Option not supported, commented]]  {{:exclamation.png|}} || | 9 | [[/oracle-to-mariadb/sequence_order|ORDER]] | Guarantee numbers in order of requests | [[/oracle-to-mariadb-compatibility/sequence_order|Option not supported, commented]]  {{:exclamation.png|}} ||
|:::| NOORDER | No guarantee, this is default | Option not supported, removed as it is default || |:::| NOORDER | No guarantee, this is default | Option not supported, removed as it is default ||
 +| 10 | NOKEEP | Do not keep value during replay | Option not supported, removed as it is default ||
 +| 11 | NOSCALE | Disable sequence scalability | Option not supported, removed as it is default ||
 +| 12 | GLOBAL | A regular sequence visible to all sessions | Option not supported, removed as it is default ||
Referencing sequence values: Referencing sequence values:
Line 62: Line 161:
|  | **Oracle** || **MariaDB**  - Oracle Compatibility Mode  || |  | **Oracle** || **MariaDB**  - Oracle Compatibility Mode  ||
| 1 | DROP SEQUENCE //seqname// || DROP SEQUENCE [IF EXISTS] //seqname// || | 1 | DROP SEQUENCE //seqname// || DROP SEQUENCE [IF EXISTS] //seqname// ||
 +
 +===== CREATE INDEX Statement =====
 +
 +Converting CREATE INDEX statement keywords and clauses:
 +
 +| | **Oracle** | **MariaDB**  - Oracle Compatibility Mode ||
 +| 1 | CREATE INDEX //schema.index_name// | CREATE INDEX //index_name// | Can contain index name only {{:exclamation.png|}} |  
 +
 +===== SELECT Statement =====
 +
 +Converting SQL SELECT statement and its clauses:
 +
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | //t1// JOIN //t2// USING (//column_name//) | USING clause for joins | //t1// JOIN //t2// USING (//column_name//) |
 +| 2 | [[/oracle-to-mariadb/connect_by_prior|CONNECT BY PRIOR]] | Hierarchical queries | [[/oracle-to-mariadb/connect_by_prior|Recursive Common Table Expressions (CTE)]] ||
 +
 +Rownum pseudo-column conditions:
 +
 +| | **Oracle** || **MariaDB** Oracle mode - Before 10.6.1 | **MariaDB** Oracle mode - Since 10.6.1 |
 +| 1 | ROWNUM = //1// | Return 1 row only | LIMIT 1 | ROWNUM = //1// |
 +|:::| ROWNUM %%<=%% //n// | Row limit | LIMIT //n// | ROWNUM %%<=%% //n// |
 +|:::| ROWNUM %%<%% //n// |:::| LIMIT //n - 1// | ROWNUM %%<%% //n// |
 +
 +===== INSERT Statement =====
 +
 +Converting SQL INSERT statement and its clauses:
 +
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | INSERT INTO //table// \\ VALUES //rowtype_var// | Insert a record from \\ %ROWTYPE variable | INSERT INTO //table// VALUES \\ (//rowtype_var.col1,  rowtype_var.col2, ...//) | Columns have to be \\ explicitly defined |
 +
 +===== CREATE FUNCTION Statement =====
 +
 +Converting user-defined functions from Oracle to MariaDB:
 +
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode ||
 +| 1 | CREATE OR REPLACE FUNCTION //name// ... || DELIMITER %%//%% \\ CREATE OR REPLACE FUNCTION //name// ... ||
 +| 2 | //param// IN %%|%% OUT %%|%% IN OUT //datatype// | Parameter definition | //param// //datatype// | Length can be skipped, \\ it is derived from the caller |
 +| 3 | RETURN //datatype// | Return value | RETURN //datatype// | RETURNS not allowed |
 +
===== PL/SQL Statements ===== ===== PL/SQL Statements =====
Line 67: Line 205:
Converting PL/SQL statements: Converting PL/SQL statements:
-| | **Oracle** || **MariaDB** - Oracle Compatibility Mode | +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode |
-| 1 | NULL; | "no-op" (no operation) | NULL; {{:green_tick.gif|}}|+| 1 | CURSOR //cur// (//param// IN //type//, ... ) IS ... | Cursor declaration | CURSOR //cur// (//param//  //type//, ... ) IS ... | IN not allowed |
Flow of control statements: Flow of control statements:
| | **Oracle** || **MariaDB** - Oracle Compatibility Mode | | | **Oracle** || **MariaDB** - Oracle Compatibility Mode |
-| 1 | RETURN; | Return from a stored procedure | RETURN; {{:green_tick.gif|}}+| 1 | RETURN; | Return from a stored procedure | RETURN; | 
-| 2 | RETURN //exp//; | Return from a user-defined function | RETURN //exp//; {{:green_tick.gif|}}|+| 2 | RETURN //exp//; | Return from a user-defined function | RETURN //exp//;
 + 
 +Other statements: 
 + 
 +| | **Oracle** || **MariaDB** - Oracle Compatibility Mode | 
 +| 1 | NULL; | "no-op" (no operation) | NULL; |
-==== Exception Block ====+**Exception Block**
Predefined exceptions handlers: Predefined exceptions handlers:
Line 111: Line 254:
| | **Oracle** | **MariaDB** - Oracle Compatibility Mode | | | **Oracle** | **MariaDB** - Oracle Compatibility Mode |
| 1 | COMMENT ON COLUMN //table.column// IS '//string//' | Not supported {{:exclamation.png|}}, should be moved to COMMENT in CREATE TABLE | | 1 | COMMENT ON COLUMN //table.column// IS '//string//' | Not supported {{:exclamation.png|}}, should be moved to COMMENT in CREATE TABLE |
 +| 2 | COMMENT ON TABLE //table// IS '//string//' | ALTER TABLE //table// COMMENT '//string//' |