4
Functions

Form ever follows function.

Louis Henri Sullivan, The Tall Office Building Artistically Considered

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on zero, one, two, or more arguments:

function(argument, argument, ...)

This chapter describes two types of functions:

SQL Functions

SQL functions are built into Oracle and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user functions written in PL/SQL. User functions are described in "User-Defined Functions". For information about functions used with Oracle interMedia, see Oracle8i interMedia Audio, Image, and Video User's Guide and Reference.

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function. See "Data Conversion".

If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not follow this rule are CONCAT, DECODE, DUMP, NVL, and REPLACE.

In the syntax diagrams for SQL functions, arguments are indicated by their datatypes, following the conventions described in "Syntax Diagrams and Notation" in the Preface of this reference. When the parameter "function" appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values. The general syntax is as follows:

function::=

Table 4-1 lists the built-in SQL functions in each of the groups illustrated above except user-defined functions. All of the built-in SQL functions are then described in alphabetical order. User-defined functions are described at the end of this chapter.

Table 4-1 SQL Function Groups

Group	Functions		Description
Single-Row Functions	Single-row functions return a single result row for every row of a queried table or view. Single-row functions can appear in select lists (if the SELECT statement does not contain a GROUP BY clause), WHERE clauses, START WITH clauses, and CONNECT BY clauses.
Number Functions	Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits.
	ABS ACOS ADD_MONTHS ATAN ATAN2 CEIL COS COSH	EXP FLOOR LN LOG MOD POWER ROUND (Number Function)	SIGN SIN SINH SQRT TAN TANH TRUNC (Number Function)
Character Functions returning character values	Character functions that return character values, unless otherwise noted, return values with the datatype VARCHAR2 and are limited in length to 4000 bytes. Functions that return values of datatype CHAR are limited in length to 2000 bytes. If the length of the return value exceeds the limit, Oracle truncates it and returns the result without an error message.
	CHR CONCAT INITCAP LOWER LPAD LTRIM NLS_INITCAP	NLS_LOWER NLSSORT NLS_UPPER REPLACE RPAD RTRIM SOUNDEX	SUBSTR SUBSTRB TRANSLATE TRIM UPPER
Character Functions	All of the functions listed below return number values.
returning number values	ASCII INSTR	INSTRB LENGTH	LENGTHB
Date Functions	Date functions operate on values of the DATE datatype. All date functions return a value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number.
	ADD_MONTHS LAST_DAY MONTHS_BETWEEN	NEW_TIME NEXT_DAY ROUND (Date Function)	SYSDATE TRUNC (Date Function)
Conversion Functions	Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. This section lists the SQL conversion functions.
	CHARTOROWID CONVERT HEXTORAW RAWTOHEX ROWIDTOCHAR	TO_CHAR (date conversion) TO_CHAR (number conversion) TO_DATE	TO_LOB TO_MULTI_BYTE TO_NUMBER TO_SINGLE_BYTE TRANSLATE ... USING
Miscellaneous Single Row Functions	The following single-row functions do not fall into any of the other single-row function categories.
	BFILENAME DUMP EMPTY_[B \| C]LOB GREATEST LEAST	NLS_CHARSET_DECL_LEN NLS_CHARSET_ID NLS_CHARSET_NAME NVL SYS_CONTEXT	SYS_GUID UID USER USERENV VSIZE
Object Reference Functions	Object functions manipulate REFs, which are references to objects of specified object types. For more information about REFs, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.
	DEREF MAKE_REF	REF REFTOHEX	VALUE
Aggregate Functions	Aggregate functions return a single row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and HAVING clauses. If you use the GROUP BY clause in a SELECT statement, Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, all elements of the select list must be expressions from the GROUP BY clause, expressions containing aggregate functions, or constants. Oracle applies the aggregate functions in the select list to each group of rows and returns a single result row for each group. If you omit the GROUP BY clause, Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view. For more information on the GROUP BY clause and HAVING clauses, see the "GROUP BY Examples" and the "HAVING" clause.
	Many aggregate functions accept these options: DISTINCT causes an aggregate function to consider only distinct values of the argument expression. ALL causes an aggregate function to consider all values, including all duplicates. For example, the DISTINCT average of 1, 1, 1, and 3 is 2; the ALL average is 1.5. If neither option is specified, the default is ALL.
	All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL in the argument to an aggregate function to substitute a value for a null. If a query with an aggregate function returns no rows or only rows with nulls for the argument to the aggregate function, the aggregate function returns null.
	AVG COUNT GROUPING	MAX MIN STDDEV	SUM VARIANCE

ASIN

Syntax

Purpose

Returns the arc sine of n. Inputs are in the range of -1 to 1, and outputs are in the range of pi/2 to pi/2 and are expressed in radians.

Example

SELECT ASIN(.3) "Arc_Sine" FROM DUAL; Arc_Sine ---------- .304692654

ATAN

Syntax

Purpose

Returns the arc tangent of n. Inputs are in an unbounded range, and outputs are in the range of -pi/2 to pi/2 and are expressed in radians.

Example

SELECT ATAN(.3) "Arc_Tangent" FROM DUAL; Arc_Tangent ---------- .291456794

ATAN2

Syntax

Purpose

Returns the arc tangent of n and m. Inputs are in an unbounded range, and outputs are in the range of -pi to pi, depending on the signs of n and m, and are expressed in radians. ATAN2(n,m) is the same as ATAN2(n/m)

Example

SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL; Arc_Tangent2 ------------ .982793723

AVG

Syntax

Purpose

Returns average value of n. See also "Aggregate Functions".

Example

SELECT AVG(sal) "Average" FROM emp; Average ---------- 2077.21429

BFILENAME

Syntax

Purpose

Returns a BFILE locator that is associated with a physical LOB binary file on the server's file system. A directory is an alias for a full pathname on the server's file system where the files are actually located, and 'filename' is the name of the file in the server's file system.
Neither 'directory' nor 'filename' needs to point to an existing object on the file system at the time you specify BFILENAME. However, you must associate a BFILE value with a physical file before performing subsequent SQL, PL/SQL, DBMS_LOB package, or OCI operations. For more information, see "CREATE DIRECTORY".
For more information about LOBs, see Oracle8i Application Developer's Guide - Large Objects (LOBs) and Oracle Call Interface Programmer's Guide.

Example

INSERT INTO file_tbl VALUES (BFILENAME ('lob_dir1', 'image1.gif'));

CEIL

Syntax

Purpose

Returns smallest integer greater than or equal to n.

Example

SELECT CEIL(15.7) "Ceiling" FROM DUAL; Ceiling ---------- 16

CHARTOROWID

Syntax

Purpose

Converts a value from CHAR or VARCHAR2 datatype to ROWID datatype.

Example

SELECT ename FROM emp WHERE ROWID = CHARTOROWID('AAAAfZAABAAACp8AAO'); ENAME ---------- LEWIS

CHR

Syntax

Purpose

Returns the character having the binary equivalent to n in either the database character set or the national character set.
If USING NCHAR_CS is not specified, this function returns the character having the binary equivalent to n as a VARCHAR2 value in the database character set.
If USING NCHAR_CS is specified, this function returns the character having the binary equivalent to n as a NVARCHAR2 value in the national character set.

Example 1

SELECT CHR(67)||CHR(65)||CHR(84) "Dog" FROM DUAL; Dog --- CAT

Example 2

SELECT CHR(16705 USING NCHAR_CS) FROM DUAL; C - A

CONCAT

Syntax

Purpose

Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). For information on this operator, see "Concatenation Operator".

Example

This example uses nesting to concatenate three character strings:
SELECT CONCAT(CONCAT(ename, ' is a '), job) "Job" FROM emp WHERE empno = 7900; Job ----------------- JAMES is a CLERK

CONVERT

Syntax

Purpose

Converts a character string from one character set to another.
The char argument is the value to be converted.
The dest_char_set argument is the name of the character set to which char is converted.
The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.

Both the destination and source character set arguments can be either literals or columns containing the name of the character set.
For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.

Example

SELECT CONVERT('Groß', 'US7ASCII', 'WE8HP') "Conversion" FROM DUAL; Conversion ---------- Gross

Common character sets include:

US7ASCII
WE8DEC
WE8HP
F7DEC
WE8EBCDIC500
WE8PC850
WE8ISO8859P1

US 7-bit ASCII character set
DEC West European 8-bit character set
HP West European Laserjet 8-bit character set
DEC French 7-bit character set
IBM West European EBCDIC Code Page 500
IBM PC Code Page 850
ISO 8859-1 West European 8-bit character set

COS

Syntax

Purpose

Returns the cosine of n (an angle expressed in radians).

Example

SELECT COS(180 * 3.14159265359/180) "Cosine of 180 degrees" FROM DUAL; Cosine of 180 degrees --------------------- -1

COSH

Syntax

Purpose

Returns the hyperbolic cosine of n.

Example

SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL; Hyperbolic cosine of 0 ---------------------- 1

COUNT

Syntax

Purpose

Returns the number of rows in the query.
If you specify expr, this function returns rows where expr is not null. You can count either all rows, or only distinct values of expr.
If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. See also "Aggregate Functions".

Example 1

SELECT COUNT(*) "Total" FROM emp; Total ---------- 18

Example 2

SELECT COUNT(job) "Count" FROM emp; Count ---------- 14

Example 3

SELECT COUNT(DISTINCT job) "Jobs" FROM emp; Jobs ---------- 5

DEREF

Syntax

Purpose

Returns the object reference of argument e. Argument e must be an expression that returns a REF to an object.

Example

CREATE TYPE emp_type AS OBJECT (eno NUMBER, ename VARCHAR2(20), salary NUMBER); CREATE TABLE emp_table OF emp_type (primary key (eno, ename)); CREATE TABLE dept_table (dno NUMBER, mgr REF emp_type SCOPE IS emp_table); INSERT INTO emp_table VALUES (10, 'jack', 50000); INSERT INTO dept_table SELECT 10, REF(e) FROM emp_table e; SELECT DEREF(mgr) from dept_table; DEREF(MGR)(ENO, ENAME, SALARY) -------------------------------------------------------- EMP_TYPE(10, 'jack', 50000)

DUMP

Syntax

Purpose

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set. For the datatype corresponding to each code, see Table 2-1 .
The argument return_fmt specifies the format of the return value and can have any of the values listed below.

By default, the return value contains no character set information. To retrieve the character set name of expr, specify any of the format values below, plus 1000. For example, a return_fmt of 1008 returns the result in octal, plus provides the character set name of expr.

8

returns result in octal notation.

10

returns result in decimal notation.

16

returns result in hexadecimal notation.

17

returns result as single characters.

The arguments start_position and length combine to determine which portion of the internal representation to return. The default is to return the entire internal representation in decimal notation.
If expr is null, this function returns 'NULL'.

Example 1

SELECT DUMP('abc', 1016) FROM DUAL; DUMP('ABC',1016) ------------------------------------------ Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63

Example 2

SELECT DUMP(ename, 8, 3, 2) "OCTAL" FROM emp WHERE ename = 'SCOTT'; OCTAL ---------------------------- Type=1 Len=5: 117,124

Example 3

SELECT DUMP(ename, 10, 3, 2) "ASCII" FROM emp WHERE ename = 'SCOTT'; ASCII ---------------------------- Type=1 Len=5: 79,84

EMPTY_[B | C]LOB

Syntax

Purpose

Returns an empty LOB locator that can be used to initialize a LOB variable or in an INSERT or UPDATE statement to initialize a LOB column or attribute to EMPTY. EMPTY means that the LOB is initialized, but not populated with data.
You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI.

Examples

INSERT INTO lob_tab1 VALUES (EMPTY_BLOB()); UPDATE lob_tab1 SET clob_col = EMPTY_BLOB();

EXP

Syntax

Purpose

Returns e raised to the nth power, where e = 2.71828183 ...

Example

SELECT EXP(4) "e to the 4th power" FROM DUAL; e to the 4th power ------------------ 54.59815

FLOOR

Syntax

Purpose

Returns largest integer equal to or less than n.

Example

SELECT FLOOR(15.7) "Floor" FROM DUAL; Floor ---------- 15

GREATEST

Syntax

Purpose

Returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. Oracle compares the exprs using nonpadded comparison semantics. Character comparison is based on the value of the character in the database character set. One character is greater than another if it has a higher character set value. If the value returned by this function is character data, its datatype is always VARCHAR2.

Example

SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL; Greatest -------- HARRY

GROUPING

Syntax

Purpose

This function is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE. These operations produce superaggregate rows that contain null values representing the set of all values. You can use the GROUPING function to distinguish a null value that represents the set of all values from an actual null value.

The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The datatype of the value returned by the GROUPING function is an Oracle NUMBER datatype. See the group_by_clause of the SELECT statement for a discussion of these terms.

Example

SELECT DECODE(GROUPING(dname), 1, 'All Departments', dname) AS dname, DECODE(GROUPING(job), 1, 'All Jobs', job) AS job, COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal" FROM emp, dept WHERE dept.deptno = emp.deptno GROUP BY ROLLUP (dname, job);

DNAME JOB Total Empl Average Sa --------------- --------- ---------- ---------- ACCOUNTING CLERK 1 15600 ACCOUNTING MANAGER 1 29400 ACCOUNTING PRESIDENT 1 60000 ACCOUNTING All Jobs 3 35000 RESEARCH ANALYST 2 36000 RESEARCH CLERK 2 11400 RESEARCH MANAGER 1 35700 RESEARCH All Jobs 5 26100 SALES CLERK 1 11400 SALES MANAGER 1 34200 SALES SALESMAN 4 16800 SALES All Jobs 6 18800 All Departments All Jobs 14 24878.5714

HEXTORAW

Syntax

Purpose

Converts char containing hexadecimal digits to a raw value.

Example

INSERT INTO graphics (raw_column) SELECT HEXTORAW('7D') FROM DUAL;

INITCAP

Syntax

Purpose

Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Example

SELECT INITCAP('the soap') "Capitals" FROM DUAL; Capitals --------- The Soap

INSTR

Syntax

Purpose

Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence. If n is negative, Oracle counts and searches backward from the end of char1. The value of m must be positive. The default values of both n and m are 1, meaning Oracle begins searching at the first character of char1 for the first occurrence of char2. The return value is relative to the beginning of char1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1) the return value is 0.

Example 1

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL; Instring ---------- 14

Example 2

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring" FROM DUAL; Reversed Instring ----------------- 2

INSTRB

Syntax

Purpose

The same as INSTR, except that n and the return value are expressed in bytes, rather than in characters. For a single-byte database character set, INSTRB is equivalent to INSTR.

Example

This example assumes a double-byte database character set.
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL; Instring in bytes ----------------- 27

LAST_DAY

Syntax

Purpose

Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.

Example 1

SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL; SYSDATE Last Days Left --------- --------- ---------- 23-OCT-97 31-OCT-97 8

Example 2

SELECT TO_CHAR( ADD_MONTHS( LAST_DAY(hiredate),5), 'DD-MON-YYYY') "Five months" FROM emp WHERE ename = 'MARTIN'; Five months ----------- 28-FEB-1982

LEAST

Syntax

Purpose

Returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. Oracle compares the exprs using nonpadded comparison semantics. If the value returned by this function is character data, its datatype is always VARCHAR2.

Example

SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL; LEAST ------ HAROLD

LENGTH

Syntax

Purpose

Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.

Example

SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL; Length in characters -------------------- 7

LENGTHB

Syntax

Purpose

Returns the length of char in bytes. If char is null, this function returns null. For a single-byte database character set, LENGTHB is equivalent to LENGTH.

Example

This example assumes a double-byte database character set.
SELECT LENGTHB ('CANDIDE') "Length in bytes" FROM DUAL; Length in bytes --------------- 14

LN

Syntax

Purpose

Returns the natural logarithm of n, where n is greater than 0.

Example

SELECT LN(95) "Natural log of 95" FROM DUAL; Natural log of 95 ----------------- 4.55387689

LOG

Syntax

Purpose

Returns the logarithm, base m, of n. The base m can be any positive number other than 0 or 1 and n can be any positive number.

Example

SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL; Log base 10 of 100 ------------------ 2

LOWER

Syntax

Purpose

Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2).

Example

SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase" FROM DUAL; Lowercase -------------------- mr. scott mcmillan

LPAD

Syntax

Purpose

Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.
The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

Example

SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL; LPAD example --------------- *.*.*.*.*Page 1

LTRIM

Syntax

Purpose

Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, you must enclose it in single quotes. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.

Example

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL; LTRIM example ------------ XxyLAST WORD

MAKE_REF

Syntax

Purpose

Creates a REF to a row of an object view or a row in an object table whose object identifier is primary key based. For more information about object views, see Oracle8i Application Developer's Guide - Fundamentals.

Example

CREATE TABLE emp (eno NUMBER, ename VARCHAR2(20), salary NUMBER, PRIMARY KEY (eno, ename)); CREATE TYPE emp_type AS OBJECT (eno NUMBER, ename CHAR(20), salary NUMBER); CREATE VIEW emp_view OF emp_type WITH OBJECT IDENTIFIER (eno, ename) AS SELECT * FROM emp; SELECT MAKE_REF(emp_view, 1, 'jack') FROM DUAL; MAKE_REF(EMP_VIEW,1,'JACK') ------------------------------------------------------ 000067030A0063420D06E06F3C00C1E03400400B40DCB10000001C2 60100010002002900000000000F0600810100140100002A0007000A 8401FE0000001F02C102146A61636B2020202020202020202020202 02020200000000000000000000000000000000000000000

MAX

Syntax

Purpose

Returns maximum value of expr. See also "Aggregate Functions".

Example

SELECT MAX(sal) "Maximum" FROM emp; Maximum ---------- 5000

MIN

Syntax

Purpose

Returns minimum value of expr. See also "Aggregate Functions".

Example

SELECT MIN(hiredate) "Earliest" FROM emp; Earliest --------- 17-DEC-80

MOD

Syntax

Purpose

Returns remainder of m divided by n. Returns m if n is 0.

Example

SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3

This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:
m - n * FLOOR(m/n)

The following statement illustrates the difference between the MOD function and the classical modulus:
SELECT m, n, MOD(m, n), m - n * FLOOR(m/n) "Classical Modulus" FROM test_mod_table; M N MOD(M,N) Classical Modulus ---------- ---------- ---------- ----------------- 11 4 3 3 11 -4 3 -1 -11 4 -3 1 -11 -4 -3 -3

MONTHS_BETWEEN

Syntax

Purpose

Returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, the result is always an integer. Otherwise Oracle calculates the fractional portion of the result based on a 31-day month and considers the difference in time components of d1 and d2.

Example

SELECT MONTHS_BETWEEN (TO_DATE('02-02-1995','MM-DD-YYYY'), TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL; Months ---------- 1.03225806

NEW_TIME

Syntax

Purpose

Returns the date and time in time zone z2 when date and time in time zone z1 are d. The arguments z1 and z2 can be any of these text strings:

AST
ADT

Atlantic Standard or Daylight Time

BST
BDT

Bering Standard or Daylight Time

CST
CDT

Central Standard or Daylight Time

EST
EDT

Eastern Standard or Daylight Time

GMT

Greenwich Mean Time

HST
HDT

Alaska-Hawaii Standard Time or Daylight Time.

MST
MDT

Mountain Standard or Daylight Time

NST

Newfoundland Standard Time

PST
PDT

Pacific Standard or Daylight Time

YST
YDT

Yukon Standard or Daylight Time

NEXT_DAY

Syntax

Purpose

Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session's date language, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument d.

Example

This example returns the date of the next Tuesday after March 15, 1998.
SELECT NEXT_DAY('15-MAR-98','TUESDAY') "NEXT DAY" FROM DUAL; NEXT DAY --------- 16-MAR-98

NLS_CHARSET_DECL_LEN

Syntax

Purpose

Returns the declaration width (in number of characters) of an NCHAR column. The bytecnt argument is the width of the column. The csid argument is the character set ID of the column.

Example

SELECT NLS_CHARSET_DECL_LEN (200, nls_charset_id('ja16eucfixed')) FROM DUAL; NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED')) -------------------------------------------------------- 100

NLS_CHARSET_ID

Syntax

Purpose

Returns the NLS character set ID number corresponding to NLS character set name, text. The text argument is a run-time VARCHAR2 value. The text value 'CHAR_CS' returns the server's database character set ID number. The text value 'NCHAR_CS' returns the server's national character set ID number.
Invalid character set names return null.
For a list of character set names, see Oracle8i Reference.

Example I

SELECT NLS_CHARSET_ID('ja16euc') FROM DUAL; NLS_CHARSET_ID('JA16EUC') ------------------------- 830

Example 2

SELECT NLS_CHARSET_ID('char_cs') FROM DUAL; NLS_CHARSET_ID('CHAR_CS') ------------------------- 2

Example 3

SELECT NLS_CHARSET_ID('nchar_cs') FROM DUAL; NLS_CHARSET_ID('NCHAR_CS') -------------------------- 2

NLS_CHARSET_NAME

Syntax

Syntax
Purpose	Returns the absolute value of n.
Example	SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15

Syntax
Purpose	Returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to pi and are expressed in radians.
Example	SELECT ACOS(.3)"Arc_Cosine" FROM DUAL; Arc_Cosine ---------- 1.26610367

Syntax
Purpose	Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.
Example	SELECT TO_CHAR( ADD_MONTHS(hiredate,1), 'DD-MON-YYYY') "Next month" FROM emp WHERE ename = 'SMITH'; Next Month ----------- 17-JAN-1981

Syntax
Purpose	Returns the decimal representation in the database character set of the first character of char. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code Page 500, this function returns an EBCDIC value. There is no similar EBCDIC character function.
Example	SELECT ASCII('Q') FROM DUAL; ASCII('Q') ---------- 81

Syntax
Purpose	Returns the arc sine of n. Inputs are in the range of -1 to 1, and outputs are in the range of pi/2 to pi/2 and are expressed in radians.
Example	SELECT ASIN(.3) "Arc_Sine" FROM DUAL; Arc_Sine ---------- .304692654

Syntax
Purpose	Returns the arc tangent of n. Inputs are in an unbounded range, and outputs are in the range of -pi/2 to pi/2 and are expressed in radians.
Example	SELECT ATAN(.3) "Arc_Tangent" FROM DUAL; Arc_Tangent ---------- .291456794

Syntax
Purpose	Returns the arc tangent of n and m. Inputs are in an unbounded range, and outputs are in the range of -pi to pi, depending on the signs of n and m, and are expressed in radians. ATAN2(n,m) is the same as ATAN2(n/m)
Example	SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL; Arc_Tangent2 ------------ .982793723

Syntax
Purpose	Returns average value of n. See also "Aggregate Functions".
Example	SELECT AVG(sal) "Average" FROM emp; Average ---------- 2077.21429

Syntax
Purpose	Returns smallest integer greater than or equal to n.
Example	SELECT CEIL(15.7) "Ceiling" FROM DUAL; Ceiling ---------- 16

Syntax
Purpose	Converts a value from CHAR or VARCHAR2 datatype to ROWID datatype.
Example	SELECT ename FROM emp WHERE ROWID = CHARTOROWID('AAAAfZAABAAACp8AAO'); ENAME ---------- LEWIS

Syntax
Purpose	Returns the character having the binary equivalent to n in either the database character set or the national character set. If USING NCHAR_CS is not specified, this function returns the character having the binary equivalent to n as a VARCHAR2 value in the database character set. If USING NCHAR_CS is specified, this function returns the character having the binary equivalent to n as a NVARCHAR2 value in the national character set.
Purpose
Example 1	SELECT CHR(67)\|\|CHR(65)\|\|CHR(84) "Dog" FROM DUAL; Dog --- CAT
Example 2	SELECT CHR(16705 USING NCHAR_CS) FROM DUAL; C - A

Syntax
Purpose	Converts a character string from one character set to another. The char argument is the value to be converted. The dest_char_set argument is the name of the character set to which char is converted. The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.
	Both the destination and source character set arguments can be either literals or columns containing the name of the character set. For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.
Example	SELECT CONVERT('Groß', 'US7ASCII', 'WE8HP') "Conversion" FROM DUAL; Conversion ---------- Gross
	Common character sets include:
	`US7ASCII` `WE8DEC` `WE8HP` `F7DEC` `WE8EBCDIC500` `WE8PC850` `WE8ISO8859P1`	US 7-bit ASCII character set DEC West European 8-bit character set HP West European Laserjet 8-bit character set DEC French 7-bit character set IBM West European EBCDIC Code Page 500 IBM PC Code Page 850 ISO 8859-1 West European 8-bit character set

Syntax
Purpose	Returns the cosine of n (an angle expressed in radians).
Example	SELECT COS(180 * 3.14159265359/180) "Cosine of 180 degrees" FROM DUAL; Cosine of 180 degrees --------------------- -1

Syntax
Purpose	Returns the hyperbolic cosine of n.
Example	SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL; Hyperbolic cosine of 0 ---------------------- 1

Syntax
Purpose	Returns the number of rows in the query. If you specify expr, this function returns rows where expr is not null. You can count either all rows, or only distinct values of expr. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. See also "Aggregate Functions".


Example 1	SELECT COUNT(*) "Total" FROM emp; Total ---------- 18
Example 2	SELECT COUNT(job) "Count" FROM emp; Count ---------- 14
Example 3	SELECT COUNT(DISTINCT job) "Jobs" FROM emp; Jobs ---------- 5

Syntax
Purpose	Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set. For the datatype corresponding to each code, see Table 2-1 . The argument return_fmt specifies the format of the return value and can have any of the values listed below.

	By default, the return value contains no character set information. To retrieve the character set name of expr, specify any of the format values below, plus 1000. For example, a return_fmt of 1008 returns the result in octal, plus provides the character set name of expr.
	8	returns result in octal notation.
	10	returns result in decimal notation.
	16	returns result in hexadecimal notation.
	17	returns result as single characters.
	The arguments start_position and length combine to determine which portion of the internal representation to return. The default is to return the entire internal representation in decimal notation. If expr is null, this function returns 'NULL'.
Example 1	SELECT DUMP('abc', 1016) FROM DUAL; DUMP('ABC',1016) ------------------------------------------ Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63
Example 2	SELECT DUMP(ename, 8, 3, 2) "OCTAL" FROM emp WHERE ename = 'SCOTT'; OCTAL ---------------------------- Type=1 Len=5: 117,124
Example 3	SELECT DUMP(ename, 10, 3, 2) "ASCII" FROM emp WHERE ename = 'SCOTT'; ASCII ---------------------------- Type=1 Len=5: 79,84

Syntax
Purpose	Returns an empty LOB locator that can be used to initialize a LOB variable or in an INSERT or UPDATE statement to initialize a LOB column or attribute to EMPTY. EMPTY means that the LOB is initialized, but not populated with data. You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI.


Examples	INSERT INTO lob_tab1 VALUES (EMPTY_BLOB()); UPDATE lob_tab1 SET clob_col = EMPTY_BLOB();

Syntax
Purpose	Returns e raised to the nth power, where e = 2.71828183 ...
Example	SELECT EXP(4) "e to the 4th power" FROM DUAL; e to the 4th power ------------------ 54.59815

Syntax
Purpose	Returns largest integer equal to or less than n.
Example	SELECT FLOOR(15.7) "Floor" FROM DUAL; Floor ---------- 15

Syntax
Purpose	Returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. Oracle compares the exprs using nonpadded comparison semantics. Character comparison is based on the value of the character in the database character set. One character is greater than another if it has a higher character set value. If the value returned by this function is character data, its datatype is always VARCHAR2.
Example	SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL; Greatest -------- HARRY

4 Functions