Here is another good example:
You can initialize the LOBs by using the following SQL INSERT statement:
INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), NULL,
EMPTY_CLOB(), NULL);
External LOBs:
INSERT INTO lob_table VALUES
(21, NULL, NULL, NULL, BFILENAME(‘IMG’, ‘image1.gif’));
INSERT INTO lob_table VALUES
(22, NULL, NULL, NULL, BFILENAME(‘IMG’, ‘image2.gif’));
UPDATE lob_table SET f_lob = BFILENAME(‘IMG’, ‘image3.gif’)
WHERE key_value = 22;
BFILENAME() is a built-in function that is used to initialize the BFILE column to point to the external file.
Selecting a LOB:
DECLARE image1 BLOB; image_no INTEGER := 101;BEGIN
SELECT b_lob INTO image1 FROM lob_table WHERE key_value = image_no;
DBMS_OUTPUT.PUT_LINE(‘Size of the Image is: ‘ ||
DBMS_LOB.GETLENGTH(image1)); — more LOB routinesEND;
Example of a Read Consistent Locator
INSERT INTO lob_table
VALUES (1, NULL, ‘abcd’, NULL, NULL);
COMMIT;
DECLARE
num_var INTEGER;
clob_selected CLOB;
clob_updated CLOB;
clob_copied CLOB;
read_amount INTEGER;
read_offset INTEGER;
write_amount INTEGER;
write_offset INTEGER;
buffer VARCHAR2(20);
BEGIN
— At time t1:
SELECT c_lob INTO clob_selected
FROM lob_table
WHERE key_value = 1;
— At time t2:
SELECT c_lob INTO clob_updated
FROM lob_table
WHERE key_value = 1
FOR UPDATE;
— At time t3:
clob_copied := clob_selected;
— After the assignment, both the clob_copied and the
— clob_selected have the same snapshot as of the point in time
— of the SELECT into clob_selected
— Reading from the clob_selected and the clob_copied will
— return the same LOB value. clob_updated also sees the same
— LOB value as of its select:
read_amount := 10;
read_offset := 1;
dbms_lob.read(clob_selected, read_amount, read_offset,
buffer);
dbms_output.put_line(‘clob_selected value: ‘ || buffer);
— Produces the output ‘abcd’
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line(‘clob_copied value: ‘ || buffer);
— Produces the output ‘abcd’
read_amount := 10;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line(‘clob_updated value: ‘ || buffer);
— Produces the output ‘abcd’
— At time t4:
write_amount := 3;
write_offset := 5;
buffer := ‘efg’;
dbms_lob.write(clob_updated, write_amount, write_offset,
buffer);
read_amount := 10;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line(‘clob_updated value: ‘ || buffer);
— Produces the output ‘abcdefg’
— At time t5:
read_amount := 10;
dbms_lob.read(clob_selected, read_amount, read_offset,
buffer);
dbms_output.put_line(‘clob_selected value: ‘ || buffer);
— Produces the output ‘abcd’
— At time t6:
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line(‘clob_copied value: ‘ || buffer);
— Produces the output ‘abcd’
END;
/
Example of an Updated LOB Locator
INSERT INTO lob_table
VALUES (1, NULL, ‘abcd’, NULL, NULL);
COMMIT;
DECLARE
num_var INTEGER;
clob_updated CLOB;
clob_copied CLOB;
read_amount INTEGER; ;
read_offset INTEGER;
write_amount INTEGER;
write_offset INTEGER;
buffer VARCHAR2(20);
BEGIN
— At time t1:
SELECT c_lob INTO clob_updated FROM lob_table
WHERE key_value = 1
FOR UPDATE;
— At time t2:
clob_copied := clob_updated;
— after the assign, clob_copied and clob_updated see the same
— LOB value
read_amount := 10;
read_offset := 1;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line(‘clob_updated value: ‘ || buffer);
— Produces the output ‘abcd’
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line(‘clob_copied value: ‘ || buffer);
— Produces the output ‘abcd’
— At time t3:
write_amount := 3;
write_offset := 5;
buffer := ‘efg’;
dbms_lob.write(clob_updated, write_amount, write_offset,
buffer);
read_amount := 10;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line(‘clob_updated value: ‘ || buffer);
— Produces the output ‘abcdefg’
— At time t4:
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line(‘clob_copied value: ‘ || buffer);
— Produces the output ‘abcd’
— At time t5:
clob_copied := clob_updated;
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line(‘clob_copied value: ‘ || buffer);
— Produces the output ‘abcdefg’
END;
/
Copying internal LOBs
INSERT INTO lob_table1 (key_value, b_lob)
(SELECT key_value, b_lob FROM lob_table2 T2
WHERE T2.key_value = 101);
creates a new LOB locator in the table lob_table1, and copies the LOB data from lob_table2 to the location pointed to by a new LOB locator which is inserted into table lob_table1.
———-
LOB Reference
EMPTY_BLOB() and EMPTY_CLOB() Functions
INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), ‘abcde’, NULL, NULL);
UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 1001;
INSERT INTO lob_table VALUES (1002, NULL, NULL, NULL, NULL);
BFILENAME() Function
DBMS_LOB Package
The DBMS_LOB package provides routines to access BLOBs, CLOBs, NCLOBs, and BFILEs. You can use DBMS_LOB for access and manipulation of specific parts of a LOB, as well as complete LOBs. DBMS_LOB can read as well as modify BLOBs, CLOBs, and NCLOBs, and provides read-only operations on BFILEs.
All DBMS_LOB routines work based on LOB locators. For the successful completion of DBMS_LOB routines, you must provide an input locator that represents a LOB that already exists in the database tablespaces or external filesystem.
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
The routines that read or examine LOB values are:
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
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