Table of Contents
This chapter briefly introduces the DBMS_LOB package and describes how to use the procedures and functions included of the package.
DBMS_LOB is a package that provides various operations for BLOB, CLOB, or BFILE column data. Using the procedures and functions in this package, users can read and write all or part of large object data.
The following information needs to be understood when using DBMS_LOB:
Privileges of the caller
This package can be executed with the privileges of the caller. If invoked by other procedures or functions, it is executed using the privileges of the executor of the procedure or function.
Parameter length and offset units of procedures and functions
If the destination data type is BLOB, the units are in bytes, and if it is CLOB, the units are in characters. The return value of the function uses the same units.
Valid parameter value range of procedures and functions
The offset, length, and size of LOB data must always be greater than 1 and less than the LOBMAXSIZE constant value.
The LOBMAXSIZE constant has the value 18446744073709551615 (2^64 – 1).
If the offset, length, or size of LOB data is less than 1 or greater than LOBMAXSIZE, an INVALID_ARGVAL exception will occur.
CLOB column data
This type saves strings in UTF-16 Unicode character set, and each letter is represented with two bytes.
The size should be less than the value of the LOBMAXSIZE constant divided by 2.
If the size of a CLOB data is greater than this value, an INVALID_ARGVAL exception will occur.
APPEND, COPY, TRIM, WRITE
These are the parameters of a procedure or function that is used to update LOB data, and the sum of the size and offset values must not exceed the maximum value (the value of the LOBMAXSIZE constant). If it is exceeded, an exception will occur. To update LOB data, its row must be locked since the procedure or function does not automatically set locks.
READ, COMPARE, INSTR, SUBSTR
These read-only procedures and functions read only until the end of the LOB data.
COMPARE, INSTR, SUBSTR
These functions perform string-pattern related operations. Wild card characters used with the LIKE operator, such as the percent sign (%) or underscore (_), cannot be used in these parameters.
When using LOB data, the data must be opened using the OPEN procedure, and closed using the CLOSE procedure after use. When LOB data, which is opened using the OPEN procedure, is closed the updates are applied to the database.
When LOB data that was not opened with the OPEN procedure is updated, the update will be immediately applied to the database. In most cases, LOB data updates require a lot of disk activity, so it is more efficient to apply all updates at once rather than at several different times.
If LOB data was opened with the OPEN procedure, it must be closed with the CLOSE procedure before executing a COMMIT statement. If COMMIT is executed when there is still open LOB data, an error will occur. If a ROLLBACK is executed while there is open LOB data, all update operations will be canceled, and all information about the LOB data will be removed.
The following constants are defined in the DBMS_LOB package:
LOBMAXSIZE
The maximum size of LOB data
LOBMAXSIZE CONSTANT BINARY_INTEGER := 18446744073709551615
The data type is BINARY_INTEGER and the maximum size of LOB data is 18446744073709551615.
LOB_READONLY
Specifies whether LOB data is read-only
LOB_READONLY CONSTANT BINARY_INTEGER := 0
The data type is BINARY_INTEGER. 0 specifies read-only mode.
LOB_READWRITE
Specifies read-write mode for LOB data.
LOB_READWRITE CONSTANT BINARY_INTEGER := 1
The data type is BINARY_INTEGER. 1 specifies read-write mode.
FILE_READONLY
Used to open a BFILE.
FILE_READONLY CONSTANT BINARY_INTEGER := 0
The data type is BINARY_INTEGER and only read-only mode is allowed.
The following are the exceptions that are provided by the DBMS_LOB package. For more information, refer to the exception section under “16.3. Procedures” and “16.4. Functions”.
INVALID_ARGVAL
ACCESS_ERROR
NOTEXIST_DIRECTORY
NOPRIV_DIRECTORY
INVALID_DIRECTORY
OPERATION_FAILED
UNOPENED_FILE
OPEN_TOOMANY
This section describes the procedures provided by the DBMS_LOB package, in alphabetical order.
Appends the entire source LOB to the end of the target LOB.
Details about the APPEND procedure are as follows:
Prototype
BLOB type
DBMS_LOB.APPEND
(
dest_lob IN OUT NOCOPY BLOB,
src_lob IN BLOB
);
CLOB type
DBMS_LOB.APPEND
(
dest_lob IN OUT NOCOPY CLOB,
src_lob IN CLOB
);
When transmitting CLOB data, LOB data in the dest_lob and src_lob parameters must use the same character set.
Parameter
| Parameter | Description |
|---|---|
| dest_lob | Target LOB. |
| src_lob | Source LOB. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the target or source LOB is null. |
Example
DECLARE
dest_lob CLOB := 'All''s fair in ';
src_lob CLOB := 'love and war';
BEGIN
DBMS_LOB.APPEND(dest_lob, src_lob);
DBMS_OUTPUT.PUT_LINE('Result = ' || dest_lob);
END;
/
Result = All's fair in love and war
PSM completed
SQL>
Copies all or part of the source LOB to the target LOB. The offsets of the source and target LOB can be specified. If the offset of the target LOB is shorter than the length of the target LOB, the source data will overwrite any existing data after the offset.
Reversely, if the offset of the target LOB is longer than the length of the target LOB, any additional spaces will be filled with zeros for BLOB data or blank spaces for CLOB data.
Details about the COPY procedure are as follows:
Prototype
BLOB type
DBMS_LOB.COPY
(
dest_lob IN OUT NOCOPY BLOB,
src_lob IN BLOB,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1
);
CLOB type
DBMS_LOB.COPY
(
dest_lob IN OUT NOCOPY CLOB,
src_lob IN CLOB,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1
);
When transmitting CLOB data, LOB data in the dest_lob and src_lob parameters must use the same character set.
Parameter
| Parameter | Description |
|---|---|
| dest_lob | Target LOB. |
| src_lob | Source LOB. |
| amount | Number of bytes (BLOBs) or characters (CLOBs) to copy. |
| dest_offset | Offset in the target LOB, in bytes for BLOBs or in characters for CLOBs. |
| src_offset | Offset in the source LOB, in bytes for BLOBs or in characters for CLOBs. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when one or more input parameters are null. |
| INVALID_POS | Occurs when the value of src_offset or dest_offset is less than 1 or greater than LOBMAXSIZE. |
| INVALID_LEN | Occurs when the value of amount is less than 1 or greater than LOBMAXSIZE. |
Example
DECLARE
dest_lob CLOB := 'It you would be loved, ';
src_lob CLOB := 'be worthy to be loved';
BEGIN
DBMS_LOB.COPY(dest_lob, src_lob, length(src_lob),
length(dest_lob) + 1, 1);
DBMS_OUTPUT.PUT_LINE('Result = ' || dest_lob);
END;
/
Result = It you would be loved, be worthy to be loved
PSM completed
SQL>
Converts a CLOB data to BOLB data.
Details about the CONVERTTOBLOB procedure are as follows:
Prototype
DBMS_LOB.CONVERTTOBLOB
(
dest_lob IN OUT NO COPY BLOB,
src_lob IN CLOB,
amount IN INTEGER,
dest_offset IN OUT INTEGER,
src_offset IN OUT INTEGER,
blob_csid IN NUMBER,
lang_context IN OUT INTEGER,
warning OUT INTEGER
);
Parameter
| Parameter | Description |
|---|---|
| dest_lob | Target LOB locator. |
| src_lob | Source LOB locator. |
| amount | Number of characters to copy. |
| dest_offset | Offset of the target LOB data. Output value is the last address where the target LOB data was saved. (Unit: byte (BLOB data)) |
| src_offset | Offset of the source LOB data. Output value is the last address where the source LOB data was read. (Unit: character (CLOB data)) |
| blob_csid | ID of the character set used to save the converted BLOB data. |
| lang_context | Unused. |
| warning | Unused. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when one or more input parameters are NULL. |
Example
declare
src_clob clob;
dest_blob blob;
dest_clob clob;
amount integer;
dest_offset integer;
src_offset integer;
blob_csid integer;
lang_context integer;
warning integer;
begin
src_clob := 'abcdefghijklmn';
dbms_lob.createtemporary(dest_blob, false);
dbms_lob.createtemporary(dest_clob, false);
amount := 10;
dest_offset := 1;
src_offset := 1;
blob_csid := 0;
lang_context := 0;
warning := 0;
dbms_lob.CONVERTTOBLOB(dest_blob, src_clob, amount,
dest_offset, src_offset, blob_csid,
lang_context, warning);
end;
/
PSM completed
SQL>
Converts a BLOB data to COLB data.
Details about the CONVERTTOCLOB procedure are as follows:
Prototype
DBMS_LOB.CONVERTTOCLOB
(
dest_lob IN OUT NO COPY CLOB,
src_lob IN BLOB,
amount IN INTEGER,
dest_offset IN OUT INTEGER,
src_offset IN OUT INTEGER,
blob_csid IN NUMBER,
lang_context IN OUT INTEGER,
warning OUT INTEGER
);
Parameter
| Parameter | Description |
|---|---|
| dest_lob | Target LOB locator. |
| src_lob | Source LOB locator. |
| amount | Number of characters to copy. |
| dest_offset | Offset in the target LOB data. Output value is the last address where the target LOB data was saved. (Unit: byte (CLOB data)) |
| src_offset | Offset in the source LOB data. Output value is the last address where the source LOB data was read. (Unit: character (BLOB data)) |
| blob_csid | ID of a character set used to save the converted CLOB data. |
| lang_context | Unused. |
| warning | Unused. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when one or more input parameters are NULL. |
Example
declare
src_clob clob;
dest_blob blob;
dest_clob clob;
amount integer;
dest_offset integer;
src_offset integer;
blob_csid integer;
lang_context integer;
warning integer;
begin
src_clob := 'abcdefghijklmn';
dbms_lob.createtemporary(dest_blob, false);
dbms_lob.createtemporary(dest_clob, false);
amount := 10;
dest_offset := 1;
src_offset := 1;
blob_csid := 0;
lang_context := 0;
warning := 0;
dbms_output.put_line('org clob : ' || src_clob);
dbms_lob.CONVERTTOBLOB(dest_blob, src_clob, amount,
dest_offset, src_offset, blob_csid,
lang_context, warning);
dbms_output.put_line('dest offset : ' || dest_offset);
dbms_output.put_line('srct offset : ' || src_offset);
amount := 20;
dest_offset := 1;
src_offset := 1;
blob_csid := 0;
lang_context := 0;
warning := 0;
dbms_lob.CONVERTTOCLOB(dest_clob, dest_blob, amount,
dest_offset, src_offset, blob_csid,
lang_context, warning);
dbms_output.put_line('dest clob : ' || dest_clob);
dbms_output.put_line('dest offset : ' || dest_offset);
dbms_output.put_line('srct offset : ' || src_offset);
end;
/
org clob : abcdefghijklmn
dest offset : 11
srct offset : 11
dest clob : abcdefghij
dest offset : 11
srct offset : 11
PSM completed.
SQL>
Creates a temporary CLOB or BLOB.
Details about the CREATETEMPORARY procedure are as follows:
Prototype
BLOB type
DBMS_LOB.CREATETEMPORARY
(
lob IN OUT NOCOPY BLOB,
cache IN BOOLEAN,
dur IN PLS_INTEGER := 10
);
CLOB type
DBMS_LOB.CREATETEMPORARY
(
lob IN OUT NOCOPY CLOB,
cache IN BOOLEAN,
dur IN PLS_INTEGER := 10
);
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB data. |
| cache | Option to save LOB data in the buffer cache. |
| dur | This parameter does not currently support adjusting the lifetime of the CREATETEMPORARY procedure. By default, the CREATETEMPORARY procedure is automatically deleted after the session ends. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the cache parameter is NULL. |
Example
DECLARE
lob_1 CLOB;
lob_2 CLOB := 'tibero';
BEGIN
DBMS_LOB.CREATETEMPORARY(lob_1, false);
DBMS_LOB.APPEND(lob_1, lob_2);
DBMS_OUTPUT.PUT_LINE(lob_1);
END;
/
tibero
PSM completed
SQL>
Deletes all or part of a LOB. The deleted space is padded with zeros (BLOBs) or blank spaces (CLOBs). If the target LOB is shorter than the sum of the sizes of the offset and the space to be deleted, the size of the actual deleted space can be smaller than the specified size. The actual size of the deleted space is stored in the amount parameter.
Details about the ERASE procedure are as follows:
Prototype
BLOB type
DBMS_LOB.ERASE
(
lob IN OUT NOCOPY BLOB,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER := 1
);
CLOB type
DBMS_LOB.ERASE
(
lob IN OUT NOCOPY CLOB,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER := 1
);
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
| amount | Number of bytes (BLOBs) or characters (CLOBs) to delete. |
| offset | Offset in bytes (BLOBs) or characters (CLOBs) from which to delete. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when one or more input parameters is null. |
| INVALID_POS | Occurs when the value of offset is less than 1 or greater than LOBMAXSIZE. |
| INVALID_LEN | Occurs when the value of amount is less than 1 or greater than LOBMAXSIZE. |
Example
DECLARE
lob CLOB := 'Tmaxsoft Tibero';
amount NUMBER := 7;
BEGIN
DBMS_OUTPUT.PUT_LINE('Length of original LOB = ' || length(lob));
DBMS_LOB.ERASE(lob, amount, 9);
DBMS_OUTPUT.PUT_LINE('Value of erased LOB = ' || lob);
DBMS_OUTPUT.PUT_LINE('Length of erased LOB = ' || length(lob));
END;
/
Length of original LOB = 15
Value of erased LOB = Tmaxsoft
Length of erased LOB = 15
PSM completed
SQL>
Closes a file using the file locator.
Details about the FILECLOSE procedure are as follows:
Prototype
DBMS_LOB.FILECLOSE
(
file_loc IN OUT NOCOPY BFILE
);
Parameter
| Parameter | Description |
|---|---|
| file_loc | File locator of the target file to close. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the file locator is NULL. |
Example
Refer to the FILEOPEN example.
Closes all open files using the DBMS_LOB package in the current session.
Details about the FILECLOSEALL are as follows.
Prototype
DBMS_LOB.FILECLOSEALL;
Example
BEGIN
DBMS_LOB.FILECLOSEALL;
END;
/
Gets the name of the file and its directory alias from the file locator.
Details about the FILEGETNAME are as follows:
Prototype
DBMS_LOB.FILEGETNAME
(
file_loc IN BFILE,
dir_alias OUT VARCHAR2,
filename OUT VARCHAR2
);
Parameter
| Parameter | Description |
|---|---|
| file_loc | File locator. |
| dir_alias | Directory alias read from the file locator. |
| filename | File name read from the file locator. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the file locator is NULL. |
Example
declare
f1 bfile;
dir_alias varchar(128);
filename varchar(128);
begin
f1 := bfilename('BFILETESTDIR', 'test001.bin');
dbms_lob.filegetname(f1, dir_alias, filename);
dbms_output.put_line(dir_alias);
dbms_output.put_line(filename);
end;
/
BFILETESTDIR
test001.bin
Opens a file as read-only binary mode using the file locator.
Details about the FILEOPEN are as follows:
Prototype
DBMS_LOB.FILEOPEN
(
file_loc IN OUT NOCOPY BFILE,
open_mode IN BINARY_INTEGER := file_readonly
);
Parameter
| Parameter | Description |
|---|---|
| file_loc | File locator of the target file. |
| open_mode | Only file_readonly is allowed. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the file locator is NULL. |
| INVALID_ARGVAL | Occurs when open_mode is set to a value other than file_readonly. |
| INVALID_DIRECTORY | Occurs when the directory specified in the file locator is invalid. |
| NOEXIST_DIRECTORY | Occurs when the directory path specified in the file locator does not exist. |
| OPERATION_FAILED | Occurs when the file does not exist in the directory path or cannot be opened. |
Example
create directory BFILETESTDIR as '/mybasepath/bfiletest/';
declare
f utl_file.file_type;
buffer raw(16) := '000102030405060708090A0B0C0D0E0F';
begin
f := utl_file.fopen('BFILETESTDIR', 'test001.bin', 'wb');
utl_file.put_raw(f, buffer);
utl_file.fclose(f);
end;
/
declare
f1 bfile;
amount number;
buffer raw(16);
result raw(16) := '000102030405060708090A0B0C0C0E0F';
begin
f1 := bfilename('BFILETESTDIR', 'test001.bin');
dbms_lob.fileopen(f1, dbms_lob.file_readonly);
amount := 16;
dbms_lob.read(f1, amount, 1, buffer);
dbms_output.put_line(amount);
dbms_output.put_line(buffer);
dbms_lob.fileclose(f1);
end;
/
16
000102030405060708090A0B0C0D0E0F
Deletes a previously created temporary BLOB or CLOB.
Details about the FREETEMPORARY procedure are as follows:
Prototype
BLOB type
DBMS_LOB.FREETEMPORARY
(
lob IN OUT NOCOPY BLOB
);
CLOB type
DBMS_LOB.FREETEMPORARY
(
lob IN OUT NOCOPY CLOB
);
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the input parameter is NULL. |
Example
DECLARE
lob CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(lob, false);
DBMS_LOB.FREETEMPORARY(lob);
END;
/
PSM completed
SQL>
Writes the data from a file to a LOB. The data file must be open, and the LOB must have been created. Character set conversion is not performed in this procedure. (The CLOB file encoding must be UCS2 as CLOB data is stored as UCS2. Otherwise, this procedure will not function correctly).
Details about the LOADFROMFILE procedure are as follows:
Prototypes
BLOB
DBMS_LOB.LOADFROMFILE
(
dest_lob IN OUT NOCOPY BLOB,
src_file IN BFILE,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1
);
CLOB
DBMS_LOB.LOADFROMFILE
(
dest_lob IN OUT NOCOPY CLOB,
src_file IN BFILE,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1
);
Parameters
| Parameter | Description |
|---|---|
| dest_lob | LOB locator. |
| src_file | File handler of the bfile type that will read in LOB data. No character set conversion is performed for BLOB and CLOB version (For CLOB version, only UCS2 encoding is supported for the input text file). |
| amount | Data size to read in. Unit is in bytes for BLOB, and character count for CLOB (A character is 2 bytes for UCS2 encoding). |
| dest_offset | LOB offset. (Default value: 1, base: 1) |
| src_offset | File offset. (Default value: 1, base: 1) |
Exceptions
| Exceptions | Description |
|---|---|
| VALUE_ERROR | Occurs when an input parameter value is invalid. |
Example
SQL> create directory BFILETESTDIR as '/home/tests/test_bfile/';
Directory 'BFILETESTDIR' created.
SQL> DECLARE
f UTL_FILE.FILE_TYPE;
buffer RAW(16) := '000102030405060708090A0B0C0C0E0F';
begin
f := UTL_FILE.FOPEN('BFILETESTDIR', 'test001.bin', 'wb');
UTL_FILE.PUT_RAW(f, buffer);
UTL_FILE.FCLOSE(f);
end;
/
PSM completed.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
f1 BFILE;
bl1 BLOB := HEXTORAW('AAAAAA');
amount NUMBER;
buffer RAW(16);
result RAW(16) := '000102030405060708090A0B0C0C0E0F';
buffer2 RAW(32);
result2 RAW(32) := '000102030405060708090A0B0C0D0E0F';
begin
f1 := BFILENAME('BFILETESTDIR', 'test001.bin');
DBMS_LOB.FILEOPEN(f1, DBMS_LOB.FILE_READONLY);
amount := 16;
DBMS_LOB.LOADFROMFILE(bl1, f1, amount);
DBMS_LOB.READ(bl1, amount, 1, buffer);
DBMS_OUTPUT.PUT_LINE(amount);
DBMS_LOB.FILECLOSE(f1);
end;
/
16
PSM completed.
SQL> DECLARE
lob CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(lob, false);
DBMS_LOB.FREETEMPORARY(lob);
END;
/
PSM completed.
SQL> DECLARE
f UTL_FILE.FILE_TYPE;
buffer RAW(16) := '00610062006300640065006600670068';
BEGIN
f := UTL_FILE.FOPEN('BFILETESTDIR', 'ucs2_file.bin', 'wb');
UTL_FILE.PUT_RAW(f, buffer);
UTL_FILE.FCLOSE(f);
END;
/
PSM completed.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
f1 BFILE;
bl1 CLOB := 'init';
amount NUMBER;
BEGIN
F1 := BFILENAME('BFILETESTDIR', 'ucs2_file.bin');
DBMS_LOB.FILEOPEN(f1, DBMS_LOB.FILE_READONLY);
AMOUNT := 8;
DBMS_LOB.LOADFROMFILE(bl1, f1, amount);
DBMS_OUTPUT.PUT_LINE (bl1);
DBMS_LOB.FILECLOSE(f1);
END;
/
abcdefgh
PSM completed.
SQL>
Writes the data from a file to a BLOB. The data file must be open, and the BLOB must have been created.
Details about the LOADBLOBFROMFILE procedure is as follows:
Prototype
DBMS_LOB.LOADBLOBFROMFILE
(
dest_lob IN OUT NOCOPY BLOB,
src_file IN BFILE,
amount IN INTEGER,
dest_offset IN OUT INTEGER,
src_offset IN OUT INTEGER
);
Parameters
| Parameter | Description |
|---|---|
| dest_lob | BLOB locator. |
| src_file | File handler of the bfile type that will read in BLOB data. No character set conversion. |
| amount | Data size (in bytes) to read in. |
| dest_offset | BLOB offset (base: 1). After the procedure execution, the offset is changed to the last written position. |
| src_offset | File offset (base: 1). After the procedure execution, the offset is changed to the last read position. |
Exceptions
| Exceptions | Description |
|---|---|
| VALUE_ERROR | Occurs when an input parameter value is invalid. |
Example
SQL> SET SERVEROUTPUT ON
SQL> create directory BFILETESTDIR as '/home/tests/test_bfile/';
Directory 'BFILETESTDIR' created.
SQL> DECLARE
f UTL_FILE.FILE_TYPE;
buffer RAW(16) := '000102030405060708090A0B0C0C0E0F';
begin
f := UTL_FILE.FOPEN('BFILETESTDIR', 'test001.bin', 'wb');
UTL_FILE.PUT_RAW(f, buffer);
UTL_FILE.FCLOSE(f);
end;
/
PSM completed.
SQL> DECLARE
f1 BFILE;
bl1 BLOB := HEXTORAW('AAAAAA');
amount NUMBER;
buffer RAW(16);
result RAW(16) := '000102030405060708090A0B0C0C0E0F';
buffer2 RAW(32);
result2 RAW(32) := '000102030405060708090A0B0C0D0E0F';
src_ofst NUMBER;
dst_ofst NUMBER;
begin
f1 := BFILENAME('BFILETESTDIR', 'test001.bin');
DBMS_LOB.FILEOPEN(f1, dbms_lob.file_readonly);
amount := 16;
src_ofst := 1;
dst_ofst := 1;
DBMS_LOB.LOADBLOBFROMFILE(bl1, f1, amount, dst_ofst, src_ofst);
DBMS_LOB.READ(bl1, amount, 1, buffer);
DBMS_OUTPUT.PUT_LINE(amount);
DBMS_OUTPUT.PUT_LINE(src_ofst);
DBMS_OUTPUT.PUT_LINE(dst_ofst);
DBMS_LOB.FILECLOSE(f1);
end;
/
16
17
17
PSM completed.
SQL>
Opens a file as read-only binary mode using the file locator.
Details about the OPEN procedure are as follows:
Prototype
DBMS_LOB.OPEN
(
file_loc IN OUT NOCOPY BFILE,
open_mode IN BINARY_INTEGER := file_readonly
);
Parameters
| Parameter | Description |
|---|---|
| file_loc | File locator of the target file. |
| open_mode | Only file_readonly is allowed. |
Exceptions
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the file locator is NULL. |
| INVALID_ARGVAL | Occurs when open_mode is set to a value other than file_readonly. |
| INVALID_DIRECTORY | Occurs when the directory specified in the file locator is invalid. |
| NOEXIST_DIRECTORY | Occurs when the directory path specified in the file locator does not exist. |
| OPERATION_FAILED | Occurs when the file does not exist in the directory path or cannot be opened. |
Example
create directory BFILETESTDIR as '/mybasepath/bfiletest/';
declare
f utl_file.file_type;
buffer raw(16) := '000102030405060708090A0B0C0D0E0F';
begin
f := utl_file.fopen('BFILETESTDIR', 'test001.bin', 'wb');
utl_file.put_raw(f, buffer);
utl_file.fclose(f);
end;
/
declare
f1 bfile;
amount number;
buffer raw(16);
result raw(16) := '000102030405060708090A0B0C0C0E0F';
begin
f1 := bfilename('BFILETESTDIR', 'test001.bin');
dbms_lob.open(f1, dbms_lob.file_readonly);
amount := 16;
dbms_lob.read(f1, amount, 1, buffer);
dbms_output.put_line(amount);
dbms_output.put_line(buffer);
dbms_lob.close(f1);
end;
/
16
000102030405060708090A0B0C0D0E0F
Reads all or part of a LOB and stores it in the buffer of the output parameter. If the sum of the size of the offset and the size of data to be read is larger than the target LOB, and the procedure needs to read past the end of the LOB, the size of the data that has been actually read can be smaller than the size of the data to be read given as the parameter. If so, the size of the actual read data is stored in the parameter amount. If the location of the offset to be read is larger than the target LOB data, a NO_DATA_FOUND exception will occur.
When transmitting data read from the CLOB to the client, the data is automatically converted to the character set of the client. This can result in the displayed data being different from the actual data read from the CLOB.
Details about the READ procedure are as follows:
Prototype
BLOB type
DBMS_LOB.READ
(
lob IN BLOB,
amount IN OUT NOCOPY BINARY_INTEGER,
offset IN INTEGER,
buffer OUT RAW
);
CLOB type
DBMS_LOB.READ
(
lob IN CLOB,
amount IN OUT NOCOPY BINARY_INTEGER,
offset IN INTEGER,
buffer OUT VARCHAR2
);
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
| amount | As an input, specifies the amount of data to read. As an output, contains the amount of data that was actually read. The unit is in bytes for BLOBs or in characters for CLOBs. |
| offset | Offset in bytes (BLOBs) or characters (CLOBs) from which to read. |
| buffer | Output buffer to store the data. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when one or more input parameters are NULL. |
| INVALID_POS | Occurs when the offset value is less than 1 or greater than LOBMAXSIZE. |
| INVALID_LEN |
|
| NO_DATA_FOUND | Occurs when offset is greater than the size of the target LOB. |
Example
DECLARE
lob CLOB := 'TIBERO fighting!!!';
buffer VARCHAR2(256);
amount BINARY_INTEGER := 8;
BEGIN
DBMS_LOB.READ(lob, amount, 8, buffer);
DBMS_OUTPUT.PUT_LINE('Value to be read = ' || buffer);
END;
/
Value to be read = fighting
PSM completed
SQL>
Specifies the length of the target LOB. The length is in bytes for BLOBs or in characters for CLOBs. Any data that exceeds the specified length will be deleted.
If executing this procedure with a LOB with a length of 0, no value will be returned.
Details about the TRIM procedure are as follows:
Prototype
BLOB type
DBMS_LOB.TRIM
(
lob IN OUT NOCOPY BLOB,
newlen IN INTEGER
);
CLOB type
DBMS_LOB.TRIM
(
lob IN OUT NOCOPY CLOB,
newlen IN INTEGER
);
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
| newlen | New LOB length, in bytes for BLOBs or in characters for CLOBs. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when one or more input parameters are NULL. |
| INVALID_LEN | Occurs when the value of newlen is less than 0 or greater than LOBMAXSIZE. |
Example
DECLARE
lob CLOB := 'A pity beyond all telling is in the heart of love';
BEGIN
DBMS_LOB.TRIM(lob, 25);
DBMS_OUTPUT.PUT_LINE('Value = ' || lob);
DBMS_OUTPUT.PUT_LINE('Length = ' || length(lob));
END;
/
Value = A pity beyond all telling
Length = 25
PSM completed
SQL>
Saves a specified length of given data to the specified offset location of the target LOB. The existing data in the target location will be deleted. The offset unit is in bytes for BLOBS or in characters for CLOBs.
An exception occurs if the specified size is larger than the actual size of the given data. If the specified size is smaller than the actual size, data will only be saved up to the specified size. If the specified offset is larger than the target LOB, the extra spaces are padded with zeros for BLOBs or blank spaces for CLOBs.
When executing this procedure for CLOB data, the character set of the CLOB data and that of the data to save must be the same. When the client calls this procedure, the character set of the data will be converted to the character set of the CLOB data.
Details about the WRITE procedure are as follows:
Prototype
BLOB type
DBMS_LOB.WRITE
(
lob IN OUT NOCOPY BLOB,
amount IN BINARY_INTEGER,
offset IN INTEGER,
buffer IN RAW
);
CLOB type
DBMS_LOB.WRITE
(
lob IN OUT NOCOPY CLOB,
amount IN BINARY_INTEGER,
offset IN INTEGER,
buffer IN VARCHAR
);
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
| amount | Number of bytes (BLOBs) or characters (CLOBs) to save. |
| offset | Offset in bytes (BLOBs) or characters (CLOBs) from which to save. |
| buffer | Data to save. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when one or more parameters are NULL. |
| INVALID_POS | Occurs when the value of offset is less than 1 or greater than LOBMAXSIZE. |
| INVALID_LEN | Occurs when the value of amount is less than 1 or greater than MAXBUFSIZE. |
Example
DECLARE
lob CLOB;
buffer VARCHAR2(100);
BEGIN
DBMS_LOB.CREATETEMPORARY(lob, false);
buffer := 'Love is friendship set on fire';
DBMS_LOB.WRITE(lob, length(buffer), 1, buffer);
DBMS_OUTPUT.PUT_LINE(lob);
END;
/
Love is friendship set on fire
PSM completed
SQL>
Saves a specified length of given data to the end of the target LOB. This has the same effect as using the WRITE procedure with the offset parameter specified as the length of the target LOB. The unit is in bytes for BLOBs or in characters for CLOBs.
If the specified length is longer than the actual size of the given data, an exception occurs. If it is shorter, only the specified length of data is saved.
When this procedure is executed for CLOB data, the character set of the CLOB data must be the same as that of the data to be saved. When the client calls the procedure, data is saved after the character set of the client is converted to that of the CLOB.
Details about the WRITEAPPEND procedure are as follows:
Prototype
BLOB type
DBMS_LOB.WRITEAPPEND
(
lob IN OUT NOCOPY BLOB,
amount IN BINARY_INTEGER,
buffer IN RAW
);
CLOB type
DBMS_LOB.WRITEAPPEND
(
lob IN OUT NOCOPY CLOB,
amount IN BINARY_INTEGER,
buffer IN VARCHAR2
);
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
| amount | Number of bytes (BLOBs) or characters (CLOBs) to save. |
| buffer | Data to save, in bytes for BLOBs or in characters for CLOBs. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when one or more parameters are NULL. |
| INVALID_LEN | Occurs when the value of amount is less than 1 or greater than MAXBUFSIZE. |
Example
DECLARE
lob CLOB := 'Parting is such ';
buffer VARCHAR2(100) := 'sweet sorrow';
BEGIN
DBMS_LOB.WRITEAPPEND(lob, length(buffer), buffer);
DBMS_OUTPUT.PUT_LINE('Result = ' || lob);
END;
/
Result = Parting is such sweet sorrow
PSM completed
SQL>
This section describes the functions in the DBMS_LOB package, in alphabetical order.
Closes a file using the file locator.
Details about the CLOSE function are as follows:
Prototype
DBMS_LOB.CLOSE
(
file_loc IN OUT NOCOPY BFILE
);
Parameter
| Parameter | Description |
|---|---|
| file_loc | File locator of the target file to close. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the file locator is NULL. |
Example
Refer to the OPEN example.
This function compares two whole LOBs or parts of two LOBs. The type of the LOBs must be the same for comparison.
Details about the COMPARE function are as follows:
Prototype
BLOB type
DBMS_LOB.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;
CLOB type
DBMS_LOB.COMPARE
(
lob_1 IN CLOB,
lob_2 IN CLOB,
amount IN INTEGER := 4294967295,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1
) RETURN INTEGER;
When transferring CLOB data, the LOB data character set of the parameter lob_1 must match that of lob_2.
Parameter
| Parameter | Description |
|---|---|
| lob_1 | First LOB for comparison |
| lob_2 | Second LOB for comparison. |
| amount | Number of bytes (BLOBs) or characters (CLOBs) to compare. |
| offset_1 | Offset in bytes (BLOBs) or characters (CLOBs) on the first LOB for the comparison. |
| offset_2 | Offset in bytes (BLOBs) or characters (CLOBs) on the second LOB for the comparison. |
Return Value
| Value | Description |
|---|---|
| 0 | Returned when LOB data of lob_1 is the same as that of lob_2. |
| N < 0 or N > 0 | Returned when LOB data of lob_1 is different from that of lob_2. |
NULL |
|
Example
DECLARE
lob_1 CLOB := 'abcdefgh';
lob_2 CLOB := 'abcdefgg';
BEGIN
IF DBMS_LOB.COMPARE(lob_1, lob_2) = 0 then
DBMS_OUTPUT.PUT_LINE('LOB_1 equals LOB_2');
ELSE
DBMS_OUTPUT.PUT_LINE('LOB_1 does not equals LOB_2');
END IF;
END;
/
LOB_1 does not equals LOB_2
PSM completed
SQL>
Checks whether the file exists.
Details about the FILEEXISTS function are as follows:
Prototype
DBMS_LOB.FILEEXISTS
(
file_loc IN OUT NOCOPY BFILE,
)
RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| file_loc | File locator of the target file. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the file locator is NULL. |
| INVALID_DIRECTORY | Occurs when the directory specified in the file locator is invalid. |
| NOEXIST_DIRECTORY | Occurs when the directory path specified in the file locator does not exist. |
Example
create directory BFILETESTDIR as '/mybasepath/bfiletest/';
declare
f utl_file.file_type;
buffer raw(16) := '000102030405060708090A0B0C0D0E0F';
begin
f := utl_file.fopen('BFILETESTDIR', 'test001.bin', 'wb');
utl_file.put_raw(f, buffer);
utl_file.fclose(f);
end;
/
declare
f1 bfile;
f2 bfile;
begin
f1 := bfilename('BFILETESTDIR', 'test001.bin');
f2 := bfilename('BFILETESTDIR', 'test002.bin');
dbms_output.put_line(dbms_lob.fileexists(f1));
dbms_output.put_line(dbms_lob.fileexists(f2));
end;
/
1
0
Checks whether the file was opened with the given locator.
Details about the FILEISOPEN function are as follows:
Prototype
DBMS_LOB.FILEISOPEN
(
file_loc IN OUT NOCOPY BFILE,
)
RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| file_loc | File locator. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the file locator is NULL. |
Example
create directory BFILETESTDIR as '/mybasepath/bfiletest/';
declare
f utl_file.file_type;
buffer raw(16) := '000102030405060708090A0B0C0D0E0F';
begin
f := utl_file.fopen('BFILETESTDIR', 'test001.bin', 'wb');
utl_file.put_raw(f, buffer);
utl_file.fclose(f);
end;
/
declare
f1 bfile;
begin
f1 := bfilename('BFILETESTDIR', 'test001.bin');
dbms_lob.open(f1, dbms_lob.file_readonly);
dbms_output.put_line(dbmb_lob.fileisopen(f1));
dbms_lob.close(f1);
dbms_output.put_line(dbmb_lob.fileisopen(f1));
end;
/
1
0
Returns the length of the target LOB. The length is returned in either bytes or characters according to the LOB data type.
Details about the GETLENGTH function are as follows:
Prototype
BLOB type
DBMS_LOB.GETLENGTH
(
lob IN BLOB
) RETURN INTEGER;
CLOB type
DBMS_LOB.GETLENGTH
(
lob IN CLOB
) RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
Return Value
| Value | Description |
|---|---|
| 0 | Returned when the LOB is empty. |
| NULL | Returned when the lob parameter is NULL. |
Any zeros or blank spaces filled by a previous COPY, ERASE or WRITE operation are also included in the length.
Example
DECLARE
lob CLOB := 'architecture';
BEGIN
DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(lob));
END;
/
12
PSM completed
SQL>
Returns the offset for the nth matching occurrence in the target LOB. The search can start at the beginning of the LOB or at a specified position. The target pattern cannot include wildcard characters, such as a percent (%) sign or an underscore (_), that are used with the LIKE operator.
Details about the INSTR function are as follows:
Prototype
BLOB type
DBMS_LOB.INSTR
(
lob IN BLOB,
pattern IN RAW,
offset IN INTEGER := 1,
nth IN INTEGER := 1
) RETURN INTEGER;
CLOB type
DBMS_LOB.INSTR
(
lob IN CLOB,
pattern IN VARCHAR,
offset IN INTEGER := 1,
nth IN INTEGER := 1
) RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
| pattern | Pattern to match. |
| offset | Offset in bytes (BLOBs) or characters (CLOBs) from which to start the search. |
| nth | Occurrence number starting from 1. Must be greater than or equal to 1. If specified as 1, the offset of the first matched pattern will be returned. |
Return Value
| Value | Description |
|---|---|
| INTEGER | If a match is found, the offset of the start of the matched pattern is returned. If a match is not found, 0 is returned. |
| NULL |
|
Example
DECLARE
lob CLOB := 'Corporate floor';
result NUMBER;
BEGIN
result := DBMS_LOB.INSTR(lob, 'or', 3, 2);
DBMS_OUTPUT.PUT_LINE('Result offset = ' || result);
END;
/
Result offset = 14
PSM completed
SQL>
Checks whether LOB was opened already with the given locator.
Details about the ISOPEN function are as follows:
Prototype
DBMS_LOB.ISOPEN
(
file_loc IN OUT NOCOPY BFILE,
)
RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| file_loc | File locator. |
Exception
| Exception | Description |
|---|---|
| VALUE_ERROR | Occurs when the file locator is NULL. |
Example
create directory BFILETESTDIR as '/mybasepath/bfiletest/';
declare
f utl_file.file_type;
buffer raw(16) := '000102030405060708090A0B0C0D0E0F';
begin
f := utl_file.fopen('BFILETESTDIR', 'test001.bin', 'wb');
utl_file.put_raw(f, buffer);
utl_file.fclose(f);
end;
/
declare
f1 bfile;
begin
f1 := bfilename('BFILETESTDIR', 'test001.bin');
dbms_lob.open(f1, dbms_lob.file_readonly);
dbms_output.put_line(dbmb_lob.isopen(f1));
dbms_lob.close(f1);
dbms_output.put_line(dbmb_lob.isopen(f1));
end;
/
1
0
Checks if the given LOB is temporary or not.
Details about the ISTEMPORARY function are as follows:
Prototype
BLOB type
DBMS_LOB.ISTEMPORARY
(
lob IN BLOB
) RETURN INTEGER;
CLOB type
DBMS_LOB.ISTEMPORARY
(
lob IN CLOB
) RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
Return Value
| Value | Description |
|---|---|
| 1 | Returned if the LOB is temporary. |
| 0 | Returned if the LOB is not temporary. |
Example
DECLARE
lob CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(lob, false);
IF DBMS_LOB.ISTEMPORARY(lob) = 1THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
END;
/
TRUE
PSM completed
SQL>
Returns the specified length of data from the target LOB, starting from the specified offset. The maximum length of the returned data is 32,767 bytes. For CLOB data, the maximum number of characters to be returned is 32,767/2. If this function is called on CLOB data, the returned string has the same character set as the CLOB.
This function returns NULL if any argument is NULL or if the value of amount or offset is less than 1 or greater than LOBMAXSIZE.
If this function is called by the client on a CLOB data and the character set of the client is different from that of the CLOB, the character set is automatically converted during data transmission.
Details about the SUBSTR function are as follows:
Prototype
BLOB type
DBMS_LOB.SUBSTR
(
lob IN BLOB,
amount IN INTEGER := 32767,
offset IN INTEGER := 1
) RETURN RAW;
CLOB type
DBMS_LOB.SUBSTR
(
lob IN CLOB,
amount IN INTEGER := 32767,
offset IN INTEGER := 1
) RETURN VARCHAR2;
Parameter
| Parameter | Description |
|---|---|
| lob | Target LOB. |
| amount | Number of bytes (BLOBs) or characters (CLOBs) to read. |
| offset | Offset in bytes (BLOBs) or characters (CLOBs) from which to read. |
Return Value
| Value | Description |
|---|---|
| RAW/VARCHAR2 data | Returned when the SUBSTR function completes successfully. |
| NULL |
|
Example
DECLARE
lob CLOB := 'Your friend is too young';
buffer VARCHAR2(100);
BEGIN
buffer := DBMS_LOB.SUBSTR(lob, 6, 6);
DBMS_OUTPUT.PUT_LINE('My favorite word is ' || UPPER(buffer));
END;
/
My favorite word is FRIEND
PSM completed
SQL>