SYS_GUID()

In Oracle PL/SQL, SYS_GUID is a built in function which returns the Global Unique Identifier (GUID) for a row in a table.  It accepts no arguments and returns a RAW value of 16 bytes.  A GUID is a sequence of characters that are supposed to be globally unique. In other words, they should never appear more than once regardless of the circumstances. You would normally use GUIDs when you need absolutely unique values that involve multiple database instances or multiple networks. The algorithm that Oracle uses is somewhat faulty in that the values are unique but they are not random (or even pseudo-random).
Source: http://psoug.org/definition/SYS_GUID.htm

Example:

select sys_guid() from dual;  evaluates to 717A66D4915946D59152CC7FD4F517CC (in this instance).

select rawtohex(sys_guid()) from dual; evaluates to CCDE794E51F8442F99505E5EFF83FCA3 (in this instance).

One place this can be useful is in the generation of CCR, CCD, or CDA documents, where an unique identifier is required within the XML.

Another place this can be useful is as a default value for a primary key column.  However, I prefer to use a sequence and a table trigger, so the primary keys are nice, sequential and clean.

NVL vs COALESCE

Recently, I attended Kscope15 (see the blog posts in June 2015), an annual convention by Oracle Development Tools User Group.

During one of the sessions by Scott Wesley (Evidence-Based APEX: Building Better Practices), it was explained that COALESCE was better on several fronts than NVL.  Since we use NVL quite a bit at IRCS, Inc, I thought I'd compile a post about the subject.

NVL is an Oracle specific command, introduced in the 80s before there were any standards.
COALESCE is a more modern function that is part of the ANSI-92 standard.  COALESCE was introduced in Oracle 9i.

NVL (if the first parameter is null, the second parameter is returned, otherwise if the first parameter is any other value other than null, it is returned unchanged):
select NVL(null, 0) from dual; evaluates to 0
select NVL(1, 0) from dual; evaluates to 1

COALESCE (the first non-null value is returned):
select COALESCE(null,0) from dual; evaluates to 0
select COALESCE(1,0) from dual; evaluates to 1

OK, different commands, same answer.  In the case of evaluating two values, they are synonyms, except:
1) COALESCE stops evaluation whenever it finds the first non-null.
2) NVL always evaluates both arguments.
So performance wise, COALESCE potentially only has 1/2 the work to do.

However, there is more to COALESCE.  COALESCE accepts two or more parameters and returns the first non-null value in a list.  If all parameters contain null values, it returns null.
select coalesce(null,null,0) from dual;  evaluates to 0 (as it is the first non-null value evaluated).
select coalesce(null,1,2) from dual;  evaluates to 1 (as it is the first non-null value evaluated).
select coalesce(null,null,null,null,null,null,1,2,3,4,5) from dual;  evaluates to 1 (as it is the first non-null value evaluated).

COALESCE pitfalls:
NVL is tolerant of differing types, for example:
select NVL('Hello',1) from dual;  evaluates to 'Hello'
Where
select COALESCE('Hello',1) from dual;  evaluates to ORA00932: inconsistent datatypes; expected CHAR got NUMBER....

So be careful in the use of COALESCE.  This is specifically true in APEX, where all page items are in reality VARCHAR2.  If you are lazy and you are depending on implicit conversion, weird, inconsistent things can happen.  Its always best to convert your page items to a known type before you use them.

A good example of the performance difference between NVL and COALESCE is available here

A good description of NULL-related functions available within Oracle is available here