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 WHERELNNVL(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 ofSELECT 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) RETURNVARCHAR2 |
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), ssnNUMBER) 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; |