 |
|
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.
CLOB
– Character LOB
BLOB
– Binary LOB
NCLOB
– National Language CLOB
BFILE
– File Stored outside the database
on the server.
You manipulate LOBs using the dbms_lob package.
raw
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.
longraw
Like the long data type,
the longraw has been depreciated and should not be used.
rowid
A rowid
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.
urowid
Universal rowid.
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
databases.
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
type.
CREATE OR
REPLACE TYPE full_mailing_address_type
AS OBJECT
( Street VARCHAR2(80),
City VARCHAR2(80),
State CHAR(2),
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.
CREATE TABLE
customer
(
full_name full_name_type,
full_address full_mailing_address_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
types.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
