https://www.sqlines.com/ 2026-04-17T08:39:48+00:00 https://www.sqlines.com/ https://www.sqlines.com/lib/images/favicon.ico text/html 2021-05-19T09:30:28+00:00 https://www.sqlines.com/oracle-to-mysql/add_months?rev=1621416628&do=diff In Oracle, ADD_MONTHS function adds the number of month to the specified date value. In MySQL, you have to use TIMESTAMPADD function with the MONTH datetime unit. 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:28:52 text/html 2025-02-18T15:04:29+00:00 https://www.sqlines.com/oracle-to-mysql/alter_table_add_fk?rev=1739891069&do=diff In Oracle, primary and foreign key columns can have different (compatible) data types with different precision and lengths. MySQL 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 ch… text/html 2025-02-18T06:36:43+00:00 https://www.sqlines.com/oracle-to-mysql/alter_table_add_unique_using_index?rev=1739860603&do=diff In Oracle, you can add a unique constraint to a table using an existing unique index on the same columns. In MySQL, you cannot specify an existing index when adding a unique constraint, and if a unique index already exists, and you add a unique constraint, MySQL will create a duplicate unique index. text/html 2025-03-05T09:51:13+00:00 https://www.sqlines.com/oracle-to-mysql/alter_table_modify?rev=1741168273&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 MySQL, 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 NUMBER(… text/html 2025-02-04T09:33:53+00:00 https://www.sqlines.com/oracle-to-mysql/alter_user?rev=1738661633&do=diff In Oracle and MySQL, the ALTER USER statement modifies an existing user, but some clauses can differ and require conversion. DEFAULT ROLE ALL Both Oracle and MySQL allow 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:19:01+00:00 https://www.sqlines.com/oracle-to-mysql/call_proc?rev=1749122341&do=diff In Oracle, you can execute a stored procedure from another stored procedure just specifying its name and parameters. In MySQL, you have to use the CALL statement. MySQL does not support named parameters. Oracle: -- Sample procedure with parameters CREATE OR REPLACE PROCEDURE sp1(param1 VARCHAR2, param2 VARCHAR2) IS var1 VARCHAR2(60); BEGIN var1 := param1 || ', ' || param2 || '!'; END; / -- Sample procedure without parameters CREATE OR REPLACE PROCEDURE sp2 IS… text/html 2022-10-12T11:55:14+00:00 https://www.sqlines.com/oracle-to-mysql/char?rev=1665575714&do=diff Oracle and MySQL have CHAR(n) data type that stores a fixed-length character string right-padded with blanks to the specified n length. Oracle MySQL CHAR CHAR CHAR(n) CHAR(n) n <= 255 CHAR(n CHAR) Size in characters VARCHAR(n) n > 255 CHAR(n BYTE) Size in bytes 1 <= n <= 2000, default is 1 0 <= n <= 255, default is 1 Size n in characters or bytes, but the storage limit is 2000 bytes Size n in characters Default length semantics is defined by NLS_LENGTH_SEMANTICS parameter … text/html 2025-02-28T09:05:57+00:00 https://www.sqlines.com/oracle-to-mysql/computed_column?rev=1740733557&do=diff In Oracle and MySQL, you can define a computed (virtual) column that derives its values automatically by computing the specified expression. Note that MySQL requires the data type of a computed column to be explicitly specified while it is optional in Oracle. text/html 2025-06-05T20:50:20+00:00 https://www.sqlines.com/oracle-to-mysql/connect_by_prior?rev=1749156620&do=diff In Oracle, you can use CONNECT BY PRIOR clause of the SELECT statement to build hierarchical queries. MySQL 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:20:46+00:00 https://www.sqlines.com/oracle-to-mysql/create_global_temp_table?rev=1738426846&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 MySQL, you 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 2022-06-19T21:02:49+00:00 https://www.sqlines.com/oracle-to-mysql/create_trigger?rev=1655672569&do=diff CREATE TRIGGER Statement - Oracle to MySQL Migration Both Oracle and MySQL offer CREATE TRIGGER statement that allows you to define a trigger on table. But the syntax of this statement is different in these databases: For example, let's create a trigger as follows: text/html 2025-02-03T16:32:33+00:00 https://www.sqlines.com/oracle-to-mysql/create_user?rev=1738600353&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:11:59+00:00 https://www.sqlines.com/oracle-to-mysql/cross_join?rev=1750835519&do=diff In Oracle and MySQL, 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 p… text/html 2026-01-08T20:03:15+00:00 https://www.sqlines.com/oracle-to-mysql/current_date?rev=1767902595&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 MySQL, 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-10T20:45:23+00:00 https://www.sqlines.com/oracle-to-mysql/current_timestamp?rev=1768077923&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 MySQL, 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 2013-04-11T21:33:22+00:00 https://www.sqlines.com/oracle-to-mysql/data_validation?rev=1365716002&do=diff SQLines Data tool helps you validate the data transfer from Oracle to MySQL after migration. Row Count Check The fastest and simplest way that can reveal a critical problem in data migration is to check the number of rows in corresponding Oracle and MySQL tables. text/html 2025-05-29T16:43:02+00:00 https://www.sqlines.com/oracle-to-mysql/datetime_arithmetic?rev=1748536982&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 MySQL, you have to use INTERVAL expressions and functions. text/html 2026-02-04T18:47:29+00:00 https://www.sqlines.com/oracle-to-mysql/dbms_session?rev=1770230849&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 MySQL, session-level information for applications can be stored and accessed using session variables. text/html 2025-06-10T08:54:18+00:00 https://www.sqlines.com/oracle-to-mysql/dbms_sql?rev=1749545658&do=diff In Oracle, the DBMS_SQL built-in package allows you to execute dynamic SQL statements. In MySQL, you can use the PREPARE and EXECUTE statements. Oracle: -- Sample table CREATE TABLE colors (name VARCHAR(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.NATIVE)… text/html 2025-05-12T18:46:08+00:00 https://www.sqlines.com/oracle-to-mysql/default_on_null?rev=1747075568&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. MySQL 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-23T08:45:09+00:00 https://www.sqlines.com/oracle-to-mysql/dense_rank?rev=1766479509&do=diff In Oracle and MySQL, the DENSE_RANK window function assigns a rank without gaps when the preceding rows have identical values. But Oracle and MySQL 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:47:20+00:00 https://www.sqlines.com/oracle-to-mysql/empty_blob?rev=1749239240&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 MySQL, you can use x'' constant (empty binary string). Oracle: -- A sample table CREATE TABLE t1 ( c1 BLOB DEFAULT EMPTY_BLOB() ); text/html 2025-06-06T19:59:23+00:00 https://www.sqlines.com/oracle-to-mysql/empty_clob?rev=1749239963&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 MySQL, you can use '' constant (empty string). Oracle: -- A sample table CREATE TABLE t1 ( c1 CLOB DEFAULT EMPTY_CLOB() ); text/html 2025-12-24T09:05:16+00:00 https://www.sqlines.com/oracle-to-mysql/extract?rev=1766567116&do=diff In Oracle and MySQL, 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-03T16:12:44+00:00 https://www.sqlines.com/oracle-to-mysql/goto?rev=1748967164&do=diff In Oracle, the GOTO statement allows you to unconditionally jump to a specified part of the code. MySQL does not support the GOTO statement, but you can use the LEAVE statement with a labeled BEGIN-END block. 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-04T16:37:01+00:00 https://www.sqlines.com/oracle-to-mysql/grant?rev=1738687021&do=diff In Oracle and MySQL, the GRANT statement grants provileges or roles to a user, but some clauses differ and require conversion. Oracle MySQL 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 Sequences are not supported 5 GRANT CREATE SYNONYM TO user Create syn… text/html 2022-10-20T21:28:19+00:00 https://www.sqlines.com/oracle-to-mysql/identity?rev=1666301299&do=diff IDENTITY column property allows you to automatically generate sequential integer numbers (IDs) for a column. IDENTITY columns are available since Oracle 12c. In MySQL you can use AUTO_INCREMENT column property. Note that you can use the AUTO_INCREMENT table property to define the start value, but you cannot specify the increment step, it is always 1. See AUTO_INCREMENT for more details. text/html 2021-12-09T13:44:04+00:00 https://www.sqlines.com/oracle-to-mysql/initcap?rev=1639057444&do=diff In Oracle the INITCAP function capitalizes the first letter of each word and lowercases all other letters in a string. Words are delimited by a space or any other non-alphanumeric letter. In MySQL you can use a user-define function (UDF) to implement this functionality (see below). text/html 2026-01-30T18:31:09+00:00 https://www.sqlines.com/oracle-to-mysql/instr?rev=1769797869&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-15T08:53:36+00:00 https://www.sqlines.com/oracle-to-mysql/is_json?rev=1749977616&do=diff In Oracle, the IS JSON boolean condition checks if the specified value is valid JSON data. In MySQL, 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:49+00:00 https://www.sqlines.com/oracle-to-mysql/last_day?rev=1621955449&do=diff In Oracle and MySQL, 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 MySQL LAST_DAY returns the DATE data type that includes the date part only (year, month and day). text/html 2021-09-06T16:06:26+00:00 https://www.sqlines.com/oracle-to-mysql/length?rev=1630944386&do=diff In Oracle the LENGTH function returns the string length in characters. MySQL also provides LENGTH function, but it returns the string length in bytes, so you have to convert Oracle LENGTH to MySQL CHAR_LENGTH function that returns the string length in characters. text/html 2025-12-22T14:33:40+00:00 https://www.sqlines.com/oracle-to-mysql/listagg?rev=1766414020&do=diff In Oracle you can use the LISTAGG function to concatenate strings from multiple rows into a single row value. In MySQL 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 2021-09-07T11:16:24+00:00 https://www.sqlines.com/oracle-to-mysql/lpad?rev=1631013384&do=diff In Oracle, the LPAD function returns the string left-padded to the specified number of characters. LPAD accepts 2 or 3 parameters in Oracle. MySQL also provides the LPAD function, but it requires 3 parameters, so when converting Oracle LPAD with 2 parameters, you have to add ' ' as the 3rd parameter in MySQL. text/html 2021-09-13T21:28:52+00:00 https://www.sqlines.com/oracle-to-mysql/months_between?rev=1631568532&do=diff In Oracle, MONTHS_BETWEEN(date1, date2) function returns the number of months between two dates as a decimal number. Note that MySQL TIMESTAMPDIFF(month, date2, date1) function does not return exactly the same result, and you have to use an user-defined function if you need to fully emulate the Oracle MONTHS_BETWEEN function (see UDF's code below). text/html 2025-02-17T13:25:39+00:00 https://www.sqlines.com/oracle-to-mysql/namespaces?rev=1739798739&do=diff In Oracle, tables, views, procedures, functions, packages and sequences share the same namespace, meaning they cannot have identical names with other types of objects within a schema. At the same time, constraints, indexes and triggers have own namespaces, so they can have identical names with other types of objects within a schema in Oracle. text/html 2025-12-15T09:12:13+00:00 https://www.sqlines.com/oracle-to-mysql/next_day?rev=1765789933&do=diff In Oracle, the NEXT_DAY function returns the date of the next specified weekday following a given date. In MySQL, 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 2013-04-06T21:28:04+00:00 https://www.sqlines.com/oracle-to-mysql/null_order_by?rev=1365283684&do=diff In Oracle, when you sort columns in ORDER BY clause NULL values go last for ascending order, and first for descending order, by default. In MySQL, the behavior is different, NULL values go first for ascending order, and last for descending order. text/html 2024-09-27T18:47:38+00:00 https://www.sqlines.com/oracle-to-mysql/number?rev=1727462858&do=diff In Oracle, the NUMBER is the main data type to store all numeric values including integers, fixed and floating point numbers. In MySQL, you can use TINYINT, SMALLINT, INT and BIGINT for integers, DECIMAL or NUMERIC for fixed point numbers, FLOAT and DOUBLE for approximate floating point numbers. text/html 2025-02-03T08:23:49+00:00 https://www.sqlines.com/oracle-to-mysql/nvarchar2?rev=1738571029&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:33:50+00:00 https://www.sqlines.com/oracle-to-mysql/nvl?rev=1766396030&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. In MySQL you can use the IFNULL function. Oracle: -- Replace NULL SELECT NVL('John', 'N/A') FROM dual; /* John */ -- Replace NULL SELECT NVL(NULL, 'N/A') FROM dual; /* N/A */ text/html 2024-11-26T10:24:31+00:00 https://www.sqlines.com/oracle-to-mysql/offset_fetch_first?rev=1732616671&do=diff In Oracle 12c and later you can use OFFSER and FETCH FIRST (or NEXT) clause to limit the rows returned by the query. In MySQL you can use LIMIT clause, but it does not support all Oracle clauses, see below. Consider the following sample table: CREATE TABLE products (name VARCHAR(30), category INT); INSERT INTO products VALUES ('Apple', 1); INSERT INTO products VALUES ('Melon', 1); INSERT INTO products VALUES ('Orange', 2); text/html 2024-10-10T16:41:53+00:00 https://www.sqlines.com/oracle-to-mysql/order_by_nulls?rev=1728578513&do=diff Oracle allows you to specify how NULL values are ordered by ORDER BY clause. MySQL does not support this so queries can return different results as NULLs are last in Oracle and first in MySQL 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:20:23+00:00 https://www.sqlines.com/oracle-to-mysql/partition_by_list?rev=1747336823&do=diff Both Oracle and MySQL 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')); /* Table created. */ -- Inserting a sample row INSERT INTO orders VALUES ('Apple', 'A'… text/html 2025-02-26T07:10:04+00:00 https://www.sqlines.com/oracle-to-mysql/partition_by_range?rev=1740553804&do=diff Both Oracle and MySQL 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_02JAN2025… text/html 2021-09-07T15:56:21+00:00 https://www.sqlines.com/oracle-to-mysql/power?rev=1631030181&do=diff In Oracle, the POWER function raises a value to the specified power. MySQL also provides the POWER function, so no conversion is required. Oracle: -- Raise 2 to 3 power SELECT POWER(2, 3) FROM dual; # 8 MySQL: -- Raise 2 to 3 power SELECT POWER(2, 3); # 8 text/html 2024-11-13T11:31:50+00:00 https://www.sqlines.com/oracle-to-mysql/raise_application_error?rev=1731497510&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 MySQL, you can use the SIGNAL statement with the recommended SQLSTATE value '45000' for user-defined exceptions. text/html 2025-12-23T09:15:24+00:00 https://www.sqlines.com/oracle-to-mysql/rank?rev=1766481324&do=diff In Oracle and MySQL, the RANK window function assigns a rank with gaps when the preceding rows have identical values. But Oracle and MySQL 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-14T07:26:34+00:00 https://www.sqlines.com/oracle-to-mysql/regexp_substr?rev=1768375594&do=diff In Oracle and MySQL, the REGEXP_SUBSTR function returns the substring that matches the regular expression. Oracle: -- Match a string until a comma and return the 2nd occurrence SELECT REGEXP_SUBSTR('aa,bbb,cccc', '[^,]+', 1, 2) FROM dual; /* bbb */ -- Match a string until a blank and return the 3rd occurrence SELECT REGEXP_SUBSTR('aa bbb cccc', '[^[:blank:]]+', 1, 3) FROM dual; /* cccc */ -- Start at position 4 and return the 1st occurrence SELECT REGEXP_SUBSTR('aa bbb c… text/html 2021-05-24T13:37:32+00:00 https://www.sqlines.com/oracle-to-mysql/replace?rev=1621863452&do=diff In Oracle and MySQL, the REPLACE function allows you to replace or remove the specified substring from a string. Note that Oracle REPLACE accepts 2 or 3 parameters, while MySQL REPLACE requires 3 parameters. Oracle: -- When 2 parameters are specified the substring is removed SELECT REPLACE('abc', 'b') FROM dual; # ab -- Replacing with empty string '' is another way to remove the substring SELECT REPLACE('abc', 'b', '') FROM dual; # ab -- Third parameter allows you to … text/html 2025-05-29T17:15:46+00:00 https://www.sqlines.com/oracle-to-mysql/round?rev=1748538946&do=diff In Oracle and MySQL, 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 2024-11-04T09:28:26+00:00 https://www.sqlines.com/oracle-to-mysql/rownum?rev=1730712506&do=diff In Oracle, the ROWNUM pseudo-column returns the order number of rows selected from a table. It is usually used to limit the number of rows returned by a query, but ROWNUM can be also used to generate row numbers. Note that ROWNUM is applied before ORDER BY. text/html 2021-09-07T13:50:28+00:00 https://www.sqlines.com/oracle-to-mysql/rpad?rev=1631022628&do=diff In Oracle, the RPAD function returns the string right-padded to the specified number of characters. RPAD accepts 2 or 3 parameters in Oracle. MySQL also provides the RPAD function, but it requires 3 parameters, so when converting Oracle RPAD with 2 parameters, you have to add ' ' as the 3rd parameter in MySQL. text/html 2013-10-25T06:53:02+00:00 https://www.sqlines.com/oracle-to-mysql/sequence?rev=1382683982&do=diff MySQL does not provide a sequence object, but you can use stored procedures and user-defined functions to emulate DROP SEQUENCE, CREATE SEQUENCE statements and NEXTVAL function. Note that often it is enough to use AUTO_INCREMENT columns in MySQL instead of sequences, especially when they are just used for generate column IDs and not shared by multiple tables. text/html 2024-07-01T16:31:22+00:00 https://www.sqlines.com/oracle-to-mysql/skip_locked?rev=1719851482&do=diff In Oracle, FOR UPDATE SKIP LOCKED clause is usually used to select and process tasks from a queue by multiple concurrent sessions. It allows a session to query the queue table, skip rows currently locked by other sessions, select the next unlocked row, and lock it for processing. text/html 2026-02-02T08:10:41+00:00 https://www.sqlines.com/oracle-to-mysql/soundex?rev=1770019841&do=diff In Oracle and MySQL, the SOUNDEX function returns a phonetic representation of a specified string. Oracle: SELECT SOUNDEX('Orange') FROM dual; /* O652 */ MySQL: SELECT SOUNDEX('Orange'); /* O652 */ For more information, see Oracle to MySQL Migration. text/html 2025-12-17T12:26:54+00:00 https://www.sqlines.com/oracle-to-mysql/statement-level-triggers?rev=1765974414&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. MySQL does not support statement-level triggers, and the FOR EACH ROW clause is mandatory. text/html 2024-11-19T15:34:59+00:00 https://www.sqlines.com/oracle-to-mysql/string_concat?rev=1732030499&do=diff In Oracle you can use || operator to concatenate strings. In MySQL you have to use CONCAT function. Oracle: -- Concatenate strings SELECT 'New ' || 'York ' || 'City' FROM dual; # New York City MySQL: -- Concatenate strings SELECT CONCAT('New ', 'York ', 'City'); # New York City text/html 2024-07-15T12:14:51+00:00 https://www.sqlines.com/oracle-to-mysql/subquery_alias?rev=1721045691&do=diff In Oracle, you can use a SELECT subquery without specifying an alias. In MySQL you have to specify aliases for subqueries in SELECT statements. Oracle: -- No alias in subquery SELECT * FROM (SELECT * FROM cities); # Ok -- Alias 't' is specified (it is optional) SELECT * FROM (SELECT * FROM cities) t; # Ok text/html 2025-03-25T09:17:55+00:00 https://www.sqlines.com/oracle-to-mysql/substr?rev=1742894275&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, whereas in MySQL, if you specify 0 as the start position, an empty '' string is returned. text/html 2026-02-05T10:40:26+00:00 https://www.sqlines.com/oracle-to-mysql/sys_context?rev=1770288026&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 MySQL, 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:59:01+00:00 https://www.sqlines.com/oracle-to-mysql/sys_guid?rev=1740581941&do=diff In Oracle, the SYS_GUID function generates an Universal Unique Identifier (UUID) of RAW(16) data type. In MySQL, you can use the UUID function that returns a UUID as a string. Note that Oracle's SYS_GUID() returns a value without hyphens (-) and in uppercase, while MariaDB's UUID() returns a value with hyphens and in lowercase. text/html 2026-01-08T20:01:36+00:00 https://www.sqlines.com/oracle-to-mysql/sysdate?rev=1767902496&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 MySQL, 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-09T09:50:13+00:00 https://www.sqlines.com/oracle-to-mysql/systimestamp?rev=1767952213&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 MySQL, 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:14:58+00:00 https://www.sqlines.com/oracle-to-mysql/timestamp?rev=1740046498&do=diff In Oracle, the TIMESTAMP(p) data type stores date and time with fractional seconds but without time zone. In MySQL, you can use the DATETIME(p) data type. Oracle: -- Sample table with TIMESTAMP with milliseconds accuracy CREATE TABLE specs ( item VARCHAR2(30), created_dt TIMESTAMP(3) ); text/html 2013-03-22T16:32:50+00:00 https://www.sqlines.com/oracle-to-mysql/timestamp_with_time_zone?rev=1363969970&do=diff Oracle provides TIMESTAMP WITH TIME ZONE data type that allows you to store datetime with the time zone information. Oracle: -- Flights table stores departure time in airport time zone CREATE TABLE departures ( airport CHAR(3), airline VARCHAR2(30), flight CHAR(7), departure TIMESTAMP WITH TIME ZONE ); -- Departs from San Francisco at 5:25 pm local time INSERT INTO departures VALUES ('SFO', 'Virgin Atlantic', 'VS020', TIMESTAMP '2013-03-22 17:25:00 -8:00')… text/html 2026-01-06T20:06:34+00:00 https://www.sqlines.com/oracle-to-mysql/to_char_datetime?rev=1767729994&do=diff In Oracle, the TO_CHAR function converts a datetime value to string using a specified format. In MySQL, you can use the DATE_FORMAT function. Note that the TO_CHAR and DATE_FORMAT format strings are different. Oracle: -- Convert the current date and time to string (year-month-day) SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual; # 2013-02-27 text/html 2024-07-29T16:01:27+00:00 https://www.sqlines.com/oracle-to-mysql/to_char_number?rev=1722268887&do=diff In Oracle, TO_CHAR function can convert a numeric value to string using the specified format. In MySQL, you can use FORMAT function as well as other string functions and expressions. Oracle: -- Convert the price to string format SELECT TO_CHAR(1000, 'FM$9,999,999') FROM dual; # $1,000 text/html 2025-12-24T15:47:06+00:00 https://www.sqlines.com/oracle-to-mysql/to_date?rev=1766591226&do=diff In Oracle, the TO_DATE function converts a string value to DATE data type value using the specified format. In MySQL, you can use 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('2013-02-11', 'YYYY-MM-DD') FROM dual; text/html 2026-02-02T09:56:26+00:00 https://www.sqlines.com/oracle-to-mysql/to_timestamp?rev=1770026186&do=diff In Oracle, the TO_TIMESTAMP function converts a string value to a TIMESTAMP data type value using a specified format. In MySQL, you can use the STR_TO_DATE function. Note that the TO_TIMESTAMP and STR_TO_DATE format strings are different. Oracle: text/html 2021-09-08T12:14:12+00:00 https://www.sqlines.com/oracle-to-mysql/translate?rev=1631103252&do=diff In Oracle, the TRANSLATE(string, from_string, to_string) function allows you to replace all occurrences of each character in from_string to the corresponding character in to_string. In MySQL you can use nested REPLACE expressions or an user-defined function. text/html 2025-12-15T14:24:28+00:00 https://www.sqlines.com/oracle-to-mysql/trunc_datetime?rev=1765808668&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 MySQL, you can use the CAST, DATE and DATE_FORMAT functions to get the same result. text/html 2025-05-21T13:44:30+00:00 https://www.sqlines.com/oracle-to-mysql/user?rev=1747835070&do=diff In Oracle, the USER function returns the name of the session user. In MySQL, you can use the USER() function. Oracle: -- Get the current user SELECT USER FROM dual; MySQL: -- Get the current user SELECT USER(); For more information, see Oracle to MySQL Migration. text/html 2026-02-02T11:34:56+00:00 https://www.sqlines.com/oracle-to-mysql/userenv?rev=1770032096&do=diff In Oracle, the USERENV function returns information about the current session. In MySQL, 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:02:15+00:00 https://www.sqlines.com/oracle-to-mysql/varchar2?rev=1725177735&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 MySQL, you can use VARCHAR(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.