TheLOB (large object) datatypesBFILE,BLOB,CLOB, andNCLOB let you store blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size. And, they allow efficient, random, piece-wise access to the data.
TheLOBtypes differ from theLONGandLONG RAWtypes in several ways. For example, LOBs (exceptNCLOB) can be attributes of an object type, butLONGs cannot. The
maximum size of aLOB is four gigabytes, but the maximum size of aLONG is two gigabytes. Also,LOBs support random access to data, butLONGs support only sequential access.
LOBtypes storelob locators, which point to large objects stored in an external file,in-line (inside the row) orout-of-line(outside the row). Database columns of typeBLOB,CLOB, NCLOB, orBFILE store the locators.BLOB,CLOB, andNCLOB data is stored in the database, in or outside the row.BFILE data is stored in operating system files outside the database.
PL/SQL operates onLOBs through the locators. For example, when you select aBLOB column value, only a locator is returned. If you got it during a transaction, theLOB locator includes a transaction ID, so you cannot use it to update thatLOB in another transaction. Likewise, you cannot save aLOB locator during one session, then use it in another session.
Starting in Oracle9i, you can also convertCLOBs toCHAR andVARCHAR2 types and vice versa, orBLOBs toRAW and vice versa, which lets you useLOB types in most SQL and PL/SQL statements and functions. To read, write, and do piecewise operations on LOBs, you can use the supplied packageDBMS_LOB. For more information, seeOracle Database Application Developer's Guide - Large Objects.
BFILE
You use theBFILE datatype to store large binary objects in operating system files outside the database. EveryBFILEvariable stores a file locator, which points to a large
Overview of Predefined PL/SQL Datatypes
binary file on the server. The locator includes a directory alias, which specifies a full path name (logical path names are not supported).
BFILEs are read-only, so you cannot modify them. The size of aBFILE is system dependent but cannot exceed four gigabytes (2**32 - 1 bytes). Your DBA makes sure that a givenBFILE exists and that Oracle has read permissions on it. The underlying operating system maintains file integrity.
BFILEs do not participate in transactions, are not recoverable, and cannot be replicated. The maximum number of openBFILEs is set by the Oracle initialization parameterSESSION_MAX_OPEN_FILES, which is system dependent.
BLOB
You use theBLOB datatype to store large binary objects in the database, in-line or out-of-line. EveryBLOB variable stores a locator, which points to a large binary object. The size of aBLOB cannot exceed four gigabytes.
BLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by packageDBMS_LOB can be committed or rolled back.BLOB locators can span transactions (for reads only), but they cannot span sessions.
CLOB
You use theCLOB datatype to store large blocks of character data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. EveryCLOB variable stores a locator, which points to a large block of character data. The size of aCLOB cannot exceed four gigabytes.
CLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by packageDBMS_LOB can be committed or rolled back.CLOB locators can span transactions (for reads only), but they cannot span sessions.
NCLOB
You use theNCLOBdatatype to store large blocks ofNCHARdata in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every NCLOB variable stores a locator, which points to a large block ofNCHAR data. The size of anNCLOB cannot exceed four gigabytes.
NCLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by packageDBMS_LOB can be committed or rolled back.NCLOB locators can span transactions (for reads only), but they cannot span sessions.
PL/SQL Boolean Types
PL/SQL has a type for representing Boolean values (true and false). Because SQL does not have an equivalent type, you can useBOOLEAN variables and parameters in PL/SQL contexts but not inside SQL statements or queries.
BOOLEAN
You use theBOOLEAN datatype to store the logical valuesTRUE,FALSE, andNULL (which stands for a missing, unknown, or inapplicable value). Only logic operations are allowed onBOOLEAN variables.
TheBOOLEAN datatype takes no parameters. Only the valuesTRUE,FALSE, andNULL can be assigned to aBOOLEAN variable.
You cannot insert the valuesTRUE andFALSE into a database column. You cannot select or fetch column values into aBOOLEAN variable. Functions called from a SQL
Overview of Predefined PL/SQL Datatypes
query cannot take anyBOOLEAN parameters. Neither can built-in SQL functions such asTO_CHAR; to representBOOLEAN values in output, you must useIF-THEN orCASE constructs to translateBOOLEAN values into some other type, such as 0 or 1, 'Y' or 'N', 'true' or 'false', and so on.