All Products
Search

This Product

    :Supported Oracle functions

    Document Center

    :Supported Oracle functions

    Last Updated:Nov 29, 2023

    In Hologres V1.3 and later, the Orafce extension is introduced to support some Oracle functions. This topic describes the syntax and details of the Oracle functions that are supported in Hologres.

    Install the Orafce extension

    Before you use a supported Oracle function in a database in Hologres, you must execute the following statement as a superuser to install the Orafce extension in the database. The extension is installed at the database level. You need to install the extension only once for each database. If you create a database, you must execute the following statement again: 

    -- Install the Orafce extension.
create extension if not exists orafce; 
-- Uninstall the Orafce extension.
DROP EXTENSION orafce;
    Note

    When the Orafce extension is installed, multiple schemas are automatically created, including 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 Orafce extension, make sure that no schema with the same name exists in the database. Otherwise, the Orafce extension fails to be installed.

    Oracle functions

    The following table describes the Oracle functions that are supported in Hologres.

    • Date functions

      Function

      Description

      Example

      Return value

      add_months(day date, value int)

      This function adds the number of months that is specified by the second parameter to the date that is specified by the first parameter and returns a date. 

      SELECT add_months(current_date, 2);

      2022-05-21

      oracle_last_day(value date)

      This function locates the last day of the month to which the specified date belongs and returns a date. 

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

      2022-05-31

      next_day(value date, weekday text)

      This function returns the date of a day. The day is the first of the specified day of a week to be encountered starting from the specified date. The first parameter specifies a start date. The second parameter specifies a day of a week. Example: Friday. 

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

      2022-05-06

      next_day(value date, weekday integer)

      This function returns the date of a day. The day is the first of the specified day of a week to be encountered starting from the specified date. The first parameter specifies a start date. The second parameter specifies a number that represents a day of a week. The number ranges from 1 to 7. The value 1 represents Sunday, and the value 2 represents Monday. In a similar manner, the value 7 represents Saturday. 

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

      This function returns the number of months between date1 and date2. If date1 is later than date2, the return value is positive. If date1 is earlier than date2, the return value is negative. 

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

      This function truncates a timestamp to return a date. The first parameter specifies the timestamp that you want to truncate. The second parameter specifies the unit of measurement that you want to use to truncate the timestamp. A value of Y for the second parameter specifies that the timestamp is truncated to the first day of the year that corresponds to the timestamp. A value of Q for the second parameter specifies that the timestamp is truncated to the first day of the quarter that corresponds to the timestamp. 

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

      • 2022-01-01

      • 2022-01-01

      trunc(value timestamp with time zone)

      This function returns a timestamp based on a specified date. By default, a timestamp with the hour, minute, and second values is returned. 

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

      2022-03-01 00:00:00

      round(value timestamp with time zone, fmt text)

      This function rounds a timestamp to the nearest value based on a unit such as week or day. 

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

      2022-01-01 00:00:00

      round(value timestamp with time zone)

      This function rounds a timestamp to the nearest value based on the unit day. 

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

      2022-02-23 00:00:00

      round(value date, fmt text)

      This function returns a rounded date based on a specified format. 

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

      This function returns a rounded date based on a specified format. 

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

      2000-02-27

    • String functions

      Function

      Description

      Example

      Return value

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

      This function searches for a substring in a string. If a substring is obtained, the function returns the position of the substring. Otherwise, the function returns 0. The start parameter specifies the start position of the search. The nth parameter specifies the position of the nth occurrence of the substring. 

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

      • 4

      • 2

      instr(str text, patt text, start integer)

      This function returns the position of the first occurrence of a substring in a string. 

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

      4

      instr(str text, patt text)

      This function searches for a substring from the start of a string. 

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

      2

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

      This function reverses the order of characters in a specified string. The str parameter specifies the string, and the start and end parameters specify the start position and end position of the characters whose order you want to reverse. 

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

      rg

      plvstr.rvrs(str text, start integer)

      This function reverses the order of characters from the character that is specified by the start parameter to the end of a specified string. 

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

      sergo

      plvstr.rvrs(str text)

      This function reverses the order of an entire string. 

      SELECT plvstr.rvrs('Hologres');

      sergoloH

      substr(str text, start integer)

      This function obtains the characters from the character that is specified by the start parameter to the end of a specified string. 

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

      • Hologres

      • ogres

      substr(str text, start integer, len integer)

      This function obtains a specified number of characters starting from the character that is specified by the start parameter. The len parameter specifies the number of characters that you want to obtain. 

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

      • gresle

      • lefun

    • Other functions

      Function

      Description

      Example

      Return value

      listagg(str text)

      This function returns a clustered string for texts. 

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

      hologres

      listagg(str text, str text)

      This function returns a clustered string for texts. The value of the second parameter is used as a delimiter. 

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

      holo.gres

      concat(str text, str text)

      This function joins two strings together. 

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

      hologres

      • concat(str text, anyarray)

      • concat(anyarray, str text)

      • concat(anyarray, anyarray)

      This function joins data of the same or different data types together. 

      • 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 of the NUMERIC data type, this function returns the value of the first parameter. Otherwise, this function returns the value of the second parameter. 

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

      • 1.1

      • 1.2

      bitand(num bigint, num bigint)

      This function performs an AND operation for two binary numbers of the INTEGER data type. Only one row is returned. 

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

      • 1

      • 2

      • 4

      nvl2(anyelement, anyelement, anyelement)

      If the value of the first parameter is null, this function returns the value of the third parameter. Otherwise, this function returns the value of the second parameter. 

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

      • 2

      • 1

      lnnvl(boolean)

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

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

      • t

      • t

      • f

      sinh(num double precision)

      This function returns a hyperbolic sine value. 

      SELECT sinh(0.1);

      0.100166750019844

      tanh(num double precision)

      This function returns a hyperbolic tangent value. 

      SELECT tanh(3);

      0.995054753686731

      cosh(num double precision)

      This function returns a hyperbolic cosine value. 

      SELECT cosh(0.2);

      1.02006675561908

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

      This function searches for a value from an expression and returns the value if the value is found or the specified default value if the value is not found. 

      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)

      This function returns a text 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)

      This function returns a text. The second parameter specifies the numeral system that is used by the internal representation of the first parameter. 

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

      This function sorts data in a specific order.

      Execute the following statements to create a table and insert data to the table: 

      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