LOB large object processing in Oracle

LOB large Object processing: A database field primarily used to store large amounts of data, which can store up to 4G of unstructured data. Mainly introduces the storage of character type and binary file type LOB data, separately describes the binary type LOB data storage.

Classification of LOB data types in Oracle

1, according to the type of storage data:

① Character Type:

CLOB: Stores large amounts of single-byte character data.

Nlob: Stores the fixed-width multibyte character data.

② binary Type:

BLOB: Storing large, unstructured binary data.

③ binary file type:

BFILE: Stores binaries in operating system files that are external to the database. The file path is stored.

2, by storage mode:

① is stored in the internal table space:

Clob,nlob and BLOBs

② point to external operating system files:

BFILE

Input of large Object data

1. Declaring LOB type columns

Create tablespace Dave datafile ' d:/dave/dave.dbf ' Size 20m;

Create Table Tlob (

No number (4),

Name VarChar2 (10),

Resume CLob,

Photo BLob,

Record BFile

）

Lob (Resume,photo) Store as (

Tablespace Dave--Specify the table space to be stored

Chunk 6k--Specify data block size

Disable Storage in Row

）；

2. Insert large Object columns

① insert Normal column data first

Inserts a blank constructor when ② encounters a large object column.

Character type: Empty_clob (), Empty_nclob ()

Binary type: Empty_blob ()

Binary file type: The Bfilename function points to an external file.

Bfilename function:

Bfilename (' Logical directory name ', ' filename ');

Logical directory names can only be capitalized, because the data dictionaries are stored in uppercase. Oracle is case-sensitive.

When you create, you do not need to point the Bfilename function logical directory to the physical path, and check to see if they are associated.

③ associates a logical directory with a physical directory. (if it is a binary file type)

Grant CREATE any DIRECTORY permissions

Grant CREATE any DIRECTORY to username with ADMIN OPTION;

Associating logical directories and physical directories

Local

Create Directory logical directory name as ' file physical directory ';

Internet:

Create Directory logical directory name as '//host name (IP)/shared directory ';

Example:

Create Directory Mydir as ' d:/dave ';

Insert Example:

/* Formatted on 2009-12-24 17:27:00 (QP5 v5.115.810.9015) * *

INSERT into Tlob

VALUES (1,

' Dave ',

' Clob Big object column ',

Empty_blob (),

Bfilename (' Mydir ', ' dave.jpg '));

INSERT into Tlob

VALUES (2,

' Dave ',

' Clob Big object column ',

Empty_blob (),

Bfilename (' Mydir ', ' dave.jpg '));

INSERT into Tlob

VALUES (3,

' Dave ',

' Clob Big object column ',

Empty_blob (),

Bfilename (' Mydir ', ' dave.jpg '));

Three, large object data reading and operation: Dbms_lob package

Dbms_lob Packages: procedures and functions to handle large objects

1. The process and function of reading large object data

①:dbms_lob. Read (): The process of reading the specified length data to the buffer from the LOB data.

Dbms_lob. Read (LOB data, specifying length, starting position,

Store returns LOB type value variable);

Example:

/* Formatted on 2009-12-24 17:25:01 (QP5 v5.115.810.9015) * *

DECLARE

Varc CLOB;