Why MariaDB refuses to create a simple table with error [42000][1064]?

Question

I just want to know how to fix this, or why MariaDB server keeps me returning an error. I just install the database, create a user, a new schema, I'm pointing to that database with use database testing; and I'm ready to create a simple table on a database with this query:

CREATE TABLE USERS
(
    ID          NUMBER(4)    NOT NULL PRIMARY KEY,
    NAME        VARCHAR2(20) NOT NULL,
    DEPTO       VARCHAR2(20) NOT NULL
);

The user in the database I'm using has granted all privileges. But MariaDB returns:

[42000][1064] (conn=14) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NUMBER(4) NOT NULL PRIMARY KEY
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NUMBER(4) NOT NULL PRIMARY KEY
)' at line 2
Query is: CREATE TABLE USERS (
ID NUMBER(4) NOT NULL PRIMARY KEY
)
java thread: RMI TCP Connection(5)-127.0.0.1

Can someone help me?

Answer 1

The data types are from Oracle, and you could set the SQL_MODE to accept that syntax:

In MariaDB 10.3 and later, setting the sql_mode system variable to Oracle allows the server to understand a subset of Oracle's PL/SQL language. For example:

SET SQL_MODE='ORACLE';

CREATE TABLE USERS
(
    ID          NUMBER(4)    NOT NULL PRIMARY KEY,
    NAME        VARCHAR2(20) NOT NULL,
    DEPTO       VARCHAR2(20) NOT NULL
);

Keep in mind that NUMBER and VARCHAR2 are synonyms:

+-------------+-----------------+
| Oracle type | MariaDB synonym |
+-------------+-----------------+
| VARCHAR2    | VARCHAR         |
| NUMBER      | DECIMAL         |
+-------------+-----------------+

db<>fiddle demo