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>) |
conn hr/hr
SELECT COUNT(*) FROM employees WHERE commission_pct >= .2;
SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2); |
|
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.
|
NULLIF(<expression1>,
<expression2>) |
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; |
|
NVL |
Returns a Value if the Expression IS NULL |
NVL(
s1 VARCHAR2 CHARACTER SET ANY_CS, -- expression
s2 VARCHAR2 CHARACTER SET s1%CHARSET) -- return value if null
RETURN VARCHAR2 CHARACTER SET s1%CHARSET;
NVL(B1 BOOLEAN, B2 BOOLEAN) return BOOLEAN; |
set serveroutput on
DECLARE
i PLS_INTEGER;
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;
/ |
|
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 |
standard.sqlerrm RETURN
VARCHAR2;
standard.sqlerrm(code_in IN INTEGER := SQLCODE) RETURN VARCHAR2 |
set serveroutput on
BEGIN
dbms_output.put_line(SQLERRM);
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>) |
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 |
SELECT UID
FROM DUAL;
SELECT user#
FROM gv$session
WHERE schemaname = USER; |
|
USER |
User As Logged On |
SELECT USER FROM DUAL; |
|
USERENV (deprecated: use SYS_CONTEXT) |
Usage
|
SELECT userenv('<parameter>')
FROM DUAL;
|
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; |
ISDBA 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 your session, along
with the database character set, in the form:
language_territory dot characterset.
|
SELECT userenv('LANGUAGE')
FROM DUAL; |
The auditing session identifier. You cannot use this option in distributed SQL statements.
|
SELECT userenv('SESSIONID')
FROM DUAL;
SELECT audsid
FROM v_$session; |
TERMINAL 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(correlation_variable)
|
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; |