Oracle PL/SQL Programming, Third Edition

Chapter 12. Miscellaneous Datatypes

In this chapter, we’ll explore all the native PL/SQL datatypes that we haven’t yet covered. These include the BOOLEAN and UROWID/ROWID types, as well as the LOB (large object) family of types. We’ll also discuss some useful, predefined object types, including XMLType, which enables you to store XML data in a database column, and ANYDATA type, which allows you to store, well, just about anything.

The BOOLEAN Datatype

the Oracle RDBMS/SQL language offers features not found in PL/SQL, such as the Oracle SQL DECODE construct. PL/SQL, on the other hand, has a few tricks up its sleeve that are unavailable in native SQL. One particularly pleasant example of this is the BOOLEAN datatype.^[15] Boolean data may only be TRUE, FALSE, or NULL. A Boolean is a “logical” datatype.

The Oracle RDBMS does not support a Boolean datatype. You can create a table with a column of datatype CHAR(1) and store either “Y” or “N” in that column to indicate TRUE or FALSE. That is a poor substitute, however, for a datatype that stores actual Boolean values (or NULL). Because there is no counterpart for the PL/SQL Boolean in the Oracle RDBMS, you can neither SELECT into a Boolean variable nor insert a TRUE or FALSE value directly into a database column.

Boolean values and variables are very useful in PL/SQL. Because a Boolean variable can only be TRUE, FALSE, or NULL, you can use that variable to explain what is happening in your code. With Booleans you can write code that ...