MANIPULATING LARGE OBJECTS USING BMS_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 these
subprograms 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.
Manipulating External LOBS (BFILES) with DBMS_LOB Package
---------------------------------------------------------
A BFILE column or attribute for a particular row or user-defined type stores
a BFILE locator which can be considered as an opaque pointer to a file on the
server's file system. The locator contains the directory alias, the filename,
and some state information. A new DIRECTORY database item enables
administering access and usage of BFILES. A DIRECTORY item specifies an alias
for a directory on the server's file system.
The schema creating a directory object needs special privileges. The SQL
statement below grants to user naveen the privilege to create directory
objects.
grant create any directory to naveen;
Creating a directory object which maps an operating system path where the flat
file is stored. The directory object name is used to map an operating system
file directory to a BFILE Lob Locator.
create or replace directory "LOBMANIP" as '/home/usupport/npasumar/bulletin/';
Loading a lob locator of a BFILE using the DBMS_LOB.LOADFROMFILE
The following four steps are involved in loading a BFILE locator to map OS
file. Note that ORACLE has only read-only access to BFILEs.
1) Check if the external file was opened by using the specified FILE locator
using the DBMS_LOB.FILEISOPEN() function.
Syntax
FUNCTION FILEISOPEN (
file_loc IN BFILE)
RETURN INTEGER;
2) Open the external file using the DBMS_LOB.FILEOPEN() procedure.
Syntax
PROCEDURE FILEOPEN (
file_loc IN OUT BFILE,
open_mode IN BINARY_INTEGER := file_readonly);
3) Copy a part or whole of the external LOB into a Internal LOB using
the DBMS_LOB.LOADFROMFILE.
Syntax
PROCEDURE loadfromfile (
dest_lob IN OUT BLOB,
src_file IN BFILE,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);
4) Close the BFILE that was opened via the DBMS_LOB.FILEOPEN() procedure.
Syntax
PROCEDURE FILECLOSE (
file_loc IN OUT BFILE);
Example: This program reads data from an operating system file and reads it
into a BLOB. It assumes that the directory object LOBMANIP has been
created and the directory to which it maps contains a file
'sample10.pc'.
CREATE OR REPLACE PROCEDURE load_lob IS
temp_blob BLOB;
file_on_os BFILE := bfilename('LOBMANIP', 'append.sql');
ignore INTEGER;
BEGIN
ignore := dbms_lob.fileexists(file_on_os);
IF ignore=1 THEN
ignore := dbms_lob.fileisopen(file_on_os);
IF (ignore=1) THEN
null;
ELSE
dbms_lob.fileopen(file_on_os, dbms_lob.file_readonly);
END IF;
SELECT video_clip INTO temp_blob FROM lob_store WHERE lob_id = 5 FOR UPDATE;
dbms_output.put_line('External file size is: ' ||
dbms_lob.getlength(file_on_os));
dbms_lob.loadfromfile(temp_blob,file_on_os, dbms_lob.getlength(file_on_os));
dbms_lob.fileclose(file_on_os);
dbms_output.put_line('Internal BLOB size is: ' ||
dbms_lob.getlength(temp_blob));
ELSE
dbms_output.put_line('File does not exist');
END IF;
COMMIT;
END;
SQL> exec load_lob
External file size is: 1230
Internal BLOB size is: 1230
PL/SQL procedure successfully completed.
The next example demonstrates how the data stored in an internal LOB can be
written to a flat file using a combination of the built-in packages DBMS_LOB
and UTL_FILE.
CREATE OR REPLACE PROCEDURE retrieve_lob IS
temp_blob BLOB;
data_buffer RAW (1);
temp_buffer VARCHAR2(1);
amount BINARY_INTEGER := 1;
position INTEGER := 1;
filehandle utl_file.file_type;
error_number NUMBER;
error_message VARCHAR2(100);
length_count INTEGER;
BEGIN
SELECT video_clip INTO temp_blob FROM lob_store WHERE lob_id = 1;
length_count := dbms_lob.getlength(temp_blob);
dbms_output.put_line('Internal LOB size is: ' || length_count);
filehandle := utl_file.fopen('/home/usupport/npasumar/bulletin',
'lob_flat.out','W');
WHILE length_count <> 0 LOOP
dbms_lob.read (temp_blob, amount, position, data_buffer);
temp_buffer := utl_raw.cast_to_varchar2(data_buffer);
utl_file.put (filehandle, temp_buffer);
position := position + 1;
length_count := length_count - 1;
data_buffer := null;
END LOOP;
dbms_output.put_line('Exit the loop');
utl_file.fclose(filehandle);
dbms_output.put_line('Close the file');
EXCEPTION
WHEN OTHERS THEN
BEGIN
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose_all;
END;
END;
Note: The utl_file package is only suitable for handling TEXT data.
SQL> exec retrieve_lob
Internal LOB size is: 1230
Exit the loop
Close the file
PL/SQL procedure successfully completed.
Comparison Between OCI Functions and DBMS_LOB PACKAGES
------------------------------------------------------
The LOBs can also be manipulated using OCI. Here is a comparison of the
DBMS_LOB package procedures and the OCI functions.
OCI (ociap.h) DBMS_LOB (dbmslob.sql)
N/A DBMS_LOB.COMPARE()
N/A DBMS_LOB.INSTR()
N/A DBMS_LOB.SUBSTR()
OCILobAppend DBMS_LOB.APPEND()
OCILobAssign N/A [use Pl/SQL assign operator]
OCILobCharSetForm N/A
OCILobCharSetId N/A
OCILobCopy DBMS_LOB.COPY()
OCILobDisableBuffering N/A
OCILobEnableBuffering N/A
OCILobErase DBMS_LOB.ERASE()
OCILobFileClose DBMS_LOB.FILECLOSE()
OCILobFileCloseAll DBMS_LOB.FILECLOSEALL()
OCILobFileExists DBMS_LOB.FILEEXISTS()
OCILobFileGetName DBMS_LOB.FILEGETNAME()
OCILobFileIsOpen DBMS_LOB.FILEISOPEN()
OCILobFileOpen DBMS_LOB.FILEOPEN()
OCILobFileSetName N/A (use BFILENAME operator)
OCILobFlushBuffer N/A
OCILobGetLength DBMS_LOB.GETLENGTH()
OCILobIsEqual N/A [use Pl/SQL equal operator]
OCILobLoadFromFile DBMS_LOB.LOADFROMFILE()
OCILobLocatorIsInit N/A [always initialize]
OCILobRead DBMS_LOB.READ()
OCILobTrim DBMS_LOB.TRIM()
OCILobWrite DBMS_LOB.WRITE()
Frequently Asked Questions
--------------------------
1. What are the differences between LONG and LOB?
LONG LOB
---- ---
Single column per table Multiple columns per table
Up to 2 Gigabytes Up to 4 Gigabytes
SELECT returns data SELECT returns locator
Data stored in-line Data stored in-line or out-of-line
No object type support Support object types
Sequential access of data Random access of data
2. When can LOBs be stored in-line?
When LOB columns are created as a part of a table, they are created by default
in in-line mode. In this mode, if the LOB (data+locator) < 4000 bytes, then
the LOB is stored inline. If the LOB (data+locator) > 4000 bytes, then it is
stored out-of-line. Inline storage of LOBs smaller than 4K can be disabled
(disable storage in row). In this case, only the locator is stored in the row
and the data is stored in the LOB segment.
Related Article
---------------
[NOTE:66312.1] How to Display Images Stored in BFILE Columns
.
from oracle
|