An expression is the basic unit of information in an SQL statement or function. Oracle supports several different types of expressions:

Simple expression

May be any of the following:

  • Text within quotes

  • A number

  • A ROWID for a row specified as part of a table, a schema and table, or a query

  • A column value for a row specified as part of a table, a schema and table, or a query


  • A sequence followed by .NEXTVAL or .CURRVAL

  • NULL

Compound expression

Consists of multiple expressions, joined by a standard operator, such as +, -, *, /, or || where applicable. Compound expressions can also be the result of a function.

CASE expression

Returns a single value, based on the result of a logical comparison. A CASE expression takes one of the following two forms:

CASE expression WHEN comparison_expression THEN RETURN return_expression
        [WHEN comparison_expression THEN RETURN return_expression . . .]


CASE WHEN condition THEN RETURN return_expression
        [WHEN condition THEN RETURN return_expression . . .]

CASE expressions are new with Oracle9i. Both of the forms shown here can take an ELSE clause in the form:

ELSE else_expression

You specify this clause before the END keyword, where else_expression is returned if none of the comparisons or conditions return a value.

CURSOR expression

Returns a nested cursor, the equivalent of a PL/SQL REF CURSOR (see Chapter 9). The form of this expression is:


CURSOR expressions are new with Oracle9i.

DATETIME expression

Returns a datetime value, ...

Get Oracle in a Nutshell now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.