This topic describes the compatibility of OceanBase Database's Oracle mode with native Oracle Database in terms of built-in functions.
Oracle Database 19c supports 312 built-in functions and OceanBase Database supports 176 of them. For more information, see Functions.
Numeric functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
ABS |
Yes |
| 2 |
ACOS |
Yes |
| 3 |
ASIN |
Yes |
| 4 |
ATAN |
Yes |
| 5 |
ATAN2 |
Yes |
| 6 |
BITAND |
Yes |
| 7 |
CEIL |
Yes |
| 8 |
COS |
Yes |
| 9 |
COSH |
Yes |
| 10 |
EXP |
Yes |
| 11 |
FLOOR |
Yes |
| 12 |
LN |
Yes |
| 13 |
LOG |
Yes |
| 14 |
MOD |
Yes |
| 15 |
NANVL |
Yes |
| 16 |
POWER |
Yes |
| 17 |
REMAINDER |
Yes |
| 18 |
ROUND (number) |
Yes |
| 19 |
SIGN |
Yes |
| 20 |
SIN |
Yes |
| 21 |
SINH |
Yes |
| 22 |
SQRT |
Yes |
| 23 |
TAN |
Yes |
| 24 |
TANH |
Yes |
| 25 |
TRUNC (number) |
Yes |
| 26 |
WIDTH_BUCKET |
Yes |
String functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
CHR |
Yes |
| 2 |
CONCAT |
Yes |
| 3 |
INITCAP |
Yes |
| 4 |
LOWER |
Yes |
| 5 |
LPAD |
Yes |
| 6 |
LTRIM |
Yes |
| 7 |
NLS_LOWER |
Yes |
| 8 |
NLS_UPPER |
Yes |
| 9 |
REGEXP_REPLACE |
Yes |
| 10 |
REGEXP_SUBSTR |
Yes |
| 11 |
REPLACE |
Yes |
| 12 |
RPAD |
Yes |
| 13 |
RTRIM |
Yes |
| 14 |
SUBSTR |
Yes |
| 15 |
TRANSLATE |
Yes |
| 16 |
TRANSLATE ... USING |
Yes |
| 17 |
TRIM |
Yes |
| 18 |
UPPER |
Yes |
| 19 |
ASCII |
Yes |
| 20 |
INSTR |
Yes |
| 21 |
LENGTH |
Yes |
| 22 |
REGEXP_COUNT |
Yes |
| 23 |
REGEXP_INSTR |
Yes |
Date functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
ADD_MONTHS |
Yes |
| 2 |
CURRENT_DATE |
Yes |
| 3 |
CURRENT_TIMESTAMP |
Yes |
| 4 |
DBTIMEZONE |
Yes |
| 5 |
EXTRACT (datetime) |
Yes |
| 6 |
FROM_TZ |
Yes |
| 7 |
LAST_DAY |
Yes |
| 8 |
LOCALTIMESTAMP |
Yes |
| 9 |
MONTHS_BETWEEN |
Yes |
| 10 |
NEXT_DAY |
Yes |
| 11 |
NUMTODSINTERVAL |
Yes |
| 12 |
NUMTOYMINTERVAL |
Yes |
| 13 |
ROUND(date) |
Yes |
| 14 |
SESSIONTIMEZONE |
Yes |
| 15 |
SYS_EXTRACT_UTC |
Yes |
| 16 |
SYSDATE |
Yes |
| 17 |
SYSTIMESTAMP |
Yes |
| 18 |
TO_CHAR(datetime) |
Yes |
| 19 |
TO_DSINTERVAL |
Yes |
| 20 |
TO_TIMESTAMP |
Yes |
| 21 |
TO_TIMESTAMP_TZ |
Yes |
| 22 |
TO_YMINTERVAL |
Yes |
| 23 |
TRUNC(date) |
Yes |
| 24 |
TZ_OFFSET |
Yes |
General comparison functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
GREATEST |
Yes |
| 2 |
LEAST |
Yes |
Conversion functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
ASCIISTR |
Yes |
| 2 |
CAST |
Yes |
| 3 |
CHARTOROWID |
Yes |
| 4 |
CONVERT |
Yes |
| 5 |
HEXTORAW |
Yes |
| 6 |
NUMTODSINTERVAL |
Yes |
| 7 |
NUMTOYMINTERVAL |
Yes |
| 8 |
RAWTOHEX |
Yes |
| 9 |
ROWIDTOCHAR |
Yes |
| 10 |
ROWIDTONCHAR |
Yes |
| 11 |
SCN_TO_TIMESTAMP |
Yes |
| 12 |
TIMESTAMP_TO_SCN |
Yes |
| 13 |
TO_BINARY_DOUBLE |
Yes |
| 14 |
TO_BINARY_FLOAT |
Yes |
| 15 |
TO_BLOB |
Yes |
| 16 |
TO_CHAR (character) |
Yes |
| 17 |
TO_CHAR (datetime) |
Yes |
| 18 |
TO_CHAR (number) |
Yes |
| 19 |
TO_CLOB |
Yes |
| 20 |
TO_DATE |
Yes |
| 21 |
TO_DSINTERVAL |
Yes |
| 22 |
TO_MULTI_BYTE |
Yes |
| 23 |
TO_NUMBER |
Yes |
| 24 |
TO_NCHAR (character) |
Yes |
| 25 |
TO_NCHAR (datetime) |
Yes |
| 26 |
TO_NCHAR (number) |
Yes |
| 27 |
TO_SINGLE_BYTE |
Yes |
| 28 |
TO_TIMESTAMP |
Yes |
| 29 |
TO_TIMESTAMP_TZ |
Yes |
| 30 |
TO_YMINTERVAL |
Yes |
| 31 |
UNISTR |
Yes |
Encoding and decoding functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
DECODE |
Yes |
| 2 |
DUMP |
Yes |
| 3 |
ORA_HASH |
Yes |
| 4 |
VSIZE |
Yes |
Environment and identifier functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
SYS_CONTEXT |
Yes |
| 2 |
SYS_GUID |
Yes |
| 3 |
UID |
Yes |
| 4 |
USER |
Yes |
| 5 |
USERENV |
Yes |
Hierarchical functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
SYS_CONNECT_BY_PATH |
Yes |
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
COALESCE |
Yes |
| 2 |
LNNVL |
Yes |
| 3 |
NANVL |
Yes |
| 4 |
NULLIF |
Yes |
| 5 |
NVL |
Yes |
| 6 |
NVL2 |
Yes |
JSON functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
JSON_ARRAY |
Yes |
| 2 |
JSON_OBJECT |
Yes |
| 3 |
JSON_ARRAYAGG |
Yes |
| 4 |
JSON_OBJECTAGG |
Yes |
| 5 |
JSON_VALUE |
Yes |
| 6 |
JSON_QUERY |
Yes |
| 7 |
JSON_MERGEPATCH |
Yes |
| 8 |
JSON_EQUAL |
Yes |
| 9 |
JSON_TABLE |
Yes |
XML functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
XMLAGG |
Yes |
| 2 |
XMLELEMENT |
Yes |
| 3 |
XMLPARSE |
Yes |
| 4 |
EXTRACT |
Yes |
| 5 |
EXTRACTVALUE |
Yes |
| 6 |
XMLSERIALIZE |
Yes |
| 7 |
XMLCAST |
Yes |
| 8 |
UPDATEXML |
Yes |
Aggregate functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
APPROX_COUNT_DISTINCT |
Yes |
| 2 |
AVG * |
Yes |
| 3 |
CORR * |
Yes |
| 4 |
COUNT * |
Yes |
| 5 |
COVAR_POP * |
Yes |
| 6 |
COVAR_SAMP * |
Yes |
| 7 |
CUME_DIST * |
Yes |
| 8 |
DENSE_RANK * |
Yes |
| 9 |
GROUPING |
Yes |
| 10 |
KEEP * |
Yes |
| 11 |
LISTAGG * |
Yes |
| 12 |
MAX * |
Yes |
| 13 |
MIN * |
Yes |
| 14 |
MEDIAN * |
Yes |
| 15 |
PERCENT_RANK * |
Yes |
| 16 |
PERCENTILE_CONT * |
Yes |
| 17 |
PERCENTILE_DISC * |
Yes |
| 18 |
RANK * |
Yes |
| 19 |
REGR_ (Linear Regression) * |
Yes |
| 20 |
ROLLUP |
Yes |
| 21 |
STDDEV * |
Yes |
| 22 |
STDDEV_POP * |
Yes |
| 23 |
STDDEV_SAMP * |
Yes |
| 24 |
SUM * |
Yes |
| 25 |
VAR_POP * |
Yes |
| 26 |
VAR_SAMP * |
Yes |
| 27 |
VARIANCE * |
Yes |
| 28 |
WMSYS.WM_CONCAT/WM_CONCAT * |
Yes |
Note
A function followed by an asterisk (*) in the preceding table can be used as both an aggregate function and an analytic function.
Analytic functions
| No. |
Oracle function |
Supported in OceanBase Database |
| 1 |
FIRST_VALUE |
Yes |
| 2 |
LAG |
Yes |
| 3 |
LAST_VALUE |
Yes |
| 4 |
LEAD |
Yes |
| 5 |
NTH_VALUE |
Yes |
| 6 |
NTILE |
Yes |
| 7 |
RATIO_TO_REPORT |
Yes |
| 8 |
ROW_NUMBER |
Yes |
Note
Analytic functions that can also be used as aggregate functions are listed in the preceding Aggregate functions table and are marked with an asterisk (*). Therefore, these functions are not listed in this table.
