| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Sample BLOB Scripts
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:
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);
dblob BLOB;
sblob BLOB;
dclob CLOB;
sclob CLOB;
/* 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(' ');
SQL> execute tst_append;
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 ---------------');
/* 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
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;
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
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;
/* 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;
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
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.
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=0) 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;
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;
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()
I hope you find this of some help!
Regards
"The Views expressed here are my own and not necessarily those of Oracle Corporation"
Rob Calfee <trace_at_primenet.com> wrote in message news:37935c6e.248969717_at_news.primenet.com...
> I'm just starting to use BLOBs. Does anyone have any sample scripts > for inserting and extracting BLOBs (SQL-PL/SQL (? and Visual Basic, > maybe)? Thanks in advance. Just need some basic stuff to get going. > > > Rob Calfee > DBA > rcalfee_at_incsystem.comReceived on Mon Jul 19 1999 - 15:25:28 CDT
 |
 |