Help Center/ GaussDB/ Service Overview/ Compatibility with Oracle/ Distributed Instances/ System Functions/ Single-Row Functions
Updated on 2024-11-11 GMT+08:00
View PDF
Share

Single-Row Functions

No.

Oracle Database

GaussDB

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.
Table 1 Numeric functions

No.

Oracle Database

GaussDB

Difference

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_version 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 Database:

The type of returned values is number.

25

TRUNC

Supported.

-

26

WIDTH_BUCKET

Supported.

-
Table 2 Character functions returning character values

No.

Oracle Database

GaussDB

Difference

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 types of returned values 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 types of returned values 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 types of returned values 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 Database 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.

-
Table 3 Character functions returning number values

No.

Oracle Database

GaussDB

Difference

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

-
Table 4 Datetime functions

No.

Oracle Database

GaussDB

Difference

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.

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.

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.
  • The assignment syntax is different. In GaussDB, the SET SESSION TIME ZONE 8 syntax is used. In Oracle Database, alter session set time_zone= '+08:00' is used.
  • The default values are different. GaussDB: The time zone name is displayed, 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.

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.
Table 5 General comparison functions

No.

Oracle Database

GaussDB

Difference

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.
Table 6 Conversion functions

No.

Oracle Database

GaussDB

Difference

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 this fmt.

Oracle Database:

  • In the 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

In GaussDB, when there is FM, the comma in the format can be more than that in the original data. In other words, the number of commas is not necessarily 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.
Table 7 Large object functions

No.

Oracle Database

GaussDB

Difference

1

EMPTY_BLOB

Supported.

-

2

EMPTY_CLOB

Supported, with differences.

GaussDB: The CLOB type does not support the locator concept in Oracle Database.
Table 8 Hierarchical functions

No.

Oracle Database

GaussDB

1

SYS_CONNECT_BY_PATH

Not supported.
Table 9 XML functions

No.

Oracle Database

GaussDB

Difference

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.

-
Table 10 Encoding and decoding functions

No.

Oracle Database

GaussDB

Difference

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. 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. In GaussDB, select vsize(999); returns 4. In Oracle Database, select vsize(999) from dual; returns 3.
Table 11 Null-related functions

No.

Oracle Database

GaussDB

1

COALESCE

Supported.

2

LNNVL

Supported.

3

NULLIF

Supported.

4

NVL

Supported.

5

NVL2

Supported.
Table 12 Environment and identifier functions

No.

Oracle Database

GaussDB

Difference

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 types of returned values are different.

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'
Parent topic: System Functions