4
Functions

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 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 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'));

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.  

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  

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

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

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

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 

Syntax  

Purpose  

Converts char containing hexadecimal digits to a raw value.  

Example  

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

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

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

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

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

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 

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

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

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  

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 

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 

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

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

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

Syntax  

Purpose  

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

Example  

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

Syntax  

Purpose  

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

Example  

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

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  

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

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  

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 

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 

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

Syntax  

Purpose  

Returns the name of the NLS character set corresponding to ID number n. The character set name is returned as a VARCHAR2 value in the database character set.

If n is not recognized as a valid character set ID, this function returns null.

For a list of character set IDs, see Oracle8i Reference.  

Example  

SELECT NLS_CHARSET_NAME(2)
  FROM DUAL;

NLS_CH 
------ 
WE8DEC

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. The value of 'nlsparam' can have this form:

'NLS_SORT = sort'

where sort is either a linguistic sort sequence or BINARY. The linguistic sort sequence handles special linguistic requirements for case conversions. Note that these requirements can result in a return value of a different length than the char. If you omit 'nlsparam', this function uses the default sort sequence for your session. For information on sort sequences, see Oracle8i Reference.  

Example  

SELECT NLS_INITCAP
   ('ijsland', 'NLS_SORT = XDutch') "Capitalized"
   FROM DUAL;

Capital
-------

IJsland  

Syntax  

Purpose  

Returns char, with all letters lowercase. The 'nlsparam' can have the same form and serve the same purpose as in the NLS_INITCAP function.  

Example  

SELECT NLS_LOWER
   ('CITTA''', 'NLS_SORT = XGerman') "Lowercase"
   FROM DUAL;

Lower
-----
cittá

Syntax  

Purpose  

Returns the string of bytes used to sort char. The value of 'nlsparams' can have the form

'NLS_SORT = sort'

where sort is a linguistic sort sequence or BINARY. If you omit 'nlsparams', this function uses the default sort sequence for your session. If you specify BINARY, this function returns char. For information on sort sequences, see Oracle8i National Language Support Guide.  

Example  

This function can be used to specify comparisons based on a linguistic sort sequence rather on the binary value of a string:

SELECT ename FROM emp
   WHERE NLSSORT (ename, 'NLS_SORT = German')
   > NLSSORT ('S', 'NLS_SORT = German') ORDER BY ename;
 
ENAME
----------
SCOTT
SMITH
TURNER
WARD

Syntax  

Purpose  

Returns char, with all letters uppercase. The 'nlsparam' can have the same form and serve the same purpose as in the NLS_INITCAP function.  

Example  

SELECT NLS_UPPER
   ('große', 'NLS_SORT = XGerman') "Uppercase"
     FROM DUAL;

Upper
-----
GROSS 

Syntax  

Purpose  

If expr1 is null, returns expr2; if expr1 is not null, returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, Oracle converts expr2 to the datatype of expr1 before comparing them. The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data, in which case the return value's datatype is VARCHAR2.  

Example  

SELECT ename, NVL(TO_CHAR(COMM), 'NOT APPLICABLE')
   "COMMISSION" FROM emp
   WHERE deptno = 30;
 
ENAME      COMMISSION
---------- -------------------------------------
ALLEN      300
WARD       500
MARTIN     1400
BLAKE      NOT APPLICABLE
TURNER     0
JAMES      NOT APPLICABLE

Syntax  

Purpose  

Returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer.  

Example  

SELECT POWER(3,2) "Raised" FROM DUAL;

    Raised
----------
         9

Syntax  

Purpose  

Converts raw to a character value containing its hexadecimal equivalent.  

Example  

SELECT RAWTOHEX(raw_column) "Graphics"
   FROM graphics;

Graphics
--------
7D  

Syntax  

Purpose  

In a SQL statement, REF takes as its argument a correlation variable (table alias) associated with a row of an object table or an object view. A REF value is returned for the object instance that is bound to the variable or row. For more information about REFs, see Oracle8i Concepts.  

Examples  

CREATE TYPE emp_type AS OBJECT
   (eno NUMBER, ename VARCHAR2(20), salary NUMBER);
CREATE TABLE emp_table OF emp_type 
   (primary key (eno, ename));
INSERT INTO emp_table VALUES (10, 'jack', 50000);
SELECT REF(e) FROM emp_table e;

REF(E)
-----------------------------------------------------
0000280209420D2FEABD9400C3E03400400B40DCB1420D2FEABD930
0C3E03400400B40DCB1004049EE0000

Syntax  

Purpose  

Converts argument r to a character value containing its hexadecimal equivalent.  

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 
   (dno NUMBER, mgr REF emp_type SCOPE IS emp);
INSERT INTO emp_table VALUES (10, 'jack', 50000);
INSERT INTO dept SELECT 10, REF(e) FROM emp_table e;
SELECT REFTOHEX(mgr) FROM dept;

REFTOHEX(MGR)
------------------------------------------------------
0000220208420D2FEABD9400C3E03400400B40DCB1420D2FEABD930
0C3E03400400B40DCB1

Syntax  

Purpose  

Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.  

Example  

SELECT REPLACE('JACK and JUE','J','BL') "Changes"
     FROM DUAL;

Changes
--------------
BLACK and BLUE

Syntax  

Purpose  

Returns n rounded to m places right of the decimal point. If m is omitted, n is rounded to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.  

Example 1  

SELECT ROUND(15.193,1) "Round" FROM DUAL;

     Round
----------
      15.2

Example 2  

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

     Round
----------
        20 

Syntax  

Purpose  

Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day. See "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.  

Example  

SELECT ROUND (TO_DATE ('27-OCT-92'),'YEAR')
   "New Year" FROM DUAL;
 
New Year
---------
01-JAN-93 

Syntax  

Purpose  

Converts a rowid value to VARCHAR2 datatype. The result of this conversion is always 18 characters long.  

Example  

SELECT ROWID 
     FROM offices
     WHERE 
     ROWIDTOCHAR(ROWID) LIKE '%Br1AAB%';

ROWID
------------------
AAAAZ6AABAAABr1AAB

Syntax  

Purpose  

Returns char1, right-padded to length n with char2, replicated as many times as necessary; 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 RPAD('MORRISON',12,'ab') "RPAD example"
     FROM DUAL;

RPAD example
-----------------
MORRISONabab

Syntax  

Purpose  

Returns char, with all the rightmost 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. RTRIM works similarly to LTRIM. See "LTRIM".  

Example  

SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
     FROM DUAL;
 
RTRIM e.g
-------------
BROWNINGyxX

Syntax  

Purpose  

If n<0, the function returns -1. If n=0, the function returns 0. If n>0, the function returns 1.  

Example  

SELECT SIGN(-15) "Sign" FROM DUAL;

      Sign
----------
        -1

Syntax  

Purpose  

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

Example  

SELECT SIN(30 * 3.14159265359/180)
 "Sine of 30 degrees" FROM DUAL;

Sine of 30 degrees
------------------
                .5

Syntax  

Purpose  

Returns the hyperbolic sine of n.  

Example  

SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL;

Hyperbolic sine of 1
--------------------
          1.17520119

Syntax  

Purpose  

Returns a character string containing the phonetic representation of char. This function allows you to compare words that are spelled differently, but sound alike in English.  

The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:  

• Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.

• Assign numbers to the remaining letters (after the first) as follows:

  b, f, p, v = 1
  c, g, j, k, q, s, x, z = 2
  d, t = 3
  l = 4
  m, n = 5
  r = 6
  

• If two or more letters with the same assigned number are adjacent, remove all but the first.

• Return the first four bytes padded with 0.

Example  

SELECT ename
     FROM emp
     WHERE SOUNDEX(ename)
         = SOUNDEX('SMYTHE');

ENAME
----------
SMITH

Syntax  

Purpose  

Returns square root of n. The value n cannot be negative. SQRT returns a "real" result.  

Example  

SELECT SQRT(26) "Square root" FROM DUAL;

Square root
-----------
5.09901951 

Syntax  

Purpose  

Returns standard deviation of x, a number. Oracle calculates the standard deviation as the square root of the variance defined for the VARIANCE aggregate function. See also "Aggregate Functions".  

Example  

SELECT STDDEV(sal) "Deviation"
     FROM emp;
 
 Deviation
----------
1182.50322

Syntax  

Purpose  

Returns a portion of char, beginning at character m, n characters long.

• If m is 0, it is treated as 1.

• If m is positive, Oracle counts from the beginning of char to find the first character.

• If m is negative, Oracle counts backwards from the end of char.

• If n is omitted, Oracle returns all characters to the end of char. If n is less than 1, a null is returned.

Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.  

Example 1  

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;
 
Substring
---------
CDEF

Example 2  

SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
     FROM DUAL;

Substring
---------
CDEF

Syntax  

Purpose  

The same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than in characters. For a single-byte database character set, SUBSTRB is equivalent to SUBSTR.

Floating-point numbers passed as arguments to SUBSTRB are automatically converted to integers.  

Example  

Assume a double-byte database character set:

SELECT SUBSTRB('ABCDEFG',5,4.2)
     "Substring with bytes"
     FROM DUAL;

Substring with bytes
--------------------
CD

Syntax  

Purpose  

Returns sum of values of n. See also "Aggregate Functions".  

Example  

SELECT SUM(sal) "Total"
     FROM emp;
 
     Total
----------
     29081

Syntax  

Purpose  

Returns the value of attribute_name as defined in the package currently associated with the context namespace. See "CREATE CONTEXT". The argument attribute_name can have any of the following predefined values:  

'NLS_TERRITORY'  

returns the territory  

'NLS_CURRENCY'  

returns the currency symbol  

'NLS_CALENDAR'  

returns the NLS calendar used for dates  

'NLS_DATE_FORMAT'  

returns the current date format  

'NLS_DATE_LANGUAGE'  

returns the language used for days of the week, months, and so forth, in dates  

'NLS_SORT'  

indicates whether the sort base is binary or linguistic  

'SESSION_USER'  

returns the name of the user who logged on  

'CURRENT_USER'  

returns the current session user name, which may be different from SESSION_USER from within a stored procedure (such as an invoker-rights procedure).  

'CURRENT_SCHEMA'  

returns the current schema name, which may be changed with an ALTER SESSION SET SCHEMA statement.  

'CURRENT SCHEMAID'  

returns the current schema ID  

'SESSION_USERID  

returns the logged on user ID  

'CURRENT_USERID'  

returns the current session user ID  

You can also specify SYS_CONTEXT ('USERENV','IP_ADDRESS') to obtain the IP address of the client if the client is connected to Oracle using the TCP protocol.  

Example  

The following example returns the group number specified as the value for the attribute GROUP_NO in the PL/SQL package that was associated with the context ABC when ABC was created:

SELECT SYS_CONTEXT ('abc', 'group_no') "User Group" 
   FROM DUAL;
 
User Group
----------
Sales

Syntax  

Purpose  

Generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier and a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.  

Example  

The second line of this example returns the 32-character hexadecimal representation of the 16-byte raw value of the global unique identifier.

INSERT INTO my_table VALUES ('BOB', SYS_GUID());
SELECT SYS_GUID() FROM DUAL;

Syntax  

Purpose  

Returns the current date and time. Requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.  

Example  

SELECT TO_CHAR
    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW"
     FROM DUAL;

NOW
-------------------
10-29-1993 20:27:11 

Syntax  

Purpose  

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

Example  

SELECT TAN(135 * 3.14159265359/180)
"Tangent of 135 degrees"  FROM DUAL;

Tangent of 135 degrees
----------------------
                   - 1

Syntax  

Purpose  

Returns the hyperbolic tangent of n.  

Example  

SELECT TANH(.5) "Hyperbolic tangent of .5" 
   FROM DUAL;

Hyperbolic tangent of .5
------------------------
              .462117157 

Syntax  

Purpose  

Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. For information on date formats, see "Format Models".  

The 'nlsparams' specifies the language in which month and day names and abbreviations are returned. This argument can have this form:

'NLS_DATE_LANGUAGE = language'

If you omit nlsparams, this function uses the default date language for your session.  

Example  

SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY')
    "New date format" FROM emp
    WHERE ename = 'BLAKE';
 
New date format
------------------
May       01, 1981

Syntax  

Purpose

Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. For information on number formats, see "Format Models".  

The 'nlsparams' specifies these characters that are returned by number format elements:

• decimal character

• group separator

• local currency symbol

• international currency symbol

This argument can have this form:

'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '

The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Note that within the quoted string, you must use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.  

If you omit 'nlsparams' or any one of the parameters, this function uses the default parameter values for your session.  

Example 1  

In this example, the output is blank padded to the left of the currency symbol.

SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount"
     FROM DUAL;

Amount
--------------
  $10,000.00-

Example 2

SELECT TO_CHAR(-10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars'' ') "Amount"
     FROM DUAL;

Amount
-------------------
AusDollars10.000,00-

Note: In the optional number format fmt, L designates local currency symbol and MI designates a trailing minus sign. See Table 2-7 for a complete listing of number format elements.  

Syntax  

Purpose  

Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer. For information on date formats, see "Format Models".

The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for date conversion.

Do not use the TO_DATE function with a DATE value for the char argument. The returned DATE value can have a different century value than the original char, depending on fmt or the default date format.

For information on date formats, see "Date Format Models".  

Example  

INSERT INTO bonus (bonus_date)
  SELECT TO_DATE(
    'January 15, 1989, 11:00 A.M.',
    'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')
     FROM DUAL;

Syntax  

Purpose  

Converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the SELECT list of a subquery in an INSERT statement (see "INSERT").

Before using this function, you must create a LOB column to receive the converted LONG values. To convert LONGs, the LOB column must be of type CLOB or NCLOB. To convert LONG RAWs, the LOB column must be of type BLOB.  

Example  

Given the following tables:

CREATE TABLE long_table (n NUMBER, long_col LONG);
CREATE TABLE lob_table (n NUMBER, lob_col CLOB);

use this function to convert LONG to LOB values as follows:

INSERT INTO lob_table
  SELECT n, TO_LOB(long_col) FROM long_table;

Syntax  

Purpose  

Returns char with all of its single-byte characters converted to their corresponding multibyte characters. Any single-byte characters in char that have no multibyte equivalents appear in the output string as single-byte characters. This function is useful only if your database character set contains both single-byte and multibyte characters.  

Syntax  

Purpose  

Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.  

Example 1  

UPDATE emp SET sal = sal + 
   TO_NUMBER('100.00', '9G999D99')
   WHERE ename = 'BLAKE';

The 'nlsparams' string in this function has the same purpose as it does in the TO_CHAR function for number conversions. See also "TO_CHAR (number conversion)".  

Example 2  

SELECT TO_NUMBER('-AusDollars100','L9G999D99',
   ' NLS_NUMERIC_CHARACTERS = '',.''
     NLS_CURRENCY            = ''AusDollars''
   ') "Amount"
     FROM DUAL;

    Amount
----------
      -100

Syntax  

Purpose  

Returns char with all of its multibyte character converted to their corresponding single-byte characters. Any multibyte characters in char that have no single-byte equivalents appear in the output as multibyte characters. This function is useful only if your database character set contains both single-byte and multibyte characters.  

Syntax  

Purpose  

Returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value. Oracle interprets the empty string as null, and if this function has a null argument, it returns null.  

Example 1  

The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':  

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
     FROM DUAL;
 
License
--------
9XXX999 

Example 2  

The following statement returns a license number with the characters removed and the digits remaining:  

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
"Translate example"
     FROM DUAL;
 
Translate example
-----------------
2229

Syntax  

Purpose  

Converts text into the character set specified for conversions between the database character set and the national character set.

The text argument is the expression to be converted.

Specifying the USING CHAR_CS argument converts text into the database character set. The output datatype is VARCHAR2.

Specifying the USING NCHAR_CS argument converts text into the national character set. The output datatype is NVARCHAR2.

This function is similar to the Oracle CONVERT function, but must be used instead of CONVERT if either the input or the output datatype is being used as NCHAR or NVARCHAR2.  

Example 1  

CREATE TABLE t1 (char_col  CHAR(20),
                  nchar_col nchar(20));
INSERT INTO t1 
  VALUES ('Hi', N'Bye');
SELECT * FROM t1;

CHAR_COL     NCHAR_COL
--------     ---------
Hi           Bye

Example 2  

UPDATE t1 SET
  nchar_col = TRANSLATE(char_col USING NCHAR_CS);
UPDATE t1 SET
  char_col = TRANSLATE(nchar_col USING CHAR_CS);
SELECT * FROM t1;

CHAR_COL     NCHAR_COL
--------     ---------
Hi           Hi

Example 3  

UPDATE t1 SET
  nchar_col = TRANSLATE('deo' USING NCHAR_CS);
UPDATE t1 SET
  char_col = TRANSLATE(N'deo' USING CHAR_CS);
SELECT * FROM t1;

CHAR_COL     NCHAR_COL
--------     ---------
deo          deo

Syntax
 

Purpose  

enables you to trim heading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, you must enclose it in single quotes.

• If you specify LEADING, Oracle removes any leading characters equal to trim_character.

• If you specify TRAILING, Oracle removes any trailing characters equal to trim_character.

• If you specify BOTH or none of the three, Oracle removes leading and trailing characters equal to trim_character.

• If you do not specify trim_character, the default value is a blank space.

• The function returns a value with datatype VARCHAR2. The maximum length of the value is the length of trim_source.

• If either trim_source or trim_character is a null value, then the TRIM function returns a null value.

Example  

This example trims leading and trailing zeroes from a number:

SELECT TRIM (0 FROM 0009872348900) "TRIM Example"
   FROM DUAL;

TRIM example
------------
    98723489

Syntax  

Purpose  

Returns n truncated to m decimal places. If m is omitted, n is truncated to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.  

Examples  

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

  Truncate
----------
      15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

  Truncate
----------
        10

Syntax  

Purpose  

Returns d with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, d is truncated to the nearest day. See "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.  

Example  

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92 

Syntax  

Purpose  

Returns an integer that uniquely identifies the current user.  

Syntax  

Purpose  

Returns char, with all letters uppercase. The return value has the same datatype as the argument char.  

Example  

SELECT UPPER('Large') "Uppercase"
     FROM DUAL;

Upper
-----
LARGE

Syntax  

Purpose  

Returns the current Oracle user with the datatype VARCHAR2. Oracle compares values of this function with blank-padded comparison semantics.

In a distributed SQL statement, the UID and USER functions identify the user on your local database. You cannot use these functions in the condition of a CHECK constraint.  

Example  

SELECT USER, UID FROM DUAL;
 
USER                                  UID
------------------------------ ----------
SCOTT                                  19

Syntax  

Purpose  

Returns information of VARCHAR2 datatype about the current session. This information can be useful for writing an application-specific audit trail table or for determining the language-specific characters currently used by your session. You cannot use USERENV in the condition of a CHECK constraint. The argument option can have any of these values:  

'ISDBA'

returns 'TRUE' if you currently have the ISDBA role enabled and 'FALSE' if you do not.  

'LANGUAGE'

returns the language and territory currently used by your session along with the database character set in this form:

language_territory.characterset

'TERMINAL'

returns the operating system identifier for your current session's terminal. In distributed SQL statements, this option returns the identifier for your local session. In a distributed environment, this is supported only for remote SELECTs, not for remote INSERTs, UPDATEs, or DELETEs.  

'SESSIONID'

returns your auditing session identifier. You cannot use this option in distributed SQL statements.  

'ENTRYID'

returns available auditing entry identifier. You cannot use this option in distributed SQL statements. To use this keyword in USERENV, the initialization parameter AUDIT_TRAIL must be set to TRUE.  

'LANG'

Returns the ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter.  

'INSTANCE'

Returns the instance identification number of the current instance.  

'CLIENT_INFO'

returns up to 64 bytes of user session information that can be stored by an application using the DBMS_APPLICATION_INFO package.  

CAUTION: Some commercial applications may be using this context value. Check the applicable documentation for those applications to determine what restrictions they may impose on use of this context area.  

Oracle recommends that you use the application context feature or the SYS_CONTEXT function with the USERENV option. These alternatives are more secure and flexible. For information on application context, see Oracle8i Concepts. See also "CREATE CONTEXT" and "SYS_CONTEXT".  

Example  

SELECT USERENV('LANGUAGE') "Language" FROM DUAL;

Language
-----------------------------------
AMERICAN_AMERICA.WE8DEC

Syntax  

Purpose  

In a SQL statement, VALUE takes as its argument a correlation variable (table alias) associated with a row of an object table and returns object instances stored in the object table. The type of the object instances is the same type as the object table.  

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));
INSERT INTO emp_table VALUES (10, 'jack', 50000);
SELECT VALUE(e) FROM emp_table e;

VALUE(E)(ENO, ENAME, SALARY)
----------------------------------------------------
EMP_TYPE(10, 'jack', 50000)

Syntax  

Purpose  

Returns variance of x, a number. Oracle calculates the variance of x using this formula:  

where:

x_i is one of the elements of x.

n is the number of elements in the set x. If n is 1, the variance is defined to be 0. See also "Aggregate Functions".  

Example  

SELECT VARIANCE(sal) "Variance"
     FROM emp;
 
Variance
----------
1389313.87

Syntax  

Purpose  

Returns the number of bytes in the internal representation of expr. If expr is null, this function returns null.  

Example  

SELECT ename, VSIZE (ename) "BYTES"      
  FROM emp
  WHERE deptno = 10;
 
ENAME           BYTES
---------- ----------
CLARK               5
KING                4
MILLER              6