https://sqlines.com/ 2026-04-04T00:40:49+00:00 https://sqlines.com/ https://sqlines.com/lib/images/favicon.ico text/html 2021-05-19T09:30:05+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/add_months?rev=1621416605&do=diff In Oracle, ADD_MONTHS function adds the number of month to the specified date value. In MariaDB, you have to use TIMESTAMPADD function even in Oracle Compatibility mode. Oracle: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; -- Add 2 months to the current date SELECT ADD_MONTHS(SYSDATE, 2) FROM dual; # 2021-07-19 12:14:17 text/html 2025-02-18T15:02:12+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/alter_table_add_fk?rev=1739890932&do=diff In Oracle, primary and foreign key columns can have different (compatible) data types with different precision and lengths. MariaDB requires primary and foreign key columns to have exactly the same data types. Oracle: -- Parent table CREATE TABLE parent1 (c1 NUMBER PRIMARY KEY); -- Child tables CREATE TABLE child1 (c1 NUMBER(*,0)); CREATE TABLE child2 (c1 NUMBER(5,0)); ALTER TABLE child1 ADD FOREIGN KEY (c1) REFERENCES parent1 (c1); /* Table altered. */ ALTER TABLE … text/html 2025-02-18T10:51:03+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/alter_table_add_unique_using_index?rev=1739875863&do=diff In Oracle, you can add a unique constraint to a table using an existing unique index on the same columns. In MariaDB, you cannot specify an existing index when adding a unique constraint, and if a unique index already exists, and you add a unique constraint, MariaDB will create a duplicate unique index. text/html 2025-03-05T09:49:34+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/alter_table_modify?rev=1741168174&do=diff In Oracle, you can use the ALTER TABLE statement with the MODIFY clause to modify the definition of one or more columns. In MariaDB, you can also use the MODIFY clause of ALTER TABLE, but the syntax is slightly different. Oracle: CREATE TABLE t1 (c1 VARCHAR2(10), c2 NUMBER(5)); /* Table created. */ -- Modify single column ALTER TABLE t1 MODIFY (c1 VARCHAR2(30) NOT NULL); /* Table altered. */ -- Modify multiple columns ALTER TABLE t1 MODIFY (c1 VARCHAR2(30) NULL, c2 NUMBE… text/html 2025-02-04T09:42:25+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/alter_user?rev=1738662145&do=diff In Oracle and MariaDB, the ALTER USER statement modifies an existing user, but some clauses can differ and require conversion. DEFAULT ROLE ALL Oracle allows you to enable all roles granted to a user at logon. Oracle: ALTER USER "USER1" DEFAULT ROLE ALL; /* User altered. */ text/html 2025-06-05T11:16:40+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/call_proc?rev=1749122200&do=diff In Oracle, you can execute a stored procedures from another stored procedure just specifying its name and parameters. In MariaDB Oracle Compatibility mode, you can use the same syntax, no need to use the CALL statement. MariaDB does not support named parameters even in the Oracle Compatibility mode. text/html 2025-02-28T09:03:57+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/computed_column?rev=1740733437&do=diff In Oracle and MariaDB, you can define a computed (virtual) column that derives its values automatically by computing the specified expression. Note that MariaDB requires the data type of a computed column to be explicitly specified while it is optional in Oracle. text/html 2025-06-06T09:57:52+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/connect_by_prior?rev=1749203872&do=diff In Oracle, you can use CONNECT BY PRIOR clause of the SELECT statement to build hierarchical queries. MariaDB allows you to use Recursive Commom Table Expressions (CTE) to get the same functionality. Rows Generator One of the simplest use of CONNECT BY is to generate an arbitrary number of rows. For example, the following query generates 5 rows: text/html 2025-02-01T16:25:46+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/create_global_temp_table?rev=1738427146&do=diff In Oracle, you can create a global temporary table (GTT) whose definition is visible to all sessions but data is only visible within the session that inserted it. In MariaDB Oracle Compatibility mode, you still have to create a temporary table in each session, as temporary table definitions are not global, they are session-specific and not shared across sessions. text/html 2025-04-15T09:03:54+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/create_synonym?rev=1744707834&do=diff In Oracle, the CREATE SYNONYM statement defines an alternative name for a table, view, stored procedure, user-defined function, or other database object. MariaDB currently does not support synonyms (see MDEV-16482), but you can use a view when you need an alternative name for a table or another view. text/html 2025-02-03T19:01:45+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/create_user?rev=1738609305&do=diff In Oracle and MySQL, the CREATE USER statement creates a new user, but some clauses differ and require conversion. Oracle: -- Create a sample user CREATE USER "USER1" IDENTIFIED BY VALUES 'S:E4406C13C44...;H:6AE38B92223...;T:B99DEE30A...' DEFAULT TABLESPACE "TBS_DATA" TEMPORARY TABLESPACE "TEMP" PROFILE "USER_PROFILE" PASSWORD EXPIRE; text/html 2025-06-25T07:10:01+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/cross_join?rev=1750835401&do=diff In Oracle and MariaDB, the CROSS JOIN clause allows you to perform a cartesian product (cross-product) i.e. join each row from the first table with each row from the second table. Consider sample data: -- Sample tables CREATE TABLE colors (name VARCHAR(30)); CREATE TABLE products (name VARCHAR(30)); -- Sample rows INSERT INTO colors VALUES ('Green'); INSERT INTO colors VALUES ('Red'); INSERT INTO colors VALUES ('Yellow'); INSERT INTO products VALUES ('Apple'); INSERT INTO… text/html 2026-01-10T21:11:27+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/current_date?rev=1768079487&do=diff In Oracle, the CURRENT_DATE function returns the current date and time in the session time zone at the statement execution time. In MariaDB, you can use the NOW() function, which also includes the time and returns the value in the session time zone at the statement execution time. text/html 2026-01-11T13:01:19+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/current_timestamp?rev=1768136479&do=diff In Oracle, the CURRENT_TIMESTAMP function returns the current date and time with fractional seconds and with the time zone (TIMESTAMP WITH TIME ZONE data type) in the session time zone. In MariaDB, you can also use the CURRENT_TIMESTAMP function, which returns the value in the session time zone but does not include the time zone. text/html 2024-11-13T07:38:44+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/date?rev=1731483524&do=diff In Oracle, the DATE data type stores date and time data (year, month, day, hour, minutes and seconds). In MariaDB Oracle compatibility mode, you can also use the DATE data type. Oracle: -- Sample table CREATE TABLE specs ( cnt1 DATE ); text/html 2025-05-29T16:45:40+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/datetime_arithmetic?rev=1748537140&do=diff In Oracle, you can use the + and - operators with DATE (includes time) and TIMESTAMP values i.e. you can add or subtract the specified number of days and fractions (hours, minutes etc.) of the day. In MariaDB, you have to use INTERVAL expressions and functions. text/html 2026-02-05T15:18:14+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/dbms_session?rev=1770304694&do=diff In Oracle, the DBMS_SESSION package can be used to set session-level information that can later be retrieved using the SYS_CONTEXT function. In MariaDB, session-level information for applications can be stored and accessed using session variables. text/html 2025-06-10T08:50:02+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/dbms_sql?rev=1749545402&do=diff In Oracle, the DBMS_SQL built-in package allows you to execute dynamic SQL statements. In MariaDB, you can use the PREPARE and EXECUTE statements. Oracle: -- Sample table CREATE TABLE colors (name VARCHAR2(30), category CHAR(1)); -- Sample procedure CREATE OR REPLACE PROCEDURE sp1(name VARCHAR2, category CHAR) AS cur INTEGER; processed INTEGER; BEGIN cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cur, 'INSERT INTO colors VALUES (:name, :category)', DBMS_SQL.NATI… text/html 2025-05-12T18:43:06+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/default_on_null?rev=1747075386&do=diff In Oracle, you can use the DEFAULT ON NULL clause to apply the column's default value when a NULL is explicitly inserted into the table. MariaDB does not support the ON NULL clause, so you must avoid explicitly specifying NULL to apply the column's default value when migrating Oracle tables with DEFAULT ON NULL. text/html 2025-12-23T09:10:01+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/dense_rank?rev=1766481001&do=diff In Oracle and MariaDB, the DENSE_RANK window function assigns a rank without gaps when the preceding rows have identical values. But Oracle and MariaDB handle NULL values differently. Consider an example: CREATE TABLE colors (name VARCHAR(30)); -- Insert sample rows INSERT INTO colors VALUES ('Green'); INSERT INTO colors VALUES ('Red'); INSERT INTO colors VALUES (NULL); INSERT INTO colors VALUES ('Blue'); INSERT INTO colors VALUES ('Green'); text/html 2025-06-06T19:41:50+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/empty_blob?rev=1749238910&do=diff In Oracle, the EMPTY_BLOB function creates an empty BLOB locator i.e. an empty (with 0 length), but non-NULL BLOB value. In MariaDB, you can use x'' constant (empty binary string). Oracle: -- A sample table CREATE TABLE t1 ( c1 BLOB DEFAULT EMPTY_BLOB() ); text/html 2025-06-06T20:01:22+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/empty_clob?rev=1749240082&do=diff In Oracle, the EMPTY_CLOB function creates an empty CLOB locator i.e. an empty (with 0 length), but non-NULL CLOB value. In MariaDB, you can use '' constant (empty string). Oracle: -- A sample table CREATE TABLE t1 ( c1 CLOB DEFAULT EMPTY_CLOB() ); text/html 2025-05-30T15:49:15+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/execute_immediate?rev=1748620155&do=diff In Oracle, you can execute a dynamic SQL using the EXECUTE IMMEDIATE statement. MariaDB supports the EXECUTE IMMEDIATE statement in the Oracle Compatibility mode, but there are some restrictions (see below). Oracle: CREATE OR REPLACE PROCEDURE sp1 AS BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dual'; END; / /* Procedure created.*/ text/html 2025-12-24T09:04:12+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/extract?rev=1766567052&do=diff In Oracle and MariaDB, the EXTRACT function returns the value of a specified datetime field (year, month, day etc.) from a datetime. Oracle: -- Get the day of December 24, 2025 SELECT EXTRACT(DAY FROM DATE '2025-12-24') FROM dual; /* 24 */ text/html 2025-06-04T14:26:19+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/goto?rev=1749047179&do=diff In Oracle, the GOTO statement allows you to unconditionally jump to a specified part of the code. MariaDB supports the GOTO statement and <<name>> label syntax in the Oracle Compatibility mode. Oracle: -- A sample procedure CREATE OR REPLACE PROCEDURE sp1 AS BEGIN DBMS_OUTPUT.PUT_LINE('A'); GOTO label; DBMS_OUTPUT.PUT_LINE('B'); <<label>> DBMS_OUTPUT.PUT_LINE('C'); END; / text/html 2025-02-04T18:49:18+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/grant?rev=1738694958&do=diff In Oracle and MariaDB, the GRANT statement grants provileges or roles to a user, but some clauses differ and require conversion. Oracle MariaDB - Oracle Compatibility 1 GRANT DEBUG ON tab TO user Access with debugger Commented 2 GRANT CONNECT TO user Connect to the database GRANT USAGE ON *.* TO user 3 GRANT CREATE SESSION TO user Connect to the database GRANT USAGE ON *.* TO user 4 GRANT CREATE SEQUENCE TO user Create sequences GRANT CREATE ON *.* TO user 5 GRANT CREATE S… text/html 2026-01-30T18:34:08+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/instr?rev=1769798048&do=diff In Oracle the INSTR function allows you to find the position of a substring within a string. It accepts 2, 3 or 4 parameters. Oracle's INSTR function also supports a negative starting position, meaning that it is counted backward from the end of the string and the search is performed backward from the resulting position. text/html 2025-06-15T18:15:52+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/is_json?rev=1750011352&do=diff In Oracle, the IS JSON boolean condition checks if the specified value is valid JSON data. In MariaDB, you can use the JSON_VALID function. Oracle: -- IS JSON returns TRUE for valid JSON, and FALSE for invalid SELECT CASE WHEN '{"a":1}' IS JSON THEN 'Ok' ELSE 'Not Valid' END AS c1 FROM dual; text/html 2021-05-25T15:10:15+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/last_day?rev=1621955415&do=diff In Oracle and MariaDB, LAST_DAY function returns the date of the last day of the month for the specified datetime expression. Note that Oracle LAST_DAY returns the DATE data type that includes both date and time, while MariaDB LAST_DAY returns the DATE data type that includes the date part only (year, month and day). text/html 2025-12-22T14:35:12+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/listagg?rev=1766414112&do=diff In Oracle you can use the LISTAGG function to concatenate strings from multiple rows into a single row value. In MariaDB you can use the GROUP_CONCAT function. Note that LISTAGG uses '' empty string as the default separator, whereas GROUP_CONCAT uses a comma by default. text/html 2025-12-15T09:22:52+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/next_day?rev=1765790572&do=diff In Oracle, the NEXT_DAY function returns the date of the next specified weekday following a given date. In MariaDB, you can use an expression with the DATE_ADD and WEEKDAY functions to get the same result. Oracle: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; -- Get the date of next Thursday following 2025-12-15 (Monday), the time part remained unchanged SELECT NEXT_DAY(TIMESTAMP '2025-12-15 10:34:17', 'Thursday') FROM dual; /* 2025-12-18 10:34:17 */ text/html 2024-11-12T16:05:25+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/number?rev=1731427525&do=diff In Oracle, the NUMBER is the main data type to store all numeric values including integers, fixed and floating point numbers. In MariaDB Oracle compatibility mode, you can also use the NUMBER data type but note some issues and restrictions outlined below. text/html 2025-02-03T09:15:44+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/nvarchar2?rev=1738574144&do=diff In Oracle, the NVARCHAR2(n) data type stores variable-length Unicode character strings of up to n characters. The maximum size is 4,000 or 32,767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDED (which is not set by default). text/html 2025-12-22T09:35:30+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/nvl?rev=1766396130&do=diff In Oracle, the NVL function allows you to replace NULL with the specified expression: it returns the first operand if it is not NULL, and the second operand otherwise. MariaDB also provides the NVL function (since 10.3.1) in both native and Oracle compatibility modes. text/html 2025-12-22T09:55:44+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/nvl2?rev=1766397344&do=diff In Oracle, the NVL2(exp, exp2, exp3) function evaluates exp and returns exp2 if exp is not NULL, otherwise, it returns exp3. MariaDB also provides the NVL2 function (since 10.3.1) in both native and Oracle compatibility modes. Oracle: -- Replace NULL SELECT NVL2('John', 'A', 'N/A') FROM dual; /* A */ -- Replace NULL SELECT NVL2(NULL, 'A', 'N/A') FROM dual; /* N/A */ text/html 2025-05-26T12:47:21+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/order_by_nulls?rev=1748263641&do=diff Oracle allows you to specify how NULL values are ordered by ORDER BY clause. MariaDB does not support this so queries can return different results as NULLs are last in Oracle and first in MariaDB by default. Consider sample data: CREATE TABLE colors (name VARCHAR(30), category CHAR(1)); INSERT INTO colors VALUES ('Green', 'G'); INSERT INTO colors VALUES ('Black', NULL); INSERT INTO colors VALUES ('Red', 'R'); text/html 2025-05-15T19:16:30+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/partition_by_list?rev=1747336590&do=diff Both Oracle and MariaDB support list partitioning, which allows you to partition a table based on a list of values for a specified column, but some clauses differ and require conversion. Oracle: -- A sample list-partitioned table CREATE TABLE orders ( name VARCHAR2(30), category CHAR(1) ) PARTITION BY LIST (category) (PARTITION "A" VALUES ('A'), PARTITION "B" VALUES ('B'), PARTITION "OTHERS" VALUES (DEFAULT)); /* Table created. */ -- Inserting a sample ro… text/html 2025-02-26T07:07:54+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/partition_by_range?rev=1740553674&do=diff Both Oracle and MariaDB support range partitioning, which allows you to partition a table based on a range of values for a specified column, but some clauses differ and require conversion. Oracle: -- A sample range-partitioned table CREATE TABLE orders ( name CHAR(1), created_dt TIMESTAMP(6) ) PARTITION BY RANGE (created_dt) INTERVAL (NUMTODSINTERVAL(1, 'DAY')) (PARTITION "P_01JAN2025" VALUES LESS THAN (TIMESTAMP '2025-01-01 00:00:00'), PARTITION "P_02JAN20… text/html 2024-11-13T11:11:45+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/raise_application_error?rev=1731496305&do=diff In Oracle, the RAISE_APPLICATION_ERROR procedure allows you to raise an user-defined error with the error number in the negative range from -20000 to -20999. In MariaDB Oracle compatibility mode, you can use the SIGNAL statement with the recommended SQLSTATE value '45000' for user-defined exceptions. Note that in future versions, MariaDB will support the RAISE_APPLICATION_ERROR procedure, see MDEV-20649. text/html 2025-12-23T09:11:48+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/rank?rev=1766481108&do=diff In Oracle and MariaDB, the RANK window function assigns a rank with gaps when the preceding rows have identical values. But Oracle and MariaDB handle NULL values differently. Consider an example: CREATE TABLE colors (name VARCHAR(30)); -- Insert sample rows INSERT INTO colors VALUES ('Green'); INSERT INTO colors VALUES ('Red'); INSERT INTO colors VALUES (NULL); INSERT INTO colors VALUES ('Blue'); INSERT INTO colors VALUES ('Green'); text/html 2026-01-14T08:55:04+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/regexp_substr?rev=1768380904&do=diff In Oracle and MariaDB, the REGEXP_SUBSTR function returns the substring that matches the regular expression. Oracle's REGEXP_SUBSTR allows specifying four parameters, including the starting position and the Nth occurrence, while MariaDB's REGEXP_SUBSTR allows specifying only two parameters. text/html 2021-05-24T07:06:47+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/replace?rev=1621840007&do=diff In Oracle and MariaDB, the REPLACE function allows you to replace or remove the specified substring from a string. Oracle REPLACE accepts 2 or 3 parameters, while MariaDB REPLACE always requires 3 parameters even in Oracle Compatibility mode. Oracle: text/html 2025-05-29T17:14:49+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/round?rev=1748538889&do=diff In Oracle and MariaDB, the ROUND function rounds the number to the nearest value with the specified precision Oracle: -- Round to integer SELECT ROUND(123.4) FROM dual; /* 123 */ -- Round to 1 decimal digit (rounded to lower value as 4 <= 5) SELECT ROUND(123.34, 1) FROM dual; /* 123.30 */ -- Round to 1 decimal digit (rounded to upper value as 6 > 5) SELECT ROUND(123.36, 1) FROM dual; /* 123.40 */ text/html 2019-06-04T11:15:03+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/sequence_order?rev=1559646903&do=diff In Oracle you can specify the ORDER option in CREATE SEQUENCE statement to guarantee that sequence numbers are generated in order of request. Note that this option has relevance to Oracle RAC databases only: Oracle: CREATE SEQUENCE sales_seq ORDER; -- Sequence created. text/html 2026-02-02T08:09:44+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/soundex?rev=1770019784&do=diff In Oracle and MariaDB, the SOUNDEX function returns a phonetic representation of a specified string. Oracle: SELECT SOUNDEX('Orange') FROM dual; /* O652 */ MariaDB - Oracle Compatibility: SELECT SOUNDEX('Orange'); /* O652 */ For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode. text/html 2025-12-17T12:25:14+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/statement-level-triggers?rev=1765974314&do=diff In Oracle, if you omit the FOR EACH ROW clause in CREATE TRIGGER, then the trigger is a statement trigger which is executed once per triggering statement. MariaDB does not support statement-level triggers, and the FOR EACH ROW clause is mandatory. text/html 2025-03-25T09:07:08+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/substr?rev=1742893628&do=diff In Oracle and MariaDB, the SUBSTR function returns a substring from a string, starting at the specified position (which can be negative) and for the specified length, or until the end of the string if no length is provided. In Oracle, a start position of 0 is treated as 1. In MariaDB (since 10.3.3), this behavior only occurs when Oracle Compatibility mode is enabled. text/html 2026-02-05T15:29:49+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/sys_context?rev=1770305389&do=diff In Oracle, the SYS_CONTEXT function retrieves system or session-level information previously set by the user application using the DBMS_SESSION.SET_CONTEXT procedure. In MariaDB, system information is retrieved using dedicated built-in functions, while session-level information for applications can be stored and accessed using session variables. text/html 2025-02-26T14:50:09+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/sys_guid?rev=1740581409&do=diff In Oracle, the SYS_GUID function generates an Universal Unique Identifier (UUID) of RAW(16) data type. In MariaDB, you can use the SYS_GUID (MariaDB 10.6.1 and later) and UUID functions that return a UUID as a string. Note that Oracle's SYS_GUID() returns a value without hyphens (-) and in uppercase, while MariaDB's SYS_GUID also returns a value without hyphens but in lowercase, and UUID() returns a value with hyphens and in lowercase. text/html 2026-01-11T13:40:54+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/sysdate?rev=1768138854&do=diff In Oracle, the SYSDATE function returns the current date and time for the database server's host operating system at the statement execution time. In MariaDB, you can use the NOW() and SYSDATE() functions (parentheses are required), which also include the time. They both return the value in the session time zone. text/html 2026-01-11T13:45:16+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/systimestamp?rev=1768139116&do=diff In Oracle, the SYSTIMESTAMP function returns the current date and time with fractional seconds and with the time zone (TIMESTAMP WITH TIME ZONE data type) for the database server's host operating system. In MariaDB, you can use the NOW(6) function, which returns the value in the session time zone and does not include the time zone. text/html 2025-02-20T10:21:19+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/timestamp?rev=1740046879&do=diff In Oracle, the TIMESTAMP(p) data type stores date and time with fractional seconds but without time zone. In MariaDB Oracle compatibility mode, you can also use the TIMESTAMP(p) data type. Oracle: -- Sample table with TIMESTAMP with milliseconds accuracy CREATE TABLE specs ( item VARCHAR2(30), created_dt TIMESTAMP(3) ); text/html 2026-01-06T18:17:30+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/to_char_datetime?rev=1767723450&do=diff In Oracle, the TO_CHAR function converts a datetime value to string using a specified format. In MariaDB, you can use the DATE_FORMAT function. Note that the TO_CHAR and DATE_FORMAT format strings are different. MariaDB supports the TO_CHAR function starting with version 10.6.1 in both native and Oracle compatibility mode, with support limited to the following format specifiers: YYYY, YY, RRRR, RR, MM, MON, MONTH, MI, DD, DY, DAY, HH, HH12, HH24 and SS. text/html 2025-12-24T15:45:35+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/to_date?rev=1766591135&do=diff In Oracle, the TO_DATE function converts a string value to DATE data type value using the specified format. In MariaDB, you can use the STR_TO_DATE function. Note that the TO_DATE and STR_TO_DATE format strings are different. Oracle: -- Specify a datetime string literal and its exact format SELECT TO_DATE('2020-10-25', 'YYYY-MM-DD') FROM dual; text/html 2026-02-02T09:51:53+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/to_timestamp?rev=1770025913&do=diff In Oracle, the TO_TIMESTAMP function converts a string value to a TIMESTAMP data type value using a specified format. In MariaDB, you can use the STR_TO_DATE function. Note that the TO_TIMESTAMP and STR_TO_DATE format strings are different. Oracle: text/html 2025-12-15T14:23:49+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/trunc_datetime?rev=1765808629&do=diff In Oracle, the TRUNC(datetime, 'unit') function truncates a datetime value to the specified unit (for example, setting the time to zero or setting the date to the first day of the month). By default, it truncates to the day. In MariaDB, the TRUNC(datetime, 'unit') function is available since version 12.2 in the Oracle Compatibility mode only. In earlier versions, you can use the CAST, DATE and DATE_FORMAT functions. text/html 2025-05-21T13:43:45+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/user?rev=1747835025&do=diff In Oracle, the USER function returns the name of the session user. In MariaDB, you can use the USER() function. Oracle: -- Get the current user SELECT USER FROM dual; MariaDB - Oracle Compatibility: -- Get the current user SELECT USER(); text/html 2026-02-02T11:35:05+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/userenv?rev=1770032105&do=diff In Oracle, the USERENV function returns information about the current session. In MariaDB, session information must be retrieved using dedicated functions for each specific attribute. Oracle: -- Get the current session ID SELECT USERENV('SESSIONID') FROM dual; /* 797676 */ text/html 2024-09-01T08:53:16+00:00 https://sqlines.com/oracle-to-mariadb-compatibility/varchar2?rev=1725180796&do=diff In Oracle, the VARCHAR2(n) data type stores variable-length character strings up to n bytes (default) or characters with the maximum of 4000 or 32,767 bytes if MAX_STRING_SIZE initialization parameter is set to EXTENDED (not set by default). In MariaDB Oracle compatibility mode, you can also use VARCHAR2(n) that can store up to 65,535 characters. But effective maximum length is subject to the maximum row size limit of 65,535 bytes (shared among all columns) and the character set used.