|
MANIPULATING LARGE OBJECTS USING DBMS_LOB PACKAGE
Overview
--------
This bulletin describes the DBMS_LOB package which provides functions and procedures allowing the manipulation of specific parts, as well as complete internal LOBs and read-only operations on BFILEs.
Introduction
------------
Databases have long been used to store large objects. Oracle 8 significantly enhances this feature by introducing the Large Object(LOB) datatypes. LOBs can be broadly categorized as Internal LOBs and External LOBs.
Internal LOBS can store binary data (BLOBs), single-byte character data (CLOBs), or multi-byte character data (NCLOBs). Internal LOBs are stored in the database tablespaces in a way that optimizes space and provides efficient access. Internal LOBs participate in the transaction model of the server.
External LOBs (henceforth referred to as BFILES), are stored in operating system files outside the database tablespaces. These LOBs do not participate in transactions.
Data stored in a LOB is called the LOB's value. To the Oracle8 Server, a LOB's value is unstructured and cannot be queried against. LOBs can be stored along with other row data or separate from row data. Regardless of how the data is stored, every LOB has a locator associated with it which can be viewed as a handle or pointer to the actual location. Selecting a LOB returns the LOB locator instead of the LOB value. Two new special functions in Oracle8 SQL DML, EMPTY_BLOB() and EMPTY_CLOB(), allow initialization of NULL or non-NULL LOB columns to empty.
PL/SQL provides a mechanism to manipulate these LOBs via the DBMS_LOB package. The DBMS_LOB package provides functions and procedures which allow manipulation of specific parts as well as complete internal LOBs and read-only operations on BFILEs. All DBMS_LOB routines work based on LOB locators. This bulletin
assumes that readers have some experience working with LOBs, i.e. creating tables with LOB columns, initializing LOBs either EMPTY_CLOB() or EMPTY_BLOB(), and using the constructors to initialize the LOBs if the tables are defined on object types.
Cautionary Note
---------------
The examples presented in this note make use of DBMS_OUTPUT and/or UTL_FILE packages. Both packages have line and buffer limitations which result in an exception being raised if the size of the data output exceeds those limitations.
DDL for LOBs
------------
The LOB definition can involve CREATE TYPE and CREATE TABLE statements. For example, the following statement specifies a CLOB, BLOB, and BFILE within a datatype lob_type:
CREATE TYPE lob_type AS object (
lob_id NUMBER(3),
video_clip BLOB,
document CLOB ,
some_file BFILE );
This DDL creates an object table of lob_type where each row is an instance of lob_type data:
CREATE TABLE lob_store OF lob_type;
The following statement stores LOBs in a relational table, as opposed to an object table as in the preceding statement:
CREATE TABLE lob_store
( lob_id NUMBER(3),
video_clip BLOB DEFAULT empty_blob(),
document CLOB DEFAULT NULL,
some_file BFILE DEFAULT NULL);
DML for LOBs
------------
The PL/SQL block below populates the table LOB_STORE with 10 LOB IDs which defaults to initializing the video_clip to empty, and the document and some_file to null.
DECLARE
loop_count INTEGER;
BEGIN
loop_count := 1;
WHILE loop_count <= 10 LOOP
INSERT INTO lob_store (lob_id) VALUES (loop_count);
loop_count := loop_count + 1;
END LOOP;
UPDATE lob_store SET video_clip=utl_raw.cast_to_raw('0123456789'),
document = 'abcdefgh' where lob_id=2;
UPDATE lob_store SET video_clip=utl_raw.cast_to_raw('7777777'),
document = 'ijklmn' where lob_id=3;
UPDATE lob_store SET video_clip=empty_blob(),
document = empty_clob() where lob_id=4;
END;
/
The examples listed in this bulletin are based on the relational table LOB_STORE described in the DDL section of the bulletin and on the data inserted in the table using the PL/SQL block above.
Subprograms in the DBMS_LOB Package
-----------------------------------
The more commonly used procedures and functions contained in the DBMS_LOB package can be broadly categorized as follows:
a) The routines that can modify BLOB, CLOB, and NCLOB values are:
APPEND() - append the contents of the source LOB to the destination LOB
COPY() - copy all or part of the source LOB to the destination
LOB ERASE() - erase all or part of a LOB
LOADFROMFILE() - load BFILE data into an internal LOB
TRIM() - trim the LOB value to the specified shorter length
WRITE() - write data to the LOB from a specified offset
b) The routines that read or examine LOB values are:
COMPARE() - comapre two entire or part of two lobs
GETLENGTH() - get the length of the LOB value
INSTR() - return the matching position of the nth occurrence of the pattern in the LOB
READ() - read data from the LOB starting at the specified offset
SUBSTR() - return part of the LOB value starting at the specified offset
c) The read-only routines specific to BFILEs are:
FILECLOSE() - close the file
FILECLOSEALL()- close all previously opened files
FILEEXISTS() - check if the file exists on the server
FILEGETNAME() - get the directory alias and file name
FILEISOPEN() - check if the file was opened using the input BFILE locators
FILEOPEN() - open a file
The remainder of this bulletin explains each of the functions/procedures in the DBMS_LOB package and contains an example demonstrating how thesesubprograms can be used to achieve the desired result.
Manipulating Internal LOBS (BLOB, CLOB, NCLOB) with DBMS_LOB Package
--------------------------------------------------------------------
I) Finding the length of the variable/column which is declared of LOB
datatype.
Syntax
FUNCTION GETLENGTH (
lob_loc IN BLOB)
RETURN INTEGER;
FUNCTION GETLENGTH (
lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;
FUNCTION GETLENGTH (
lob_loc IN BFILE)
RETURN INTEGER;
Example:
SQL> select dbms_lob.getlength(document) from lob_store where lob_id = 1;
DBMS_LOB.GETLENGTH(DOCUMENT)
----------------------------
The length is not defined because the document was initialized to null by default. The document column needs to be initialized using the built in special function EMPTY_CLOB() to empty.
SQL> update lob_store set document = empty_clob() where lob_id = 1;
1 row updated.
SQL> select dbms_lob.getlength(document) from lob_store where lob_id = 1;
DBMS_LOB.GETLENGTH(DOCUMENT)
----------------------------
0
II) Populating an internal LOB. The DBMS_LOB.WRITE procedure can be used to insert values into a LOB column of a table. WRITE() overwrites any data that already exists in the LOB at the offset, for the length specified.
Syntax
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);
Example:
This PL/SQL code demonstrates how a LOB column can be populated.
DECLARE
lobloc CLOB;
buffer VARCHAR2(32000);
amount NUMBER := 20;
offset NUMBER := 1;
BEGIN
--Initialize buffer with data to be inserted
buffer := 'abcdefghijklmnopqrstuvwxyz';
amount := length(buffer);
dbms_output.put_line(buffer);
dbms_output.put_line(to_char(amount));
SELECT document INTO lobloc -- get LOB handle
FROM lob_store
WHERE lob_id = 1 FOR UPDATE;
dbms_lob.write(lobloc,amount,1,buffer);
COMMIT;
END;
/
SQL> SELECT dbms_lob.getlength(document) FROM lob_store WHERE lob_id = 1;
DBMS_LOB.GETLENGTH(DOCUMENT)
----------------------------
26
III) Reading from a LOB column can be achieved by using the DBMS_LOB.READ procedure. This procedure allows piecewise retrieval as well as retrieval of the entire LOB into a buffer.
Syntax
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);
PROCEDURE READ (
lob_loc IN BFILE,
amount IN OUT BINARY_INTEGER,
offset IN INTEGER,
buffer OUT RAW);
Example: This example shows how a piece wise fetch can be accomplished
using DBMS_LOB.READ procedure.
DECLARE
lobloc CLOB;
buffer VARCHAR2(32000);
amount NUMBER := 10;
amount_in_buffer NUMBER;
offset NUMBER := 4;
BEGIN
--Initialize buffer with data to be inserted
SELECT document INTO lobloc -- get LOB handle
FROM lob_store
WHERE lob_id = 2;
dbms_lob.read(lobloc,amount,offset,buffer);
--using length built-in function to find the length of the buffer
amount_in_buffer := length(buffer);
dbms_output.put_line(buffer);
dbms_output.put_line(to_char(amount_in_buffer));
COMMIT;
END;
/
defgh
5
PL/SQL procedure successfully completed.
IV) The overloaded APPEND() procedure appends the complete source LOB to the destination LOB.
Syntax
PROCEDURE APPEND (dest_lob IN OUT BLOB,
src_lob IN BLOB);
PROCEDURE APPEND (dest_lob IN OUT CLOB CHARACTER SET ANY_CS,
src_lob IN CLOB CHARACTER SET
dest_lob%CHARSET);
Example:
CREATE OR REPLACE PROCEDURE tst_append IS
dblob BLOB;
sblob BLOB;
dclob CLOB;
sclob CLOB;
spos NUMBER;
amt BINARY_INTEGER;
bufb RAW(20);
bufc VARCHAR2(20);
BEGIN
dbms_output.put_line('--------------- APPEND Begin ---------------');
/* append the BLOBs in the lob_store table */
SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 3;
dbms_lob.append(dblob, sblob);
/* append the CLOBs in the lob_store table */
SELECT document INTO dclob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
SELECT document INTO sclob FROM lob_store WHERE lob_id = 3;
dbms_lob.append(dclob, sclob);
/* end Xn */
COMMIT;
/* testing APPEND operation */
amt := 20; spos := 1;
SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 2;
dbms_lob.read(sblob, amt, spos, bufb);
dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));
SELECT document INTO sclob FROM lob_store WHERE lob_id = 2;
dbms_lob.read(sclob, amt, spos, bufc);
dbms_output.put_line('Result CLOB: ' || bufc);
dbms_output.put_line('--------------- APPEND End ---------------');
dbms_output.put_line(' ');
END tst_append;
/
SQL> execute tst_append;
--------------- APPEND Begin ---------------
Result BLOB: 01234567897777777
Result CLOB: abcdefghijklmn
--------------- APPEND End ---------------
PL/SQL procedure successfully completed.
V) The ERASE() procedure allows one to erase a part or the entire LOB depending on the amount and offset parameters passed to the procedure.
The actual number of bytes or characters erased can differ from the number specified in the amount parameter if the end of the LOB parameter has been reached. Erased characters are replaced with zero byte filters for BLOBs and spaces for CLOBs.
CREATE OR REPLACE PROCEDURE tst_erase IS
dblob BLOB;
dclob CLOB;
amt NUMBER;
pos NUMBER;
bufb RAW(10);
bufc VARCHAR2(10);
BEGIN
dbms_output.put_line('--------------- ERASE Begin ---------------');
-- Case 1: erase 5 bytes from the middle of the LOB
-- amt := 5; pos := 3;
-- Case 2: trim 2 bytes from the end of the LOB
amt := 2; pos := 6;
-- Case 3: erase 5 bytes from the beginning of the LOB
-- amt := 5; pos := 1;
-- Case 4: erase from an empty LOB
/* test ERASE for BLOBs */
SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
dbms_lob.erase(dblob, amt, pos);
/* test ERASE for CLOBs */
SELECT document INTO dclob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
dbms_lob.erase(dclob, amt, pos);
/* end the transaction */
COMMIT;
/* checking the ERASE operation */
amt := 10; pos := 1;
SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2;
dbms_lob.read(dblob, amt, pos, bufb);
dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));
SELECT document INTO dclob FROM lob_store WHERE lob_id = 2;
dbms_lob.read(dclob, amt, pos, bufc);
dbms_output.put_line('Result CLOB: ' || bufc);
dbms_output.put_line('--------------- ERASE End ---------------');
dbms_output.put_line(' ');
end tst_erase;
/
SQL> execute tst_erase
--------------- ERASE Begin ---------------
Result BLOB: 01234
Result CLOB: abcde h
--------------- ERASE End ---------------
When using DBMS_LOB.ERASE on BLOBs, the second parameter to the DBMS_LOB.ERASE procedure is not taken into consideration. It from the offset to the end of the data. This is a known bug (BUG:554028).
VI) The COPY procedure allows one to copy part or all of a source internal LOB into a destination internal LOB. The offsets for both the source and destination LOBs can be specified.
Syntax
PROCEDURE COPY (
dest_lob IN OUT BLOB,
src_lob IN BLOB,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);
PROCEDURE COPY (
dest_lob IN OUT CLOB CHARACTER SET ANY_CS,
src_lob IN CLOB CHARACTER SET dest_lob%CHARSET,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);
Example: This example demonstrates how part of the LOBs in row with
lob_id 2 can be copied into row with lob_id 4;
CREATE OR REPLACE PROCEDURE tst_copy IS
dblob BLOB;
sblob BLOB;
dclob CLOB;
sclob CLOB;
amt NUMBER;
dpos NUMBER;
spos NUMBER;
bufb RAW(30);
bufc VARCHAR2(30);
BEGIN
dbms_output.put_line('--------------- COPY Begin ---------------');
dbms_output.put_line(' ');
-- Case 1: copy row 3 LOBs to row 4 LOBs
amt := 5; dpos := 1; spos := 3;
-- test COPY for BLOBs
SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 4 FOR UPDATE;
SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 2;
dbms_lob.copy(dblob, sblob, amt, dpos, spos);
COMMIT;
-- test COPY for CLOBs
SELECT document INTO dclob FROM lob_store WHERE lob_id = 4 FOR UPDATE;
SELECT document INTO sclob FROM lob_store WHERE lob_id = 2;
dbms_lob.copy(dclob, sclob, amt, dpos, spos);
COMMIT;
-- check of COPY operation
SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 4;
dbms_lob.read(sblob, amt, dpos, bufb);
dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));
SELECT document INTO sclob FROM lob_store WHERE lob_id = 4;
dbms_lob.read(sclob, amt, dpos, bufc);
dbms_output.put_line('Result CLOB: ' || bufc);
dbms_output.put_line('--------------- COPY End ---------------');
dbms_output.put_line(' ');
EXCEPTION
WHEN NO_DATA_FOUND
THEN dbms_output.put_line('COPY: no_data_found error');
END tst_copy;
/
SQL> exec tst_copy
--------------- COPY Begin ---------------
Result BLOB: 23456
Result CLOB: cdefg
--------------- COPY End ---------------
PL/SQL procedure successfully completed.
VII) The COMPARE() function allows comparison between two entire LOBs or parts of the LOBs. COMPARE() returns a zero if the data exactly matches over the specified range. Otherwise, it returns a non-zero value.
Syntax
FUNCTION COMPARE (
lob_1 IN BLOB,
lob_2 IN BLOB,
amount IN INTEGER := 4294967295,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
FUNCTION COMPARE (
lob_1 IN CLOB CHARACTER SET ANY_CS,
lob_2 IN CLOB CHARACTER SET lob_1%CHARSET,
amount IN INTEGER := 4294967295,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
Example: If this procedure tst_compare is run immediately after the tst_copy
procedure, the result is similar to that shown in the output of the
execution below.
CREATE OR REPLACE PROCEDURE tst_compare IS
dblob BLOB;
sblob BLOB;
dclob CLOB;
sclob CLOB;
amt NUMBER;
dpos NUMBER;
spos NUMBER;
ret INTEGER;
BEGIN
dbms_output.put_line('--------------- COMPARE Begin ---------------');
dpos := 3; spos := 1;
amt := 5;
/* test COMPARE for BLOBs */
SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2;
SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 4;
ret := -1;
ret := dbms_lob.compare(dblob, sblob, amt, dpos, spos);
dbms_output.put_line('Return value for BLOB: ' || ret);
/* testing the validity of COMPARE for CLOBs */
SELECT document INTO dclob FROM lob_store WHERE lob_id = 2;
SELECT document INTO sclob FROM lob_store WHERE lob_id = 4;
ret := -1;
ret := dbms_lob.compare(dclob, sclob, amt, dpos, spos);
dbms_output.put_line('Return value for CLOB: ' || ret);
dbms_output.put_line('--------------- COMPARE End ---------------');
dbms_output.put_line(' ');
END tst_compare;
SQL> exec tst_compare;
--------------- COMPARE Begin ---------------
Return value for BLOB: 1
Return value for CLOB: 1
--------------- COMPARE End ---------------
PL/SQL procedure successfully completed.
VIII) The TRIM() procedure can be used to trim the value of an internal LOB to a length specified in the newlen parameter.
Syntax
FUNCTION TRIM (
lob_loc IN BLOB,
newlen IN INTEGER);
FUNCTION TRIM (
lob_loc IN CLOB,
newlen IN INTEGER);
Example: The following example shows how the TRIM() procedure can be used to
truncate the value stored in a CLOB to 5.
CREATE OR REPLACE PROCEDURE tst_trim IS
dclob CLOB;
nlen NUMBER;
amt BINARY_INTEGER;
bufc VARCHAR2(5);
BEGIN
dbms_output.put_line('--------------- TRIM Begin ---------------');
nlen := 5;
/* test TRIM for CLOBs */
SELECT document INTO dclob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
dbms_lob.trim(dclob, nlen);
COMMIT;
/* check of TRIM operation */
SELECT document INTO dclob FROM lob_store WHERE lob_id = 2;
bufc := '';
dbms_lob.read(dclob, nlen, 1, bufc);
dbms_output.put_line('Result CLOB: ' || bufc);
dbms_output.put_line('--------------- TRIM End ---------------');
dbms_output.put_line(' ');
END tst_trim;
/
SQL> exec tst_trim
--------------- TRIM Begin ---------------
Result CLOB: abcde
--------------- TRIM End ---------------
PL/SQL procedure successfully completed.
|
