Convert Online

SQLines Data

Overview
Command Line
Configuration File
Connection Strings
Data Validation

SQLines SQL Converter

Overview
Command Line
Configuration File

Migration to MariaDB

IBM DB2 to MariaDB
Informix to MariaDB
MySQL to MariaDB
Oracle to MariaDB
PostgreSQL to MariaDB
SQL Server to MariaDB
Sybase ASE to MariaDB
Sybase ASA to MariaDB

Migration to MySQL

IBM DB2 to MySQL
Informix to MySQL
MariaDB to MySQL
Oracle to MySQL
PostgreSQL to MySQL
SQL Server to MySQL
Sybase ASE to MySQL
Sybase ASA to MySQL

Migration to PostgreSQL

IBM DB2 to PostgreSQL
Informix to PostgreSQL
MySQL to PostgreSQL
Oracle to PostgreSQL
SQL Server to PostgreSQL
Sybase ASE to PostgreSQL
Sybase ASA to PostgreSQL

Migration to SQL Server

IBM DB2 to SQL Server
Informix to SQL Server
MySQL to SQL Server
Oracle to SQL Server
PostgreSQL to SQL Server
Sybase ASE to SQL Server
Sybase ASA to SQL Server
Sybase ADS to SQL Server

Migration to Oracle

IBM DB2 to Oracle
Informix to Oracle
PostgreSQL to Oracle
Sybase ASE to Oracle
Sybase ASA to Oracle
SQL Server to Oracle
MySQL to Oracle
MariaDB to Oracle

SQL in Applications

Java
Mybatis


Differences

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

oracle-to-mariadb [May 30, 2019 6:34 am]
sqlines 		oracle-to-mariadb [February 05, 2026 3:33 pm] (current)
sqlines
Line 1: Line 1:
 +====== Oracle to MariaDB Migration ======
 +
 +SQLines tools can 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
 +  * [[/overview|SQLines SQL Converter]] - SQL Scripts Conversion and Assessment tool.   [[http://www.sqlines.com/online|Try Online]]
 +
 +**Databases:**
 +
 +  * Oracle 23ai, 21c, 19c, 18c, 12c, 11g, 10g and 9i
 +  * MariaDB 12.x, 11.x, 10.x and 5.x  
 +
 +Starting from MariaDB 10.3 there is the Oracle compatibility mode, see [[/oracle-to-mariadb-compatibility|Oracle to MariaDB Migration - Compatibility Mode]].
 +
 +===== Migration Reference =====
 +
 +  * [[/oracle-to-mariadb#identifiers|Identifiers]]
 +  * [[/oracle-to-mariadb#sql-language-elements|SQL Language Elements]]
 +  * [[/oracle-to-mariadb#data-types|Data Types]]
 +  * [[/oracle-to-mariadb#built-in-sql-functions|Built-in SQL Functions]]
 +  * [[/oracle-to-mariadb#select-statement|SELECT]]
 +  * [[/oracle-to-mariadb#create-table-statement|CREATE TABLE]]
 +  * [[/oracle-to-mariadb#create-view-statement|CREATE VIEW]]
 +  * [[/oracle-to-mariadb#delete-statement|DELETE]]
 +  * [[/oracle-to-mariadb#sequences|Sequences]]
 +  * [[/oracle-to-mariadb#create-index-statement|CREATE INDEX]]
 +  * [[/oracle-to-mariadb#create-function-statement|CREATE FUNCTION]]
 +  * [[/oracle-to-mariadb#create-trigger-statement|CREATE TRIGGER]]
 +  * [[/oracle-to-mariadb#create-synonym-statement|CREATE SYNONYM]]
 +  * [[/oracle-to-mariadb#plsql-statements|PL/SQL Statements]]
 +  * [[/oracle-to-mariadb#sql-statements|SQL Statements]]
 +  * [[/oracle-to-mariadb#built-in-plsql-packages|Built-in PL/SQL Packages]]
 +
 +
 +===== Identifiers =====
 +
 +Max length:
 +
 +| | **Oracle** | **MariaDB** |
 +| Tables, columns, indexes, views, procedures, triggers | 128 (was 30 before Oracle 12c) | 64 |
 +
 +===== SQL Language Elements =====
 +
 +Converting SQL language elements:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | %%--%% //comment// | Single line comment | %%--%% //comment// | A blank is required after %%--%% {{:exclamation.png|}} |
 +
 +Operators and predicates:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/datetime_arithmetic|+ and - Operators for datetime]] | Datetime arithmetic | [[/oracle-to-mariadb/datetime_arithmetic|Interval expressions and functions]] {{:exclamation.png|}} |
 +| 2 | (//start1, end1//) OVERLAPS (//start2, end2//) | Check if datetime intervals \\ overlap | (//start2 < end1// AND //end2 > start1//) |
 +| 3 | [[/oracle-to-mariadb/is_json|exp IS JSON]] | Check if JSON is valid | [[/oracle-to-mariadb/is_json|JSON_VALID(exp)]] ||
 +
 +===== Data Types =====
 +
 +Character data types:
 +
 +|  | **Oracle** || **MariaDB** ||
 +| 1 | CLOB | Character large object,  4 GB | LONGTEXT ||
 +| 2 | LONG | Character data, 2 GB | LONGTEXT ||
 +| 3 | NCHAR(//n//) | Fixed-length Unicode string, //n// %%<=%% 2000 | NCHAR(//n//) | n %%<=%% 255 |
 +|:::|:::|:::| NVARCHAR(//n//) {{:exclamation.png|}} | //n// > 255 |
 +| 4 | NCLOB | Unicode large character object | LONGTEXT ||
 +| 5 | [[/oracle-to-mariadb/nvarchar2|NVARCHAR2(n)]] | Variable-length Unicode string, 1 <= //n// <= 32,767 {{:exclamation.png|}} | [[/oracle-to-mariadb/nvarchar2|NVARCHAR(n)]] | 0 <= //n// <= 65,535 {{:exclamation.png|}} |
 +| 6 | VARCHAR(//n//) | Synonym for VARCHAR2 | VARCHAR(//n//) ||
 +| 7 | [[/oracle-to-mariadb/varchar2|VARCHAR2(n)]] | Variable-length string, 1 <= //n// <= 32,767 {{:exclamation.png|}} | [[/oracle-to-mariadb/varchar2|VARCHAR(n)]] | 0 <= //n// <= 65,535 {{:exclamation.png|}} |
 +
 +Numeric data types:
 +
 +|  | **Oracle** || **MariaDB** ||
 +| 1 | BINARY_FLOAT | 32-bit floating-point number | FLOAT ||
 +| 2 | BINARY_DOUBLE | 64-bit floating-point number | DOUBLE ||
 +| 3 | DECIMAL(//p,s//) | Fixed-point number | DECIMAL(//p,s//) ||
 +| 4 | DOUBLE PRECISION | Floating-point number | DOUBLE PRECISION ||
 +| 5 | FLOAT(//p//) | Floating-point number | DOUBLE ||
 +| 6 | INTEGER, INT | 38 digits integer | INT | DECIMAL(38) |
 +| 7 | [[/oracle-to-mariadb/number|NUMBER(p,0),  NUMBER(p)]] | 8-bit integer, 1 <=  //p// < 3  | [[/oracle-to-mariadb/number|TINYINT]] | (0 to 255) |
 +|:::|:::| 16-bit integer, 3 <=  //p// < 5 | [[/oracle-to-mariadb/number|SMALLINT]] ||
 +|:::|:::| 32-bit integer, 5 <=  //p// < 9 | [[/oracle-to-mariadb/number|INT]] ||
 +|:::|:::| 64-bit integer, 9 <=  //p// < 19 | [[/oracle-to-mariadb/number|BIGINT]] ||
 +|:::|:::| Fixed-point number, 19 <=  //p// <= 38 | [[/oracle-to-mariadb/number|DECIMAL(p)]] ||
 +| 8 | [[/oracle-to-mariadb/number|NUMBER(p,s)]] | Fixed-point number, s > 0 | [[/oracle-to-mariadb/number|DECIMAL(p,s)]] ||
 +| 9 | [[/oracle-to-mariadb/number|NUMBER, NUMBER(*)]] | Floating-point number | [[/oracle-to-mariadb/number|DECIMAL(p,s)]] | [[/oracle-to-mariadb/number|DOUBLE]] {{:exclamation.png|}} |
 +| 10 | NUMERIC(//p,s//) | Fixed-point number | NUMERIC(//p,s//) ||
 +| 11 | REAL | Floating-point number | DOUBLE ||
 +| 12 | SMALLINT | 38 digits integer | DECIMAL(38) ||
 +
 +Date and time data types:
 +
 +|  | **Oracle** || **MariaDB** ||
 +| 1 | DATE | Date and time with seconds {{:exclamation.png|}} | DATETIME ||
 +| 2 | [[/oracle-to-mariadb/timestamp|TIMESTAMP(p)]] | Date and time with fraction, 0 <= //p// <= 9, default is 6 | [[/oracle-to-mariadb/timestamp|DATETIME(p)]], 0 <= //p// <= 6 {{:exclamation.png|}} ||
 +
 +Binary data types:
 +
 +| 1 | BLOB | Binary large object, <= 4G | LONGBLOB |
 +| 2 | RAW(//n//) | Variable-length binary data, 1 <= //n// <= 2000 | VARBINARY(//n//) ||
 +| 3 | LONG RAW | Binary data, %%<%% 2G | LONGBLOB ||
 +
 +Other data types:
 +
 +| 1 | ROWID | Physical row address {{:exclamation.png|}} | CHAR(10) |
 +| 2 | XMLTYPE | XML data | LONGTEXT |
 +
 +===== Built-in SQL Functions =====
 +
 +String functions:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | CHR(//num//) | Get character from ASCII code | CHR(//num//) | since 10.3.1 |
 +| 2 | [[/oracle-to-mariadb/initcap|INITCAP(str)]] | Capitalize words in string | [[/oracle-to-mariadb/initcap|User-defined function]] ||
 +| 3 | [[/oracle-to-mariadb/instr|INSTR(str, substr)]] | Get //position// of //substring// | [[/oracle-to-mariadb/instr|INSTR(str, substr)]] ||
 +|:::| [[/oracle-to-mariadb/instr|INSTR(str, substr, pos)]] |:::| [[/oracle-to-mariadb/instr|LOCATE(substr, str, pos)]] | param order {{:exclamation.png|}} |
 +|:::| [[/oracle-to-mariadb/instr|INSTR(str, substr, pos, num)]] |:::| [[/oracle-to-mariadb/instr|User-defined function]] ||
 +| 4 | [[/oracle-to-mariadb/length|LENGTH(string)]] | Get length of //string// in chars | [[/oracle-to-mariadb/length|CHAR_LENGTH(string)]] ||
 +| 5 | [[/oracle-to-mariadb/listagg|LISTAGG(exp, delim)...]] | Aggregate concatenation | [[/oracle-to-mariadb/listagg|GROUP_CONCAT(exp SEPARATOR delim)]] ||
 +| 6 | [[/oracle-to-mariadb/lpad|LPAD(string, len, pad)]] | Pad the left-side of //string// | [[/oracle-to-mariadb/lpad|LPAD(string, len, pad)]] ||
 +| 7 | LTRIM(//string//) | Remove leading spaces | LTRIM(//string//) ||
 +|:::| LTRIM(//string, set//) | Remove leading chars | TRIM(LEADING //set// FROM //string//) ||
 +| 8 | [[/oracle-to-mariadb/months_between|MONTHS_BETWEEN(dt1, dt2)]] | Get difference in months | [[/oracle-to-mariadb/months_between|User-defined function]] {{:exclamation.png|}} ||
 +| 9 | [[/oracle-to-mariadb/regexp_substr|REGEXP_SUBSTR(str, pat, pos, nth)]] | Get substring matching pattern | [[/oracle-to-mariadb/regexp_substr|REGEXP_SUBSTR(str, pat)]] {{:exclamation.png|}}  \\ [[/oracle-to-mariadb/regexp_substr|User-defined function]] ||
 +| 10 | [[/oracle-to-mariadb/replace|REPLACE(str, search)]] | Remove //search//-string | [[/oracle-to-mariadb/replace|REPLACE(str, search, ")]] ||
 +|:::| [[/oracle-to-mariadb/replace|REPLACE(str, search, replace)]] | Replace //search//-string | [[/oracle-to-mariadb/replace|REPLACE(str, search, replace) ]] ||
 +| 11 | [[/oracle-to-mariadb/rpad|RPAD(string, len, pad)]] | Pad the right-side of //string// | [[/oracle-to-mariadb/rpad|RPAD(string, len, pad)]] ||
 +| 12 | RTRIM(//string//) | Remove trailing spaces | RTRIM(//string//) ||
 +|:::| RTRIM(//string, set//) | Remove trailing chars | TRIM(TRAILING //set// FROM //string//) ||
 +| 13 | [[oracle-to-mariadb/substr|SUBSTR(str, start, len)]] | Get substring | [[oracle-to-mariadb/substr|SUBSTR(str, start, len)]] {{:exclamation.png|}} ||
 +| 14 | [[oracle-to-mariadb/to_char_datetime|TO_CHAR(datetime, format)]] | Convert datetime to string | [[oracle-to-mariadb/to_char_datetime|TO_CHAR(datetime, format)]] | since 10.6.1 |
 +|:::|:::|:::| [[oracle-to-mariadb/to_char_datetime|DATE_FORMAT(datetime, format)]] {{:exclamation.png|}} ||
 +|:::| [[oracle-to-mariadb/to_char_number|TO_CHAR(number, format)]] | Convert number to string | [[oracle-to-mariadb/to_char_number|FORMAT(number, decimal_digits)]] {{:exclamation.png|}} ||
 +| 15 | [[/oracle-to-mariadb/soundex|SOUNDEX(string)]] | Phonetic representation of //string// | [[/oracle-to-mariadb/soundex|SOUNDEX(string)]] ||
 +| 16 | [[/oracle-to-mariadb/translate|TRANSLATE(string, from, to)]] | Replace characters | [[/oracle-to-mariadb/translate|Nested REPLACE or User-defined function]] ||
 +
 +
 +Date and time functions:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/add_months|ADD_MONTHS(date, num)]] | Add //num// months to //date// | [[/oracle-to-mariadb/add_months|TIMESTAMPADD(MONTH, num, date)]] ||
 +| 2 | [[/oracle-to-mariadb/current_date|CURRENT_DATE]] | Get current date and time \\ in session time zone | [[/oracle-to-mariadb/current_date|NOW()]] | Includes time, \\ session time zone |
 +|:::|:::|:::| [[/oracle-to-mariadb/current_date|CURRENT_DATE]] | Date only, no time {{:exclamation.png|}} |
 +| 3 | [[/oracle-to-mariadb/current_timestamp|CURRENT_TIMESTAMP(p)]] | Get current date and time \\ in session time zone | [[/oracle-to-mariadb/current_timestamp|CURRENT_TIMESTAMP(p)]] | Session time zone |
 +| 4 | [[/oracle-to-mariadb/extract|EXTRACT(part FROM date)]] | Extract datetime part | [[/oracle-to-mariadb/extract|EXTRACT(part FROM date)]] ||
 +| 5 | [[/oracle-to-mariadb/last_day|LAST_DAY(date)]] | Get last day of month (includes time) | [[/oracle-to-mariadb/last_day|LAST_DAY(date)]] | Date only {{:exclamation.png|}} |
 +| 6 | [[/oracle-to-mariadb/next_day|NEXT_DAY(date, weekday)]] | Get next weekday (includes time) | [[/oracle-to-mariadb/next_day|DATE_ADD and WEEKDAY expression]] ||
 +| 7 | [[/oracle-to-mariadb/sysdate|SYSDATE]] | Get current date and time \\ in database OS time zone | [[/oracle-to-mariadb/sysdate|SYSDATE()]] | Includes time, \\ session time zone {{:exclamation.png|}} |
 +| 8 | [[oracle-to-mariadb/systimestamp|SYSTIMESTAMP]] | Get current date and time \\ in database OS time zone | [[oracle-to-mariadb/systimestamp|NOW(6)]] | Session time zone {{:exclamation.png|}} |
 +| 9 | [[/oracle-to-mariadb/to_date|TO_DATE(string, format)]] | Convert string to datetime | [[/oracle-to-mariadb/to_date|STR_TO_DATE(string, format)]] {{:exclamation.png|}} ||
 +|:::| [[/oracle-to-mariadb/to_date|TO_DATE(string)]] |:::| [[/oracle-to-mariadb/to_date|CAST(string AS DATETIME)]] ||
 +| 10 | [[/oracle-to-mariadb/to_timestamp|TO_TIMESTAMP(str, format)]] | Convert string to datetime | [[/oracle-to-mariadb/to_timestamp|STR_TO_DATE(string, format)]] {{:exclamation.png|}} ||
 +| 11 | [[/oracle-to-mariadb/trunc_datetime|TRUNC(datetime)]] | Truncate datetime value | [[oracle-to-mariadb/trunc_datetime|CAST and DATE_FORMAT]] ||
 +
 +Numeric functions:
 +
 +| | **Oracle** || **MariaDB** |
 +| 1 | [[/oracle-to-mariadb/round|ROUND(exp, len)]] | Round to specified precision | [[/oracle-to-mariadb/round|ROUND(exp, len)]] |
 +
 +NULL processing functions:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/nvl|NVL(exp, replacement)]] | Replace NULL with the specified value | [[/oracle-to-mariadb/nvl|NVL(exp, replacement)]] ||
 +| 2 | [[/oracle-to-mariadb/nvl2|NVL2(exp, exp2, exp3)]] | Return //exp2// if //exp// is not NULL, otherwise //exp3// | [[/oracle-to-mariadb/nvl2|NVL2(exp, exp2, exp3)]] ||
 +
 +Aggregation and window functions:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/dense_rank|DENSE_RANK() OVER (...)]] | Assign rank without gaps | [[/oracle-to-mariadb/dense_rank|DENSE_RANK() OVER (...)]] {{:exclamation.png|}} ||
 +| 2 | [[/oracle-to-mariadb/rank|RANK() OVER (...)]] | Assign rank with gaps | [[/oracle-to-mariadb/rank|RANK() OVER (...)]] {{:exclamation.png|}} ||
 +
 +Math functions:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/power|POWER(value, n)]] | Raise //value// to the //n//th power | [[/oracle-to-mariadb/power|POWER(value, n)]] ||
 +
 +LOB functions:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/empty_blob|EMPTY_BLOB()]] | Get empty BLOB locator | [[/oracle-to-mariadb/empty_blob|x'']]||
 +| 2 | [[/oracle-to-mariadb/empty_clob|EMPTY_CLOB()]] | Get empty CLOB locator | [[/oracle-to-mariadb/empty_clob|'']]||
 +
 +System functions:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/sys_context|SYS_CONTEXT('USERENV', 'IP_ADDRESS')]] | Get user IP | [[/oracle-to-mariadb/sys_context|SUBSTRING_INDEX(USER(), '@', -1)]] {{:exclamation.png|}} ||
 +| 2 | [[/oracle-to-mariadb/sys_context|SYS_CONTEXT('USERENV', 'OS_USER')]] | Get OS user | [[/oracle-to-mariadb/sys_context|USER()]] {{:exclamation.png|}} ||
 +| 3 | [[/oracle-to-mariadb/sys_context|SYS_CONTEXT('USERENV', 'SESSION_USER')]] | Get current user | [[/oracle-to-mariadb/sys_context|USER()]] ||
 +| 4 | [[/oracle-to-mariadb/sys_context|SYS_CONTEXT(namespace, parameter)]] | Get application's value | [[/oracle-to-mariadb/sys_context|Session variable]] ||
 +| 5 | [[/oracle-to-mariadb/user|USER]] | Get current user | [[/oracle-to-mariadb/user|USER()]] ||
 +| 6 | [[/oracle-to-mariadb/userenv|USERENV('SESSIONID')]] | Get session ID | [[/oracle-to-mariadb/userenv|CONNECTION_ID()]] {{:exclamation.png|}} ||
 +
 +Other functions:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/sys_guid|SYS_GUID()]] | Generate UUID | [[/oracle-to-mariadb/sys_guid|SYS_GUID()]] {{:exclamation.png|}} | [[/oracle-to-mariadb/sys_guid|UUID()]] {{:exclamation.png|}} |
 +
 +===== SELECT Statement =====
 +
 +Converting queris:
 +
 +|  | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/subquery_alias|SELECT * FROM (SELECT ...)]] | Subquey alias is optional | [[/oracle-to-mariadb/subquery_alias|SELECT * FROM (SELECT ...) t]] | Alias required {{:exclamation.png|}} |
 +| 2 | //t1// JOIN //t2// USING (//column_name//) | USING clause for joins | //t1// JOIN //t2// USING (//column_name//) ||
 +| 3 | [[/oracle-to-mariadb/cross_join|CROSS JOIN]] | Cartesian product | [[/oracle-to-mariadb/cross_join|CROSS JOIN]] |
 +| 4 | [[/oracle-to-mariadb/connect_by_prior|CONNECT BY PRIOR]] | Hierarchical queries | [[/oracle-to-mariadb/connect_by_prior|Recursive Common Table Expressions (CTE)]] ||
 +| 5 | //select// INTERSECT //select2// | Select rows existing in both | //select// INTERSECT //select2// | Since 10.3.0 |
 +| 6 | [[/oracle-to-mariadb/order_by_nulls|ORDER BY col NULLS FIRST | LAST]] | Order of NULLs | [[/oracle-to-mariadb/order_by_nulls|ORDER BY col]] {{:exclamation.png|}} ||
 +
 +Row limitation:
 +
 +| | **Oracle** || **MariaDB** - Before 10.6.1 | **MariaDB** - Since 10.6.1 |
 +| 1 | [[/oracle-to-mariadb/offset_fetch_first|OFFSET k FETCH FIRST n ROWS ONLY]] | Row limiting | [[/oracle-to-mariadb/offset_fetch_first|LIMIT k, n]] ||
 +| 2 | 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// |
 +
 +===== CREATE TABLE Statement =====
 +
 +Converting CREATE TABLE statement keywords and clauses:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/identity|GENERATED AS IDENTITY]] | Identity column | [[/oracle-to-mariadb/identity|AUTO_INCREMENT]] ||
 +| 2 | CONSTRAINT //name// NOT NULL | Named NOT NULL constraint | NOT NULL ||
 +| 3 | [[/oracle-to-mariadb/default_on_null|DEFAULT ON NULL exp]] | Default when NULL is inserted | [[/oracle-to-mariadb/default_on_null|DEFAULT exp]] {{:exclamation.png|}} ||
 +| 4 | ENABLE constraint attribute || Removed ||
 +| 5 | [[/oracle-to-mariadb/computed_column|col AS (exp)]] | Computed column | [[/oracle-to-mariadb/computed_column|type col AS (exp)]] | Data type must be specified {{:exclamation.png|}} |
 +
 +Temporary tables:
 +
 +| | **Oracle** || **MariaDB** |
 +| 1 | [[/oracle-to-mariadb/create_global_temp_table|CREATE GLOBAL TEMPORARY TABLE]] | Global temporary table | [[/oracle-to-mariadb/create_global_temp_table|CREATE TEMPORARY TABLE]] {{:exclamation.png|}} |
 +| 2 | //schema.name// | Qualified object name | //db_name.name// |
 +
 +Partitioned tables:
 +
 +| | **Oracle** || **MariaDB** |
 +| 1 | [[/oracle-to-mariadb/partition_by_range|PARTITION BY RANGE]] | Range partitions | [[/oracle-to-mariadb/partition_by_range|PARTITION BY RANGE]] {{:exclamation.png|}} |
 +| 2 | [[/oracle-to-mariadb/partition_by_list|PARTITION BY LIST]] | List partitions | [[/oracle-to-mariadb/partition_by_list|PARTITION BY LIST]] {{:exclamation.png|}} |
 +
 +===== CREATE VIEW Statement =====
 +
 +Converting views:
 +
 +| | **Oracle** || **MariaDB** |
 +| 1 | CREATE OR REPLACE VIEW //name// || CREATE OR REPLACE VIEW //name// |
 +| 2 | FORCE keyword | Create view even if tables not exist | Removed |
 +| 3 | WITH READ ONLY || Clause not supported, removed {{:exclamation.png|}} |
 +
 +===== DELETE Statement =====
 +
 +Converting deleting rows:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | DELETE [FROM] //table// | Delete rows | DELETE FROM //table// | FROM keyword is required {{:exclamation.png|}} |
 +
 +
 +
 +===== Sequences =====
 +
 +Note that sequences are available since MariaDB 10.3 {{:exclamation.png|}}
 +
 +CREATE SEQUENCE and ALTER SEQUENCE statements:
 +
 +|  | **Oracle** || **MariaDB** ||
 +| 1 | CREATE SEQUENCE //seqname// || CREATE [OR REPLACE] SEQUENCE //seqname// ||
 +| 2 | ALTER SEQUENCE //seqname// || ALTER SEQUENCE [IF EXISTS] //seqname// ||
 +| 3 | INCREMENT BY //num// | Positive or negative increment, default is 1 | INCREMENT BY //num// ||
 +| 4 | START WITH //num// | Initial value | START WITH //num// ||
 +| 5 | MAXVALUE //num// | Maximum value is //num// | MAXVALUE //num// ||
 +|:::| NOMAXVALUE | System limit | NOMAXVALUE ||
 +| 6 | MINVALUE //num// | Minimum value is //num// | MINVALUE //num// ||
 +|:::| NOMINVALUE | System limit | NOMINVALUE ||
 +| 7 | CYCLE | Reuse values  after reaching the limit | CYCLE ||
 +|:::| NOCYCLE | No reuse, this is default | NOCYCLE ||
 +| 8 | CACHE //num// | Cache //num// values, default is 20 | CACHE //num// | Default is 1000 |
 +|:::| NOCACHE | Values are not preallocated | NOCACHE ||
 +| 9 | [[/oracle-to-mariadb/sequence_order|ORDER]] | Guarantee numbers in order of requests | [[/oracle-to-mariadb/sequence_order|Option not supported, commented]]  {{:exclamation.png|}} ||
 +|:::| 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:
 +
 +|  | **Oracle** || **MariaDB** ||
 +| 1 | //seqname//.CURRVAL | The current value of //seqname// | NEXTVAL(//seqname//) | NEXT VALUE FOR //seqname// |
 +| 2 | //seqname//.NEXTVAL | The next value of //seqname// | LASTVAL(//seqname//) | PREVIOUS VALUE FOR //seqname// |
 +
 +Note that in Oracle mode (sql_mode = oracle), you can use the Oracle syntax //seqname//.CURRVAL and //seqname//.NEXTVAL to get sequence values in MariaDB.
 +
 +DROP SEQUENCE statement:
 +
 +|  | **Oracle** || **MariaDB** ||
 +| 1 | DROP SEQUENCE //seqname// || DROP SEQUENCE [IF EXISTS] //seqname// ||
 +
 +===== CREATE INDEX Statement =====
 +
 +Converting indexes:
 +
 +| | **Oracle** | **MariaDB** ||
 +| 1 | CREATE INDEX //schema.index_name// | CREATE INDEX //index_name// | Can contain index name only {{:exclamation.png|}} |  
 +
 +===== CREATE FUNCTION Statement =====
 +
 +Converting user-defined functions:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | CREATE OR REPLACE FUNCTION || DROP FUNCTION IF EXISTS and CREATE FUNCTION ||
 +| 2 | //param// IN %%|%% OUT %%|%% IN OUT //datatype// | Parameter definition | //param// //datatype(length)// ||
 +| 3 | RETURN //datatype// | Return value | RETURNS //datatype(length)// ||
 +| 4 | [[/oracle-to-mariadb/pipelined_function|PIPELINED]] | Table-valued function | [[/oracle-to-mariadb/pipelined_function|RETURNS JSON array]] ||
 +| 5 | IS %%|%% AS || Removed ||
 +| 6 | Variable declaration is before BEGIN || Variable declaration is after BEGIN ||
 +| 7 | END //func_name// || END ||
 +
 +For more information, see [[/oracle-to-mariadb#plsql-statements|Conversion of PL/SQL Statements]].
 +
 +===== CREATE TRIGGER Statement =====
 +
 +Converting triggers:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | CREATE OR REPLACE TRIGGER //name// ... || DELIMITER %%//%% \\ CREATE OR REPLACE TRIGGER //name// ... ||
 +| 2 | BEFORE %%|%% AFTER %%|%% INSTEAD OF {{:exclamation.png|}} || BEFORE %%|%% AFTER ||
 +| 3 | INSERT OR UPDATE OR DELETE | Multiple events in one trigger | Only one event in trigger {{:exclamation.png|}} ||
 +| 4 | UPDATE OF //col, ...// | List of updated columns | UPDATE OF //col, ...// | Since 11.8.1 {{:exclamation.png|}} |
 +| 5 | ON //table// || ON //table// ||
 +| 6 | REFERENCING OLD AS //old_name// NEW AS //new_name// || Clause is not supported, OLD and NEW used ||
 +| 7 | :NEW.//col//, :OLD.//col// | Referencing column values | NEW.//col//, OLD.//col// ||
 +| 8 | [[/oracle-to-mariadb/statement-level-triggers|Statement-level trigger by default]] || [[/oracle-to-mariadb/statement-level-triggers|Statement-level triggers are not supported]] {{:exclamation.png|}} ||
 +| 9 | FOR EACH ROW | Row-level trigger | FOR EACH ROW | Must be specified {{:exclamation.png|}} |
 +| 10 | BEGIN stmt; … END; / || BEGIN stmt; … END; %%//%% DELIMITER ; ||
 +
 +For more information, see [[/oracle-to-mariadb#plsql-statements|Conversion of PL/SQL Statements]].
 +
 +===== CREATE SYNONYM Statement =====
 +
 +Converting synonyms:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/create_synonym|CREATE OR REPLACE SYNONYM name]] || [[/oracle-to-mariadb/create_synonym|CREATE OR REPLACE VIEW name]] | Synonyms are not supported {{:exclamation.png|}} |
 +
 +===== PL/SQL Statements =====
 +
 +Converting variable declaration and assignments:
 +
 +| | **Oracle** || **MariaDB** |
 +| 1 | //variable// //datatype// := //value// | Variable declaration | DECLARE //variable// //datatype// DEFAULT //value// |
 +| 2 | //variable// := //value// | Assignment statement | SET //variable// = //value// |
 +
 +Flow-of-control statements:
 +
 +| | **Oracle** || **MariaDB** |
 +| 1 | [[/oracle-to-mariadb/goto|GOTO label]] | Jump to label | [[/oracle-to-mariadb/goto|LEAVE label and BEGIN-END block]] {{:exclamation.png|}} |
 +
 +Executing stored procedures from a PL/SQL block:
 +
 +| | **Oracle** || **MariaDB** |
 +| 1 | [[/oracle-to-mariadb/call_proc|sp_name(param1, ...)]] | Execute procedure | [[/oracle-to-mariadb/call_proc|CALL sp_name(param1, ...)]] |
 +|:::| [[/oracle-to-mariadb/call_proc|sp_name]] | Procedure without parameters | [[/oracle-to-mariadb/call_proc|CALL sp_name]] |
 +|:::| [[/oracle-to-mariadb/call_proc|sp_name(param1 => value1, ...)]] | Procedure with named parameters | [[/oracle-to-mariadb/call_proc|sp_name(value1, ...)]] {{:exclamation.png|}} |
 +
 +Error handling:
 +
 +| | **Oracle** || **MariaDB** |
 +| 1 | [[/oracle-to-mariadb/raise_application_error|RAISE_APPLICATION_ERROR(-20xxx, text)]] | Raise user error | [[/oracle-to-mariadb/raise_application_error|SIGNAL SQLSTATE '45xxx' SET MESSAGE_TEXT text]] |
 +
 +===== SQL Statements =====
 +
 +Table manipulation statements:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/alter_table_add_fk|ALTER TABLE ... ADD FOREIGN KEY]] | Add foreign key | [[/oracle-to-mariadb/alter_table_add_fk|ALTER TABLE ... ADD FOREIGN KEY]] \\ Data type issues {{:exclamation.png|}} ||
 +| 2 | [[/oracle-to-mariadb/alter_table_add_unique_using_index|ALTER TABLE ... ADD UNIQUE ... USING INDEX]] | Add unique constraint \\ with existing index | [[/oracle-to-mariadb/alter_table_add_unique_using_index|Index commented]] {{:exclamation.png|}} ||
 +| 3 | [[/oracle-to-mariadb/alter_table_modify|ALTER TABLE ... MODIFY (coldef, ...)]] | Modify column(s) | [[/oracle-to-mariadb/alter_table_modify|ALTER TABLE ... MODIFY coldef, ...]] {{:exclamation.png|}} ||
 +
 +SQL statements and clauses:
 +
 +| | **Oracle** | **MariaDB** |
 +| 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//' |
 +
 +
 +System and Control statements:
 +
 +| | **Oracle** || **MariaDB** ||
 +| 1 | [[/oracle-to-mariadb/alter_user|ALTER USER]] | Alter user | [[/oracle-to-mariadb/alter_user|ALTER USER]] {{:exclamation.png|}} |
 +| 2 | [[/oracle-to-mariadb/create_user|CREATE USER]] | Create user | [[/oracle-to-mariadb/create_user|CREATE USER]] {{:exclamation.png|}} |
 +| 3 | [[/oracle-to-mariadb/grant|GRANT]] | Grant privileges | [[/oracle-to-mariadb/grant|GRANT]] {{:exclamation.png|}} |
 +
 +===== Built-in PL/SQL Packages =====
 +
 +Converting built-in PL/SQL packages:
 +
 +| | **Oracle** || **MariaDB** |
 +| 1 | [[/oracle-to-mariadb/dbms_session|DBMS_SESSION]] | Session information | [[/oracle-to-mariadb/dbms_session|Built-in functions and session variables]] |
 +| 2 | [[/oracle-to-mariadb/dbms_sql|DBMS_SQL]] | Dynamic SQL | [[/oracle-to-mariadb/dbms_sql|PREPARE and EXECUTE statements]] |