O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The LOB Datatypes

Oracle and PL/SQL support several variations of LOB (large object) datatypes. LOBs can store large amounts—up to four gigabytes—of binary data (such as images) or character text data.

Within PL/SQL you can declare LOB variables of the following datatypes:

BFILE

Binary file. Declares a variable that holds a file locator pointing to an operating-system file outside the database. Oracle treats the data in the file as binary data.

BLOB

Binary large object. Declares a variable that holds a LOB locator pointing to a large binary object stored inside the database.

CLOB

Character large object. Declares a variable that holds a LOB locator pointing to a large block of single-byte, fixed-width character data stored inside the database.

NCLOB

National Language Support (NLS) character large object. Declares a variable that holds a LOB locator pointing to a large block of single-byte, fixed-width multibyte, or variable-width multibyte character data stored inside the database.

There are two types of LOBs: internal and external. Internal LOBs (BLOBs, CLOBs, and NCLOBs) are stored in the database and can participate in a transaction in the database server. External LOBs (BFILEs) represent binary data stored in operating-system files outside the database tablespaces. External LOBs cannot participate in transactions; in other words, you cannot commit or roll back changes to a BFILE. Instead, you must rely on the underlying filesystem for data integrity.

The BFILE Datatype ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required