CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Miscellaneous Functions
Version 11.1
 
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;
 
Related Topics
Character Set Functions
Conversion Functions
Date Functions
Data Mining Functions
Exception Handling
Numeric Functions
String Functions
SYS_CONTEXT
Timestamp
XML Functions
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [220 users online]    © 2010 psoug.org