Oracle8i SQL Reference
Release 3 (8.1.7)

Part Number A85397-01

Library

Product

Contents

Index

Go to previous page Go to next page

4
Functions

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

function(argument, argument, ...) 

This chapter contains these sections:

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.

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. If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.

In the syntax diagrams for SQL functions, arguments are indicated by their datatypes. 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.

See Also:

 

The general syntax is as follows:

function::=


single_row_function::=


The sections that follow list 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.

The examples provided with the function descriptions use the emp and dept tables that are part of the scott schema in your sample Oracle database. Many examples also use a sales table, which has the following contents:

REGION PRODUCT  S_DAY    S_MONTH     S_YEAR   S_AMOUNT   S_PROFIT
------ ------- ------ ---------- ---------- ----------   --------
200          1     10          6       1998      77586        586
200          1     26          8       1998      62109        509
200          1     11         11       1998      46632        432
200          1     14          4       1999      15678        278
201          1      9          6       1998      77972        587
201          1     25          8       1998      62418        510
201          1     10         11       1998      46864        433
201          1     13          4       1999      15756        279
200          2      9          6       1998      39087      293.5
200          2     25          8       1998      31310        255
200          2     10         11       1998      23533      216.5
200          2     13          4       1999       7979      139.5
201          2      9         11       1998    23649.5        217
201          2     12          4       1999     8018.5        140
200          3      9         11       1998      15834     144.67
200          3     12          4       1999    5413.33      93.33
201          3     11          4       1999       5440      93.67
200          4     11          4       1999       4131      70.25
201          4     10          4       1999    4151.25       70.5
200          5     10          4       1999       3362       56.4
201          5      5          6       1998      16068      118.2
201          5     21          8       1998    12895.6      102.8
201          5      9          4       1999     3378.4       56.6

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, 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. The number functions are:

ABS

ACOS

ADD_MONTHS

ATAN

ATAN2

BITAND

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. The character functions that return character values are:

CHR

CONCAT

INITCAP

LOWER

LPAD

LTRIM

NLS_INITCAP 

NLS_LOWER

NLSSORT

NLS_UPPER

REPLACE

RPAD

RTRIM

SOUNDEX 

SUBSTR

SUBSTRB

TRANSLATE

TRIM

UPPER 

Character Functions Returning Number Values

The character functions that return number values are:

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. The date functions are:

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. The SQL conversion functions are:

CHARTOROWID

CONVERT

HEXTORAW

NUMTODSINTERVAL

NUMTOYMINTERVAL

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

NVL2

SYS_CONTEXT 

SYS_GUID

UID

USER

USERENV

VSIZE 

Aggregate Functions

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions 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.

See Also: "GROUP BY Examples" and the HAVING clause for more information on the GROUP BY clause and HAVING clauses in queries and subqueries 

Many (but not all) aggregate functions that take a single argument accept these options:

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither option, the default is ALL.

All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the scott schema:

SELECT AVG(MAX(sal)) FROM emp GROUP BY deptno;

AVG(MAX(SAL))
-------------
   3616.66667

This calculation evaluates the inner aggregate (MAX(sal)) for each group defined by the GROUP BY clause (deptno), and aggregates the results again.

The aggregate functions are:

AVG

CORR

COUNT

COVAR_POP

COVAR_SAMP

GROUPING 

MAX

MIN

REGR_ (linear regression) functions

STDDEV

STDDEV_POP 

STDDEV_SAMP

SUM

VAR_POP

VAR_SAMP

VARIANCE 

Analytic Functions

Analytic functions compute an aggregate value based on a group of rows. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

analytic_function::=


analytic_clause::=


query_partition_clause::=


ORDER_BY_clause::=


windowing_clause::=


The keywords and parameters of this syntax are:

analytic_function

Specify the name of an analytic function (see the listings of different types of analytic functions following this table).

arguments

Analytic functions take 0 to 3 arguments.

analytic_clause

Use analytic_clause OVER clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.


Note: You cannot specify any analytic function in any part of the analytic_clause. That is, you cannot nest analytic functions. However, you can specify an analytic function in a subquery and compute another analytic function over it.  


query_partition_clause

PARTITION BY 

Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr. If you omit this clause, the function treats all rows of the query result set as a single group.

You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys. 

 

Note: If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause, then the function computations are parallelized as well.

 

value_expr 

Valid value expressions are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these. 

ORDER_BY_clause

Use the ORDER BY clause to specify how data is ordered within a partition. You can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.

Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.

Restriction: When used in an analytic function, the ORDER_BY_clause must take an expression (expr). Position (position) and column aliases (c_alias) are invalid. Otherwise this ORDER_BY_clause is the same as that used to order the overall query or subquery.


Note: Analytic functions always operate on rows in the order specified in the ORDER_BY_clause of the function. However, the ORDER_BY_clause of the function does not guarantee the order of the result. Use the ORDER_BY_clause of the query to guarantee the final result ordering. 


See Also: order_by_clause of "SELECT and Subqueries" for more information on this clause 

ASC | DESC 

Specify the ordering sequence (ascending or descending). ASC is the default. 

NULLS FIRST | NULLS LAST 

Specify whether returned rows containing null values should appear first or last in the ordering sequence.

NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order. 

windowing_clause

ROWS | RANGE 

These keywords define for each row a "window" (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window "slides" through the query result set or partition from top to bottom.

  • ROWS specifies the window in physical units (rows).

  • RANGE specifies the window as a logical offset.

You cannot specify this clause unless you have specified the ORDER_BY_clause

 

Note: The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the ORDER_BY_clause to achieve this unique ordering.

 

BETWEEN ... AND 

Use the BETWEEN ... AND clause to specify a start point and end point for the window. The first expression (before AND) defines the start point and the second expression (after AND) defines the end point.  

 

If you omit BETWEEN and specify only one end point, Oracle considers it the start point, and the end point defaults to the current row. 

UNBOUNDED PRECEDING 

Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification. 

UNBOUNDED FOLLOWING 

Specify UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification. 

CURRENT ROW 

As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the end point cannot be value_expr PRECEDING.

As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the start point cannot be value_expr FOLLOWING

value_expr PRECEDING

value_expr FOLLOWING

 

For RANGE or ROW:

  • If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING.

  • If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING.

 

 

If you are defining a logical window defined by an interval of time in numeric format, you may need to use conversion functions.

See Also: NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into interval literals 

 

If you specified ROWS:

  • value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.

  • If value_expr is part of the start point, it must evaluate to a row before the end point.

 

 

If you specified RANGE:

  • value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal.

    See Also: "Literals" for information on interval literals.

 

 

  • You can specify only one expression in the ORDER_BY_clause

  • If value_expr evaluates to a numeric value, the ORDER BY expr must be a NUMBER or DATE datatype.

  • If value_expr evaluates to an interval value, the ORDER BY expr must be a DATE datatype.

 

If you omit the windowing_clause entirely, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Analytic functions are commonly used in data warehousing environments. The analytic functions are:

AVG

CORR

COVAR_POP

COVAR_SAMP

COUNT

CUME_DIST

DENSE_RANK

LAG

FIRST_VALUE

LAST_VALUE 

LEAD

MAX

MIN

NTILE

PERCENT_RANK

RATIO_TO_REPORT

RANK

REGR_ (linear regression) functions

ROW_NUMBER 

STDDEV

STDDEV_POP

STDDEV_SAMP

SUM

VAR_POP

VAR_SAMP

VARIANCE 

See Also: Oracle8i Data Warehousing Guide for more information on these functions, and for scenarios illustrating their use 

Object Reference Functions

Object functions manipulate REFs, which are references to objects of specified object types. The object reference functions are:

DEREF

MAKE_REF 

REF

REFTOHEX 

VALUE 

See Also: Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals for more information about REFs 

ABS

Syntax


Purpose

ABS returns the absolute value of n.

Example

SELECT ABS(-15) "Absolute" FROM DUAL;

  Absolute
----------
        15

ACOS

Syntax


Purpose

ACOS 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

ADD_MONTHS

Syntax


Purpose

ADD_MONTHS 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

ASCII

Syntax


Purpose

ASCII 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, this function returns an EBCDIC value. There is no corresponding EBCDIC character function.

Example

SELECT ASCII('Q') FROM DUAL;
 
ASCII('Q')
----------
        81

ASIN

Syntax


Purpose

ASIN 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

ATAN 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

ATAN2 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


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

AVG returns average value of expr. You can use it as an aggregate or analytic function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

See Also:

 

Aggregate Example

The following example calculates the average salary of all employees in the emp table:

SELECT AVG(sal) "Average" FROM emp;

   Average
----------
2077.21429

Analytic Example

The following example calculates, for each employee in the emp table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee:

SELECT mgr, ename, hiredate, sal,
   AVG(sal) OVER (PARTITION BY mgr ORDER BY hiredate 
   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
   FROM emp;

       MGR ENAME      HIREDATE         SAL     C_MAVG
---------- ---------- --------- ---------- ----------
      7566 FORD       03-DEC-81       3000       3000
      7566 SCOTT      19-APR-87       3000       3000
      7698 ALLEN      20-FEB-81       1600       1425
      7698 WARD       22-FEB-81       1250       1450
      7698 TURNER     08-SEP-81       1500 1333.33333
      7698 MARTIN     28-SEP-81       1250 1233.33333
      7698 JAMES      03-DEC-81        950       1100
      7782 MILLER     23-JAN-82       1300       1300
      7788 ADAMS      23-MAY-87       1100       1100
      7839 JONES      02-APR-81       2975     2912.5
      7839 BLAKE      01-MAY-81       2850 2758.33333
      7839 CLARK      09-JUN-81       2450       2650
      7902 SMITH      17-DEC-80        800        800
           KING       17-NOV-81       5000       5000

BFILENAME

Syntax


Purpose

BFILENAME 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.

See Also:

 

Example

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

BITAND

Syntax


Purpose

BITAND computes an AND operation on the bits of argument1 and argument2, both of which must resolve to nonnegative integers, and returns an integer. This function is commonly used with the DECODE expression, as illustrated in the example that follows.

Example

Consider the following table named cars:

MANUFACTURER    MODEL         OPTIONS
--------------- ---------- ----------
TOYOTA          CAMRY               3
TOYOTA          COROLLA             5
NISSAN          MAXIMA              6

The following example represents each option in each car by individual bits:

SELECT manufacturer, model,
   DECODE(BITAND(options, 1), 1, 'Automatic', 'Stick-shift'),
   DECODE(BITAND(options, 2), 2, 'CD', 'Radio'),
   DECODE(BITAND(options, 4), 4, 'ABS', 'No-ABS') 
FROM cars;

MANUFACTURER    MODEL      DECODE(BITA DECOD DECODE
--------------- ---------- ----------- ----- ------
TOYOTA          CAMRY      Automatic   CD    No-ABS
TOYOTA          COROLLA    Automatic   Radio ABS
NISSAN          MAXIMA     Stick-shift CD    ABS

CEIL

Syntax


Purpose

CEIL returns smallest integer greater than or equal to n.

Example

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

   Ceiling
----------
        16

CHARTOROWID

Syntax


Purpose

CHARTOROWID 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

CHR 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.


Note:

Use of the CHR function (either with or without the optional USING NCHAR_CS clause) results in code that is not portable between ASCII- and EBCDIC-based machine architectures. 


Examples

The following example is run on an ASCII-based machine with the database character set defined as WE8ISO8859P1:

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

Dog
---
CAT

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

To produce the same results on an EBCDIC-based machine with the WE8EBCDIC1047 character set, the first example above would have to be modified as follows:

SELECT CHR(195)||CHR(193)||CHR(227) "Dog" 
   FROM DUAL; 

Dog 
--- 
CAT 

CONCAT

Syntax


Purpose

CONCAT returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||).

See Also: "Concatenation Operator" for information on the CONCAT 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

CONVERT converts a character string from one character set to another.

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:

CORR

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

CORR returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.

Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) after eliminating the pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:

COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))

The function returns a value of type NUMBER. If the function is applied to an empty set, it returns null.

See Also:

 

Aggregate Example

The following example calculates the coefficient of correlation between the salaries and commissions of the employees whose manager is 7698 from the emp table:

SELECT mgr, CORR(sal, comm) FROM EMP
   GROUP BY mgr
   HAVING mgr = 7698;

       MGR CORR(SAL,COMM)
---------- --------------
      7698     -.69920974

Analytic Example

The following example returns the cumulative coefficient of correlation of monthly sales and monthly profits from the sales table for year 1998:

SELECT s_month, CORR(SUM(s_amount), SUM(s_profit))
   OVER (ORDER BY s_month) AS CUM_CORR
   FROM sales
   WHERE s_year=1998
   GROUP BY s_month
   ORDER BY s_month;

 S_MONTH    CUM_CORR  
---------- ----------
         6           
         8          1
        11 .860554259

Correlation functions require more than one row on which to operate, so the first row in the preceding example has no value calculated for it.

COS

Syntax


Purpose

COS 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

COSH returns the hyperbolic cosine of n.

Example

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

COUNT

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

COUNT returns the number of rows in the query. You can use it as an aggregate or analytic function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

If you specify expr, COUNT returns the number of 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. COUNT never returns null.

See Also:

 

Aggregate Examples

SELECT COUNT(*) "Total" FROM emp;

Total     
----------
        14

SELECT COUNT(*) "Allstars" FROM emp
   WHERE comm > 0;

Allstars
--------
       3

SELECT COUNT(mgr) "Count" FROM emp;

Count     
----------
        13

SELECT COUNT(DISTINCT mgr) "Managers" FROM emp;

Managers   
----------
         6

Analytic Example

The following example calculates, for each employee in the emp table, the moving count of employees earning salaries in the range $50 less than through $150 greater than the employee's salary.

SELECT ename, sal,
   COUNT(*) OVER (ORDER BY sal RANGE BETWEEN 50 PRECEDING
      AND 150 FOLLOWING) AS mov_count
   FROM emp;

ENAME             SAL  MOV_COUNT
---------- ---------- ----------
SMITH             800          2
JAMES             950          2
ADAMS            1100          3
WARD             1250          3
MARTIN           1250          3
MILLER           1300          3
TURNER           1500          2
ALLEN            1600          1
CLARK            2450          1
BLAKE            2850          4
JONES            2975          3
SCOTT            3000          3
FORD             3000          3
KING             5000          1

COVAR_POP

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

COVAR_POP returns the population covariance of a set of number pairs. You can use it as an aggregate or analytic function.

Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:

(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n

where n is the number of (expr1 , expr2) pairs where neither expr1 nor expr2 is null.

The function returns a value of type NUMBER. If the function is applied to an empty set, it returns null.

See Also:

 

Aggregate Example

The following example calculates the population covariance for the amount of sales and sale profits for each year from the table sales.

SELECT s_year, 
   COVAR_POP(s_amount, s_profit) AS COVAR_POP,
   COVAR_SAMP(s_amount, s_profit) AS COVAR_SAMP
   FROM sales GROUP BY s_year;

S_YEAR      COVAR_POP COVAR_SAMP
---------- ---------- ----------
      1998 3747965.53 4060295.99
      1999 360536.162 400595.736

Analytic Example

The following example calculates cumulative sample covariance of the amount of sales and sale profits in 1998.

SELECT s_year, s_month, s_day, 
   COVAR_POP(s_amount, s_profit) 
      OVER (ORDER BY s_month, s_day) AS CUM_COVP,
   COVAR_SAMP(s_amount, s_profit)
      OVER (ORDER BY s_month, s_day) AS CUM_COVS 
   FROM sales
   WHERE s_year=1998
   ORDER BY s_year, s_month, s_day;

S_YEAR     S_MONTH    S_DAY      CUM_COVP   CUM_COVS  
---------- ---------- ---------- ---------- ----------
      1998          6          5          0           
      1998          6          9  4940952.6  7411428.9
      1998          6          9  4940952.6  7411428.9
      1998          6         10 5281752.33 7042336.44
      1998          8         21 6092799.46 7615999.32
      1998          8         25 4938283.61 5761330.88
      1998          8         25 4938283.61 5761330.88
      1998          8         26 4612074.09 5270941.82
      1998         11          9 4556799.53 5063110.59
      1998         11          9 4556799.53 5063110.59
      1998         11         10 4014833.65 4379818.52
      1998         11         10 4014833.65 4379818.52
      1998         11         11 3747965.53 4060295.99

COVAR_SAMP

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

COVAR_SAMP returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function.

Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:

(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1)

where n is the number of (expr1 , expr2) pairs where neither expr1 nor expr2 is null.

The function returns a value of type NUMBER. If the function is applied to an empty set, it returns null.

See Also:

 

Aggregate Example

The following example calculates the population covariance for the amount of sales and sale profits for each year from the table sales.

SELECT s_year, 
   COVAR_POP(s_amount, s_profit) AS COVAR_POP,
   COVAR_SAMP(s_amount, s_profit) AS COVAR_SAMP
   FROM sales GROUP BY s_year;

S_YEAR      COVAR_POP COVAR_SAMP
---------- ---------- ----------
      1998 3747965.53 4060295.99
      1999 360536.162 400595.736

Analytic Example

The following example calculates cumulative sample covariance of the amount of sales and sale profits in 1998.

SELECT s_year, s_month, s_day, 
   COVAR_POP(s_amount, s_profit) 
      OVER (ORDER BY s_month, s_day) AS CUM_COVP,
   COVAR_SAMP(s_amount, s_profit)
      OVER (ORDER BY s_month, s_day) AS CUM_COVS 
   FROM sales
   WHERE s_year=1998
   ORDER BY s_year, s_month, s_day;

S_YEAR     S_MONTH    S_DAY      CUM_COVP   CUM_COVS  
---------- ---------- ---------- ---------- ----------
      1998          6          5          0           
      1998          6          9  4940952.6  7411428.9
      1998          6          9  4940952.6  7411428.9
      1998          6         10 5281752.33 7042336.44
      1998          8         21 6092799.46 7615999.32
      1998          8         25 4938283.61 5761330.88
      1998          8         25 4938283.61 5761330.88
      1998          8         26 4612074.09 5270941.82
      1998         11          9 4556799.53 5063110.59
      1998         11          9 4556799.53 5063110.59
      1998         11         10 4014833.65 4379818.52
      1998         11         10 4014833.65 4379818.52
      1998         11         11 3747965.53 4060295.99

CUME_DIST

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

CUME_DIST (cumulative distribution) is an analytic function. It computes the relative position of a specified value in a group of values. For a row R, assuming ascending ordering, the CUME_DIST of R is the number of rows with values lower than or equal to the value of R, divided by the number of rows being evaluated (the entire query result set or a partition). The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.

Example

The following example calculates the salary percentile for each employee within each job category excluding job categories PRESIDENT and MANAGER. For example, 50% of clerks have salaries less than or equal to James.

SELECT job, ename, sal, CUME_DIST() 
   OVER (PARTITION BY job ORDER BY sal) AS cume_dist
   FROM emp
   WHERE job NOT IN ('MANAGER', 'PRESIDENT');

JOB       ENAME             SAL  CUME_DIST
--------- ---------- ---------- ----------
ANALYST   SCOTT            3000          1
ANALYST   FORD             3000          1
CLERK     SMITH             800        .25
CLERK     JAMES             950         .5
CLERK     ADAMS            1100        .75
CLERK     MILLER           1300          1
SALESMAN  WARD             1250         .5
SALESMAN  MARTIN           1250         .5
SALESMAN  TURNER           1500        .75
SALESMAN  ALLEN            1600          1

DENSE_RANK

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

DENSE_RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties.

Example

The following statement selects the department name, employee name, and salary of all employees who work in the RESEARCH or SALES department, and then computes a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank. Compare this example with the example for RANK.

SELECT dname, ename, sal, DENSE_RANK() 
   OVER (PARTITION BY dname ORDER BY sal) as drank
   FROM emp, dept
   WHERE emp.deptno = dept.deptno 
   AND dname IN ('SALES', 'RESEARCH');

DNAME          ENAME             SAL      DRANK
-------------- ---------- ---------- ----------
RESEARCH       SMITH             800          1
RESEARCH       ADAMS            1100          2
RESEARCH       JONES            2975          3
RESEARCH       FORD             3000          4
RESEARCH       SCOTT            3000          4
SALES          JAMES             950          1
SALES          MARTIN           1250          2
SALES          WARD             1250          2
SALES          TURNER           1500          3
SALES          ALLEN            1600          4
SALES          BLAKE            2850          5

DEREF

Syntax


Purpose

DEREF returns the object reference of argument expr, where expr must return a REF to an object. If you do not use this function in a query, Oracle returns the object ID of the REF instead, as shown in the example that follows.

See Also: MAKE_REF 

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 mgr FROM dept_table;

MGR
--------------------------------------------------------------------
00002202085928CB5CDF7B61CAE03400400B40DCB15928C35861E761BCE03400400B40DCB1

SELECT DEREF(mgr) from dept_table;

DEREF(MGR)(ENO, ENAME, SALARY)
--------------------------------------------------------
EMP_TYPE(10, 'jack', 50000)

DUMP

Syntax


Purpose

DUMP 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 following values:

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

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 a null.

Examples

SELECT DUMP('abc', 1016)
   FROM DUAL;

DUMP('ABC',1016)                          
------------------------------------------ 

Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63



SELECT DUMP(ename, 8, 3, 2) "OCTAL"
   FROM emp
   WHERE ename = 'SCOTT';

OCTAL
----------------------------
Type=1 Len=5: 117,124 


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

EMPTY_BLOB and EMPTY_CLOB 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.

Example

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

EXP

Syntax


Purpose

EXP 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 

FIRST_VALUE

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

FIRST_VALUE is an analytic function. It returns the first value in an ordered set of values.

You cannot use FIRST_VALUE or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.

See Also: "Expressions" for information on valid forms of expr 

Examples

The following example selects, for each employee in Department 20, the name of the employee with the highest salary.

SELECT deptno, ename, sal, FIRST_VALUE(ename) 
   OVER (ORDER BY sal DESC ROWS UNBOUNDED PRECEDING) AS rich_emp
   FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno);

    DEPTNO ENAME             SAL RICH_EMP
---------- ---------- ---------- ----------
        20 SCOTT            3000 SCOTT
        20 FORD             3000 SCOTT
        20 JONES            2975 SCOTT
        20 ADAMS            1100 SCOTT
        20 SMITH             800 SCOTT

The example illustrates the nondeterministic nature of the FIRST_VALUE function. Scott and Ford have the same salary, so are in adjacent rows. Scott appears first because the rows returned by the subquery are ordered by empno. However, if the rows returned by the subquery are ordered by empno in descending order, as in the next example, the function returns a different value:

SELECT deptno, ename, sal, FIRST_VALUE(ename) 
   OVER (ORDER BY sal DESC ROWS UNBOUNDED PRECEDING) AS fv
   FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno desc);

    DEPTNO ENAME             SAL FV
---------- ---------- ---------- ----------
        20 FORD             3000 FORD
        20 SCOTT            3000 FORD
        20 JONES            2975 FORD
        20 ADAMS            1100 FORD
        20 SMITH             800 FORD

The following example shows how to make the FIRST_VALUE function deterministic by ordering on a unique key.

SELECT deptno, ename, sal, hiredate, FIRST_VALUE(ename) 
   OVER (ORDER BY sal DESC, hiredate ROWS UNBOUNDED PRECEDING) AS fv
   FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno desc);

DEPTNO     ENAME      SAL        HIREDATE  FV         
---------- ---------- ---------- --------- ----------
        20 FORD             3000 03-DEC-81 FORD      
        20 SCOTT            3000 19-APR-87 FORD      
        20 JONES            2975 02-APR-81 FORD      
        20 ADAMS            1100 23-MAY-87 FORD      
        20 SMITH             800 17-DEC-80 FORD      

FLOOR

Syntax


Purpose

FLOOR returns largest integer equal to or less than n.

Example

SELECT FLOOR(15.7) "Floor" FROM DUAL;

     Floor
----------
        15

GREATEST

Syntax


Purpose

GREATEST 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.

See Also: "Datatype Comparison Rules" 

Example

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

GROUPING

Syntax


Purpose

The GROUPING 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 nulls representing the set of all values. You can use the GROUPING function to distinguish a null that represents the set of all values in a superaggregate row from an actual null.

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 Oracle NUMBER.

See Also: group_by_clause of the SELECT statement for a discussion of these terms 

Example

In the following example, if the GROUPING function returns 1 (indicating a superaggregate row rather than a data row from the table), the string "All Jobs" appears instead of the null that would otherwise appear:

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

HEXTORAW converts char containing hexadecimal digits to a raw value.

Example

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

See Also: "RAW and LONG RAW Datatypes" and RAWTOHEX 

INITCAP

Syntax


Purpose

INITCAP 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

INSTR searches string for substring.

The function returns an integer indicating the position of the character in string that is the first character of this occurrence. The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times after the position character of string) the return value is 0.

Examples

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

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

INSTRB

Syntax


Purpose

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

See Also: 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

LAG

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

LAG is an analytic function. It provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.

If you do not specify offset, its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, its default value is null.

You cannot use LAG or any other analytic function for value_expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.

See Also: "Expressions" for information on valid forms of expr 

Example

The following example provides, for each salesperson in the emp table, the salary of the employee hired just before:

SELECT ename, hiredate, sal, 
   LAG(sal, 1, 0) OVER (ORDER BY hiredate) as prev_sal
   FROM emp
   WHERE job = 'SALESMAN';

ENAME      HIREDATE         SAL   PREV_SAL
---------- --------- ---------- ----------
ALLEN      20-FEB-81       1600          0
WARD       22-FEB-81       1250       1600
TURNER     08-SEP-81       1500       1250
MARTIN     28-SEP-81       1250       1500

LAST_DAY

Syntax


Purpose

LAST_DAY 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.

Examples

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

The following example adds 5 months to the hiredate of each employee to give an evaluation date:

SELECT ename, hiredate, TO_CHAR(
   ADD_MONTHS(LAST_DAY(hiredate), 5)) "Eval Date"
   FROM emp;

ENAME      HIREDATE  Eval Date
---------- --------- ---------
SMITH      17-DEC-80 31-MAY-81
ALLEN      20-FEB-81 31-JUL-81
WARD       22-FEB-81 31-JUL-81
JONES      02-APR-81 30-SEP-81
MARTIN     28-SEP-81 28-FEB-82
BLAKE      01-MAY-81 31-OCT-81
CLARK      09-JUN-81 30-NOV-81
SCOTT      19-APR-87 30-SEP-87
KING       17-NOV-81 30-APR-82
TURNER     08-SEP-81 28-FEB-82
ADAMS      23-MAY-87 31-OCT-87
JAMES      03-DEC-81 31-MAY-82
FORD       03-DEC-81 31-MAY-82
MILLER     23-JAN-82 30-JUN-82

LAST_VALUE

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

LAST_VALUE is an analytic function. It returns the last value in an ordered set of values.

You cannot use LAST_VALUE or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.

See Also: "Expressions" for information on valid forms of expr 

Examples

The following example returns the hiredate of the employee earning the highest salary.

SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER
   (ORDER BY sal 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate);

ENAME             SAL HIREDATE  LV
---------- ---------- --------- ---------
SMITH             800 17-DEC-80 19-APR-87
ADAMS            1100 23-MAY-87 19-APR-87
JONES            2975 02-APR-81 19-APR-87
FORD             3000 03-DEC-81 19-APR-87
SCOTT            3000 19-APR-87 19-APR-87

This example illustrates the nondeterministic nature of the LAST_VALUE function. Ford and Scott have the same salary, so they are in adjacent rows. Ford appears first because the rows in the subquery are ordered by hiredate. However, if the rows are ordered by hiredate in descending order, as in the next example, the function returns a different value:

SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER
   (ORDER BY sal 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate DESC);

ENAME             SAL HIREDATE  LV
---------- ---------- --------- ---------
SMITH             800 17-DEC-80 03-DEC-81
ADAMS            1100 23-MAY-87 03-DEC-81
JONES            2975 02-APR-81 03-DEC-81
SCOTT            3000 19-APR-87 03-DEC-81
FORD             3000 03-DEC-81 03-DEC-81

The following two examples show how to make the LAST_VALUE function deterministic by ordering on a unique key. By ordering within the function by both salary and hiredate, you can ensure the same result regardless of the ordering in the subquery.

SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER
   (ORDER BY sal, hiredate 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate);

ENAME             SAL HIREDATE  LV
---------- ---------- --------- ---------
SMITH             800 17-DEC-80 19-APR-87
ADAMS            1100 23-MAY-87 19-APR-87
JONES            2975 02-APR-81 19-APR-87
FORD             3000 03-DEC-81 19-APR-87
SCOTT            3000 19-APR-87 19-APR-87

SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER
   (ORDER BY sal, hiredate 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate DESC);

ENAME             SAL HIREDATE  LV
---------- ---------- --------- ---------
SMITH             800 17-DEC-80 19-APR-87
ADAMS            1100 23-MAY-87 19-APR-87
JONES            2975 02-APR-81 19-APR-87
FORD             3000 03-DEC-81 19-APR-87
SCOTT            3000 19-APR-87 19-APR-87

LEAD

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

LEAD is an analytic function. It provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LEAD provides access to a row at a given physical offset beyond that position.

If you do not specify offset, its default is 1. The optional default value is returned if the offset goes beyond the scope of the table. If you do not specify default, its default value is null.

You cannot use LEAD or any other analytic function for value_expr. That is, you can use other built-in function expressions for value_expr, but you cannot nest analytic functions.

See Also: "Expressions" for information on valid forms of expr 

Example

The following example provides, for each employee in the emp table, the hiredate of the employee hired just after:

SELECT ename, hiredate, 
   LEAD(hiredate, 1) OVER (ORDER BY hiredate) AS "NextHired" 
   FROM emp;

ENAME      HIREDATE  NextHired
---------- --------- ---------
SMITH      17-DEC-80 20-FEB-81
ALLEN      20-FEB-81 22-FEB-81
WARD       22-FEB-81 02-APR-81
JONES      02-APR-81 01-MAY-81
BLAKE      01-MAY-81 09-JUN-81
CLARK      09-JUN-81 08-SEP-81
TURNER     08-SEP-81 28-SEP-81
MARTIN     28-SEP-81 17-NOV-81
KING       17-NOV-81 03-DEC-81
JAMES      03-DEC-81 03-DEC-81
FORD       03-DEC-81 23-JAN-82
MILLER     23-JAN-82 19-APR-87
SCOTT      19-APR-87 23-MAY-87
ADAMS      23-MAY-87    

LEAST

Syntax


Purpose

LEAST 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

LENGTH 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

LENGTHB 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

LN 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

LOG 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

LOWER 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

LPAD 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

LTRIM 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

MAKE_REF creates a REF to a row of an object view or a row in an object table whose object identifier is primary key based.

See Also:

 

Example

CREATE TABLE employee (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')
------------------------------------------------------
000067030A0063420D06E06F3C00C1E03400400B40DCB10000001C26010001000200
2900000000000F0600810100140100002A0007000A8401FE0000001F02C102146A61
636B2020202020202020202020202020202000000000000000000000000000000000
00000000

MAX

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

MAX returns maximum value of expr. You can use it as an aggregate or analytic function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

See Also:

 

Aggregate Example

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

Analytic Example

The following example calculates, for each employee, the highest salary of the employees reporting to the same manager as the employee.

SELECT mgr, ename, sal, 
   MAX(sal) OVER (PARTITION BY mgr) AS mgr_max
   FROM emp;

      MGR  ENAME      SAL        MGR_MAX
---------- ---------- ---------- ----------
      7566 SCOTT            3000       3000
      7566 FORD             3000       3000
      7698 ALLEN            1600       1600
      7698 WARD             1250       1600
      7698 JAMES             950       1600
      7698 TURNER           1500       1600
      7698 MARTIN           1250       1600
      7782 MILLER           1300       1300
      7788 ADAMS            1100       1100
      7839 JONES            2975       2975
      7839 CLARK            2450       2975
      7839 BLAKE            2850       2975
      7902 SMITH             800        800
           KING             5000       5000

If you enclose this query in the parent query with a predicate, you can determine the employee who makes the highest salary in each department:

SELECT mgr, ename, sal
   FROM (SELECT mgr, ename, sal, 
      MAX(sal) OVER (PARTITION BY mgr) AS rmax_sal
      FROM emp)
      WHERE sal = rmax_sal;

       MGR ENAME             SAL
---------- ---------- ----------
      7566 SCOTT            3000
      7566 FORD             3000
      7698 ALLEN            1600
      7782 MILLER           1300
      7788 ADAMS            1100
      7839 JONES            2975
      7902 SMITH             800
           KING             5000

MIN

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

MIN returns minimum value of expr. You can use it as an aggregate or analytic function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

See Also:

 

Aggregate Example

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

Analytic Example

The following example determines, for each employee, the employees who were hired on or before the same date as the employee. It then determines the subset of employees reporting to the same manager as the employee, and returns the lowest salary in that subset.

SELECT mgr, ename, hiredate, sal,
   MIN(sal) OVER(PARTITION BY mgr ORDER BY hiredate
   RANGE UNBOUNDED PRECEDING) as p_cmin
   FROM emp;

       MGR ENAME      HIREDATE         SAL     P_CMIN
---------- ---------- --------- ---------- ----------
      7566 FORD       03-DEC-81       3000       3000
      7566 SCOTT      19-APR-87       3000       3000
      7698 ALLEN      20-FEB-81       1600       1600
      7698 WARD       22-FEB-81       1250       1250
      7698 TURNER     08-SEP-81       1500       1250
      7698 MARTIN     28-SEP-81       1250       1250
      7698 JAMES      03-DEC-81        950        950
      7782 MILLER     23-JAN-82       1300       1300
      7788 ADAMS      23-MAY-87       1100       1100
      7839 JONES      02-APR-81       2975       2975
      7839 BLAKE      01-MAY-81       2850       2850
      7839 CLARK      09-JUN-81       2450       2450
      7902 SMITH      17-DEC-80        800        800
           KING       17-NOV-81       5000       5000

MOD

Syntax


Purpose

MOD 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  

See Also: FLOOR 

MONTHS_BETWEEN

Syntax


Purpose

MONTHS_BETWEEN 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

NEW_TIME returns the date and time in time zone z2 when date and time in time zone z1 are d. Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.

The arguments z1 and z2 can be any of these text strings:

Example

The following example returns an Atlantic Standard time, given the Pacific Standard time equivalent:

ALTER SESSION SET NLS_DATE_FORMAT =
   'DD-MON-YYYY HH24:MI:SS';

SELECT NEW_TIME(TO_DATE(
   '11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'),
   'AST', 'PST') "New Date and Time" FROM DUAL;

New Date and Time
--------------------
09-NOV-1999 21:23:45

NEXT_DAY

Syntax


Purpose

NEXT_DAY 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 the date language of your session, 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

NLS_CHARSET_DECL_LEN 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

NLS_CHARSET_ID 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 database character set ID number of the server. The text value 'NCHAR_CS' returns the national character set ID number of the server.

Invalid character set names return null.

Example

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

See Also: Oracle8i National Language Support Guide for a list of character set names 

NLS_CHARSET_NAME

Syntax


Purpose

NLS_CHARSET_NAME 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.

Example

SELECT NLS_CHARSET_NAME(2)
  FROM DUAL;

NLS_CH 
------ 
WE8DEC

See Also: Oracle8i National Language Support Guide for a list of character set IDs 

NLS_INITCAP

Syntax


Purpose

NLS_INITCAP 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. 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.

Example

The following examples show how the linguistic sort sequence results in a different return value from the function:

SELECT NLS_INITCAP
   ('ijsland') "InitCap" FROM DUAL;

InitCap
-------
Ijsland

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

InitCap
-------
IJsland

See Also: Oracle8i National Language Support Guide for information on sort sequences 

NLS_LOWER

Syntax


Purpose

NLS_LOWER 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á

NLSSORT

Syntax


Purpose

NLSSORT 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.

Example

This function can be used to specify comparisons based on a linguistic sort sequence rather than 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

See Also: Oracle8i National Language Support Guide for information on sort sequences 

NLS_UPPER

Syntax


Purpose

NLS_UPPER 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 

See Also: NLS_INITCAP 

NTILE

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

NTILE is an analytic function. It divides an ordered dataset into a number of buckets indicated by expr and assigns the appropriate bucket number to each row. The buckets are numbered 1 through expr, and expr must resolve to a positive constant for each partition.

The number of rows in the buckets can differ by at most 1. The remainder values (the remainder of number of rows divided by buckets) are distributed 1 per bucket, starting with bucket 1.

If expr is greater than the number of rows, a number of buckets equal to the number of rows will be filled, and the remaining buckets will be empty.

You cannot use NTILE or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.

See Also: "Expressions" for information on valid forms of expr 

Example

The following example divides the values in the SAL column into 4 buckets. The SAL column has 14 values, so the two extra values (the remainder of 14 / 4) are allocated to buckets 1 and 2, which therefore have one more value than buckets 3 or 4.

SELECT ename, sal, NTILE(4) OVER (ORDER BY sal DESC) AS quartile
   FROM emp;

ENAME             SAL   QUARTILE
---------- ---------- ----------
KING             5000          1
SCOTT            3000          1
FORD             3000          1
JONES            2975          1
BLAKE            2850          2
CLARK            2450          2
ALLEN            1600          2
TURNER           1500          2
MILLER           1300          3
WARD             1250          3
MARTIN           1250          3
ADAMS            1100          4
JAMES             950          4
SMITH             800          4

NUMTODSINTERVAL


Note: This function is restricted to use with analytic functions. It accepts only numbers as arguments, and returns interval literals. See "Analytic Functions" and "Interval"


Syntax


Purpose

NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. n can be a number or an expression resolving to a number. The value for char_expr specifies the unit of n and must resolve to one of the following string values:

char_expr is case insensitive. Leading and trailing values within the parentheses are ignored. By default, precision of the return is 9.

Example

The following example calculates for each employee, the number of employees hired, by the same manager, within the last 100 days from his/her hiredate:


SELECT mgr, ename, hiredate, 
   COUNT(*) OVER (PARTITION BY mgr ORDER BY hiredate 
   RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count 
   FROM emp;

       MGR ENAME      HIREDATE     T_COUNT
---------- ---------- --------- ----------
      7566 FORD       03-DEC-81          1
      7566 SCOTT      19-APR-87          1
      7698 ALLEN      20-FEB-81          1
      7698 WARD       22-FEB-81          2
      7698 TURNER     08-SEP-81          1
      7698 MARTIN     28-SEP-81          2
      7698 JAMES      03-DEC-81          3
      7782 MILLER     23-JAN-82          1
      7788 ADAMS      23-MAY-87          1
      7839 JONES      02-APR-81          1
      7839 BLAKE      01-MAY-81          2
      7839 CLARK      09-JUN-81          3
      7902 SMITH      17-DEC-80          1
           KING       17-NOV-81          1

NUMTOYMINTERVAL


Note: This function is restricted to use with analytic functions. It accepts only numbers as arguments, and returns interval literals. See "Analytic Functions" and "Interval"


Syntax


Purpose

NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. n can be a number or an expression resolving to a number. The value for char_expr specifies the unit of n, and must resolve to one of the following string values:

char_expr is case insensitive. Leading and trailing values within the parentheses are ignored. By default, precision of the return is 9.

Example

The following example calculates, for each employee, the total salary of employees hired in the past one year from his/her hiredate.

SELECT ename, hiredate, sal, SUM(sal) OVER (ORDER BY hiredate 
   RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal 
   FROM emp;

ENAME      HIREDATE         SAL      T_SAL
---------- --------- ---------- ----------
SMITH      17-DEC-80        800        800
ALLEN      20-FEB-81       1600       2400
WARD       22-FEB-81       1250       3650
JONES      02-APR-81       2975       6625
BLAKE      01-MAY-81       2850       9475
CLARK      09-JUN-81       2450      11925
TURNER     08-SEP-81       1500      13425
MARTIN     28-SEP-81       1250      14675
KING       17-NOV-81       5000      19675
JAMES      03-DEC-81        950      23625
FORD       03-DEC-81       3000      23625
MILLER     23-JAN-82       1300      24125
SCOTT      19-APR-87       3000       3000
ADAMS      23-MAY-87       1100       4100

NVL

Syntax


Purpose

If expr1 is null, NVL returns expr2; if expr1 is not null, NVL 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

NVL2

Syntax


Purpose

If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG.

If the datatypes of expr2 and expr3 are different, Oracle converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary.

The datatype of the return value is always the same as the datatype of expr2, unless expr2 is character data, in which case the return value's datatype is VARCHAR2.

Example

The following example shows whether the income of each employee in department 30 is made up of salary plus commission, or just salary, depending on whether the comm column of emp is null or not.

SELECT ename, NVL2(TO_CHAR(COMM), 'SAL & COMM', 'SAL') income
   FROM emp WHERE deptno = 30;

ENAME      INCOME    
---------- ----------
ALLEN      SAL & COMM
WARD       SAL & COMM
MARTIN     SAL & COMM
BLAKE      SAL       
TURNER     SAL & COMM
JAMES      SAL 

PERCENT_RANK

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

PERCENT_RANK is an analytic function, and is similar to the CUME_DIST (cumulative distribution) function. For a row R, PERCENT_RANK calculates the rank of R minus 1, divided by 1 less than the number of rows being evaluated (the entire query result set or a partition). The range of values returned by PERCENT_RANK is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK of 0.

Example

The following example calculates, for each employee, the percent rank of the employee's salary within the department:

SELECT deptno, ename, sal, 
   PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS pr
   FROM emp;

    DEPTNO ENAME             SAL         PR
---------- ---------- ---------- ----------
        10 KING             5000          0
        10 CLARK            2450         .5
        10 MILLER           1300          1
        20 SCOTT            3000          0
        20 FORD             3000          0
        20 JONES            2975         .5
        20 ADAMS            1100        .75
        20 SMITH             800          1
        30 BLAKE            2850          0
        30 ALLEN            1600         .2
        30 TURNER           1500         .4
        30 WARD             1250         .6
        30 MARTIN           1250         .6
        30 JAMES             950          1

POWER

Syntax


Purpose

POWER 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

RANK

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

Example

The following statement ranks the employees within each department based on their salary and commission. Identical salary values receive the same rank and cause nonconsecutive ranks. Compare this example with the example for DENSE_RANK.

SELECT deptno, ename, sal, comm, 
   RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) as rk
   FROM emp;

    DEPTNO ENAME             SAL       COMM         RK
---------- ---------- ---------- ---------- ----------
        10 KING             5000                     1
        10 CLARK            2450                     2
        10 MILLER           1300                     3
        20 SCOTT            3000                     1
        20 FORD             3000                     1
        20 JONES            2975                     3
        20 ADAMS            1100                     4
        20 SMITH             800                     5
        30 BLAKE            2850                     1
        30 ALLEN            1600        300          2
        30 TURNER           1500          0          3
        30 WARD             1250        500          4
        30 MARTIN           1250       1400          5
        30 JAMES             950                     6

RATIO_TO_REPORT

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, the ratio-to-report value also evaluates to null.

The set of values is determined by the query_partition_clause. If you omit that clause, the ratio-to-report is computed over all rows returned by the query.

You cannot use RATIO_TO_REPORT or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.

See Also: "Expressions" for information on valid forms of expr 

Example

The following example calculates the ratio-to-report of each salesperson's salary to the total of all salespeople's salaries:

SELECT ename, sal, RATIO_TO_REPORT(sal) OVER () AS rr
   FROM emp
   WHERE job = 'SALESMAN';

ENAME             SAL         RR
---------- ---------- ----------
ALLEN            1600 .285714286
WARD             1250 .223214286
MARTIN           1250 .223214286
TURNER           1500 .267857143

RAWTOHEX

Syntax


Purpose

RAWTOHEX converts raw to a character value containing its hexadecimal equivalent.

Example

SELECT RAWTOHEX(raw_column) "Graphics"
   FROM graphics;

Graphics
--------
7D  

See Also: "RAW and LONG RAW Datatypes" and HEXTORAW 

REF

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.

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 REF(e) FROM emp_table e;

REF(E)
-----------------------------------------------------
0000280209420D2FEABD9400C3E03400400B40DCB1420D2FEABD9300C3E03400400B
40DCB1004049EE0000

See Also: Oracle8i Concepts 

REFTOHEX

Syntax


Purpose

REFTOHEX converts argument expr to a character value containing its hexadecimal equivalent. expr must return a REF.

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)
------------------------------------------------------
0000220208420D2FEABD9400C3E03400400B40DCB1420D2FEABD9300C3E03400400B
40DCB1

REGR_ (linear regression) functions

The linear regression functions are:

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

The linear regression functions fit an ordinary-least-squares regression line to a set of number pairs. You can use them as both aggregate and analytic functions.

See Also:

 

Oracle applies the function to the set of (expr1, expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Oracle computes all the regression functions simultaneously during a single pass through the data.

expr1 is interpreted as a value of the dependent variable (a "y value"), and expr2 is interpreted as a value of the independent variable (an "x value"). Both expressions must be numbers.

All of the remaining regression functions return a number and can be null:

REGR_SXY, REGR_SXX, REGR_SYY are auxiliary functions that are used to compute various diagnostic statistics.

The following examples are based on the sales table, described in COVAR_POP.

REGR_SLOPE and REGR_INTERCEPT Examples

The following example determines the slope and intercept of the regression line for the amount of sales and sale profits for each year.

SELECT s_year, 
   REGR_SLOPE(s_amount, s_profit),
   REGR_INTERCEPT(s_amount, s_profit) 
FROM sales GROUP BY s_year;

 S_YEAR     REGR_SLOPE REGR_INTER
---------- ---------- ----------
      1998 128.401558 -2277.5684
      1999  55.618655 226.855296

The following example determines the cumulative slope and cumulative intercept of the regression line for the amount of sales and sale profits for each day in 1998:

SELECT s_year, s_month, s_day, 
   REGR_SLOPE(s_amount, s_profit) 
      OVER (ORDER BY s_month, s_day) AS CUM_SLOPE,
   REGR_INTERCEPT(s_amount, s_profit) 
      OVER (ORDER BY s_month, s_day) AS CUM_ICPT 
   FROM sales
   WHERE s_year=1998
   ORDER BY s_month, s_day;

S_YEAR     S_MONTH    S_DAY      CUM_SLOPE  CUM_ICPT  
---------- ---------- ---------- ---------- ----------
      1998          6          5                      
      1998          6          9 132.093066 401.884833
      1998          6          9 132.093066 401.884833
      1998          6         10 131.829612  450.65349
      1998          8         21 132.963737  -153.5413
      1998          8         25 130.681718 -451.47349
      1998          8         25 130.681718 -451.47349
      1998          8         26  128.76502 -236.50096
      1998         11          9 131.499934 -1806.7535
      1998         11          9 131.499934 -1806.7535
      1998         11         10 130.190972 -2323.3056
      1998         11         10 130.190972 -2323.3056
      1998         11         11 128.401558 -2277.5684

REGR_COUNT Examples

The following example returns the number of sales transactions in the sales table that resulted in a profit. (None of the rows for containing a sales amount have a null in the s_profit column, so the function returns the total number of rows in the sales table.)

SELECT REGR_COUNT(s_amount, s_profit) FROM sales;

REGR_COUNT
----------
        23

The following example computes, for each day, the cumulative number of transactions within each month for the year 1998:

SELECT s_month, s_day, 
   REGR_COUNT(s_amount,s_profit) 
      OVER (PARTITION BY s_month ORDER BY s_day) 
FROM SALES 
WHERE S_YEAR=1998 
ORDER BY S_MONTH; 
 
S_MONTH    S_DAY      REGR_COUNT 
---------- ---------- ---------- 
         6          5          1 
         6          9          3 
         6          9          3 
         6         10          4 
         8         21          1 
         8         25          3 
         8         25          3 
         8         26          4 
        11          9          2 
        11          9          2 
        11         10          4 
        11         10          4 
        11         11          5 

REGR_R2 Examples

The following example computes the coefficient of determination of the regression line for amount of sales and sale profits:

SELECT REGR_R2(s_amount, s_profit) FROM sales;

REGR_R2(S_
----------
.942435028

The following example computes the cumulative coefficient of determination of the regression line for monthly sales and monthly profits for each month in 1998:

SELECT s_month,
       REGR_R2(SUM(s_amount), SUM(s_profit))
              OVER (ORDER BY s_month) 
FROM SALES
WHERE s_year=1998
GROUP BY s_month
ORDER BY s_month;

S_MONTH    REGR_R2(SU
---------- ----------
         6           
         8          1
        11 .740553632

REGR_AVGY and REGR_AVGX Examples

The following example calculates the regression average for the amount of sales and sale profits for each year:

SELECT s_year,
   REGR_AVGY(s_amount, s_profit),
   REGR_AVGX(s_amount, s_profit)
FROM sales GROUP BY s_year;

 S_YEAR     REGR_AVGY( REGR_AVGX(
---------- ---------- ----------
      1998 41227.5462 338.820769
      1999   7330.748    127.725

The following example calculates the cumulative averages for the amount of sales and sale profits in 1998:

SELECT s_year, s_month, s_day, 
   REGR_AVGY(s_amount, s_profit) 
      OVER (ORDER BY s_month, s_day) AS CUM_AMOUNT,
   REGR_AVGX(s_amount, s_profit)
      OVER (ORDER BY s_month, s_day) AS CUM_PROFIT
   FROM sales
   WHERE s_year=1998
   ORDER BY s_month, s_day;

S_YEAR     S_MONTH    S_DAY      CUM_AMOUNT CUM_PROFIT
---------- ---------- ---------- ---------- ----------
      1998          6          5      16068      118.2
      1998          6          9 44375.6667      332.9
      1998          6          9 44375.6667      332.9
      1998          6         10   52678.25    396.175
      1998          8         21   44721.72      337.5
      1998          8         25    45333.8 350.357143
      1998          8         25    45333.8 350.357143
      1998          8         26    47430.7   370.1875
      1998         11          9   41892.91    332.317
      1998         11          9   41892.91    332.317
      1998         11         10  40777.175 331.055833
      1998         11         10  40777.175 331.055833
      1998         11         11 41227.5462 338.820769

REGR_SXY, REGR_SXX, and REGR_SYY Examples

The following example computes the REGR_SXY, REGR_SXX, and REGR_SYY values for the regression analysis of amount of sales and sale profits for each year:

SELECT s_year,
   REGR_SXY(s_amount, s_profit),
       REGR_SYY(s_amount, s_profit),
       REGR_SXX(s_amount, s_profit)
FROM sales GROUP BY s_year;

S_YEAR     REGR_SXY(S REGR_SYY(S REGR_SXX(S
---------- ---------- ---------- ----------
      1998 48723551.8 6423698688 379462.311
      1999 3605361.62  200525751 64822.8841

The following example computes the cumulative REGR_SXY, REGR_SXX, and REGR_SYY statistics for amount of sales and sale profits for each month-day value in 1998:

SELECT s_year, s_month, s_day,
       REGR_SXY(s_amount, s_profit)
              OVER (ORDER BY s_month, s_day) AS CUM_SXY,
       REGR_SYY(s_amount, s_profit)
              OVER (ORDER BY s_month, s_day) AS CUM_SXY,
       REGR_SXX(s_amount, s_profit)
              OVER (ORDER BY s_month, s_day) AS CUM_SXX
   FROM sales
   WHERE s_year=1998
   ORDER BY s_month, s_day;

S_YEAR     S_MONTH    S_DAY      CUM_SXY    CUM_SXY    CUM_SXX   
---------- ---------- ---------- ---------- ---------- ----------
      1998          6          5          0          0          0
      1998          6          9 14822857.8 1958007601  112215.26
      1998          6          9 14822857.8 1958007601  112215.26
      1998          6         10 21127009.3 2785202281 160259.968
      1998          8         21 30463997.3 4051329674  229115.08
      1998          8         25 34567985.3 4541739739 264520.437
      1998          8         25 34567985.3 4541739739 264520.437
      1998          8         26 36896592.7 4787971157 286542.049
      1998         11          9 45567995.3 6045196901 346524.854
      1998         11          9 45567995.3 6045196901 346524.854
      1998         11         10 48178003.8 6392056557 370056.411
      1998         11         10 48178003.8 6392056557 370056.411
      1998         11         11 48723551.8 6423698688 379462.311

REPLACE

Syntax


Purpose

REPLACE 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

ROUND (number function)

Syntax


Purpose

ROUND 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.

Examples

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

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


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

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

ROUND (date function)

Syntax


Purpose

ROUND returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day.

See Also: "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 

ROW_NUMBER

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the ORDER_BY_clause, beginning with 1.

You cannot use ROW_NUMBER or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.

See Also: "Expressions" for information on valid forms of expr 

Example

For each department in the emp table, the following example assigns numbers to each row in order of employee's hire date:

SELECT deptno, ename, hiredate, ROW_NUMBER() 
   OVER (PARTITION BY deptno ORDER BY hiredate) AS emp_id
   FROM emp;

    DEPTNO ENAME      HIREDATE      EMP_ID
---------- ---------- --------- ----------
        10 CLARK      09-JUN-81          1
        10 KING       17-NOV-81          2
        10 MILLER     23-JAN-82          3
        20 SMITH      17-DEC-80          1
        20 JONES      02-APR-81          2
        20 FORD       03-DEC-81          3
        20 SCOTT      19-APR-87          4
        20 ADAMS      23-MAY-87          5
        30 ALLEN      20-FEB-81          1
        30 WARD       22-FEB-81          2
        30 BLAKE      01-MAY-81          3
        30 TURNER     08-SEP-81          4
        30 MARTIN     28-SEP-81          5
        30 JAMES      03-DEC-81          6

ROW_NUMBER is a nondeterministic function. However, hiredate is a unique key, so the results of this application of the function are deterministic.

See Also: FIRST_VALUE and LAST_VALUE for examples of nondeterministic behavior 

ROWIDTOCHAR

Syntax


Purpose

ROWIDTOCHAR 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

RPAD

Syntax


Purpose

RPAD 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

RTRIM

Syntax


Purpose

RTRIM 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.

Example

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

See Also: LTRIM 

SIGN

Syntax


Purpose

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

Example

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

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

SIN

Syntax


Purpose

SIN 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

SINH

Syntax


Purpose

SINH returns the hyperbolic sine of n.

Example

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

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

SOUNDEX

Syntax


Purpose

SOUNDEX 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:

Example

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

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

SQRT

Syntax


Purpose

SQRT 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 

STDDEV

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

STDDEV returns sample standard deviation of expr, a set of numbers. You can use it as both an aggregate and analytic function. It differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns a null.

Oracle calculates the standard deviation as the square root of the variance defined for the VARIANCE aggregate function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

See Also:

 

Aggregate Example

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

Analytic Example

The query in the following example returns the cumulative standard deviation of salary values in Department 30 ordered by hiredate:

SELECT ENAME, SAL, STDDEV(SAL) OVER (ORDER BY HIREDATE) 
   FROM EMP  
   WHERE DEPTNO=30; 
 
ENAME      SAL        STDDEV(SAL 
---------- ---------- ---------- 
ALLEN            1600          0 
WARD             1250 247.487373 
BLAKE            2850 841.130192 
TURNER           1500 715.308791 
MARTIN           1250 666.520817 
JAMES             950 668.331255 

STDDEV_POP

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

STDDEV_POP computes the population standard deviation and returns the square root of the population variance. You can use it as both an aggregate and analytic function.

The expr is a number expression, and the function returns a value of type NUMBER. This function is same as the square root of the VAR_POP function. When VAR_POP returns null, this function returns null.

See Also:

 

Aggregate Example

The following example returns the population and sample standard deviations of profit from sales in the SALES table.

SELECT STDDEV_POP(s_profit), STDDEV_SAMP(s_profit) FROM sales;

STDDEV_POP STDDEV_SAM
---------- ----------
173.975774 177.885831

Analytic Example

The following example returns the population standard deviations of salaries in the emp table by department:

SELECT deptno, ename, sal, 
   STDDEV_POP(sal) OVER (PARTITION BY deptno) AS pop_std
   FROM emp;

    DEPTNO ENAME             SAL    POP_STD
---------- ---------- ---------- ----------
        10 CLARK            2450 1546.14215
        10 KING             5000 1546.14215
        10 MILLER           1300 1546.14215
        20 SMITH             800 1004.73877
        20 ADAMS            1100 1004.73877
        20 FORD             3000 1004.73877
        20 SCOTT            3000 1004.73877
        20 JONES            2975 1004.73877
        30 ALLEN            1600 610.100174
        30 BLAKE            2850 610.100174
        30 MARTIN           1250 610.100174
        30 JAMES             950 610.100174
        30 TURNER           1500 610.100174
        30 WARD             1250 610.100174

STDDEV_SAMP

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

STDDEV_SAMP computes the cumulative sample standard deviation and returns the square root of the sample variance. You can use it as both an aggregate and analytic function.

The expr is a number expression, and the function returns a value of type NUMBER. This function is same as the square root of the VAR_SAMP function. When VAR_SAMP returns null, this function returns null.

See Also:

 

Aggregate Example

The following example returns the population and sample standard deviations of profit from sales in the SALES table.

SELECT STDDEV_POP(s_profit), STDDEV_SAMP(s_profit) FROM sales;

STDDEV_POP STDDEV_SAM
---------- ----------
173.975774 177.885831

Analytic Example

The following example returns the sample standard deviation of salaries in the EMP table by department:

SELECT deptno, ename, hiredate, sal, 
   STDDEV_SAMP(sal) OVER (PARTITION BY deptno ORDER BY hiredate 
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev 
   FROM emp;

    DEPTNO ENAME      HIREDATE         SAL   CUM_SDEV
---------- ---------- --------- ---------- ----------
        10 CLARK      09-JUN-81       2450
        10 KING       17-NOV-81       5000 1803.12229
        10 MILLER     23-JAN-82       1300 1893.62967
        20 SMITH      17-DEC-80        800
        20 JONES      02-APR-81       2975 1537.95725
        20 FORD       03-DEC-81       3000 1263.01557
        20 SCOTT      19-APR-87       3000  1095.8967
        20 ADAMS      23-MAY-87       1100  1123.3321
        30 ALLEN      20-FEB-81       1600
        30 WARD       22-FEB-81       1250 247.487373
        30 BLAKE      01-MAY-81       2850 841.130192
        30 TURNER     08-SEP-81       1500 715.308791
        30 MARTIN     28-SEP-81       1250 666.520817
        30 JAMES      03-DEC-81        950 668.331255

SUBSTR

Syntax


Purpose

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

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

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

Example 2

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

Substring
---------
CDEF

SUBSTRB

Syntax


Purpose

SUBSTRB is 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

SUM

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

SUM returns sum of values of expr. You can use it as an aggregate or analytic function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

See Also:

 

Aggregate Example

The following example calculates the sum of all salaries in the emp table:

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

Analytic Example

The following example calculates, for each manager, a cumulative total of salaries of employees who answer to that manager that are equal to or less than the current salary:

SELECT mgr, ename, sal,
   SUM(sal) OVER (PARTITION BY mgr ORDER BY sal 
   RANGE UNBOUNDED PRECEDING) l_csum
   FROM emp;

       MGR ENAME             SAL     L_CSUM
---------- ---------- ---------- ----------
      7566 SCOTT            3000       6000
      7566 FORD             3000       6000
      7698 JAMES             950        950
      7698 WARD             1250       3450
      7698 MARTIN           1250       3450
      7698 TURNER           1500       4950
      7698 ALLEN            1600       6550
      7782 MILLER           1300       1300
      7788 ADAMS            1100       1100
      7839 CLARK            2450       2450
      7839 BLAKE            2850       5300
      7839 JONES            2975       8275
      7902 SMITH             800        800
           KING             5000       5000

SYS_CONTEXT

Syntax


Purpose

SYS_CONTEXT returns the value of attribute associated with the context namespace. You can use this function in both SQL and PL/SQL statements. The context namespace must already have been created, and the associated attribute and its value must also have been set using the DBMS_SESSION.set_context procedure. The namespace must be a valid SQL identifier. The attribute name can be any string, and it is not case sensitive, but it cannot exceed 30 bytes in length.

The datatype of the return value is VARCHAR2. The default maximum size of the return value is 256 bytes. You can override this default by specifying the optional length parameter. The valid range of values is 1 to 4000 bytes. (If you specify an invalid value, Oracle ignores it and uses the default.)

Oracle8i provides a built-in namespace called USERENV, which describes the current session. The predefined attributes of namespace USERENV are listed Table 4-1, along with the lengths of their return strings.

See Also:

 

Examples

The following statement returns the name of the user who logged onto the database:

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') 
   FROM DUAL;

SYS_CONTEXT ('USERENV', 'SESSION_USER')
------------------------------------------------------
SCOTT

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

SELECT SYS_CONTEXT ('hr_apps', 'group_no') "User Group" 
   FROM DUAL;
 
User Group
----------
Sales
Table 4-1 Predefined Attributes of Namespace USERENV
Attribute  Return Value  Return Length (bytes) 

AUTHENTICATION_DATA 

Data being used to authenticate the login user. For X.503 certificate authenticated sessions, this field returns the context of the certificate in HEX2 format. 

256 

 

Note: You can change the return value of the AUTHENTICATION_DATA attribute using the length parameter of the syntax. Values of up to 4000 are accepted. This is the only attribute of USERENV for which Oracle implements such a change. 

 

AUTHENTICATION_TYPE 

How the user was authenticated:

  • DATABASE: username/password authentication

  • OS: operating system external user authentication

  • NETWORK: network protocol or ANO authentication

  • PROXY: OCI proxy connection authentication

 

30 

BG_JOB_ID 

Job ID of the current session if it was established by an Oracle background process. Null if the session was not established by a background process. 

30 

CLIENT_INFO 

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

64 

CURRENT_SCHEMA 

Name of the default schema being used in the current schema. This value can be changed during the session with an ALTER SESSION SET CURRENT_SCHEMA statement. 

30 

CURRENT_SCHEMAID 

Identifier of the default schema being used in the current session. 

30 

CURRENT_USER 

The name of the user whose privilege the current session is under. 

30 

CURRENT_USERID 

User ID of the user whose privilege the current session is under 

30 

DB_DOMAIN 

Domain of the database as specified in the DB_DOMAIN initialization parameter. 

256 

DB_NAME 

Name of the database as specified in the DB_NAME initialization parameter 

30 

ENTRYID 

The 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.  

30 

EXTERNAL_NAME 

External name of the database user. For SSL authenticated sessions using v.503 certificates, this field returns the distinguished name (DN) stored in the user certificate. 

256 

FG_JOB_ID 

Job ID of the current session if it was established by a client foreground process. Null if the session was not established by a foreground process. 

30 

HOST 

Name of the host machine from which the client has connected. 

54 

INSTANCE 

The instance identification number of the current instance.  

30 

IP_ADDRESS 

IP address of the machine from which the client is connected. 

30 

ISDBA 

TRUE if you currently have the DBA role enabled and FALSE if you do not.  

30 

LANG 

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

62 

LANGUAGE 

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

language_territory.characterset 

52 

NETWORK_PROTOCOL 

Network protocol being used for communication, as specified in the 'PROTOCOL=protocol' portion of the connect string. 

256 

NLS_CALENDAR 

The current calendar of the current session. 

62 

NLS_CURRENCY 

The currency of the current session. 

62 

NLS_DATE_FORMAT 

The date format for the session. 

62 

NLS_DATE_LANGUAGE 

The language used for expressing dates. 

62 

NLS_SORT 

BINARY or the linguistic sort basis. 

62 

NLS_TERRITORY 

The territory of the current session. 

62 

OS_USER 

Operating system username of the client process that initiated the database session 

30 

PROXY_USER 

Name of the database user who opened the current session on behalf of SESSION_USER

30 

PROXY_USERID 

Identifier of the database user who opened the current session on behalf of SESSION_USER

30 

SESSION_USER 

Database user name by which the current user is authenticated. This value remains the same throughout the duration of the session. 

30 

SESSION_USERID 

Identifier of the database user name by which the current user is authenticated. 

30 

SESSIONID 

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

30 

TERMINAL 

The operating system identifier for the client of the current session. In distributed SQL statements, this option returns the identifier for your local session. In a distributed environment, this is supported only for remote SELECT statements, not for remote INSERT, UPDATE, or DELETE operations. (The return length of this parameter may vary by operating system.) 

10 

SYS_GUID

Syntax


Purpose

SYS_GUID 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 following examples return the 32-character hexadecimal representation of the 16-byte raw value of the global unique identifier:

CREATE TABLE mytable (col1 VARCHAR2(10), col2 RAW(32));
INSERT INTO mytable VALUES ('BOB', SYS_GUID());
SELECT * FROM mytable;

COL1       COL2
---------- --------------------------------------------------
BOB        5901B85D996C570CE03400400B40DCB1

SELECT SYS_GUID() FROM DUAL;

SYS_GUID()
--------------------------------
5901B85D996D570CE03400400B40DCB1

SYSDATE

Syntax


Purpose

SYSDATE 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-1999 20:27:11 

TAN

Syntax


Purpose

TAN 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

TANH

Syntax


Purpose

TANH returns the hyperbolic tangent of n.

Example

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

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

TO_CHAR (date conversion)

Syntax


Purpose

TO_CHAR 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.

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.

See Also: "Format Models" for information on date formats 

Example

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

TO_CHAR (number conversion)

Syntax


Purpose

TO_CHAR 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.

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

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.

See Also: "Format Models" for information on number formats 

Examples

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-

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. 


TO_DATE

Syntax


Purpose

TO_DATE 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.

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 first 2 digits of the returned DATE value can differ from the original char, depending on fmt or the default date format.

See Also: "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;

TO_LOB

Syntax


Purpose

TO_LOB 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.

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

See Also: INSERT for information on the subquery of an INSERT statement 

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;

TO_MULTI_BYTE

Syntax


Purpose

TO_MULTI_BYTE 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.

TO_NUMBER

Syntax


Purpose

TO_NUMBER 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.

Examples

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

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

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

TO_SINGLE_BYTE

Syntax


Purpose

TO_SINGLE_BYTE returns char with all of its multibyte characters 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.

TRANSLATE

Syntax


Purpose

TRANSLATE 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.

Examples

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 

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

TRANSLATE ... USING

Syntax


Purpose

TRANSLATE ... USING 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.

Examples

The examples below use the following table and table values:

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

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

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

TRIM

Syntax


Purpose

TRIM enables you to trim leading 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.

This example trims leading and trailing zeroes from a number:

Example

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

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

TRUNC (number function)

Syntax


Purpose

TRUNC 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.

Example

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

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

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

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

TRUNC (date function)

Syntax


Purpose

TRUNC 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 Also: "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 

UID

Syntax


Purpose

UID returns an integer that uniquely identifies the session user (the user who logged on).

Example

SELECT UID FROM DUAL;

       UID
----------
        19

UPPER

Syntax


Purpose

UPPER 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

USER

Syntax


Purpose

USER returns the name of the session user (the user who logged on) 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

USERENV

Syntax


Purpose

USERENV 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. Table 4-2 describes the values for the option argument.

Table 4-2  USERENV Options
Option  Return Value 

'CLIENT_INFO

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.

 

'ENTRYID

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.  

'INSTANCE

INSTANCE returns the instance identification number of the current instance.  

'ISDBA

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

'LANG

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

'LANGUAGE

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

language_territory.characterset
 

'SESSIONID

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

'TERMINAL

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 SELECT statements, not for remote INSERT, UPDATE, or DELETE operations. 

Example

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

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

VALUE

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)

VAR_POP

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

VAR_POP returns the population variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.

The expr is a number expression, and the function returns a value of type NUMBER. If the function is applied to an empty set, it returns null. The function makes the following calculation:

(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

See Also:

 

Aggregate Example

The following example returns the population variance of the salaries in the EMP table:

SELECT VAR_POP(sal) FROM emp;

VAR_POP(SAL)
------------
  1298434.31

Analytic Example

The following example calculates the cumulative population and sample variances of the monthly sales in 1998:

SELECT s_month, VAR_POP(SUM(s_amount)) OVER (ORDER BY s_month),
   VAR_SAMP(SUM(s_amount)) OVER (ORDER BY s_month) 
   FROM sales
   WHERE s_year =1998
   GROUP BY s_month;

S_MONTH    VAR_POP(SU VAR_SAMP(S
---------- ---------- ----------
         6          0           
         8  440588496  881176992
        11  538819892  808229838

VAR_SAMP

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

VAR_SAMP returns the sample variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.

The expr is a number expression, and the function returns a value of type NUMBER. If the function is applied to an empty set, it returns null. The function makes the following calculation:

(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)

This function is similar to VARIANCE, except that given an input set of one element, VARIANCE returns 0 and VAR_SAMP returns null.

See Also:

 

Aggregate Example

The following example returns the sample variance of the salaries in the emp table.

SELECT VAR_SAMP(sal) FROM emp;

VAR_SAMP(SAL)
-------------
   1398313.87

Analytic Example

The following example calculates the cumulative population and sample variances of the monthly sales in 1998:

SELECT s_month, VAR_POP(SUM(s_amount)) OVER (ORDER BY s_month),
   VAR_SAMP(SUM(s_amount)) OVER (ORDER BY s_month) 
   FROM sales
   WHERE s_year =1998
   GROUP BY s_month;

S_MONTH    VAR_POP(SU VAR_SAMP(S
---------- ---------- ----------
         6          0           
         8  440588496  881176992
        11  538819892  808229838

VARIANCE

Syntax


See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 

Purpose

VARIANCE returns variance of expr. You can use it as an aggregate or analytic function.

Oracle calculates the variance of expr as follows:

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

See Also:

 

Aggregate Example

The following example calculates the variance of all salaries in the emp table:

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

Analytic Example

The query returns the cumulative variance of salary values in Department 30 ordered by hiredate.

SELECT ename, sal, VARIANCE(sal) OVER (ORDER BY hiredate) 
   FROM emp 
   WHERE deptno=30; 

ENAME      SAL        VARIANCE(S 
---------- ---------- ---------- 
ALLEN            1600          0 
WARD             1250      61250 
BLAKE            2850     707500 
TURNER           1500 511666.667 
MARTIN           1250     444250 
JAMES             950 446666.667 

VSIZE

Syntax


Purpose

VSIZE 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

ROUND and TRUNC Date Functions

Table 4-3 lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

Table 4-3  Date Format Models for the ROUND and TRUNC Date Functions
Format Model  Rounding or Truncating Unit 
CC
SCC
 

One greater than the first two digits of a four-digit year. 

SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y
 

Year (rounds up on July 1) 

IYYY
IY
IY
I
 

ISO Year 

Q
 

Quarter (rounds up on the sixteenth day of the second month of the quarter) 

MONTH
MON
MM
RM
 

Month (rounds up on the sixteenth day) 

WW
 

Same day of the week as the first day of the year. 

IW
 

Same day of the week as the first day of the ISO year. 

W
 

Same day of the week as the first day of the month. 

DDD
DD
J
 

Day 

DAY
DY
D
 

Starting day of the week 

HH
HH12
HH24
 

Hour 

MI
 

Minute 

The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY.

See Also: Oracle8i Reference and Oracle8i National Language Support Guide for information on this parameter 

User-Defined Functions

You can write user-defined functions in PL/SQL or Java to provide functionality that is not available in SQL or SQL functions. User functions can appear in a SQL statement anywhere SQL functions can appear, that is, wherever an expression can occur.

For example, user functions can be used in the following:

Prerequisites

User functions must be created as top-level functions or declared with a package specification before they can be named within a SQL statement.

To use a user function in a SQL expression, you must own or have EXECUTE privilege on the user function. To query a view defined with a user function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view.

See Also:

 

Name Precedence

Within a SQL statement, the names of database columns take precedence over the names of functions with no parameters. For example, if user scott creates the following two objects in his own schema:

CREATE TABLE emp(new_sal NUMBER, ...);
CREATE FUNCTION new_sal RETURN NUMBER IS BEGIN ... END;

then in the following two statements, the reference to NEW_SAL refers to the column emp.new_sal:

SELECT new_sal FROM emp;
SELECT emp.new_sal FROM emp;

To access the function new_sal, you would enter:

SELECT scott.new_sal FROM emp;

Here are some sample calls to user functions that are allowed in SQL expressions:

circle_area (radius)
payroll.tax_rate (empno)
scott.payroll.tax_rate (dependent, empno)@ny

Example

To call the tax_rate user function from schema scott, execute it against the ss_no and sal columns in tax_table, and place the results in the variable income_tax, specify the following:

SELECT scott.tax_rate (ss_no, sal)
    INTO income_tax
    FROM tax_table
    WHERE ss_no = tax_id;

Naming Conventions

If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether PAYROLL in the reference PAYROLL.TAX_RATE is a schema or package name, Oracle proceeds as follows:

  1. Check for the PAYROLL package in the current schema.

  2. If a PAYROLL package is not found, look for a schema name PAYROLL that contains a top-level TAX_RATE function. If no such function is found, return an error.

  3. If the PAYROLL package is found in the current schema, look for a TAX_RATE function in the PAYROLL package. If no such function is found, return an error.

You can also refer to a stored top-level function using any synonym that you have defined for it.


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index