||Oracle Tips by Burleson
Large Objects (LOB)
People are putting more and more non-test stuff
in their databases. When they start placing pictures, video files,
even their MS Word documents into the database, the database has to
have a way to handle this unstructured data. Enter the Large Object (LOB) data type.
A LOB stores unstructured data as an object. It is stored offline,
which means that a reference is stored in the table and the actual
object is stored somewhere else. This is important because a LOB can
contain up to 4 Gigabytes of data. Imagine searching a table with
four LOB columns and a million rows if the LOBs were stored in the
table columns! LOBs come in 4 types.
– Character LOB
– Binary LOB
– National Language CLOB
– File Stored outside the database
on the server.
You manipulate LOBs using the dbms_lob package.
A raw data type is data
that is treated as binary data in that there is not manipulation by
the database. It is inserted as received and retrieved as is. No
character set conversion, etc. A raw data type can be up to 2000
bytes in length.
Like the long data type,
the longraw has been depreciated and should not be used.
is a hexadecimal string
representing the unique address of a row in its table. You cannot
store the logical rowid of an index-organized table.
Same as rowid except that it can store both physical and logical
rowids, including those from an index-organized table. Can also store
a foreign table rowed, including those from remote non-oracle
So these are the Oracle basic data types. Oracle
allows you to create your own data types, but they must be constructed
from the basic data types. For example, if I wanted to have a column
that contained the complete author address, I could create my own data
REPLACE TYPE full_mailing_address_type
( Street VARCHAR2(80),
Zip VARCHAR2(10) );
Here, I created a type called
full_mailing_address_type. I defined it using the Oracle built-in
data types. Once I have created this type in the database, I can use
it in a table column. This is called a user defined data type.
Here, I created a table with two columns, each
containing a user defined data type. The full_address column contains
all the fields of my full_mailing_address_type data type. User
defined data types are a powerful feature but a bit advanced for this
book, so we will confine our examples to the Oracle built-in data
The above book excerpt is from:
Fast writing SQL Reports with SQL*Plus
Col. John Garmany