Oracle Miscellaneous Functions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
This page is a collection of functions not covered on other pages in the Library.

For a close to complete list of Oracle built-in functions and demos in the library, both stand-alone and in built-in packages: [ Click Here ].
 
DATAOBJ_TO_PARTITION
According to the docs is "useful only to Data Cartridge Developers performing data maintenance activities on the base table of a domain index: Well maybe.

Introduced version 6.0
dataobj_to_partition(<table_name, <partition_id>) RETURN NUMBER;

Values must be passed to this procedure by the appropriate ODCIIndex method and the NUMBER returned is the partition ID of the corresponding system partitioned table.
conn uwclass/uwclass@pdborcl

CREATE TABLE syst_part (
tx_id   NUMBER(5),
begdate DATE)
PARTITION BY SYSTEM (
PARTITION p1,
PARTITION p2,
PARTITION p3);

-- this will not work
INSERT INTO syst_part VALUES (1, SYSDATE-10);

-- this will
INSERT INTO syst_part PARTITION (p1) VALUES (1, SYSDATE-10);
INSERT INTO syst_part PARTITION (p2) VALUES (2, SYSDATE);
INSERT INTO syst_part PARTITION (p3) VALUES (3, SYSDATE+10);
COMMIT;

-- and so will this
SELECT dataobj#
FROM sys.tabpart$
WHERE obj# in (select object_id from dba_objects where object_name = 'SYST_PART');

INSERT INTO syst_part
PARTITION (dataobj_to_partition(SYST_PART, 92213))
VALUES
(1, SYSDATE+2);
 
LNNVL
Evaluates a condition when one or both operands of the condition may be NULL LNNVL(<condition>) RETURN BOOLEAN;
conn hr/hr@pdborcl

SELECT commission_pct, COUNT(*) FROM employees GROUP BY commission_pct;

SELECT COUNT(*) FROM employees WHERE commission_pct >= .2;

-- NULLs plus those that are less than or equal to .2
SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);

SELECT commission_pct, COUNT(*)
FROM employees
WHERE commission_pct >= .2
GROUP BY commission_pct

SELECT commission_pct, COUNT(*)
FROM employees
WHERE LNNVL(commission_pct >= .2)
GROUP BY commission_pct;
 
MATCH_NUMBER
returns the sequential number of a row pattern match within the row pattern partition. Refer to the CLASSIFIER function above. MATCH_NUMBER()
TBD
 
NULLIF
Compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.

Overload 1
NULLIF(v1 IN VARCHAR2, v2 IN VARCHAR2) RETURN VARCHAR2;
conn hr/hr@pdborcl

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "OLD JOB ID"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;
Overload 2 NULLIF(v1 IN BOOLEAN, b2 IN BOOLEAN) RETURN VARCHAR2;
TBD
Overload 3 NULLIF(a1 IN "<OPAQUE_1>", a2 IN "<OPAQUE_1>") RETURN VARCHAR2;
TBD
Overload 4 NULLIF(a1 IN "<ADT_1>", a2 IN "<ADT_1>") RETURN VARCHAR2;
TBD
 
NVL
Returns a value if the BOOLEAN expression IS NULL

Overload 1
NVL(b1 IN BOOLEAN, b2 IN BOOLEAN) RETURN BOOLEAN;
set serveroutput on

DECLARE
 a BOOLEAN;
 b BOOLEAN := TRUE;
BEGIN
  IF NVL(a, TRUE) THEN
    dbms_output.put_line('1');
  END IF;

  IF NVL(b, TRUE) THEN
    dbms_output.put_line('2');
  END IF;
END;
/
Returns a value if the VARCHAR2 expression IS NULL

Overload 2
NVL(
s1 IN VARCHAR2 CHARACTER SET ANY_CS,        -- expression
s2 IN VARCHAR2 CHARACTER SET s1%CHARSET)    -- return value if null
RETURN VARCHAR2 CHARACTER SET s1%CHARSET;
set serveroutput on

DECLARE
 i VARCHAR2(10);
BEGIN
  SELECT NVL(i, '93')
  INTO i
  FROM dual;

  dbms_output.put_line('i1: ' || i);

  SELECT NVL(i, '39')
  INTO i
  FROM dual;

  dbms_output.put_line('i2: ' || i);
END;
/
Returns a value if the NUMERIC expression IS NULL

Overload 3
NVL(n1 IN NUMBER, n2 IN NUMBER) RETURN NUMBER;
set serveroutput on

DECLARE
 n NUMBER;
BEGIN
  SELECT NVL(n, 42)
  INTO n
  FROM dual;

  dbms_output.put_line('n1: ' || n);

  SELECT NVL(n, 99)
  INTO n
  FROM dual;

  dbms_output.put_line('n2: ' || n);
END;
/
Returns a value if the DATE expression IS NULL

Overload 4
NVL(d1 IN DATE, d2 IN DATE) RETURN DATE;
set serveroutput on

DECLARE
 i DATE;
BEGIN
  SELECT NVL(i, SYSDATE)
  INTO i
  FROM dual;

  dbms_output.put_line('i1: ' || i);

  SELECT NVL(i, SYSDATE-180)
  INTO i
  FROM dual;

  dbms_output.put_line('i2: ' || i);
END;
/
MSLABEL
Overload 5
NVL(label1 MSLABEL, label2 IN MSLABEL) RETURN MSLABEL;
TBD
ADT
Overload 6
NVL(b1 IN "<ADT_1>", IN b2 IN "<ADT_1>") RETURN "<ADT_1>";
TBD
REF ADT
Overload 7
NVL(b1 IN REF "<ADT_1>", b2 IN REF "<ADT_1>") RETURN REF "<ADT_1>" ;
TBD
Collection
Overload 8
NVL(b1 IN "<COLLECTION_1>", b2 IN "<COLLECTION_1>")
RETURN "<COLLECTION_1>";
TBD
Returns a value if the REF CURSOR expression IS NULL

Overload 9
NVL(b1 IN "<REF_CURSOR_1>", b2 IN "<REF_CURSOR_1>")
RETURN "<REF_CURSOR_1>";
TBD
TIME

Overload 10
NVL(b1 IN TIME_UNCONSTRAINED, b2 IN TIME_UNCONSTRAINED)
RETURN TIME_UNCONSTRAINED;
TBD
TIME_TZ

Overload 11
NVL(b1 IN TIME_TZ_UNCONSTRAINED, b2 IN TIME_TZ_UNCONSTRAINED)
RETURN TIME_TZ_UNCONSTRAINED;
TBD
TIMESTAMP

Overload 12
NVL(b1 IN TIMESTAMP_UNCONSTRAINED, b2 IN TIMESTAMP_UNCONSTRAINED)
RETURN TIMESTAMP_UNCONSTRAINED;
TBD
TIMESTAMP_TZ

Overload 13
NVL(b1 IN TIMESTAMP_TZ_UNCONSTRAINED, b2 IN TIMESTAMP_TZ_UNCONSTRAINED)
RETURN TIMESTAMP_TZ_UNCONSTRAINED;
TBD
TIMESTAMP_LTZ

Overload 14
NVL(b1 IN TIMESTAMP_LTZ_UNCONSTRAINED, b2 IN TIMESTAMP_LTZ_UNCONSTRAINED)
RETURN TIMESTAMP_LTZ_UNCONSTRAINED;
TBD
YMINTERVAL

Overload 15
NVL(b1 IN YMINTERVAL_UNCONSTRAINED, b2 IN YMINTERVAL_UNCONSTRAINED)
RETURN YMINTERVAL_UNCONSTRAINED;
TBD
DSINTERVAL

Overload 16
NVL(b1 IN DSINTERVAL_UNCONSTRAINED, b2 IN DSINTERVAL_UNCONSTRAINED)
RETURN DSINTERVAL_UNCONSTRAINED;
TBD
Returns a value if the CLOB expression IS NULL

Overload 17
NVL(s1 IN CLOB CHARACTER SET ANY_CS, s2 IN CHARACTER SET s1%CHARSET)
RETURN CHARACTER SET s1%CHARSET;
TBD
Returns a value if the BINARY FLOAT expression IS NULL
Overload 18
NVL(f1 IN BINARY_FLOAT, f2 IN BINARY_FLOAT) RETURN BINARY_FLOAT;
TBD
Returns a value if the BINARY DOUBLE expression IS NULL
Overload 19
NVL(d1 IN BINARY_DOUBLE, d2 IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
TBD
Returns a value if the PLS_INTEGER or BINARY_INTEGER expression IS NULL
Overload 20
NVL(i1 IN PLS_INTEGER, i2 IN PLS_INTEGER) RETURN PLS_INTEGER;
TBD
 
NVL2
Returns First Value if NOT NULL, Second Value if NULL

Thanks Cary Hogan and Kaifer Bohus for the corrections
NVL2(<expression>, <return_if_not_null>, <return_if_null>)
conn uwclass/uwclass@pdborcl

CREATE TABLE test (
category VARCHAR2(20),
outval   NUMBER(3),
inval    NUMBER(3));

INSERT INTO test VALUES ('Groceries', 10, NULL);
INSERT INTO test VALUES ('Payroll', NULL, 100);
INSERT INTO test VALUES ('Groceries', 20, NULL);
INSERT INTO test VALUES ('Payroll', NULL, 200);
INSERT INTO test VALUES ('Groceries', 30, NULL);

SELECT * FROM test;

SELECT category, SUM(NVL2(outval, -outval, inval)) NET
FROM test
GROUP BY category;

Note: If used in PL/SQL must be used in the form of SELECT INTO thus you can not use this syntax:

set serveroutput on

DECLARE
 x NUMBER(5);
BEGIN
  x := NVL2(10, 10, 20);
  dbms_output.put_line(TO_CHAR(x));
END;
/

but you can write:


DECLARE
 x NUMBER(5);
BEGIN
  SELECT NVL2(10, 10, 20)
  INTO x
  FROM dual;

  dbms_output.put_line(TO_CHAR(x));
END;
/
 
ORA_INVOKING_USER (new 12.1)
Returns the name of the database user who invoked the current statement or view ORA_INVOKING_USER RETURN VARCHAR2;
SELECT ora_invoking_user
FROM dual;
 
ORA_INVOKING_USERID (new 12.1)
Returns the user id of the database user who invoked the current statement or view ORA_INVOKING_USER RETURN NUMBER;
SELECT ora_invoking_userid
FROM dual;
 
SQLCODE
Number of the most recent exception raised by PL/SQL. 0 if none standard.sqlcode RETURN PLS_INTEGER;
set serveroutput on

BEGIN
  dbms_output.put_line(SQLCODE);
END;
/

-- see Exceptions page
 
SQLERRM
Error message associated with the specified code

Overload 1
standard.sqlerrm RETURN VARCHAR2;
set serveroutput on

BEGIN
  dbms_output.put_line(SQLERRM);
END;
/

-- see Exceptions page
Overload 2 standard.sqlerrm(code_in IN INTEGER := SQLCODE) RETURN VARCHAR2;
set serveroutput on

BEGIN
  dbms_output.put_line(SQLERRM(-60));
END;
/

-- see Exceptions page
 
STANDARD_HASH (new 12.1)
Returns a hash value using one of several hash algorithms defined and standardized by the National Institute of Standards and Technology (NIST). This function is useful for performing authentication and maintaining data integrity in security applications such as digital signatures, checksums, and fingerprinting. STANDARD_HASH(<value> ['<hashing_alogrithm>']) RETURN RAW;
SELECT STANDARD_HASH('Morgan')
FROM dual;

SELECT STANDARD_HASH('Morgan', 'SHA1')
FROM dual;

SELECT STANDARD_HASH('Morgan', 'SHA256')
FROM dual;

SELECT STANDARD_HASH('Morgan', 'SHA384')
FROM dual;

SELECT STANDARD_HASH('Morgan', 'SHA512')
FROM dual;

-- least secure ... do not use this one
SELECT STANDARD_HASH('Morgan', 'MD5')
FROM dual;
 
SQL_GUID
Returns a globally unique identifier made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, 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. SYS_GUID RETURN RAW;
conn uwclass/uwclass@pdborcl

CREATE TABLE t (
rid RAW(32),
col VARCHAR2(20));

desc t

INSERT INTO t
(rid, col)
VALUES
(SYS_GUID(), 'ABC');

INSERT INTO t
(rid, col)
VALUES
(SYS_GUID(), 'DEF');

SELECT * FROM t;
 
SYS_TYPEID
Returns the typeid of the most specific type of the operand SYS_TYPEID(<object_type_value>) RETURN ?;
CREATE TYPE person_t AS OBJECT (name VARCHAR2(30), ssn NUMBER)
NOT FINAL;
/

CREATE TABLE persons OF person_t;

INSERT INTO persons
VALUES
(person_t('Morgan', 123));

SELECT name, SYS_TYPEID(VALUE(p)) TYPE_ID FROM persons p;
 
UID
User Session ID UID RETURN PLS_INTEGER;
SELECT UID
FROM dual;

SELECT user#
FROM v$session
WHERE schemaname = USER;
 
USER
Username As Connected USER RETURN VARCHAR2;
SELECT USER FROM dual;
 
USERENV (deprecated: use SYS_CONTEXT)
Syntax SELECT userenv(envstr IN VARCHAR2) RETURN VARCHAR2;
Session info. stored with DBMS_APPLICATION_INFO SELECT USERENV('CLIENT_INFO') FROM dual;

exec dbms_application_info.set_client_info('TEST');

SELECT USERENV('CLIENT_INFO')
FROM dual;
The current audit entry number. The audit entryid sequence is shared between fine-grained audit records and regular audit records. You cannot use this attribute in distributed SQL statements SELECT userenv('ENTRYID')
FROM dual;
Current instance identifier SELECT userenv('INSTANCE')
FROM dual;
Returns 'TRUE' if the user has been authenticated as having DBA privileges either through the operating system or through a password file SELECT userenv('ISDBA')
FROM dual;
The ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter SELECT userenv('LANG')
FROM dual;
The language and territory currently used by the session, along with the database character set, in the form: language_territory dot characterset. SELECT userenv('LANGUAGE')
FROM dual;
The auditing session identifier (not available in distributed SQL statements) SELECT userenv('SESSIONID')
FROM dual;

SELECT audsid
FROM v_$session;
Returns the operating system identifier for the terminal of the current session. In distributed SQL statements, this parameter returns the identifier for your local session. In a distributed environment, this parameter is supported only for remote SELECT statements, not for remote INSERT, UPDATE, or DELETE operations. SELECT userenv('TERMINAL')
FROM dual;
 
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. VALUE(item IN "<ADT_WITH_OID>") RETURN "<ADT_1>";
CREATE TYPE address_t AS OBJECT (
hno    NUMBER,
street VARCHAR2(40),
city   VARCHAR2(20),
zip    VARCHAR2(5),
phone  VARCHAR2(10));
/

CREATE TYPE person AS OBJECT (
name        VARCHAR2(40),
dateofbirth DATE,
homeaddress address_t,
manager REF person);
/

CREATE OR REPLACE TYPE person_t AS OBJECT (
name VARCHAR2(100),
ssn  NUMBER)
NOT FINAL;
/

CREATE TABLE persons OF person_t;

INSERT INTO persons VALUES (person_t('Bob', 1234));

SELECT VALUE(p) FROM persons p;
 
XOR
Exclusive OR Operator standard.XOR(left IN BOOLEAN, right IN BOOLEAN) RETURN BOOLEAN;
set serveroutput on

BEGIN
  IF xor(TRUE, TRUE) THEN
    dbms_output.put_line('TRUE-TRUE Returns TRUE');
  END IF;
  IF xor(TRUE, FALSE) THEN
    dbms_output.put_line('TRUE-FALSE Returns TRUE');
  END IF;
END;
/

Related Topics
Analytic Functions
Built-in Functions
Collection Functions
Conversion Functions
Data Mining Functions
Date Functions
Exception Handling
Numeric Functions
Object Functions
String Functions
SYS_CONTEXT
Timestamp Functions
XML Functions

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved