| General Information |
| Note: These are functions not covered on other site pages |
| |
| LNNVL |
| Evaluates a condition when one or both operands of the condition may be null |
LNNVL(<condition>) RETURN BOOLEAN; |
conn hr/hr
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; |
| |
| 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
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 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;
/ |
| 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;
/ |
| 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;
/ |
| 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;
/ |
| Overload 5 |
NVL(label1 MSLABEL, label2 IN MSLABEL) RETURN MSLABEL; |
| TBD |
| Overload 6 |
NVL(b1 IN "<ADT_1>", IN b2 IN "<ADT_1>") RETURN "<ADT_1>"; |
| TBD |
| Overload 7 |
NVL(b1 IN REF "<ADT_1>", b2 IN REF "<ADT_1>")
RETURN REF "<ADT_1>" ; |
| TBD |
| Overload 8 |
NVL(b1 IN "<COLLECTION_1>", b2 IN "<COLLECTION_1>")
RETURN "<COLLECTION_1>"; |
| TBD |
| Overload 9 |
NVL(b1 IN "<REF_CURSOR_1>", b2 IN "<REF_CURSOR_1>")
RETURN "<REF_CURSOR_1>"; |
| TBD |
| Overload 10 |
NVL(b1 IN TIME_UNCONSTRAINED, b2 IN TIME_UNCONSTRAINED)
RETURN TIME_UNCONSTRAINED; |
| TBD |
| Overload 11 |
NVL(b1 IN TIME_TZ_UNCONSTRAINED, b2 IN TIME_TZ_UNCONSTRAINED)
RETURN TIME_TZ_UNCONSTRAINED; |
| TBD |
| Overload 12 |
NVL(b1 IN TIMESTAMP_UNCONSTRAINED, b2 IN TIMESTAMP_UNCONSTRAINED)
RETURN TIMESTAMP_UNCONSTRAINED; |
| TBD |
| Overload 13 |
NVL(b1 IN TIMESTAMP_TZ_UNCONSTRAINED, b2 IN TIMESTAMP_TZ_UNCONSTRAINED)
RETURN TIMESTAMP_TZ_UNCONSTRAINED; |
| TBD |
| Overload 14 |
NVL(b1 IN TIMESTAMP_LTZ_UNCONSTRAINED, b2 IN TIMESTAMP_LTZ_UNCONSTRAINED)
RETURN TIMESTAMP_LTZ_UNCONSTRAINED; |
| TBD |
| Overload 15 |
NVL(b1 IN YMINTERVAL_UNCONSTRAINED, b2 IN YMINTERVAL_UNCONSTRAINED)
RETURN YMINTERVAL_UNCONSTRAINED; |
| TBD |
| Overload 16 |
NVL(b1 IN DSINTERVAL_UNCONSTRAINED, b2 IN DSINTERVAL_UNCONSTRAINED)
RETURN DSINTERVAL_UNCONSTRAINED; |
| TBD |
| Overload 17 |
NVL(s1 IN CLOB CHARACTER SET ANY_CS, s2 IN CHARACTER SET s1%CHARSET)
RETURN CHARACTER SET s1%CHARSET; |
| TBD |
| Overload 18 |
NVL(f1 IN BINARY_FLOAT, f2 IN BINARY_FLOAT) RETURN BINARY_FLOAT; |
| TBD |
| Overload 19 |
NVL(d1 IN BINARY_DOUBLE, d2 IN BINARY_DOUBLE) RETURN BINARY_DOUBLE; |
| TBD |
| 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>) |
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;
/ |
| |
| 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 |
| |
| SQL_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, 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; |
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 gv$session
WHERE schemaname = USER; |
| |
| USER |
| User As Logged On |
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; |
