Oracle Summary: This article presents an example of a basic approach to using and maintaining LOB data types through DBMS_LOB packages in an Oracle database.
Key words: ORACLE dbms_lob LOB Maintenance
Middle Figure category Number: TP31
1. Introduction
With the development of the society, in the development of the modern information system, it is not only a simple text message, but also some pictures and audio-visual data or an extra long text that needs to be stored. For example, the development of a tourism information system, each site has a wealth of pictures, audio-visual materials and a large number of text introduction. This requires that the background database have the ability to store this data. Oracle has implemented this functionality in its oracle8i by providing LOB fields.
To facilitate the reader's understanding, we first introduce some basic concepts:
In an Oracle database, LOB (Large objects-) is a data type used to store large amounts of binary and textual data (a LOB field can store up to 4GB of data). Currently, it is divided into two categories: internal LOB and external lob. An internal LOB stores data as a stream of bytes inside the database. As a result, many of the internal LOB operations can participate in the transaction, or they can be backed up and restored like normal data. Oracle8i supports three types of internal lob:blob (binary data), CLOB (single-byte character data), NCLOB (multibyte country character data). Where the CLOB and NCLOB types apply to storing very long text data, blob word Zhi is used to store large amounts of binary data, such as images, video, audio, and so on. Currently, Oracle8i supports only one external LOB type, the bfile type. Within a database, the type stores only the location information of the data in the operating system, and the entity of the data exists in the file system of the operating system as an external file. Thus, the data represented by the type is read-only and does not participate in the transaction. This type can help users manage a large number of files accessed by external programs.
To facilitate the narrative 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 a variety of ways to use and maintain lobs, such as using Pl/sql Dbms_lob packages, calling OCI (Oracle call Interface), using proc * C/C + +, using JDBC, and so on. One of the most convenient and effective is to use Pl/sql to invoke the Dbms_lob package, this article will introduce this method.
In Oracle, a value stored in a lob that is called a lob, such as selecting a LOB field using SELECT, returns not the LOB value, but the locator of the LOB field (which can be interpreted as a pointer to a LOB value). For example, execute the following SQL statement:
Delcare
Audio_info BLOB;
Bengin
SELECT audio into Audio_info from View_sites_info
WHERE site_id=100;
End;
/
The LOB locator, not the LOB value, is stored in the Audio_info variable. To access and maintain operations on a lob's value, it must be done through its locator. All functions and procedures provided in the DBMS_LOB package take the LOB locator as an argument.
2.1 Internal LOB
The Dbms_lob package mainly provides the following procedures for user maintenance of internal LOB fields.
APPEND () Adds the contents of the source LOB to the destination LOB
Copy () copying data from the source LOB to the destination LOB
ERASE () Delete all or part of the LOB
TRIM () reduces LOB values to a specified length
Write () writes data to the LOB
COMPARE () compares some or all of the values of lobs with two homogeneous data types
GetLength () Gets the length of the LOB
Read () reads the data from the LOB
Let's use the most common reading and writing examples to describe the usage of these procedures in detail.
First, introduce the writing process. The syntax for this procedure is:
PROCEDURE WRITE (
Lob_loc in Out BLOB,
Amount in Binary_integer,
Offset in INTEGER,
Buffer in RAW);
PROCEDURE WRITE (
Lob_loc in Out CLOB CHARACTER SET Any_cs,
Amount in Binary_integer,
Offset in INTEGER,
Buffer in VARCHAR2 CHARACTER SET lob_loc%charset);
The meaning of each parameter is:
Lob_loc: LOB Locator to write
Amount: Writes the number of bytes in the LOB
Offset: Specifies the offset of the start operation
Buffer: Specifies the write-operation buffers
The following code is an example of using this procedure to write data to a LOB field.
DECLARE
Lobloc CLOB;
Buffer VARCHAR2 (2000);
Amount Number: = 20;
Offset number: = 1;
BEGIN
--initializes the data to be written
Buffer: = ' This is a writing example ';
Amount: = length (buffer);
SELECT document into Lobloc--get the locator and lock the row
From View_sites_info
WHERE site_id = for UPDATE;
Dbms_lob.write (Lobloc,amount,1,buffer);
COMMIT;
End;
/
The special points to be noted are:
I. Be sure to use the SELECT statement to retrieve the locator and lock the row with the FOR UPDATE clause before calling the write procedure, otherwise you cannot update the LOB;
Ii. The write process starts at the position specified by offset, writes the data of length amount to the LOB, and any data in the original LOB that is in the range is overwritten.
The maximum capacity of the buffer is 32767 bytes, so the process needs to be called multiple times when writing large amounts of data.
Here is a brief introduction to the reading process:
The syntax for this procedure is:
PROCEDURE READ (
Lob_loc in BLOB,
Amount in Out Binary_integer,
Offset in INTEGER,
Buffer out RAW);
PROCEDURE READ (
Lob_loc in CLOB CHARACTER SET Any_cs,
Amount in Out Binary_integer,
Offset in INTEGER,
Buffer out VARCHAR2 CHARACTER SET lob_loc%charset);
The meaning of each parameter is:
Lob_loc: LOB Locator to read
Amount: Number of bytes to read
Offset: Offsets from start read operation
