PL/SQL Identifiers and Literals

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. For example, you use delimiters to represent arithmetic operations such as addition and subtraction. Simple symbols consist of one character and compound symbols consist of two characters.

TO_DATE('01-OCT-1986', 'DD-MON-YYYY')
TO_TIMESTAMP_TZ('01-OCT-1986 00:00:00 -6','DD-MON-YYYY HH24:MI:SS TZH')

'Susan'
'susan'

IF 'Susan' = 'susan'

The absence of a value is represented in the Oracle database by the keyword NULL.As shown in the previous section, variables of almost all PL/SQL datatypes can exist in a null state (the exception to this rule is any associative array type, instances of which are never null). Although it can be challenging for a programmer to handle NULL variables properly regardless of their datatype, strings that are null require special consideration. In Oracle SQL and PL/SQL, a null string is usually indistinguishable from a literal of zero characters, represented literally as (two consecutive single quotes with no characters between them). For example, the following expression will evaluate to TRUE in both SQL and PL/SQL:

DECLARE
  str VARCHAR2(1) := '';
BEGIN
  IF str IS NULL -- will be TRUE

Let us look at CHAR data. If you create a CHAR(n) variable in PL/SQL and assign a zero-length string to it, the database blank-pads the empty variable with space characters, making it not null:

DECLARE
  flag CHAR(2) := ''; -- try to assign zero-length string to CHAR(2)
BEGIN
  IF flag = ' ' ... -- will be TRUE
  IF flag IS NULL ... -- will be FALSE

PL/SQL is the only place you will see such behavior. In the database, when you insert a zero-length string into a CHAR(n) table column, the database does not blank-pad the contents of the column, but leaves it NULL instead. These examples illustrate Oracle's partial adherence to the 92 and 99 versions of the ANSI SQL standard, which mandates a difference between a zero-length string and a NULL string.
Oracle admits this difference, and says they may fully adopt the standard in the future. They have been issuing that warning for about 15 years, though, and it has not happened yet. While NULL tends to behave as if its default datatype is VARCHAR2, the database will try to implicitly cast NULL to whatever type is needed for the current operation. Occasionally, you may need to make the cast explicit, using syntax such as TO_NUMBER(NULL) or CAST(NULL AS NUMBER).