Other Single-Row Functions

DUMP

 

Syntax  

 
DUMP(expr[,return_format[,start_position[,length]] ] )
 

Purpose  

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set. For the datatype corresponding to each code, see Table 2-1.

The argument return_format specifies the format of the return value and can have any of the values listed below.

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

 

 

8

 

returns result in octal notation.  

 

10

 

returns result in decimal notation.  

 

16

 

returns result in hexadecimal notation.  

 

17

 

returns result as single characters.  

 

The arguments start_position and length combine to determine which portion of the internal representation to return. The default is to return the entire internal representation in decimal notation.

If expr is null, this function returns 'NULL'.

 

Example 1  

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

DUMP('ABC',1016)                          
------------------------------------------ 
Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63
 

Example 2  

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

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

Example 3  

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

ASCII
----------------------------
Type=1 Len=5: 79,84
 

EMPTY_[B | C]LOB

 

Syntax  

 
EMPTY_[B|C]LOB()
 

Purpose  

Returns an empty LOB locator that can be used to initialize a LOB variable or in an INSERT or UPDATE statement to initialize a LOB column or attribute to EMPTY. EMPTY means that the LOB is initialized, but not populated with data.

You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI.

 

Examples  

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

BFILENAME

 

Syntax  

 
BFILENAME ('directory', 'filename')
 

Purpose  

Returns a BFILE locator that is associated with a physical LOB binary file on the server's file system. A directory is an alias for a full pathname on the server's file system where the files are actually located; 'filename' is the name of the file in the server's file system.

Neither 'directory' nor 'filename' need to point to an existing object on the file system at the time you specify BFILENAME. However, you must associate a BFILE value with a physical file before performing subsequent SQL, PL/SQL, DBMS_LOB package, or OCI operations. For more information, see CREATE DIRECTORY.

 

 

Note: This function does not verify that either the directory or file specified actually exists. Therefore, you can call the CREATE DIRECTORY command after BFILENAME. However, the object must exist by the time you actually use the BFILE locator (for example, as a parameter to one of the OCILob or DBMS_LOB operations such as OCILobFileOpen() or DBMS_LOB.FILEOPEN()).

For more information about LOBs, see Oracle8 Application Developer's Guide and Oracle Call Interface Programmer's Guide.

 

Example  

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

GREATEST

 

Syntax  

 
GREATEST(expr [,expr] ...)
 

Purpose  

Returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first exprs 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 value. If the value returned by this function is character data, its datatype is always VARCHAR2.

 

Example  

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

LEAST

 

Syntax  

 
LEAST(expr [,expr] ...)
 

Purpose  

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

 

Example  

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

HAROLD  

NLS_CHARSET_DECL_LEN

 

Syntax  

 
NLS_CHARSET_DECL_LEN(bytecnt, csid)
 

Purpose  

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

Example  

 
SELECT NLS_CHARSET_DECL_LEN
  (200, nls_charset_id('ja16eucfixed')) 
   FROM DUAL; 

NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED')) 
--------------------------------------------------------
                    100
 

NLS_CHARSET_ID

 

Syntax  

 
NLS_CHARSET_ID(text)
 

Purpose  

Returns the NLS character set ID number corresponding to NLS character set name, text. The text argument is a run-time VARCHAR2 value. The text value 'CHAR_CS' returns the server's database character set ID number. The text value 'NCHAR_CS' returns the server's national character set ID number.

Invalid character set names return null.

For a list of character set names, see Oracle8 Reference.

 

Example I

 

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

Example 2

 

 
SELECT NLS_CHARSET_ID('char_cs') 
  FROM DUAL; 

 NLS_CHARSET_ID('CHAR_CS')
 ------------------------- 
                         2
 

Example 3

 

 
SELECT NLS_CHARSET_ID('nchar_cs') 
  FROM DUAL;  

NLS_CHARSET_ID('NCHAR_CS') 
--------------------------
                         2
 

NLS_CHARSET_NAME

 

Syntax  

 
NLS_CHARSET_NAME(n)
 

Purpose  

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

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

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

 

Example  

 
SELECT NLS_CHARSET_NAME(2)
  FROM DUAL;

NLS_CH 
------ 
WE8DEC
 

NVL

 

Syntax  

 
NVL(expr1, expr2)
 

Purpose  

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

 

Example  

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

 

UID

 

Syntax  

 
UID
 

Purpose  

Returns an integer that uniquely identifies the current user.

 

USER

 

Syntax  

 
USER
 

Purpose  

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

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

 

Example  

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

USERENV

 

Syntax  

 
USERENV(option)
 

Purpose  

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

 

 

 
'ISDBA'
 

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

 

 
'LANGUAGE'
 

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

language_territory.characterset
 

 

 
'TERMINAL'
 

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

 

 
'SESSIONID'
 

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

 

 
'ENTRYID'
 

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

 

 
'LANG'
 

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

 

 
'INSTANCE'
 

Returns the instance identification number of the current instance.  

Example  

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

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

 

Syntax  

 
VSIZE(expr)
 

Purpose  

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

 

Example  

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

Object Reference Functions

Object reference functions manipulate REFs-references to objects of specified object types. For more information about REFs, see Oracle8 Concepts and Oracle8 Application Developer's Guide.

DEREF

 

Syntax  

 
DEREF(e)
 

Purpose  

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

 

Example  

 
CREATE TABLE tb1(c1 NUMBER, c2 REF t1);
SELECT DEREF(c2) FROM tb1;
 

REFTOHEX

 

Syntax  

 
REFTOHEX(r)
 

Purpose  

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

 

Example  

 
CREATE TABLE tb1(c1 NUMBER, c2 REF t1);
SELECT REFTOHEX(c2) FROM tb1;
 

MAKE_REF

 

Syntax  

 
MAKE_REF(table, key [,key...])
 

Purpose  

Creates a REF to a row of an object view using key as the primary key. For more information about object views, see Oracle8 Application Developer's Guide.

 

Example  

 
CREATE TYPE t1 AS OBJECT(a NUMBER, b NUMBER);

CREATE TABLE tb1 
   (c1 NUMBER, c2 NUMBER, PRIMARY KEY(c1, c2));

CREATE VIEW v1 OF t1 WITH OBJECT OID(a, b) AS
   SELECT * FROM tb1;

SELECT MAKE_REF(v1, 1, 3) FROM DUAL;
 

Group Functions

Group functions return results based on groups of rows, rather than on single rows. In this way, group functions are different from single-row functions. For a discussion of the differences between group functions and single-row functions, see "SQL Functions".

Many group functions accept these options:

 

DISTINCT  

This option causes a group function to consider only distinct values of the argument expression.  

ALL  

This option causes a group function to consider all values, including all duplicates.  

For example, the DISTINCT average of 1, 1, 1, and 3 is 2; the ALL average is 1.5. If neither option is specified, the default is ALL.

All group functions except COUNT(*) ignore nulls. You can use the NVL in the argument to a group function to substitute a value for a null.

If a query with a group function returns no rows or only rows with nulls for the argument to the group function, the group function returns null.

AVG

 

Syntax  

 
AVG([DISTINCT|ALL] n)
 

Purpose  

Returns average value of n.

 

Example  

 
SELECT AVG(sal) "Average"
   FROM emp;

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

COUNT

 

Syntax  

 
COUNT({* | [DISTINCT|ALL] expr})
 

Purpose  

Returns the number of rows in the query.

If you specify expr, this function returns rows where expr is not null. You can count either all rows, or only distinct values of expr.

If you specify the asterisk (*), this function returns all rows, including duplicates and nulls.

 

Example 1  

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

Example 2  

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

Example 3  

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

MAX

 

Syntax  

 
MAX([DISTINCT|ALL] expr)
 

Purpose  

Returns maximum value of expr.

 

Example  

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

MIN

 

Syntax  

 
MIN([DISTINCT|ALL] expr)
 

Purpose  

Returns minimum value of expr.

 

Example  

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

STDDEV

 

Syntax  

 
STDDEV([DISTINCT|ALL] x)
 

Purpose  

Returns standard deviation of x, a number. Oracle calculates the standard deviation as the square root of the variance defined for the VARIANCE group function.

 

Example  

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

SUM

 

Syntax  

 
SUM([DISTINCT|ALL] n)
 

Purpose  

Returns sum of values of n.

 

Example  

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

VARIANCE

 

Syntax  

 
VARIANCE([DISTINCT|ALL]x)
 

Purpose  

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

 

 

 

 

where:

xi is one of the elements of x.

n is the number of elements in the set x. If n is 1, the variance is defined to be 0.

 

Example  

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