Oracle Miscellaneous Functions
Version 20c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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_MAT_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.

According to the data dictionary this was introduced version 6.0 but I did not discover it until 12.1.0.2.		 dataobj_to_mat_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@pdbdev

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;

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_mat_partition(SYST_PART, 94169))
VALUES
(1, SYSDATE+2);
 
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@pdbdev

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;

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, 94169))
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@pdbdev

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@pdbdev

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@pdbdev

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
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
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
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(<arg> IN VARCHAR2) RETURN RAW;
standard_hash(<arg> IN NUMBER) RETURN RAW;
standard_hash(<arg> IN DATE) RETURN RAW;
standard_hash(<arg> IN TIMESTAMP) RAW;
conn / as sysdba

SQL> desc standard_hash
ERROR:
ORA-04043: object standard_hash does not exist

SELECT owner
FROM dba_objects
WHERE object_name = 'STANDARD_HASH';

no rows selected

SELECT standard_hash('MORGAN') FROM dual;

STANDARD_HASH('MORGAN')
----------------------------------------
3A1AC9C81292FC1CF0B8A4015F04C0A3D21BAE2D
-- so much for it not existing ... and yes it is fully documented in the SQL Language Reference

-- try these
SELECT standard_hash('Morgan')
FROM dual;

SELECT standard_hash(42)
FROM dual;

SELECT standard_hash(SYSDATE)
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@pdbdev

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;
 
SYS_OP_ZONE_ID
Returns the zone ID corresponding to a talble rowid and returns a zone ID. The zone ID identifies the set of contiguous disk blocks,
called the zone, that contains the row. The SYS_OP_ZONE_ID function is used when creating a zone map with the CREATE
MATERIALIZED ZONEMAP statement. You must specify SYS_OP_ZONE_ID in the SELECT and GROUP BY clauses of the defining subquery of the zone map.

Not stated is a lot of additional issues as you will see in the demo on the right side of this page including the fact that you must have EE, must purchase the PARTITIONING OPTION, and must be running on an Exadata or SuperClsuter. Too bad.		 SYS_OP_ZONE_ID('[schema.table.rowid] <rowid>', [<scale>])
SQL> conn uwclass/uwclass@pdbdev

SQL> CREATE MATERIALIZED ZONEMAP uwclass.serv_inst_mz AS
  2  SELECT sys_op_zone_id(rowid), si_status, type
  3  FROM serv_inst
  4  WHERE si_status IS NOT NULL;
WHERE si_status IS NOT NULL
*
ERROR at line 4:
ORA-00979: not a GROUP BY expression

SQL> CREATE MATERIALIZED ZONEMAP uwclass.serv_inst_mz AS
  2  SELECT sys_op_zone_id(rowid), i_status, type, COUNT(*) AS STCNT
  3  FROM serv_inst
  4  WHERE si_status IS NOT NULL
  5  GROUP BY si_status, type;
GROUP BY si_status, type
*
ERROR at line 5:
ORA-00962: too many group-by / order-by expressions

SQL> CREATE MATERIALIZED ZONEMAP uwclass.serv_inst_mz AS
  2  SELECT sys_op_zone_id(rowid), i_status, type, COUNT(*) AS STCNT
  3  FROM serv_inst
  4  WHERE si_status IS NOT NULL
  5  GROUP BY si_status, type;

SQL> CREATE MATERIALIZED ZONEMAP uwclass.serv_inst_mz AS
  2  SELECT sys_op_zone_id(rowid), COUNT(*) AS STCNT
  3  FROM serv_inst
  4  WHERE si_status IS NOT NULL
  5  GROUP BY sys_op_zone_id(rowid);
WHERE si_status IS NOT NULL
*
ERROR at line 4:
ORA-31965: incorrect predicate found

SQL> CREATE MATERIALIZED ZONEMAP uwclass.serv_inst_mz AS
  2  SELECT sys_op_zone_id(rowid), COUNT(*) AS STCNT
  3  FROM serv_inst
  4  WHERE si_status IS NOT NULL
  5  GROUP BY sys_op_zone_id(rowid);
WHERE si_status IS NOT NULL
*
ERROR at line 4:
ORA-31965: incorrect predicate found

SQL> CREATE MATERIALIZED ZONEMAP uwclass.serv_inst_mz AS
  2  SELECT sys_op_zone_id(rowid), COUNT(*) AS STCNT
  3  FROM serv_inst
  4  GROUP BY sys_op_zone_id(rowid);
SELECT sys_op_zone_id(rowid), COUNT(*) AS st_cnt
*
ERROR at line 2:
ORA-31953: expected MIN or MAX aggregate not found

SQL> CREATE MATERIALIZED ZONEMAP uwclass.serv_inst_mz AS
2 SELECT sys_op_zone_id(rowid), MAX(siid) AS MAXSIID
3 FROM serv_inst
4 GROUP BY sys_op_zone_id(rowid);
SELECT sys_op_zone_id(rowid), MAX(siid) AS MAX_SIID
*
ERROR at line 2:
ORA-31953: expected MIN or MAX aggregate not found

SQL> CREATE MATERIALIZED ZONEMAP uwclass.serv_inst_mz AS
  2  SELECT sys_op_zone_id(rowid), MIN(siid) AS MINSIID, MAX(siid) AS MAXSIID
  3  FROM serv_inst
  4* GROUP BY sys_op_zone_id(rowid);
GROUP BY sys_op_zone_id(rowid)
*
ERROR at line 4:
ORA-01031: insufficient privileges

SQL> conn sys@pdbdev as sysdba
Enter password:
Connected.

SQL> CREATE MATERIALIZED ZONEMAP uwclass.serv_inst_mz AS
  2  SELECT sys_op_zone_id(rowid), MIN(siid) AS MINSIID, MAX(siid) AS MAXSIID
  3  FROM uwclass.serv_inst
  4  GROUP BY sys_op_zone_id(rowid);
GROUP BY sys_op_zone_id(rowid)
*
ERROR at line 4:
ORA-31969: ZONEMAP not supported for table stored in tablespace of this storage type
 
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
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx