DocsICP FilingConsole
首页 Oracle-compatible functions

Oracle-compatible functions

更新时间: 2026-01-16 04:46:39

Hologres versions 1.3 and later support Oracle-compatible functions through the orafce extension. This topic describes how to use these Oracle built-in functions in Hologres and provides details about the supported functions.

Install Oracle

Before you use Oracle-compatible functions, a superuser must run the following statement in the database to install the extension. You only need to run this statement once for each database. If you create a new database, you must run the statement again.

-- Load the orafce extension
create extension if not exists orafce; 
-- Uninstall the orafce extension
DROP EXTENSION orafce;
Note

When you load the orafce extension, multiple schemas are automatically created to replace packages in Oracle. These schemas are dbms_alert, dbms_assert, dbms_output, dbms_pipe, dbms_random, dbms_utility, oracle, plunit, plvchr, plvdate, plvlex, plvstr, plvsubst, and utl_file. Before you install the extension, ensure that no schemas with the same names exist in your database. Otherwise, the extension fails to load.

Oracle functions

The following Oracle functions are supported in Hologres.

  • Date functions

    Function name

    Description

    Example

    Result

    add_months(day date, value int)

    Adds a specified number of months to a date. Returns a DATE value.

    SELECT add_months(current_date, 2);

    2022-05-21

    oracle_last_day(value date)

    Returns the last day of the month for a specified date. Returns a DATE value.

    SELECT oracle_last_day('2022-05-01');

    2022-05-31

    next_day(value date, weekday text)

    Returns the date of the first specified weekday that is later than the start date. The first parameter is the start date. The second parameter is a string that specifies the day of the week, such as 'FRIDAY'.

    SELECT next_day('2022-05-01', 'FRIDAY');

    2022-05-06

    next_day(value date, weekday integer)

    Returns the date of the first specified weekday that is later than the start date. The first parameter is the start date. The second parameter is a number from 1 to 7 that represents the day of the week. 1 represents Sunday, 2 represents Monday, and so on.

    • SELECT next_day('2022-05-01', 1);
    • SELECT next_day('2022-05-01', 2);

    • 2022-05-08

    • 2022-05-02

    months_between(date1 date, date2 date)

    Returns the number of months between date1 and date2. The result is positive if date1 is later than date2. The result is negative if date1 is earlier than date2.

    • SELECT months_between('2022-01-01', '2021-11-01');
    • SELECT months_between('2021-11-01', '2022-01-01');

    • 2

    • -2

    trunc(value timestamp with time zone, fmt text)

    Returns a truncated timestamp. The first parameter is the timestamp to truncate. The second parameter is the unit of measurement for truncation. 'Y' truncates the date to the first day of the year. 'Q' truncates the date to the first day of the quarter.

    • SELECT TRUNC(current_date,'Y');
    • SELECT TRUNC(current_date,'Q');

    • 2022-01-01

    • 2022-01-01

    trunc(value timestamp with time zone)

    Returns a truncated timestamp. By default, the hour, minute, and second parts are truncated.

    SELECT TRUNC('2022-03-01'::timestamp);

    2022-03-01 00:00:00

    round(value timestamp with time zone, fmt text)

    Rounds a timestamp to the nearest unit of measure, such as day or week.

    SELECT round('2022-02-22 13:11:22'::timestamp, 'YEAR');

    2022-01-01 00:00:00

    round(value timestamp with time zone)

    Rounds the timestamp to the nearest day by default.

    SELECT round('2022-02-22 13:11:22'::timestamp);

    2022-02-23 00:00:00

    round(value date, fmt text)

    The parameter type is date.

    • SELECT round(TO_DATE('27-OCT-00','DD-MON-YY'), 'YEAR');
    • SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'), 'YEAR');

    • 2001-01-01

    • 2000-01-01

    round(value date)

    The parameter type is date.

    SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'));

    2000-02-27

  • String functions

    Function name

    Description

    Example

    Result

    instr(str text, patt text, start integer, nth integer)

    Searches for a substring in a string. Returns the position of the substring if found, or 0 if not found. start: The position to begin the search. nth: The occurrence to find.

    • SELECT instr('Hologres', 'o',1,2);
    • SELECT instr('Hologres', 'o',1,1);

    • 4

    • 2

    instr(str text, patt text, start integer)

    If the nth parameter is not provided, the function finds the first occurrence by default.

    SELECT instr('Hologres', 'o',4);

    4

    instr(str text, patt text)

    If the start parameter is not provided, the search begins from the start of the string by default.

    SELECT instr('Hologres', 'o');

    2

    plvstr.rvrs(str text, start integer, end integer)

    Reverses the substring of the input string str from the start position to the end position.

    SELECT plvstr.rvrs('Hologres', 5,6);

    rg

    plvstr.rvrs(str text, start integer)

    Reverses the substring of the input string str from the start position to the end of the string.

    SELECT plvstr.rvrs('Hologres', 4);

    sergo

    plvstr.rvrs(str text)

    Reverses the entire string.

    SELECT plvstr.rvrs('Hologres');

    sergoloH

    substr(str text, start integer)

    Returns a substring of the input string str from the start character to the end of the string.

    • SELECT substr('Hologres', 1);
    • SELECT substr('Hologres', 4);

    • Hologres

    • ogres

    substr(str text, start integer, len integer)

    Returns a substring of the input string str with a length of len characters, starting from the start character.

    • SELECT substr('Hologres', 5, 5);
    • SELECT substr('Oracle function', 5, 5);

    • gres

    • le function

  • Other functions

    Function name

    Description

    Example

    Result

    listagg(str text)

    Aggregates text values into a single string.

    SELECT listagg(t) FROM (VALUES('holo'), ('gres')) as l(t);

    hologres

    listagg(str text, str text)

    Aggregates text values into a single string, using the second parameter as a delimiter.

    SELECT listagg(t, '.') FROM (VALUES('holo'), ('gres')) as l(t);

    holo.gres

    concat(str text, str text)

    Concatenates two strings.

    SELECT concat('holo','gres');

    hologres

    • concat(str text, anyarray)

    • concat(anyarray, str text)

    • concat(anyarray, anyarray)

    Concatenates data of any type.

    • SELECT concat('hologres', 123);
    • SELECT concat(123, 123);
    • SELECT concat(current_date, 123);

    • hologres123

    • 123123

    • 2022-03-21123

    • nanvl(num real, num real)

    • nanvl(num decimal, num decimal)

    If the first parameter is a numeric type, this function returns the first parameter. Otherwise, it returns the second parameter.

    • SELECT nanvl('NaN', 1.1);
    • SELECT nanvl('1.2', 1.1);

    • 1.1

    • 1.2

    bitand(num bigint, num bigint)

    Performs a bitwise AND operation on the binary representations of two integers and returns the result. This function outputs only one row.

    • SELECT bitand(1,3);
    • SELECT bitand(2,6);
    • SELECT bitand(4,6);

    • 1

    • 2

    • 4

    nvl2(anyelement, anyelement, anyelement)

    If the first parameter is not null, this function returns the second parameter. If the first parameter is null, it returns the third parameter.

    • SELECT nvl2(null, 1, 2);
    • SELECT nvl2(0, 1, 2);

    • 2

    • 1

    lnnvl(boolean)

    If the parameter is null or false, this function returns true. If the parameter is true, it returns false.

    • SELECT lnnvl(null);
    • SELECT lnnvl(false);
    • SELECT lnnvl(true);

    • t

    • t

    • f

    sinh(num double precision)

    Hyperbolic sine.

    SELECT sinh(0.1);

    0.100166750019844

    tanh(num double precision)

    Hyperbolic tangent.

    SELECT tanh(3);

    0.995054753686731

    cosh(num double precision)

    Hyperbolic cosine.

    SELECT cosh(0.2);

    1.02006675561908

    decode(expression, value, return [,value,return]... [, default])

    Searches for a value in an expression. If the value is found, returns a specified return value. If not found, returns a default value.

    CREATE TABLE t1(id int, name varchar(20));
INSERT INTO t1 values(1,'alibaba');
INSERT INTO t1 values(2,'hologres');
    • SELECT decode(id, 1, 'alibaba', 2, 'hologres', 'not found') from t1;
    • SELECT decode(id, 3, 'alibaba', 4, 'hologres', 'not found') from t1;
    • hologres
alibaba
    • not found
not found

    dump(anyelement)

    Returns a text value that contains the data type code, length in bytes, and internal representation of the first parameter.

    SELECT dump('hologres');

    Typ=25 Len=12: 48,0,0,0,104,111,108,111,103,114,101,115

    dump(anyelement, num integer)

    The second parameter specifies the numeral system for the internal representation in the returned text value.

    • SELECT dump('hologres', 10);
    • SELECT dump('hologres', 16);

    • Typ=25 Len=12: 48,0,0,0,104,111,108,111,103,114,101,115

    • Typ=25 Len=12: 30,0,0,0,68,6f,6c,6f,67,72,65,73

    nlssort(str text, str text)

    A data sorting function that uses a specified collation.

    Create a table and insert data.

    CREATE TABLE t1 (name text); INSERT INTO t1 VALUES('Anne'), ('anne'), ('Bob'), ('bob');
    • SELECT * FROM t1 ORDER BY nlssort(name, 'en_US.UTF-8');
    • SELECT*FROM t1 ORDER BY nlssort(name, 'C');
    • anne
Anne
bob
Bob
    • Anne
Bob
anne
bob
阿里云首页 实时数仓 Hologres 相关技术圈