Oracle Compatibility Functions Reference

Post date: Apr 23, 2013 6:3:50 PM

The following are the Oracle Compatibility Functions.

add_months

Oracle-compliant function to add a given number of months to a given date.

Synopsis

add_months(date_expression, months_to_add)

Description

This Oracle-compatible function adds months_to_add to a date_expression and returns a DATE.

If the date_expression specifies the last day of the month, or if the resulting month has fewer days than the date_expression, then the returned value is the last day of the resulting month. Otherwise, the returned value has the same day of the month as the date_expression.

Parameters

date_expression

The starting date. This can be any expression that can be implicitly converted to

DATE.

months_to_add

The number of months to add to the date_expression. This is an integer or any

value that can be implicitly converted to an integer. This parameter can be positive

or negative.

Example

SELECT name, phone, nextcalldate FROM clientdb

WHERE nextcalldate >= add_months(CURRENT_DATE,6);

Returns name, phone, and nextcalldate for all records where nextcalldate is at least six months in the future.

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

bitand

Oracle-compliant function that computes a logical AND operation on the bits of two

non-negative values.

Synopsis

bitand(expr1, expr2)

Description

This Oracle-compatible function returns an integer representing an AND operation on the bits of two non-negative values (expr1 and expr2). 1 is returned when the values are the same. 0 is returned when the values are different. Only significant bits are compared. For example, an AND operation on the integers 5 (binary 101) and 1 (binary 001 or 1) compares only the rightmost bit, and results in a value of 1 (binary 1).

The types of expr1 and expr2 are NUMBER, and the result is of type NUMBER. If either argument is NULL, the result is NULL.

The arguments must be in the range -(2(n-1)) .. ((2(n-1))-1). If an argument is out of this range, the result is undefined.

Notes:

•The current implementation of BITAND defines n = 128.

•PL/SQL supports an overload of BITAND for which the types of the inputs and of the result are all BINARY_INTEGER and for which n = 32.

Parameters

expr1

A non-negative integer expression.

expr2

A non-negative integer expression.

Example

SELECT bitand(expr1, expr2)

FROM ClientDB;

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

concat

Oracle-compliant function to concatenate two strings together.

Synopsis

concat (string1, string2)

Description

This Oracle-compatible function concatenates two strings (string1 and string2) together.

The string returned is in the same character set as string1. Its datatype depends on the datatypes of the arguments.

In concatenations of two different datatypes, the datatype returned is the one that results in a lossless conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national datatype, then the returned value is a national datatype. For example:

concat(CLOB, NCLOB) returns NCLOB

concat(NCLOB, NCHAR) returns NCLOB

concat(NCLOB, CHAR) returns NCLOB

concat(NCHAR, CLOB) returns NCLOB

This function is equivalent to the concatenation operator (||).

Parameters

string1/string2

The two strings to concatenate together.

Both string1 and string2 can be any of the datatypes CHAR, VARCHAR2, NCHAR,

NVARCHAR2, CLOB, or NCLOB.

Example

SELECT concat(concat(last_name, '''s job category is '),

job_id)

FROM employees

Returns ‘Smith’s job category is 4B’

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

cosh

Oracle-compliant function to return the hyperbolic cosine of a given number.

Synopsis

cosh(float8)

Description

This Oracle-compatible function returns the hyperbolic cosine of the floating 8 input number (float8).

Note: This function is available by default and can be accessed without running the Oracle Compatibility installer.

Parameters

float8

The input number.

Example

SELECT cosh(0.2)

FROM ClientDB;

Returns ‘1.02006675561908’' (hyperbolic cosine of 0.2)

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

decode

Oracle-compliant function to transform a data value to a specified return value. This

function is a way to implement a set of CASE statements.

Note: decode is converted into a reserved word in Greenplum Database. If you want to use the Postgres two-argument decode function that decodes binary strings previously encoded to ASCII-only representation, you must invoke it by using the full schema-qualified syntax, pg_catalog.decode(), or by enclosing the function name in quotes "decode" ().

Note: Greenplum’s implementation of this function transforms decode into case.

This results in the following type of output:

gptest=# select decode(a, 1, 'A', 2, 'B', 'C') from decodetest;

case

------

C

A

C

B

C

(5 rows)

This also means that if you deparse your view with decode, you will see case expression instead.

Greenplum recommends you use the case function instead of decode.

Synopsis

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

[, default])

Description

The Oracle-compatible function decode searches for a value in an expression. If the value is found, the function returns the specified value.

Note: This function is available by default and can be accessed without running the Oracle Compatibility installer.

Parameters

expression

The expression to search.

value

The value to find in the expression.

return

What to return if expression matches value.

default

What to return if expression does not match any of the values.

Only one expression is passed to the function. Multiple value/return pairs can be passed.

The default parameter is optional. If default is not specified and if expression does not match any of the passed value parameters, decode returns null. The Greenplum implementation restricts return and default to be of the same data type. The expression and value can be different types if the data type of value can be converted into the data type of the expression. This is done implicitly. Otherwise, decode fails with an invalid input syntax error.

Examples

In the following code, decode searches for a value for company_id and returns a specified value for that company. If company_id not one of the listed values, the default value Other is returned.

SELECT decode(company_id, 1, 'EMC',

2, 'Greenplum',

'Other')

FROM suppliers;

The following code using CASE statements to produce the same result as the example using decode.

SELECT CASE company_id

WHEN IS NOT DISTINCT FROM 1 THEN 'EMC'

WHEN IS NOT DISTINCT FROM 2 THEN 'Greenplum'

ELSE 'Other'

END

FROM suppliers;

Method 1 - Include 12 values in the decode function

SELECT decode(curr_month, 1, 'Q2',

2, 'Q3',

3, 'Q3',

4, 'Q3',

5, 'Q4',

6, 'Q4',

7, 'Q4',

8, 'Q1',

9, 'Q1',

10, 'Q1',

11, 'Q2',

12, 'Q2')

FROM suppliers;

Method 2 - Use an expression that defines a unique value to decode

SELECT decode((1+MOD(curr_month+4,12)/3)::int, 1, 'Q1',

2, 'Q2',

3, 'Q3',

4, 'Q4',

FROM suppliers;

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

dump

Oracle-compliant function that returns a text value that includes the datatype code,the

length in bytes, and the internal representation of the expression.

Synopsis

dump(expression [,integer])

Description

This Oracle-compatible function returns a text value that includes the datatype code, the length in bytes, and the internal representation of the expression.

Parameters

expression

Any expression

integer

The number of characters to return

Example

dump('Tech') returns 'Typ=96 Len=4: 84,101,99,104'

dump (‘tech’) returns ‘Typ-96 Len=4: 84,101,99,104’

dump('Tech', 10) returns 'Typ=96 Len=4: 84,101,99,104'

dump('Tech', 16) returns 'Typ=96 Len=4: 54,65,63,68'

dump('Tech', 1016) returns 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'

dump('Tech', 1017) returns 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

instr

Oracle-compliant function to return the location of a substring in a string.

Synopsis

instr(string, substring, [position[,occurrence]])

Description

This Oracle-compatible function searches for a substring in a string. If found, it returns an integer indicating the position of the substring in the string, if not found, the function returns 0.

Optionally you can specify that the search starts at a given position in the string, and only return the nth occurrence of the substring in the string.

instr calculates strings using characters as defined by the input character set.

The value returned is of NUMBER datatype.

Parameters

string

The string to search.

substring

The substring to search for in string.

Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR,

NVARCHAR2, CLOB, or NCLOB.

position

The position is a nonzero integer in string where the search will start. If not

specified, this defaults to 1. If this value is negative, the function counts backwards

from the end of string then searches towards to beginning from the resulting

position.

occurrence

Occurrence is an integer indicating which occurrence of the substring should be

searched for. The value of occurrence must be positive.

Both position and occurrence must be of datatype NUMBER, or any datatype that

can be implicitly converted to NUMBER, and must resolve to an integer. The default

values of both position and occurrence are 1, meaning that the search begins at

the first character of string for the first occurrence of substring. The return

value is relative to the beginning of string, regardless of the value of position,

and is expressed in characters.

Examples

SELECT instr('Greenplum', 'e')

FROM ClientDB;

Returns 3; the first occurrence of 'e'

SELECT instr('Greenplum', 'e',1,2)

FROM ClientDB;

Returns 4; the second occurrence of 'e'

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

last_day

Oracle-compliant function to return the last day in a given month.

Synopsis

last_day(date_expression)

Description

This Oracle-compatible function returns the last day of the month specified by a date_expression.

The return type is always DATE, regardless of the datatype of date_expression.

Parameters

date_expression

The date value used to calculate the last day of the month. This can be any

expression that can be implicitly converted to DATE.

Example

SELECT name, hiredate, last_day(hiredate) "Option Date"

FROM employees;

Returns the name, hiredate, and last_day of the month of hiredate labeled "Option Date."

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

listagg

Oracle-compliant function that aggregates text values into a string.

Note: This function is an overloaded function. There are two Oracle-compliant listagg functions, one that takes one argument, the text to be aggregated (see below), and one that takes two arguments, the text to be aggregated and a delimiter (see next page).

Synopsis

listagg(text)

Description

This Oracle-compatible function aggregates text values into a string.

Parameters

text

The text value to be aggregated into a string.

Example

SELECT listagg(t) FROM (VALUES('abc'), ('def')) as l(t)

Returns: abcdef

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

listagg (2)

Oracle-compliant function that aggregates text values into a string, separating each by

the separator specified in a second argument.

Note: This function is an overloaded function. There are two Oracle-compliant listagg functions, one that takes one argument, the text to be aggregated (see previous page), and one that takes two arguments, the text to be aggregated and a delimiter (see below).

Synopsis

listagg(text, separator)

Description

This Oracle-compatible function aggregates text values into a string, separating each by the separator specified in a second argument (separator).

Parameters

text

The text value to be aggregated into a string.

separator

The separator by which to delimit the text values.

Example

SELECT oracompat.listagg(t, '.') FROM (VALUES('abc'), ('def')) as l(t)

Returns: abc.def

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

lnnvl

Oracle-compliant function that returns true if the argument is false or NULL, or

false.

Synopsis

listagg(condition)

Description

This Oracle-compatible function takes as an argument a condition and returns true if the condition is false or NULL and false if the condition is true.

Parameters

condition

Any condition that evaluates to true, false, or null.

Example

SELECT lnnvl(true)

Returns: false

SELECT lnnvl(NULL)

Returns: true

SELECT lnnvl(false)

Returns: true

SELECT (3=5)

Returns: true

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

months_between

Oracle-compliant function to evaluate the number of months between two given dates.

Synopsis

months_between(date_expression1, date_expression2)

Description

This Oracle-compatible function returns the number of months between date_expression1 and date_expression2.

If date_expression1 is later than date_expression2, then the result is positive.

If date_expression1 is earlier than date_expression2, then the result is negative.

If date_expression1 and date_expression2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise the function calculates the fractional portion of the month based on a 31-day month.

Parameters

date_expression1, date_expression2

The date values used to calculate the number of months. This can be any expression

that can be implicitly converted to DATE.

Examples

SELECT months_between

(to_date ('2003/07/01', 'yyyy/mm/dd'),

to_date ('2003/03/14', 'yyyy/mm/dd'));

Returns the number of months between July 1, 2003 and March 14, 2014.

SELECT * FROM employees

where months_between(hire_date, leave_date) <12;

Returns the number of months between hire_date and leave_date.

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

nanvl

Oracle-compliant function to substitute a value for a floating point number when a

non-number value is encountered.

Synopsis

nanvl(float1, float2)

Description

This Oracle-compatible function evaluates a floating point number (float1) such as BINARY_FLOAT or BINARY_DOUBLE. If it is a non-number (‘not a number’, NaN), the function returns float2. This function is most commonly used to convert non-number values into either NULL or 0.

Parameters

float1

The BINARY_FLOAT or BINARY_NUMBER to evaluate.

float2

The value to return if float1 is not a number.

float1 and float2 can be any numeric datatype or any nonnumeric datatype that

can be implicitly converted to a numeric datatype. The function determines the

argument with the highest numeric precedence, implicitly converts the remaining

arguments to that datatype, and returns that datatype.

Example

SELECT nanvl(binary1, 0)

FROM MyDB;

Returns 0 if the binary1 field contained a non-number value. Otherwise, it would return the binary1 value.

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

next_day

Oracle-compliant function to return the date of the next specified weekday after a

date.

This section describes using this function with a string argument; see the following page for details about using this function with an integer argument.

Note: This function is an overloaded function. There are two Oracle-compliant next_day functions, one that takes a date and a day of the week as its arguments (see below), and one that takes a date and an integer as its arguments (see next page).

Synopsis

next_day(date_expression, day_of_the_week)

Description

This Oracle-compatible function returns the first day_of_the_week (Tuesday, Wednesday, etc.) to occur after a date_expression.

The weekday must be specified in English.

The case of the weekday is irrelevant.

The return type is always DATE, regardless of the datatype of date_expression.

Parameters

date_expression

The starting date. This can be any expression that can be implicitly converted to

DATE.

day_of_the_week

A string containing the name of a day, in English; for example ‘Tuesday’.

Day_of_the_week is case-insensitive.

Example

SELECT name, next_day(hiredate,"MONDAY") "Second Week Start"

FROM employees;

Returns the name and the date of the next Monday after hiredate labeled "Second Week Start."

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

next_day

Oracle-compliant function to add a given number of days to a date and returns the date

of the following day.

Note: This function is an overloaded function. There are two Oracle next_day functions, one that takes a date and a day of the week as its arguments (see previous page), and one that takes a date and an integer as its arguments (see below).

Synopsis

next_day(date_expression, days_to_add)

Description

This Oracle-compatible function adds the number of days_to_add to a date_expression and returns the date of the day after the result.

The return type is always DATE, regardless of the datatype of date_expression.

Parameters

date_expression

The starting date. This can be any expression that can be implicitly converted to

DATE.

days_to_add

The number of days to be add to the date_expression. This is an integer or any

value that can be implicitly converted to an integer. This parameter can be positive

or negative.

Example

SELECT name, next_day(hiredate,90) "Benefits Eligibility Date"

FROM EMPLOYEES;

Returns the name and the date that is 90 days after hiredate labeled "Benefits Eligibility Date."

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

nlssort

Oracle-compliant function that sorts data according to a specific collation.

Synopsis

nlssort (variable, collation)

Description

This Oracle-compatible function sorts data according to a specific collation.

Parameters

variable

The data to sort.

collation

The collation type by which to sort.

Example

CREATE TABLE test (name text);

INSERT INTO test VALUES('Anne'), ('anne'), ('Bob'), ('bob');

SELECT * FROM test ORDER BY nlssort(name, 'en_US.UTF-8');

anne

Anne

bob

Bob

SELECT * FROM test ORDER BY nlssort(name, 'C');

Anne

Bob

anne

bob

In the first example, the UTF-8 collation rules are specified. This groups characters together regardless of case.

In the second example, ASCII (C) collation is specified. This sorts according to ASCII order. The result is that upper case characters are sorted ahead of lower case ones.

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

nvl

Oracle-compliant function to substitute a specified value when an expression

evaluates to null.

Note: This function is analogous to PostgreSQL coalesce function.

Synopsis

nvl(expression_to_evaluate, null_replacement_value)

Description

This Oracle-compatible function evaluates expression_to_evaluate. If it is null,

the function returns null_replacement_value; otherwise, it returns

expression_to_evaluate.

Parameters

expression_to_evaluate

The expression to evaluate for a null value.

null_replacement_value

The value to return if expression_to_evaluate is null.

Both expression_to_evaluate and null_replacement_value must be the same data type.

Examples

SELECT nvl(contact_name,’None’)

FROM clients;

SELECT nvl(amount_past_due,0)

FROM txns;

SELECT nvl(nickname, firstname)

FROM contacts;

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

nvl2

Oracle-compliant function that returns alternate values for both null and non-null

values.

Synopsis

nvl2(expression_to_evaluate, non_null_replacement_value,

null_replacement_value)

Description

This Oracle-compatible function evaluates expression_to_evaluate. If it is not null, the function returns non_null_replacement_value; otherwise, it returns null_replacement_value.

Parameters

expression_to_evaluate

The expression to evaluate for a null value.

non_null_replacement_value

The value to return if expression_to_evaluate is not null.

null_replacement_value

The value to return if expression_to_evaluate is null.

Example

select nvl2(unit_number,’Multi Unit’,’Single Unit’)

from clients;

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

oracle.substr

This Oracle-compliant function extracts a portion of a string.

Synopsis

oracle.substr(string, [start [,char_count]])

Description

This Oracle-compatible function extract a portion of a string.

If start is 0, it is evaluated as 1.

If start is negative, the starting position is negative, the starting position is start characters moving backwards from the end of string.

If char_count is not passed to the function, all characters from start to the end of string are returned.

If char_count is less than 1, null is returned.

If start or char_count is a number, but not an integer, the values are resolved to integers.

Parameters

string

The string from which to extract.

start

An integer specifying the starting position in the string.

char_count

An integer specifying the number of characters to extract.

Example

oracle.substr(name,1,15)

Returns the first 15 characters of name.

oracle.substr("Greenplum",-4,4)

Returns "plum."

oracle.substr(name,2)

Returns all characters of name, beginning with the second character.

Compatibility

PostgreSQL substr (not compatible with Oracle)

reverse

Oracle-compliant function to return the input string in reverse order.

Synopsis

reverse (string)

Description

This Oracle-compatible function returns the input string (string) in reverse order.

Parameters

string

The input string.

Example

SELECT reverse(‘gnirts’)

FROM ClientDB;

Returns ‘string’'

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

round

Oracle-compliant function to round a date to a specific unit of measure (day, week,

etc.).

Note: This function is an overloaded function. It shares the same name with the Postgres round mathematical function that rounds numeric input to the nearest integer or optionally to the nearest x number of decimal places.

Synopsis

round (date_time_expression, [unit_of_measure])

Description

This Oracle-compatible function rounds a date_expression to the nearest unit_of_measure (day, week, etc.). If a unit_of_measure is not specified, the date_expression is rounded to the nearest day. It operates according to the rules of the Gregorian calendar.

If the date_time_expression datatype is TIMESTAMP, the value returned is always of datatype TIMESTAMP.

If the date_time_expression datatype is DATE, the value returned is always of datatype DATE.

Parameters

date_time_expression

The date to round. This can be any expression that can be implicitly converted to

DATE or TIMESTAMP.

unit_of_measure

The unit of measure to apply for rounding. If not specified, then the

date_time_expression is rounded to the nearest day. 

Example

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

FROM ClientDB;

Returns ‘01-JAN-01’ (27 Oct 00 rounded to the first day of the following year (YEAR))

SELECT round('startdate','Q')

FROM ClientDB;

Returns ‘01-JUL-92’ (the startdate rounded to the first day of the quarter (Q))

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

sinh

Oracle-compliant function to return the hyperbolic sine of a given number.

Synopsis

sinh(float8)

Description

This Oracle-compatible function returns the hyperbolic sine of the floating 8 input number (float8).

Note: This function is available by default and can be accessed without running the Oracle Compatibility installer.

Parameters

float8

The input number.

Example

SELECT sinh(3)

FROM ClientDB;

Returns ‘10.0178749274099’'(hyperbolic sine of 3)

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

tanh

Oracle-compliant function to return the hyperbolic tangent of a given number.

Synopsis

tanh(float8)

Description

This Oracle-compatible function returns the hyperbolic tangent of the floating 8 input number (float8).

Note: This function is available by default and can be accessed without running the Oracle Compatibility installer.

Parameters

float8

The input number.

Example

SELECT tanh(3)

FROM ClientDB;

Returns ‘0.99505475368673’' (hyperbolic tangent of 3)

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.

trunc

Oracle-compliant function to truncate a date to a specific unit of measure (day, week,

hour, etc.).

Note: This function is an overloaded function. It shares the same name with the Postgres trunc and the Oracle trunc mathematical functions. Both of these truncate numeric input to the nearest integer or optionally to the nearest x number of decimal places.

Synopsis

trunc(date__time_expression, [unit_of_measure])

Description

This Oracle-compatible function truncates a date_time_expression to the nearest unit_of_measure (day, week, etc.). If a unit_of_measure is not specified, the date_time_expression is truncated to the nearest day. It operates according to the rules of the Gregorian calendar.

If the date_time_expression datatype is TIMESTAMP, the value returned is always of datatype TIMESTAMP, truncated to the hour/min level.

If the date_time_expression datatype is DATE, the value returned is always of datatype DATE.

Parameters

date_time_expression

The date to truncate. This can be any expression that can be implicitly converted to

DATE or TIMESTAMP.

unit_of_measure

The unit of measure to apply for truncating. If not specified, then

date_time_expression is truncated to the nearest day.

Examples

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')

FROM ClientDB;

Returns ‘01-JAN-92’ (27 Oct 92 truncated to the first day of the year (YEAR))

SELECT TRUNC(startdate,'Q')

FROM ClientDB;

Returns ‘1992-07-01’ (the startdate truncated to the first day of the quarter (Q), depending on the date_style setting)

Compatibility

This command is compatible with Oracle syntax and is provided for convenience.