Lob data operations in Oracle

Use and Maintenance of Lob fields in Oracle

This document 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 the Blob field is suitable for storing a large amount of binary data, such as, video, and audio. 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:

Delcare

Audio_info blob;

Bengin

Select audio into audio_info from view_sites_info

Where site_id = 100;

End;

/

The lob positioner is stored in the audio_info variable, instead of the lob value. To access and maintain a lob value, you must use its positioner. All functions and procedures provided in the dbms_lob package use the lob positioner as parameters.

2.1 lob internally

The dbms_lob package mainly provides the following processes for users to maintain internal lob fields.

Append (): add the content in the source lob to the target lob.

Copy () from the source lob to the destination lob

Erase () deletes all or part of Lob content

Trim () reduces the lob value to the specified length

Write () write data to lob

Compare () checks whether the partial or all values of two lobs of the same data type are the same

Getlength () gets the length of the lob.

Read () read data from lob

The following describes how to use the most common read and write processes in detail.

First, we will introduce the write process. The syntax of this process 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 meanings of parameters are as follows:

Lob_loc: Lob positioner to be written

Amount: number of bytes written to lob

Offset: Specifies the offset of the Start operation.

Buffer: Specifies the buffer for write operations.

The following code uses this process to write data to the lob field.

Declare

Lobloc clob;

Buffer varchar2 (2000 );

Amount number: = 20;

Offset number: = 1;

Begin

-- Initialize 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 = 100 for update;

Dbms_lob.write (lobloc, amount, 1, buffer );

Commit;

End;

/

Note:

I. before calling the write process, you must use the SELECT statement to retrieve the locator and use the for update clause to lock the row. Otherwise, the lob cannot be updated;

II. The write process starts from the position specified by the offset and writes data with a length of amount to the lob. Any data in the original lob within this range will be overwritten.

III. The maximum buffer capacity is 32767 bytes, so this process needs to be called multiple times when writing a large amount of data.

Next we will introduce the read process:

The syntax of this process 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 meanings of parameters are as follows:

Lob_loc: the lob positioner to read

Amount: number of bytes to read

Offset: the offset from the read operation.

Buffer: buffer for storing read operation results

The following code demonstrates how to use this process to read data from the lob field:

Declare

Lobloc clob;

Buffer varchar2 (2000 );

Amount number: = 2;

Offset number: = 6;

Begin

Select document into lobloc -- get the Locator

From lob_store

Where lob_id = 100;

Dbms_lob.read (lobloc, amount, offset, buffer); -- read data to the buffer zone

Dbms_output.put_line (buffer); -- display data in the buffer

Commit;

End;

/

After the code is executed, the following information is output:

Is