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 = 414Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace to BlaneyPark. Turn north on M-77 and drive to Seney. From Seney, take M-28 west toMunising. In front of the paper mill, turn right on H-58. Follow H-58 toWashington Street. Veer left onto Washington Street. You'll find the MunisingFalls visitor center across from the hospital at the point where WashingtonStreet 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.