Single-Row Functions

1

Numeric functions

Supported, with differences.

2

Character functions returning character values

Supported, with differences.

3

Character functions returning number values

Supported, with differences.

4

Character set functions

Not supported.

5

Collation functions

Not supported.

6

Datetime functions

Supported, with differences.

7

General comparison functions

Supported, with differences.

8

Conversion functions

Supported, with differences.

9

Large object functions

Supported, with differences.

10

Collection functions

Not supported.

11

Hierarchical functions

Supported.

12

Data mining functions

Not supported.

13

XML functions

Supported, with differences.

14

JSON functions

Not supported.

15

Encoding and decoding functions

Supported, with differences.

16

Null-related functions

Supported.

17

Environment and identifier functions

Supported, with differences.

1

ABS

Supported.

-

2

ACOS

Supported.

-

3

ASIN

Supported.

-

4

ATAN

Supported.

-

5

ATAN2

Supported.

-

6

BITAND

Supported.

-

7

CEIL

Supported.

-

8

COS

Supported.

-

9

COSH

Supported.

-

10

EXP

Supported.

-

11

FLOOR

Supported.

-

12

LN

Supported.

-

13

LOG

Supported.

-

14

MOD

Supported, with differences.

• The return types are inconsistent. In Oracle, the return types include BINARY_DOUBLE, BINARY_FLOAT, and NUMBER. In GaussDB, the return types include int2, int4, int8, and numeric.
• If one of the two input parameters is of the int type, the other parameter must be of the int, numeric, or literal integer type (a valid number without a decimal point, for example, '12').

15

NANVL

Supported, with differences.

GaussDB: NaN cannot be obtained by directly declaring or dividing a floating-point number by 0.

16

POWER

Supported.

-

17

REMAINDER

Supported, with differences.

If one of the two input parameters is of the int type, the other parameter must be of the int, numeric, or literal integer type (a valid number without a decimal point, for example, '12').

18

ROUND

Supported, with differences.

• For the first parameter n of the float type, GaussDB has precision loss, and the precision is lower than that of Oracle.
• The returned types are different. For round(n, integer), Oracle returns the NUMBER type, and GaussDB returns the numeric type. For round(n), Oracle returns the data type of n, and GaussDB returns only the float8 and numeric types. The float4 return type is missing.
• The logic for the GaussDB to determine that the input parameter is null and the execution framework to return null is different from that in Oracle.

  SELECT round(NULL,'q');

  Oracle reports null, and GaussDB reports the error: invalid input syntax for integer: "q".

19

SIGN

Supported.

-

20

SIN

Supported.

-

21

SINH

Supported.

-

22

SQRT

Supported.

-

23

TAN

Supported.

-

24

TANH

Supported, with differences.

The precision of the return value is different.

25

TRUNC

Supported.

-

26

WIDTH_BUCKET

Supported.

-

1

CHR

Supported, with differences.

• If the entered number does not comply with the existing character set, GaussDB reports an error in JDBC and Oracle returns garbled characters.
• If you enter 0 or 256, Oracle returns characters whose ASCII code is 0, and GaussDB truncates the characters at '\0;.

2

CONCAT

Supported.

-

3

INITCAP

Supported, with differences.

The returned value is restricted by the database character set. As a result, the returned result is different from that in Oracle.

4

LOWER

Supported, with differences.

• The return value types are different. The data types of Oracle are the same as the input types.
• The time format is implicitly converted. When the time type is entered, the time type is implicitly converted to a character string and then the lower operation is performed.

  SELECT LOWER(TO_DATE('2012-12-10','YYYY-MM-DD')); 

  Oracle returns 10-DEC-12, and GaussDB returns 2012-12-10 00:00:00.

• The returned value is restricted by the database character set. As a result, the returned result is different from that in Oracle.

5

LPAD

Supported.

-

6

LTRIM

Supported, with differences.

The return value types are different. If the input is of the character data type, Oracle returns the VARCHAR2 type. If the input is of the national character set specified during database creation, Oracle returns the NVARCHAR2 type. If the input is of the LOB type, Oracle returns the LOB type. GaussDB returns the TEXT type.

7

NCHR

Supported, with differences.

• The byte length of the return value is different from that of Oracle.
• The returned value is restricted by the database character set. As a result, the returned result is different from that in Oracle.
• When the byte array corresponding to the input parameter is returned, a question mark (?) is returned if a single byte ranges from 0x80 to 0xFF. In Oracle, a question mark (?) is returned, no output is returned, or an error is reported.

8

NLS_LOWER

Supported, with differences.

• The return value types are different. If the input is of the character data type, Oracle returns the VARCHAR2 type. If the input is of the LOB type, Oracle returns the LOB type. GaussDB returns the TEXT type.
• In Oracle, the nlsparam parameter can be of a type except nls_sort, and no error is reported. GaussDB supports only nls_sort.
• The returned value is restricted by the database character set. As a result, the returned result is different from that in Oracle.

9

NLS_UPPER

Supported, with differences.

• The return value types are different. If the input is of the character data type, Oracle returns the VARCHAR2 type. If the input is of the LOB type, Oracle returns the LOB type. GaussDB returns the TEXT type.
• In Oracle, the nlsparam parameter can be of a type except nls_sort, and no error is reported. GaussDB supports only nls_sort.
• The returned value is restricted by the database character set. As a result, the returned result is different from that in Oracle.

10

NLSSORT

Supported.

-

11

REGEXP_REPLACE

Supported, with differences.

• GaussDB input parameter source_char does not support the NCLOB type.
• The meaning of the 'n' option in the match_param input parameter is different. In GaussDB, the 'n' option has the same meaning as the 'm' option, indicating that the multi-row matching mode is used. In Oracle, it indicates that the dot (.) can match the '\n' character. If this option is not specified, the '\n' character cannot be matched by default. In GaussDB, the dot (.) matches '\n' by default. You do not need to specify the option.
• The matching results of some regular expressions may be different.

  SELECT REGEXP_REPLACE('abc01234xyz', '(.*?)(\d+)(.*)', '#', 'g') FROM DUAL; 

  Oracle reports an error, and GaussDB returns #####xyz.

• The matching results may be different when Chinese characters are entered in the UTF-8 character set. Oracle needs to implement regular expression matching for Chinese character strings in the GBK character set.
• The matching results of regular expressions that contain some escape characters may be different.

  SELECT REGEXP_REPLACE('abcabc', '\abc', '#', 'g') FROM DUAL; 

  Oracle reports an error, and GaussDB returns abcabc.

• The matching rules are affected by the aformat_regexp_match parameter. For details about the affected specifications, see the REGEXP_REPLACE function in "SQL Reference > Functions and Operators > Character Processing Functions and Operators" in Developer Guide.

12

REGEXP_SUBSTR

Supported, with differences.

The matching rules are affected by the aformat_regexp_match parameter. For details about the affected specifications, see the REGEXP_SUBSTR function in "SQL Reference > Functions and Operators > Character Processing Functions and Operators" in Developer Guide.

13

REPLACE

Supported.

-

14

RPAD

Supported.

-

15

RTRIM

Supported.

-

16

SUBSTR

Supported.

-

17

TRANSLATE

Supported.

-

18

TRIM

Supported.

-

19

UPPER

Supported, with differences.

• The return value types are different. The data types of Oracle are the same as the input types. GaussDB returns the TEXT type.
• The time format is implicitly converted. When the time type is entered, the time type is implicitly converted to a character string and then the upper operation is performed.

  SELECT UPPER(TO_DATE('2012-12-10','YYYY-MM-DD')); 

  Oracle returns 10-DEC-12, and GaussDB returns 2012-12-10 00:00:00.

• The returned value is restricted by the database character set. As a result, the returned result is different from that in Oracle.

20

INSTRB

Supported.

-

1

ASCII

Supported, with differences.

The return value types are different. Oracle returns the uint4 type, while GaussDB returns the int4 type.

2

INSTR

Supported.

-

3

LENGTH

Supported.

-

4

REGEXP_COUNT

Supported, with differences.

• GaussDB input parameter source_char does not support the NCLOB type.
• The meaning of the 'n' option in the match_param input parameter is different. In GaussDB, the 'n' option has the same meaning as the 'm' option, indicating that the multi-row matching mode is used. In Oracle, it indicates that the dot (.) can match the '\n' character. If this option is not specified, the '\n' character cannot be matched by default. In GaussDB, the dot (.) matches '\n' by default. You do not need to specify the option.
• The matching results of some regular expressions may be different.
• The matching results may be different when Chinese characters are entered in the UTF-8 character set. Oracle needs to implement regular expression matching for Chinese character strings in the GBK character set.
• The matching results of regular expressions that contain some escape characters may be different.
• The matching rules are affected by the aformat_regexp_match parameter. For details about the affected specifications, see the REGEXP_COUNT function in "SQL Reference > Functions and Operators > Character Processing Functions and Operators" in Developer Guide.

5

REGEXP_INSTR

Supported, with differences.

The matching rules are affected by the aformat_regexp_match parameter. For details about the affected specifications, see the REGEXP_INSTR function in "SQL Reference > Functions and Operators > Character Processing Functions and Operators" in Developer Guide.

1

ADD_MONTHS

Supported, with differences.

• From A.D. to B.C., the difference between GaussDB and Oracle is one year.
• The earliest year can be –4714 in GaussDB and –4713 in Oracle.

2

CURRENT_DATE

Supported, with differences.

GaussDB: The nls_date_format parameter cannot be used to set the time display format.

3

CURRENT_TIMESTAMP

Supported, with differences.

The value ranges from 0 to 9 in Oracle.

The value ranges from 0 to 6 in GaussDB. The trailing zeros in microseconds are not displayed.

4

DBTIMEZONE

Supported, with differences.

GaussDB: The timestamp API with the built-in tz cannot be called.

5

EXTRACT

Supported.

-

6

LAST_DAY

Supported, with differences.

The return value types are different. The return value type in GaussDB is timestamp without time zone, and that in Oracle is date.

7

LOCALTIMESTAMP

Supported, with differences.

The value ranges from 0 to 9 in Oracle.

The value ranges from 0 to 6 in GaussDB. The trailing zeros in microseconds are not displayed.

8

MONTHS_BETWEEN

Supported, with differences.

The input parameter types are different. GaussDB input parameters are of the timestamp without time zone type, and Oracle input parameters are of the date type.

9

NEW_TIME

Supported, with differences.

When the first input parameter of the new_time function is a literal, the literal format and the return value type of the function are different from those in Oracle.

10

NEXT_DAY

Supported.

-

11

NUMTODSINTERVAL

Supported, with differences.

GaussDB: The dsinterval type is not supported. Currently, interval is used to be compatible with the dsinterval type.

12

NUMTOYMINTERVAL

Supported, with differences.

GaussDB: The yminterval type is not supported. Currently, interval is used to be compatible with the yminterval type.

13

SESSIONTIMEZONE

Supported, with differences.

• Difference in the value assignment syntax: In GaussDB, the SET SESSION TIME ZONE 8 syntax is used. In Oracle, alter session set time_zone= '+08:00' is used.
• Default value difference: The time zone name is used in GaussDB, for example, PRC. The offset is used in Oracle, for example, +08:00.

14

SYS_EXTRACT_UTC

Supported.

-

15

SYSDATE

Supported, with differences.

The return value types are different. The return value type in GaussDB is timestamp without time zone, and that in Oracle is date.

16

TO_CHAR

Supported, with differences.

The fmt '5' is not included in the Oracle documents and is not adapted.

17

TO_DSINTERVAL

Supported, with differences.

GaussDB: The dsinterval type is not supported. Currently, interval is used to be compatible with the dsinterval type.

18

TO_TIMESTAMP

Supported, with differences.

GaussDB supports only six digits for millisecond calculation, and Oracle supports nine digits.

19

TO_TIMESTAMP_TZ

Supported, with differences.

The timestamptz of GaussDB is equivalent to the timestampwithloacltimezone of Oracle. The type corresponding to timestamptz of Oracle is missing. The value of nls_date_language can only be ENGLISH or AMERICAN.

20

TO_YMINTERVAL

Supported, with differences.

GaussDB: The yminterval type is not supported. Currently, interval is used to be compatible with the yminterval type.

21

TZ_OFFSET

Supported, with differences.

When a time zone name is received as an input parameter, the types of the time zone name are less than those of Oracle.

1

GREATEST

Supported, with differences.

• GaussDB: The comparison mode specified by the NLS_SORT parameter is not supported. Only binary comparison is supported.
• GaussDB: Expressions in multiple languages are not supported.

2

LEAST

Supported, with differences.

• GaussDB: The comparison mode specified by the NLS_SORT parameter is not supported. Only binary comparison is supported.
• GaussDB: Expressions in multiple languages are not supported.

1

ASCIISTR

Supported.

-

2

CAST

Supported, with differences.

• GaussDB: The MULTISET clause is not supported.
• GaussDB: The nlsparam parameter is not supported.

3

HEXTORAW

Supported.

-

4

RAWTOHEX

Supported.

-

5

TO_BINARY_DOUBLE

Supported, with differences.

GaussDB: The nlsparam parameter is not supported.

6

TO_BINARY_FLOAT

Supported, with differences.

GaussDB: The nlsparam parameter is not supported.

7

TO_BLOB

Supported, with differences.

• GaussDB: The long raw type is not supported.
• GaussDB: The bfile and mime_type types are not supported.

8

TO_CLOB

Supported.

-

9

TO_DATE

Supported, with differences.

• Multi-language parameters are not supported.
• The returned types are different.
• The control parameter NLS_DATE_FORMAT is missing.
• Some formats are not supported.
• fmt = 'j'. The output before October 15, 1582 in Oracle is inconsistent with that in GaussDB.
• If there is no separator, the value may be different from that in Oracle. Take to_date('220725','yymmdd') as an example. If yy/rr is parsed based on the fixed length 4, the year is parsed as 2207 and the month is parsed as 25. The month 25 is invalid, and an error will be reported.

10

TO_MULTI_BYTE

Supported.

-

11

TO_NCHAR

Supported, with differences.

GaussDB: The input parameter type is converted to text.

Oracle: The input parameter type is converted to the national character set.

12

TO_NUMBER

Supported, with differences.

GaussDB: The nlsparam parameter is not supported.

13

TO_SINGLE_BYTE

Supported.

-

14

TREAT

Supported, with differences.

GaussDB: The period (.) operator cannot be used to obtain values, and the values cannot be converted to the object type.

15

UNISTR

Supported, with differences.

GaussDB supports only UTF-8 encoding. Oracle supports UTF-8 and UTF-16 encodings.

1

EMPTY_BLOB

Supported.

-

2

EMPTY_CLOB

Supported, with differences.

The CLOB type in GaussDB does not support the locator concept in Oracle.

1

SYS_CONNECT_BY_PATH

Supported, with differences.

GaussDB: The column specified by the first input parameter can only be the CHAR, VARCHAR, NVARCHAR2, or TEXT type.

1

EXISTSNODE

Supported, with differences.

If the input parameter has a namespace, aliases must be defined for both the XPath and namespace.

2

EXTRACTVALUE

Supported, with differences.

Currently, only XPath 1.0 is supported.

3

SYS_XMLAGG

Supported, with differences.

This is an alias of xmlagg and can be replaced with xmlagg.

4

XMLAGG

Supported.

-

5

XMLCOMMENT

Supported.

-

6

XMLCONCAT

Supported.

-

7

XMLELEMENT

Supported, with differences.

For xmlelement and xmlattributes, when the value of name is NULL, the database behavior is different from that in Oracle. When the name column of xmlelement is set to NULL, the name information is empty and the attribute information is not displayed. When the name column of xmlattributes is set to NULL, the attribute information is not displayed.

8

XMLEXISTS

Supported, with differences.

GaussDB input parameter is of the XML type.

9

XMLFOREST

Supported, with differences.

GaussDB return value is of the XML type. GaussDB does not support the EVALNAME syntax.

10

XMLPARSE

Supported, with differences.

GaussDB return value is of the XML type. GaussDB does not support the WELLFORMED syntax.

11

XMLROOT

Supported, with differences.

GaussDB return value is of the XML type.

12

JSON_OBJECT

Supported.

-

13

XMLTABLE

Supported, with differences.

GaussDB: The XPath 1.0 expression is used to select data from the XML file. The default namespace cannot be declared, multiple groups of inputs and aliases cannot be obtained, the passing_clause clause of the input data cannot be omitted, and the RETURNING SEQUENCE BY REF and ( SEQUENCE ) BY REF clauses are not supported.

14

GETSTRINGVAL

Supported.

-

15

GETCLOBVAL

Supported.

-

16

XMLSEQUENCE

Supported.

-

1

DECODE

Supported.

-

2

DUMP

Supported, with differences.

The returned results of the numeric and time types in GaussDB are inconsistent with those in Oracle due to different storage formats. For example:

• In GaussDB, SELECT dump(123); returns Typ=23 Len=4: 123,0,0,0.
• In Oracle, SELECT dump(123) FROM dual; returns Typ=2 Len=3: 194,2,24.

3

ORA_HASH

Supported, with differences.

GaussDB has the following behaviors:

• The input parameter of the time type is converted into the character string type and then hashed.
• The maxbucket parameter is not supported.

4

VSIZE

Supported, with differences.

The returned results of the numeric and time types in GaussDB are inconsistent with those in Oracle due to different storage formats. For example:

• In GaussDB, SELECT vsize(999); returns 4.
• In Oracle, SELECT vsize(999) FROM dual; returns 3.

1

COALESCE

Supported.

-

2

LNNVL

Supported.

-

3

NULLIF

Supported.

-

4

NVL

Supported.

-

5

NVL2

Supported.

-

1

SYS_CONTEXT

Supported, with differences.

GaussDB returns NULL for unsupported parameters.

The following parameters are not supported:

'action'

'is_application_root'

'is_application_pdb'

'audited_cursorid'

'authenticated_identity'

'authentication_data'

'authentication_method'

'cdb_domain'

'cdb_name'

'client_identifier'

'con_id'

'con_name'

'current_sql_length'

'db_domain'

'db_supplemental_log_level'

'dblink_info'

'drain_status'

'entryid'

'enterprise_identity'

'fg_job_id'

'global_uid'

'identification_type'

'instance'

'is_dg_rolling_upgrade'

'ldap_server_type'

'module'

'network_protocol'

'nls_calendar'

'nls_sort'

'nls_territory'

'oracle_home'

'os_user'

'platform_slash'

'policy_invoker'

'proxy_enterprise_identity'

'proxy_user'

'proxy_userid'

'scheduler_job'

'session_edition_id'

'session_edition_name'

'sessionid'

'statementid'

'terminal'

'unified_audit_sessionid'

'session_default_collation'

'client_info'

'bg_job_id'

'client_program_name'

'current_bind'

'global_context_memory'

'host'

'current_sqln'

2

SYS_GUID

Supported.

-

3

USER

Supported, with differences.

The return value types are inconsistent. The return value type of GaussDB is name, and that of Oracle is varchar2.