Summary of DBMS_LOB package usage in Oracle

This article focuses on the use of DBMS_LOB packages in Oracle databases and the basic approach to maintaining LOB database types using DBMS_LOB packages. With the development of the society, in the development of the modern information system, the need to store is not only the simple text information, but also includes some pictures and audio-visual materials or extra-long text. For example, the development of a tourism information system, each of the attractions have a wealth of pictures, audio-visual materials and a large number of text introduction. This requires the backend database to have the ability to store this data. Oracle Corporation has implemented this functionality in its oracle8i by providing LOB fields.

In an Oracle database, a LOB large object type 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 types: internal LOB and external lob. An internal LOB stores data in the form of a byte stream inside the database. As a result, many operations on internal lobs can participate in transactions, or they can be backed up and restored as normal data. Oracle8i supports three types of internal lob:blob (binary data), CLOB (single-byte character data), NCLOB (multibyte-country character data). The CLOB and NCLOB types are suitable for storing very long text data, and BLOB word MSA is used to store large amounts of binary data, such as video, audio, and so on. Currently, Oracle8i supports only one external LOB type, the bfile type. Within a database, this type stores only the location information of the data in the operating system, while 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 transactions. This type helps users to manage a large number of files accessed by external programs.

To facilitate the narrative below, we assume that the following statements are used in a table was created in the database .

123456

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

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. The most convenient and effective way is to invoke the Dbms_lob package using PL/SQL, which is described in this article. In Oracle, a value stored in an lob that is called a lob, such as selecting a LOB field with SELECT, returns a value other than the LOB, but the locator for that LOB field (which can be understood as a pointer to the LOB value). If you execute the following SQL statement:

12345678

declare &NBSP;&NBSP; Audio_info blob; begin &NBSP;&NBSP; select  audio      into  audio_info &NBSP;&NBSP;&NBSP;&NBSP; from  < Code class= "SQL Plain" >view_sites_info &NBSP;&NBSP;&NBSP; where  site_id = 100; end

The LOB locator, not the LOB value, is stored in the Audio_info variable. To access and maintain the value of a lob, it must be done through its locator. All functions and procedures provided in the DBMS_LOB package are taken as parameters with the LOB locator.

The following procedures are primarily provided in the DBMS_LOB package for the user to maintain internal LOB fields:

APPEND: Adds content from the source lob to the destination lob.

Close: Closes the lob that is already open.

Createtemporary: Creates a temporary lob in the user's temporary table space.

FileClose: Close the OS file that is pointing to the open bfile Locator.

Filecloseall: Closes all bfile files that are already open for the current session.

FileExists: Determine if the OS file for File_loc exists, 1: exists. 0: not present.

Filegetname: Gets the directory alias and file name that corresponds to the bfile locator.

Fileisopen: Determine if the OS file for the bfile is open.

Freetemporary: Releases the temporary LOB in the default temporary table space.

FILEOPEN: Open file.

Getchunksize: When a table with Clob/blob columns is established, specifying the chunk parameter allows you to specify the number of bytes to be allocated for manipulating the LOB ( an integer multiple of the database size) By default to the size of the data block.

Copy: Copies data from the source LOB to the destination lob.

ERASE: Deletes all or some of the contents of the LOB.

TRIM: Reduces the LOB value to the specified length.

Write: Writes data to the LOB.

INSTR: Returns the specific position of the specified style data that occurs n times from the point at which the LOB has been offset.

Idopen: Determines whether the LOB is open, open: 1, not open: 0.

IsTemporary: Determines whether the locator is a temporary lob.

LoadFromFile: Copies some or all of the contents of the bfile to the target LOB variable.

Loadblobfromfile: Loads the bfile data into the BLOB and obtains the most recent offset position after loading.

Open: Opens Lob,open_mode (read-only: Dbms_lob.lob_readonly, write: Dbms_lob.lob_readwrite).

COMPARE: Compares some or all of the values of two lobs of the same data type.

GetLength: Gets the length of the lob.

READ: Reads the data from the LOB.

SUBSTR: Use the same method as the character-handling function SUBSTR.

Writeappend: Writes the buffer data to the LOB trailer.

Let's use the most commonly used reading and writing examples to describe the usage of these procedures in detail. First introduce the writing process. The syntax for this procedure is:

12345678

PROCEDUREWRITE(lob_loc INOUTBLOB,                amount  INBINARY_INTEGER,                offset  ININTEGER,                buffer  INRAW);PROCEDURE WRITE(lob_loc INOUTCLOB CHARACTERSETany_cs,                amount  INBINARY_INTEGER,                offset  ININTEGER,                buffer  INVARCHAR2 CHARACTERSETlob_loc%charset);

The meanings of each parameter are:

Lob_loc: The LOB locator to write.

Amount: The number of bytes written to the LOB.

Offset: Specifies the offset at which to start the operation.

Buffer: Specifies the buffers for the write operation.

The following code is an example of using this procedure to write data to a LOB field:

1234567891011121314151617181920

DECLARE  lobloc CLOB;  buffer VARCHAR2(2000);  amount NUMBER := 20;  offset NUMBER := 1;BEGIN  --初始化要写入的数据     buffer := ‘This is a writing example‘;  amount := length(buffer);  SELECTdocument    INTOlobloc --   获取定位器并锁定行       FROMview_sites_info   WHEREsite_id = 100     FORUPDATE;  dbms_lob.write(lobloc,                 amount,                 1,                 buffer);  COMMIT;END;

It is particularly noted that:

(1) Be sure to use the SELECT statement to retrieve the locator and lock the row with the FOR UPDATE clause before calling the write process, or you cannot update the LOB.

(2) The write process starts at offset specified, writes data of length amount to the LOB, and any data in the original LOB within that range will be overwritten.

(3) The maximum capacity of a buffer is 32767 bytes, so it needs to be called multiple times when writing large amounts of data.

The following is an introduction to the reading process, the syntax of the process is:

12345678

PROCEDUREREAD(lob_loc INBLOB,               amount  INOUT BINARY_INTEGER,               offset  ININTEGER,               buffer  OUTRAW);PROCEDUREREAD(lob_loc IN CLOB CHARACTERSETany_cs,               amount  INOUTBINARY_INTEGER,               offset  ININTEGER,               buffer  OUTVARCHAR2 CHARACTERSETlob_loc%charset);

The meanings of each parameter are:

Lob_loc: The LOB locator to read.

Amount: The number of bytes to read.

Offset: The amount at which to start the read operation.

Buffer: Buffers that store the result of a read operation .

The following code demonstrates how to use this procedure to read data from a LOB field:

1234567891011121314151617

DECLARE  lobloc CLOB;  buffer VARCHAR2(2000);  amount NUMBER := 2;  offset NUMBER := 6;BEGIN  SELECTdocument    INTO lobloc --获取定位器       FROMlob_store   WHERElob_id = 100;  dbms_lob.read(lobloc,                amount,                offset,                buffer); --读取数据到缓冲区     dbms_output.put_line(buffer); --显示缓冲区中的数据     COMMIT;END;

The append stored procedure is used to add a large object to another large object, at which point the contents of the source large object are all added past. The syntax for the append stored procedure is as follows:

1234	PROCEDUREappend(dest_lob IN OUTNOCOPY BLOB,                 src_lob  INBLOB);PROCEDUREappend(dest_lob INOUTNOCOPY CLOB CHARACTERSETany_cs,                 src_lob  INCLOB CHARACTERSETdest_lob%charset);

Among them, the meanings of each parameter are as follows:

Dest_lob is the locator for the target lob to which the source lob is added.

The Src_lob is the locator for the source LOB .

The Any_cs is used to specify a character set.

Summary of DBMS_LOB package usage in Oracle