12.5.5 Reading from a lob

To retrieve data from a LOB, you use the DBMS_LOB.READ procedure. First, of course, you must retrieve the LOB locator. When reading from a CLOB, you specify an offset in terms of characters. Reading begins at the offset that you specify, and the first character of a CLOB is always number 1. When you are working with BLOBs, offsets are in terms of bytes. Note that when you are calling DBMS_LOB.READ, you must specify the number of characters (or bytes) that you wish to read. Given that LOBs are large, it's reasonable to plan on doing more than one read to get at all the data.

The following example retrieves and displays the directions to Munising Falls. We've carefully chosen the number of characters to read both to accommodate DBMS_OUTPUT's line-length restriction and to ensure a nice-looking line break in the final output.

DECLARE

directions CLOB;

directions_1 VARCHAR2(300);

directions_2 VARCHAR2(300);

chars_read_1 BINARY_INTEGER;

chars_read_2 BINARY_INTEGER;

offset INTEGER;

BEGIN

--Retrieve the LOB locator inserted previously

SELECT falls_directions

INTO directions

FROM waterfalls

WHERE falls_name='Munising Falls';

--Begin reading with the first character

offset := 1;

--Attempt to read 229 characters of directions, chars_read_1 will

--be updated with the actual number of characters read

chars_read_1 := 229;

DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);

--If we read 229 characters, update the offset and try to

--read 255 more.

IF chars_read_1 = 229 THEN

offset := offset + chars_read_1;

chars_read_2 := 255;

DBMS_LOB.READ(directions, chars_read_2, offset, directions_2);

ELSE

chars_read_2 := 0;

directions_2 := '';

END IF;

--Display the total number of characters read

DBMS_OUTPUT.PUT_LINE('Characters read = ' ||

TO_CHAR(chars_read_1+chars_read_2));

--Display the directions

DBMS_OUTPUT.PUT_LINE(directions_1);

DBMS_OUTPUT.PUT_LINE(directions_2);

END;

The output from this code is as follows:

Characters read = 414

Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace to Blaney

Park. Turn north on M-77 and drive to Seney. From Seney, take M-28 west to

Munising. In front of the paper mill, turn right on H-58. Follow H-58 to

Washington Street. Veer left onto Washington Street. You'll find the Munising

Falls visitor center across from the hospital at the point where Washington

Street becomes Sand Point Road.

The chars_read_1 (amount to read) parameter, which is the second parameter you pass to DBMS_LOB.READ, is an IN OUT parameter, and DBMS_LOB.READ will update it to reflect the number of characters (or bytes) actually read. You'll know you've reached the end of a LOB when the number of characters or bytes read is less than the number you requested. It seems to us a bit inconvenient that the offset is not updated in the same manner. When reading several sequential portions of a LOB, you must update the offset each time based on the number of characters or bytes just read.

You can use DBMS_LOB.GET_LENGTH(lob_locator) to retrieve the length of a LOB. The length is returned as a number of bytes for BLOBs and BFILEs, and as a number of characters for CLOBs.