Introduction to BLOB fields in Oracle databases

We all know that Blob in the Oracle database is a special field, which has better performance than the long field. Its main function is to save binary data such as images. The following describes the main content of the article.

Writing Blob fields is very different from writing other types of fields. Because Blob itself has a cursor, you must use cursor to operate blob. Therefore, before writing Blob, you must obtain the cursor to write data. How can you obtain the Blob cursor?

This requires you to insert an empty blob first, which will create a blob cursor, and then you can query the cursor of the empty blob using select. In this way, you can perform two steps, you can get the blob cursor to write blob data.

Example:

Oracle series: LOB large object processing

It is mainly used to store database fields with a large amount of data. It can store up to 4 GB of unstructured data.

This section describes the storage of character type and binary file type LOB data, and separately introduces the storage of binary type LOB data.

1. LOB data type classification in Oracle databases

1. Data Types:

① Character type:

CLOB: stores a large amount of single-byte character data.

NLOB: stores fixed-width, multi-byte character data.

② Binary type:

BLOB: stores large unstructured binary data.

③ Binary file type:

BFILE: stores binary files in operating system files outside the database. Path for storing files.

2. Storage:

① Stored in the internal tablespace:

CLOB, NLOB, and BLOB

② Point to an external operating system file:

BFILE

Ii. Big Object Data Input

1. Declare the LOB type column

 
 

  
  
/*  
  
  
conn scott/tiger;  
  
  
Create TableSpace ts5_21  
  
  
DataFile 'E:\Oracle\ts5_21.dbf'  
  
  
Size 5m;  
  
  
*/  
  
  
Create Table tLob (   
  
  
no Number(4),  
  
  
name VarChar2(10),  
  
  
resume CLob,  
  
  
photo BLob,  
  
  
record BFile  
  
  
)  
  
  
Lob (resume,photo)Store As ( 
 
 

Tablespace ts5_21 -- specify the Tablespace to be stored

Chunk 6 k -- specify the data block size

 
 

  
  
Disable Storage In Row   
  
  
); 
 
 

2. Insert a large object Column

① Insert common column data first

② When a large object column is encountered, insert a blank constructor.

Role: empty_clob (), empty_nclob ()

Binary: empty_blob ()

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

BFileName function:

BFileName ('logical directory name', 'filename ');

The logical directory name can only be in uppercase, because the data dictionary is stored in uppercase. Oracle databases are case sensitive.

During creation, you do not need to point the logical directory of the BFileName function to the physical path. When using this function, you can check whether the two are associated.

Example:

 
 

  
  
Insert Into tLob Values(1,'Gene',empty_clob()
,empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));  
 
 

③ Associate the logical directory with the physical directory. (For binary files)

Grant create any directory permission

Grant create any directory to user name with admin option;

Associate logical directory and physical directory

Local

Create Directory logical Directory name As 'physical Directory of the file ';

Network:

Create Directory logical Directory name As '\ Host Name (IP) \ shared Directory ';

Example:

Create Directory MYDIR As 'e: \ Oracle ';

Insert example:

Insert into tlob values (1, 'gene ', 'clob large object Column

 
 

  
  
',empty_blob(),bfilename('MYDIR','IMG_0210.JPG')); 
 
 

The above content is an introduction to the operations and performance problems of BLOB data in Oracle databases. I hope you will gain some benefits.