BLOBs and CLOBs Creation
BLOB and CLOB datatypes are created by use of the CREATE or ALTER TABLE
or the CREATE or ALTER TYPE commands. In fact, they are created identically to
other non-sized datatypes such as DATE and LONG with the exception of the LOB
storage clause.
The LOB storage clause is not needed if the maximum size of the BLOB doesn't
exceed 4000 bytes. Up to 4000 bytes can be stored in-line with the other data in the
tablespace.
If the length of the BLOB exceeds 4000 bytes it must be stored in either a system
defaulted storage (the same as the default for the table it resides in) or in an explicitly
defined LOB storage area.
1. Develop a database application to demonstrate storing and retrieving of
BLOB and CLOB objects.
clob_test1.txt
This is a clob test1
clob_test2.txt
This is a clob test2
blob_test1.doc
This is a blob test1
blob_test2.doc
This is a blob test2
The lob_test_data.txt file contains regular data and references to the files holding the
CLOB and BLOB data.
1,one,01-JAN-2006,clob_test1.txt,blob_test1.doc
2,two,02-JAN-2006,clob_test2.txt,blob_test2.doc
Now we have all the data, we need a table to load the data .
DROP TABLE lob_tab;
CREATE TABLE lob_tab (
number_content NUMBER(10),
varchar2_content VARCHAR2(100),
date_content DATE,
clob_content CLOB,
blob_content BLOB
);
Next, we define the SQL*Loader control file, called lob_test.ctl, that will allow us to
load the data.
