Use and maintenance of LOB fields in ORACLE

Abstract: This article introduces the basic methods for using and maintaining the LOB data type in ORACLE databases through the DBMS_LOB package.
Keywords: ORACLE DBMS_LOB LOB maintenance
Graph classification: TP31
1. Introduction
With the development of society, in the development of modern information systems, it is not only simple text information that needs to be stored, but also some pictures and audio/video materials or ultra-long texts. For example, the development of a tourism information system, each scenic spot has a wealth of pictures, audio and video materials and a large number of text descriptions. This requires the backend database to have the ability to store the data. ORACLE implements this function by providing the LOB field in its Oracle8i.
To facilitate your understanding, let's first introduce some basic concepts:
In ORACLE databases, LOB (Large Objects-Large Objects) is a data type used to store a Large amount of binary and text data (a LOB field can store up to 4 GB of data ). Currently, it can be divided into two types: internal LOB and external LOB. Internal LOB stores data in the database as byte streams. Therefore, many internal LOB operations can be involved in transactions, or they can be backed up and restored just like normal data. Oracle8i supports three types of internal LOB: BLOB (binary data), CLOB (single-byte character data), and NCLOB (multi-byte country character data ). The CLOB and NCLOB types are suitable for storing ultra-long text data, and BLOB fields are suitable for storing a large amount of binary data, such as images, videos, and audios. Currently, Oracle8i only supports one type of external LOB, that is, BFILE. In the database, this type only stores the location information of data in the operating system, and the file form other than the data entity exists in the operating system file system. Therefore, the data of this type is read-only and does not participate in transactions. This type helps users manage a large number of files accessed by external programs.
To facilitate the description below, we assume that a table is created in the database using the following statement.
Create table view_sites_info
(
Site_id NUMBER (3 ),
Audio blob default empty_blob (),
Document clob default empty_clob (),
Video_file bfile default null,
Constraint PK_TAB_view_sites_info primary key (site_id)
);
2. Use and maintenance of LOB
ORACL provides multiple LOB usage and maintenance methods, such as using the PL/SQL DBMS_LOB package, calling the OCI (Oracle Call Interface), using Proc * C/C ++, and using JDBC. The most convenient and effective method is to use PL/SQL to call the DBMS_LOB package. This article will introduce this method.
In Oracle, the data stored in LOB is called the value of LOB. If you use Select to Select a LOB field, the returned value is not the value of LOB, it is the locator of the LOB field (which can be understood as a pointer to the LOB value ). Execute the following SQL statement: