Learning Objectives – after this chapter you will be able to:
- Explain LOBs and classify them as internal (CLOB, BLOB, NCLOB) or external (BFILE).
- Describe the LOB locator and compare LONG with LOB types.
- Create tables with LOB columns and use EMPTY_CLOB/EMPTY_BLOB, BFILENAME and directory aliases.
- Use DBMS_LOB procedures and functions, including the BFILE-related ones.
The size limits in this chapter (e.g. 4 GB for LOBs) are those given in the notes for Oracle8/8i; newer Oracle releases support much larger LOBs.
Introduction to LOBs
LOBs (Large Objects) hold large amounts of raw binary data (such as graphics images) and large amounts of character data. Oracle extended SQL DDL and DML for LOBs; they can also be manipulated with the DBMS_LOB package and OCI (Oracle Call Interface).
| Internal LOBs | External LOBs | |
|---|---|---|
| Stored | In the database tablespace | In operating-system files; only a pointer is in the database |
| Transactions / integrity | Supported like any scalar type | Not supported |
| Types | CLOB, BLOB, NCLOB | BFILE |
| On delete | Locator and LOB value are deleted | Only the locator is deleted – delete the file manually |
| Type | Holds |
|---|---|
| CLOB | Single-byte character data |
| NCLOB | Multi-byte or single-byte fixed-length character data of the national character set |
| BLOB | Raw binary data, such as bitmap images |
| BFILE | Data in an operating-system file; only a reference to the file is stored |
LOB Locator and LONG vs LOB
LOB data is not stored in the row with the other columns. The row holds a locator – like a pointer – to where the data is actually stored (inside the database for internal LOBs, in a file for BFILE).
| LONG / LONG RAW | LOB types |
|---|---|
| Up to 2 GB | Up to 4 GB (as per the notes) |
| Only one LONG column per table | A table can have more than one LOB column |
| A subquery cannot select a LONG column | A subquery can select a LOB column |
Defining and Manipulating LOBs
LOB columns, EMPTY_CLOB, directories and BFILENAME
create table lob_table
( id number(5),
clob_col clob,
bfile_col bfile );
insert into lob_table (id, clob_col) values (100, empty_clob()); -- empty locator
create directory "IMAGES" as 'c:\bitmaps'; -- directory alias
grant read on directory images to srikanth; -- users need READ on the directory
insert into lob_table
values (101, 'Some data in CLOB column', bfilename('IMAGES', 'BMP1.BMP'));- EMPTY_BLOB() / EMPTY_CLOB() initialise a LOB column to an empty locator in INSERT/UPDATE – required before writing to it with OCI or DBMS_LOB.
- BFILENAME(directory_alias, filename) returns a BFILE that points to a file.
- A directory alias (CREATE DIRECTORY) maps a name to a physical directory; if files move, only the alias needs to be re-pointed. The alias is case-sensitive in BFILENAME; Oracle stores it in uppercase unless created within double quotes.
- A BFILE value cannot be displayed with SELECT in SQL*Plus.
- These SQL functions work on the whole LOB; to change part of a LOB use DBMS_LOB.
DBMS_LOB Package
Normal functions such as SUBSTR cannot be used on LOBs, so DBMS_LOB is essential.
| Method | Purpose |
|---|---|
| READ(locator, nobytes, offset, output) | Reads a piece into a variable |
| WRITE(locator, nobytes, offset, buffer) | Writes from a buffer into the LOB at an offset |
| APPEND(dest, source) | Appends one LOB to another |
| ERASE(locator, nobytes, offset) | Erases bytes from an offset |
| TRIM(locator, newlength) | Reduces the LOB to a new length (trims on the right) |
| SUBSTR(locator, nobytes, offset) | Returns a portion (READ places it in a variable instead) |
| COPY(dest, source, nobytes, dest_offset, source_offset) | Replaces part of one LOB with data from another |
| GETLENGTH(locator) | Length of the LOB |
| INSTR(locator, pattern, offset, occur) | Position of a pattern (0 if not found) |
| COMPARE(lob1, lob2, nobytes, offset1, offset2) | 0 if same, 1 if first is bigger, -1 if second is bigger |
INSTR and COPY
declare
c_lob clob;
pos number(2);
begin
select clob_col into c_lob from lob_table where id = 10;
pos := dbms_lob.instr(c_lob, 'Oracle', 1, 1);
if pos = 0 then
dbms_output.put_line('Could not find pattern Oracle');
else
dbms_output.put_line('Found Oracle at = ' || to_char(pos));
end if;
end;
/
-- COPY: the destination LOB must be selected FOR UPDATE
select clob_col into dest_clob from lob_table where id = 15 for update;
dbms_lob.copy(dest_clob, source_clob, 10, 50, 10);BFILE-related methods
- FILEOPEN(bfile, mode) – open (e.g. DBMS_LOB.FILE_READONLY)
- FILECLOSE(bfile) / FILECLOSEALL
- FILEEXISTS(bfile) – non-zero if the file exists
- FILEGETNAME(loc, dir, file) – directory and file name
- FILEISOPEN(bfile) – is the file open?
Reading a BFILE in chunks
declare
value bfile;
buf char(100);
amt binary_integer := 100;
pos integer := 1;
begin
select bfile_col into value from lob_table where id = 1;
dbms_lob.fileopen(value, dbms_lob.file_readonly);
loop
dbms_lob.read(value, amt, pos, buf); -- raises NO_DATA_FOUND at the end
pos := pos + amt;
end loop;
exception
when no_data_found then
dbms_lob.fileclose(value);
end;
Key Takeaways
- CLOB/BLOB/NCLOB are internal and transactional; BFILE is external and read through a directory alias.
- Rows store LOB locators, not the data.
- Initialise with EMPTY_CLOB/EMPTY_BLOB; point to files with BFILENAME.
- Use DBMS_LOB (READ, WRITE, INSTR, GETLENGTH, COPY, FILEOPEN …) to work inside LOBs.
Chapter 21 Notes (PDF)
ch21.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 21 notes (PDF). Click a question to see the answer.
Answer : LOBs (Large Objects) are datatypes that can hold large amounts of raw binary data, such as graphics images, and large amounts of character data. Oracle extended SQL DDL and DML to support them, and they can also be manipulated using the DBMS_LOB package and OCI (Oracle Call Interface).
Answer : Internal LOBs (CLOB, BLOB, NCLOB) are stored in the database tablespace and support transaction processing like any other scalar datatype. External LOBs (BFILE) are stored in operating-system files with only a pointer stored in the database; they do not support transaction processing and integrity checking.
Answer : CLOB holds single-byte character data; NCLOB holds multi-byte or single-byte fixed-length character data of the national character set; BLOB holds raw binary data such as bitmap images; BFILE refers to data stored in an operating-system file – only a reference to the file is stored in the database.
Answer : LOB data is not stored in the row with the other columns. The row stores a locator, similar to a pointer, that points to the location where the data is actually stored – within the database for internal LOBs or in an operating-system file for external LOBs.
Answer : LONG can contain up to 2 GB whereas a LOB can contain up to 4 GB (as per the notes); a table can contain only one LONG column but more than one LOB column; a subquery cannot select a LONG column but can select a LOB column.
Answer : They are SQL functions used in INSERT and UPDATE to initialise a LOB column to an empty locator. A LOB column must be initialised to an empty locator before data is written to it with OCI or the DBMS_LOB package, e.g. INSERT INTO lob_table (id, clob_col) VALUES (100, EMPTY_CLOB()).
Answer : BFILENAME(directory_alias, filename) returns a BFILE and is used in INSERT and UPDATE to initialise a BFILE column. Directory_alias is the alias of the directory (created with CREATE DIRECTORY) where the file is stored and filename is the name of the file, e.g. BFILENAME('IMAGES', 'BMP1.BMP').
Answer : A directory alias, created with CREATE DIRECTORY (e.g. CREATE DIRECTORY "IMAGES" AS 'c:\bitmaps'), refers to a physical directory in the operating system using a name in Oracle. If files are moved, the alias can be re-pointed to the new physical directory without changing the alias used in BFILE columns. Users need READ privilege on the directory (GRANT READ ON DIRECTORY ...).
Answer : The directory alias is case-sensitive when used in BFILENAME. At creation Oracle converts the alias to uppercase unless the name is given in double quotes, so if proper case is not given in BFILENAME Oracle does not recognise the alias.
Answer : For internal LOBs (CLOB, NCLOB, BLOB) both the locator and the LOB value are deleted. For BFILE (external LOB) only the locator is deleted and the actual file remains, so BFILE data must be deleted manually.
Answer : The SQL functions such as EMPTY_CLOB and BFILENAME deal with the complete LOB column and do not allow modifying a part of it, and normal functions such as SUBSTR cannot be used with LOBs. DBMS_LOB provides procedures and functions such as READ, WRITE, APPEND, ERASE, TRIM, SUBSTR, COPY, GETLENGTH, INSTR and COMPARE.
Answer : Both extract a portion of a LOB value. READ places the extracted piece in the given output variable, whereas SUBSTR returns the extracted value.
Answer : It compares two LOB values and returns 0 if they are the same, 1 if the first is bigger than the second, and -1 if the second is bigger. Offsets specify where in each LOB the comparison begins and nobytes the number of bytes to compare.
Answer : Use DBMS_LOB.INSTR(lob, pattern, offset, occurrence); it returns the position of the pattern or 0 if not found. Before modifying a LOB (e.g. with DBMS_LOB.COPY), the destination LOB must be selected with SELECT ... FOR UPDATE.
Answer : FILEOPEN (opens the file, e.g. in DBMS_LOB.FILE_READONLY mode), FILECLOSE (closes a file), FILECLOSEALL (closes all open files), FILEEXISTS (checks whether the file referenced by the locator exists – non-zero if it does), FILEGETNAME (gets the directory and file name) and FILEISOPEN (checks whether the file is open).
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database SecureFiles and Large Objects Developer’s Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/adlob/
- Oracle Corporation. (n.d.). Oracle Database PL/SQL Packages and Types Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/