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 different. In Oracle Database, the return types include BINARY_DOUBLE, BINARY_FLOAT, and NUMBER. In GaussDB, the return types include int2, int4, int8, and numeric.
• If the first input parameter is of the numeric type, the second parameter must be of the int, numeric, or a type that can be converted to numeric. If a_format_version is set to 10c, a_format_dev_versiopn is set to s6, and the first parameter is of the text type that can be converted to numeric, the second parameter must be of the int4 type or a type with the value range smaller than int4.

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.

The data types of the returned values are different.

GaussDB:

• If one input value is of the float4 type and the other is of the numeric type, values of the float4 type are returned.
• If both input values are of the float4 type, values of the float4 type are returned.
• If both input values are of the float8 type, values of the float8 type are returned.
• For other data types, values of the numeric type are returned.

Oracle Database:

The type of returned values is number.

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 Database.
• The returned types are different. For round(n, integer), Oracle Database returns the NUMBER type, and GaussDB returns the numeric type. For round(n), Oracle Database 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 Database.

  SELECT round(NULL,'q');

  Oracle Database 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 data types of the returned values are different.

GaussDB:

If an input value is of the float8 type, a value of the float8 type is returned.

If an input value is of the numeric type, a value of the numeric type is returned.

oracle

The type of returned values is number.

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 Database returns garbled characters.
• If you enter 0 or 256, Oracle Database 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 Database.

4

LOWER

Supported, with differences.

• The types of returned values are different. The data types of Oracle Database 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 Database 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 Database.

5

LPAD

Supported.

-

6

LTRIM

Supported, with differences.

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

7

NCHR

Supported, with differences.

• The byte length of the returned value is different from that of Oracle Database.
• The returned value is restricted by the database character set. As a result, the returned result is different from that in Oracle Database.
• 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 Database, 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 Database returns the VARCHAR2 type. If the input is of the LOB type, Oracle Database returns the LOB type. GaussDB returns the TEXT type.
• In Oracle Database, 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 Database.

9

NLS_UPPER

Supported, with differences.

• The return value types are different. If the input is of the character data type, Oracle Database returns the VARCHAR2 type. If the input is of the LOB type, Oracle Database returns the LOB type. GaussDB returns the TEXT type.
• In Oracle Database, 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 Database.

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 Database, 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 Database 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 Database 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 types of returned values are different. The data types of Oracle Database 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 Database 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 Database.

20

INSTRB

Supported.

-

1

ASCII

Supported, with differences.

The types of returned values are different. Oracle Database 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 Database, 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.

6

LENGTHC

Supported.

-

1

ADD_MONTHS

Supported, with differences.

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

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 Database.

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 types of returned values are different. The return value type in GaussDB is timestamp without time zone, and that in Oracle Database is date.

7

LOCALTIMESTAMP

Supported, with differences.

The value ranges from 0 to 9 in Oracle Database.

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 Database.

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 Database, 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 Database, for example, +08:00.

14

SYS_EXTRACT_UTC

Supported.

-

15

SYSDATE

Supported, with differences.

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

16

SYSTIMESTAMP

Supported, with differences.

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

17

TO_CHAR

Supported, with differences.

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

18

TO_DSINTERVAL

Supported, with differences.

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

19

TO_TIMESTAMP

Supported, with differences.

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

20

TO_TIMESTAMP_TZ

Supported, with differences.

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

21

TO_YMINTERVAL

Supported, with differences.

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

22

TRUNC

Supported, with differences.

The type returned by GaussDB is the same as the type of the first input parameter. Oracle Database always returns the date type. In addition, the supported formats are different in the two databases. For details about the supported formats, see "SQL Reference > Functions and Operators > Date and Time Processing Functions and Operators" in Developer Guide.

23

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 Database.

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 Database is inconsistent with that in GaussDB.
• If there is no separator, the value may be different from that in Oracle Database. 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 Database: The input parameter type is converted to the national character set.

12

TO_NUMBER

Supported, with differences.

GaussDB does not support the NLS_PARAM parameter.

The differences between the fmt options of GaussDB and Oracle Database are as follows:

1. $

GaussDB does not support this fmt.

2. Comma (,)

GaussDB: Commas (,) can appear at any position of fmt.

Oracle Database:

• In format, commas can only appear in the integer part and cannot appear at the beginning of a number. In the original data, commas can appear at the beginning of a number.
• The number and position of commas in the format can be different from those in the original data, but the position of the last comma must be the same.
• Consecutive commas in the original data and the format are equivalent to no comma.
• If the original data does not contain commas, the number of digits after the last comma in the format must be the same as that in the original data.

3. B

GaussDB does not support this function.

4. C

GaussDB does not support the NLS parameter.

5. G

GaussDB does not support the NLS parameter.

6. L

GaussDB does not support the NLS parameter.

7. U

GaussDB does not support the NLS parameter.

8. D

GaussDB does not support the NLS parameter.

9. PR

GaussDB: It is equivalent to S. A negative number is returned.

Oracle Database:

Returns the negative value in the angle brackets (< >).

Returns a positive value with leading and trailing spaces.

Restriction: PR format elements can only appear at the last position of the digital format model.

10. RN | rn

GaussDB does not support this function.

TM| TM9 | TMe

GaussDB does not support this function.

11. V

GaussDB does not support this function.

12. FM

When the fm exists in GaussDB, the comma in the format can be more than that in the original data. Otherwise, the comma must be the same.

In Oracle Database, spaces before and after the return value are retained.

13. EEEE

GaussDB does not support this function.

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 Database 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 Database.

1

SYS_CONNECT_BY_PATH

Supported, with differences.

In GaussDB, the column specified by the first input parameter can only be of the CHAR, VARCHAR, NVARCHAR2, TEXT, INT1, INT2, INT4, INT8, FLOAT4, FLOAT8, NUMERIC type. Currently, the col input of the function cannot be an expression. If the column content is the same as the separator, no error is reported.

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 Database. 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 Database due to different storage formats. For example:

• In GaussDB, SELECT dump(123); returns Typ=23 Len=4: 123,0,0,0.
• In Oracle Database, 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 Database due to different storage formats. For example:

• In GaussDB, SELECT vsize(999); returns 4.
• In Oracle Database, 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 Database is varchar2.

4

USERENV

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'
• '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'