LOB large object processing in Oracle

Source: Internet
Author: User
Tags create directory

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.