Functions

add_months(date,integer)

Returns the date plus integer months. The return type is DATE.

appendchildxml(XMLType_instance, XPath_string, value_expr[, namespace_string])

Appends the value_expr node onto XPath_string specified by XMLType_instance. namespace_string provides namespace information for the XPath_string.

asciistr(string)

Returns an ASCII version of the string in the database character set. Non-ASCII characters are not supported.

bin_to_num(expr_list)

Converts a binary string in expr_list to its equivalent decimal number. The return type is NUMBER.

bitand(number1,number2)

Returns the bitwise 'AND' for two supplied integers number1 and number2. The return type is BIT.

convert(char, dest_char_set[, source_char_set])

Converts char in the source_char_set to the dest_char_set encoding format. This function takes effect only on the server.

cosh(n)

Returns the hyperbolic cosine of argument n.

decode(expr,search1, result1[[,search2, result2],......][, default])

Compares expr to each search value one (search1, search2, etc). If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

empty_blob()

Returns an empty BLOB.

hextoraw(char)

Converts a hexadecimal string to a raw value.

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

Searches a string for a substring using characters and return the position in the string that is the first character of a specified occurrence of the substring. The functions vary in how they determine the position of the substring to return.

last_day(date)

Returns the date of the last day of the month that contains date.

lengthb(char)

Returns the length of char. Char can be any of the data types (CHAR, VARCHAR2, NCHAR, or NVARCHAR2), or types (such as integer) that can be implicitly converted into character strings.

listagg(measure_expr[, 'delimiter']) within group(order_by_clause) [over query_partition_clause]

Sorts the values of the column expression measure_expr in the query_partition_clause group based on the order_by_clause rule and aggregates them into one row. Values are separated by delimiter.

lnnvl(condition)

Returns a value of condition expression. The return type is BOOLEAN.

mod(n2, n1)

Returns the remainder of n2 divided by n1. Returns n2 if n1 is 0.

months_between(date1, date2)

Returns the number of months between dates date1 and date2. If date1 is earlier than date2, then the result is negative.

nanvl(n2, n1)

Returns n1 if the single- or double-precision floating point number input value n2 is NAN. If the input value n2 is not NAN, n2 is returned.

nchr(number)

Returns the character having the binary equivalent to number in the national character set.

new_time(date, timezone1, timezone2)

Returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. The return type is DATE.

next_day(date, char)

Returns the date of the first weekday named by char that is later than the date (including workdays, weekends, and holidays). The return type is DATE.

numtodsinterval(n, interval_unit)

Converts n to an INTERVAL DAY TO SECOND literal. The value for interval_unit specifies the unit of n and must resolve to 'DAY', 'HOUR', 'MINUTE', and 'SECOND'.

numtoyminterval(n, 'interval_unit')

Converts n to an INTERVAL YEAR TO MONTH literal. The value for interval_unit can be YEAR or MONTH.

nlssort(char[, nlsparam])

Sorts the char string according to the sorting character set specified by nlsparam. By default, char is used for sorting.

nls_upper(char[, nlsparam])

Converts all alphabetic characters in the character string char to uppercase letters based on the sort sequence specified by nlsparam. The character string type is CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB, and nlsparam is in the form of NLS_SORT = sort.

nls_lower(char[, nlsparam])

Converts all alphabetic characters in the character string char to lowercase letters based on the sort sequence specified by nlsparam. The character string type is CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB, and nlsparam is in the form of NLS_SORT = sort.

nvl(expr1, expr2)

Returns the first non-null value in expr1 and expr2.

rawtohex(raw)

Converts raw to a character value containing its hexadecimal representation.

regexp_count(source_char, pattern, position, match_param)

Returns the number of times a pattern occurs in a source string starting from the position that indicates the source_char character where the database begins the search. The match_param parameter is a text literal that lets you change the default matching behavior of the function. For example, match_param='i' specifies case-insensitive matching.

regexp_instr(source_char, pattern[, position[, occurrence[, return_opt[, match_param[, subexpr]]]]])

Extends the INSTR function and allows regular expression matching. The return type is INTEGER.

• position: indicates the start position of the search.
• occurrence: indicates the sequence number of the pattern in source_char.
• return_opt:
  • The value 0 indicates the start position of the return mode.
  • The value 1 indicates the end position of the return mode.
• match_param: indicates the control parameter of the regular expression, such as case sensitive.
• subexpr: indicates the group number of the regular expression group.

regexp_like(source_char, pattern[,match_param])

source_char is a character expression. Pattern is the regular expression. The match_param parameter is a text literal that lets you change the default matching behavior of the function.

regexp_substr(source_char, pattern[,position[,occurrence[,match_param[,subexpr]]]])

Matches the character string in the source_char string based on the regular expression.

• source_char is the text expression that is searched. Supports all character strings, including CHAR, VARCHAR2, NCHAR, or NVARCHAR2, or types (such as integer) that can be implicitly converted into character strings.
• pattern is the text expression to search for.
• position is a nonzero integer indicating the character of source_char where the function begins the search.
• occurrence is an integer indicating which occurrence of pattern the function should search for.
• match_parameter is a text expression that lets you change the default matching behavior of the function.
• subexpr is a nonnegative integer from 0 to 9 indicating which subexpression in pattern is to be returned by the function.

raise_application_error(errnum, errmsg)

Sends the error code errnum and error message errmsg to the client.

remainder(n2, n1)

Returns the remainder of n2 divided by n1. The remainder function is similar to mod except that mod uses floor in its formula, whereas reminder uses ROUND. The return type is NUMERIC or double-precision floating-point number (determined by the input parameter type).

round(date, fmt)

Rounds off date according to the date format specified by fmt. The return type is DATE. If fmt is omitted, the latest day is returned.

round(n,precision)

Returns n rounded to integer places to the right of the decimal point. The precision is the number of digits in a number.

scn_to_timestamp(number)

Returns the approximate timestamp associated with a system change number (SCN).

sinh(n)

Returns the hyperbolic sine of n. If n is BINARY_FLOAT, the return type is BINARY_DOUBLE. Otherwise, the return type is NUMERIC.

substr(char,position[,substring_length])

Returns a portion of string, beginning at a specified position in the string. The functions vary in how they calculate the length of the substring to return. If substring_length is not specified, the function returns all characters to the end of string.

substrb(char, position[, substring_length])

Returns a portion of char, beginning at a specified position in the string. The functions vary in how they calculate the length of the substring to return. If substring_length is not specified, the function returns all characters to the end of string.

sys_context(namespace, parameter)

Returns the value of parameter associated with the context namespace. The return type is VARCHAR2.

sys_guid()

Returns a globally unique identifier (RAW value).

sys_connect_by_path(column, char)

Is valid only in CONNECT BY queries and returns the path of a column value from root to node.

tanh(n)

Returns the hyperbolic tangent of argument n.

to_blob(char)

Converts char strings to BLOB values. Char can be any of the data types (CHAR, VARCHAR2, NCHAR, or NVARCHAR2), or types (such as integer) that can be implicitly converted into character strings.

to_binary_float(expr)

Converts expr to the single-precision float type.

to_binary_double(expr)

Converts expr to the double-precision float type.

to_clob(char)

Converts char to the CLOB data type.

to_char(char)

Supports char types: char, character, and varchar.

to_date(char[,fmt])

Converts char of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or TIMESTAMP data type to a value of the DATE data type according to the fmt format. If fmt is omitted, char must use the default format of the DATE data type.

to_dsinterval('sql_format' | 'ds_iso_format')

Converts the time character string of the SQL standard (such as '100 00:00:00') or ISO standard (such as 'P100DT05H') to the INTERVAL DAY TO SECOND data type.

to_multi_byte(char)

Converts a single-byte character char into a multi-byte character.

to_number(expr)

Converts expr to a value of NUMBER data type.

to_number(expr, fmt, 'nlsparam')

Converts expr to a value of NUMBER data type in the format specified by fmt. The nlsparam is an international language parameter and supports the following parameters: NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, and NLS_ISO_CURRENCY.

to_timestamp(char[,fmt])

Converts char of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or TIMESTAMP data type to a value of the timestamp data type according to the fmt format. If fmt is omitted, char must use the default format of the TIMESTAMP data type.

to_single_byte(char)

Converts multibyte characters to their corresponding single-byte characters.

to_yminterval('sql_format' | 'ym_iso_format')

Converts the time character string of the SQL standard (such as '01-02') or ISO standard (such as 'P1Y2M') to the INTERVAL MONTH TO YEAR data type.

timestamp_to_scn(timestamp)

Returns the approximate system change number (SCN) associated with a timestamp.

trunc(date[, fmt])

Truncates date according to the date format specified by fmt. The return type is DATE. If fmt is omitted, the default date format is 'DDD'.

tz_offset({time_zone_name | '{+|-}hh:mi'})

Returns the specified time zone offset. The return type is VARCHAR2. The parameter is a character string in the time_zone_name or '{+|-}hh:mi' format.

value(correlation_variable)

Returns the recorded row associated with correlation_variable in object table mode. The return type is the object table associated with correlation_variable.

DBMS_OUTPUT.PUT(item)

Places the item string in the local buffer. Item indicates all types that can be converted into character strings.

DBMS_OUTPUT.PUT_LINE(item)

Places the item string in the local buffer and outputs all the content in the local buffer. Item indicates all types that can be converted into character strings.

DBMS_RANDOM.SEED(val)

Val is the seed number used to generate a random number. It can be a character string or a digit.

DBMS_RANDOM.VALUE([low,high])

Returns a 16-digit random number between low and high. If the range of low and high is not specified, the default value range is 0-1.

dbms_lob.getlength(lob_loc {clob|blob})

Returns the LOB length specified by lob_loc.

dbms_lob.read(lob_loc, amount, offset, buffer)

Returns the specified amount into the buffer parameter, starting from an absolute offset from the beginning of the LOB.

dbms_lob.write(lob_loc, amount, offset, buffer)

Writes the buffer content to the large object lob_loc buffer (the referenced large object is not affected) starting at offset. The amount represents the size.

utl_raw.cast_to_raw(char)

Converts char of the VARCHAR2 data type to RAW. The return type is RAW.

utl_raw.length(raw)

Returns the length of the raw data type. The return type is NUMBER.

utl_raw.cast_from_binary_integer(n, endianess)

Convert the integer n to the RAW type based on the memory alignment mode specified by endianess. The values of endianess are as follows:

• 1: big_endian
• 2: little_endian
• 3: machine_endian