- Let you store text, images, music, and video in the database.
- You can store character large objects in CLOB columns, and binary large objects inside the database as BLOB columns or outside the database as BFILE (binary file) columns.
- BFILE columns: store a locator that points to the physical location of an external file.
- LOBs can hold up to a maximum of 8 to 128 terabytes, depending on how you configure your database.
- A call to the
DBMS_LOB.GET_STORAGE_LIMITfunction tells you your database maximum LOB size.
set serveroutput on
declare
var1 clob := 'some string';
var2 blob := hextoraw( '43'||'421'|| '52');
result number;
begin
result := dbms_lob.get_storage_limit(var1);
dbms_output.put_line('CLOB maximum limit: '||
to_char(result/1024/1024/1024, '999,999') || ' Gigabytes');
dbms_output.put_line('CLOB length: ' || dbms_lob.getlength(var1));
result := dbms_lob.get_storage_limit(var2);
dbms_output.put_line('BLOB maximum limit: '||
to_char(result/1024/1024/1024, '999,999') || ' Gigabytes');
dbms_output.put_line('BLOB length: ' || dbms_lob.getlength(var2));
end;
anonymous block completed
CLOB maximum limit: 16,264 Gigabytes
CLOB length: 11
BLOB maximum limit: 32,528 Gigabytes
BLOB length: 4
what for?
- simple structured data => relational tables
- complex structured data => object-relational constructs (collections, references, UDTs)
- semi-structured data => logical structure not typically interpreted by the database. LOBs can be used here. Examples of semi-structured data include document files such as XML documents or word processor files.
- unstructured data => photos, videos. LOBs can be used here.
 | On 11g R1: SecureFiles. LOBs reengineered |
- You can now define BLOB, CLOB, or NCLOB columns as SecureFiles when you create a table or alter it.
- SecureFiles are declared with special storage parameters that let you encrypt, compress, and deduplicate them.
- Oracle 11g lets you store SecureFiles in BLOB, CLOB, and NCLOB columns.
- They work on a principle of Transparent Data Encryption (TDE) and use an Oracle Wallet as the encryption key.
- BLOB, CLOB and NCLOB can be stored in the database (internal) or in external files.
Internal LOBs can be either persistent or temporary. - Persistent LOB: Exists in a table row in the database.
- Temporary LOB: Created when you instantiate a LOB only within the scope of your local application.
- External LOBs are data objects stored in operating system files, outside the database tablespaces.
- The database accesses external LOBs using the SQL data type BFILE, which is the only external LOB data type.
- BFILEs are read-only data types.
- External LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying file system as governed by the operating system.
COPY vs. REFERENCE semantics
- Internal LOBs use copy semantics.: both the LOB locator and LOB value are logically copied during insert, update, or assignment operations.
- External LOBs use reference semantics.: only the LOB locator is copied during insert operations.
A LOB (BLOB, CLOB, NCLOB) column can be in one of three states
| NULL | The table cell is created, but the cell holds no locator or value. (The column contains a NULL value) |
| Empty | The column contains a LOB locator that has no value. Length of LOB is zero. |
| Populated | The column contains a LOB locator and a value. A call to dbms_lob.getlength here returns a postive value. |
CLOB and NCLOB
The CLOB datatype is an object type, therefore it requires implicit or explicit construction of an object instance.
Declaring a CLOB:
var1 CLOB; -- Declare a null reference to a CLOB. var1 CLOB := empty_clob(); -- Declare an empty CLOB. var2 CLOB := 'some_string'; -- Declare a CLOB with a string literal.
 | Manipulating a CLOB column |
create table item ( item_id number, item_title varchar2(20), item_desc clob, item_photo bfile); desc item Name Null Type ---------- ---- ------------ ITEM_ID NUMBER ITEM_TITLE VARCHAR2(20) ITEM_DESC CLOB ITEM_PHOTO BFILE() INSERT into item values (1, 'Ulysses', empty_clob(), null); 1 row inserted. -- This approach limits the amount of data that can be -- inserted into a CLOB column to is limited to 4K (SQL) or 32K (PL/SQL) UPDATE item SET item_desc = 'The Lord of the Rings is a high fantasy epic written by '|| 'philologist and University of Oxford professor J. R. R. Tolkien. The story '|| 'began as a sequel to Tolkien's earlier, less complex children's fantasy '|| 'novel The Hobbit (1937), but eventually developed into a much larger work. '|| 'It was written in stages between 1937 and 1949, much of it during the '|| 'Second World War.[1] It is the second best-selling novel ever written, '|| 'with over 150 million copies sold.' WHERE item_id =1;
Alternatively, you can use the DBMS_LOB package
Using DBMS_LOB, you can:
(a) Insert a new row and initialize the CLOB column with a call to
empty_clob() (b) Read chunks from the file and insert into the CLOB Column, appending each new chunk until copying the entire file (up to 128Tb).
(c) To open and read the file at the OS level from within the PLSQL Package, you need to create a DB Directory object and grant permission for the user to read from that directory.
(d) You may use conditional compilation directives to insert debut information..
-- You need to connect as SYSTEM to perform the following two steps:
-- (1) create a virtual directory. should be conn as system
create directory generic as '/tmp/clob-file';
-- grant read permissions on the directory to the user;
grant read on directory generic to dev2;
-- Create procedure to load the file into the CLOB column.
CREATE OR REPLACE PROCEDURE load_clob_from_file
(src_file_name in varchar2,
table_name in varchar2,
column_name in varchar2,
pk_name in varchar2,
pk_value in varchar2) is
-- define local vars for dbms_lob.loadclobfromfile proc
des_clob clob;
-- the BFILENAME function secures the directory path for
-- the GENERIC directory from the database dictionary and
-- returns the absolute filename
src_clob bfile := bfilename('GENERIC', src_file_name);
des_offset number:= 1;
src_offset number := 1;
ctx_lang number := dbms_lob.default_lang_ctx;
warning number;
-- declare var to hold the size of the file to be imported.
src_clob_size number;
stmt varchar2(2000);
BEGIN
-- open source file and reads it into a BFILE datatype
if dbms_lob.fileexists(src_clob) = 1 and
NOT dbms_lob.isopen(src_clob)= 1 THEN
src_clob_size := dbms_lob.getlength(src_clob);
$IF $$DEBUG $THEN
dbms_output.put_line('Opening CLOB. Size: '|| src_clob_size);
$END
dbms_lob.open(src_clob, DBMS_LOB.LOB_READONLY);
end if;
-- assign a dynamic string to the stmt var
-- Initializes the CLOB column to an empty_clob()
-- and returns a reference to the column into an
-- output variable
stmt := 'UPDATE ' ||table_name||' '
|| 'SET '||column_name||' = empty_clob()'
|| 'WHERE '|| pk_name||' = '||''''||pk_value||''' '
|| 'RETURNING '||column_name||' INTO :locator';
-- execute dynamic stmt
-- the bind variable :locator is assigned to the output var
$IF $$DEBUG $THEN
dbms_output.put_line('Preparing to initialize CLOB column');
$END
EXECUTE IMMEDIATE stmt USING OUT des_clob;
$IF $$DEBUG $THEN
dbms_output.put_line('Initialization successful..');
$END
-- read and write file to CLOB, close source file and commit;
-- the call to DBMS_LOB.LOADCLOBFROMFILE. The procedure
-- copy the contents of the file into the table column.
-- This R/W operation bypasses the 32Kb handling limitation of PL/SQL
dbms_lob.loadclobfromfile(
dest_lob => des_clob,
src_bfile => src_clob,
amount => dbms_lob.getlength(src_clob),
dest_offset => des_offset,
src_offset => src_offset,
bfile_csid => dbms_lob.default_csid,
lang_context => ctx_lang,
warning => warning);
-- close the OS file.
dbms_lob.close(src_clob);
IF src_clob_size = dbms_lob.getlength(des_clob) THEN
$IF $$DEBUG $THEN
dbms_output.put_line('Success!');
$END
commit;
ELSE
$IF $$DEBUG $THEN
dbms_output.put_line('Failure.');
$END
RAISE dbms_lob.operation_failed;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error stack: '|| dbms_utility.format_error_stack());
dbms_output.put_line('Error Backtrace: '|| dbms_utility.format_error_backtrace());
END load_clob_from_file;
/
PROCEDURE load_clob_from_file compiled
-- Alter session to set conditional compilation directive
-- and turn on debugging
SQL> alter session set plsql_ccflags = 'DEBUG:TRUE';
SQL> set serveroutput on
SQL> BEGIN
load_clob_from_file (
src_file_name => 'ulysses.txt',
table_name => 'item',
column_name => 'item_desc',
pk_name => 'item_id',
pk_value => to_char(1));
END;
/
anonymous block completed
Opening CLOB. Size: 11989153
Preparing to initialize CLOB column
Initialization successful..
Success!
