Oracle LOBs

Oracle LOBs such as BLOBs (Binary LOBs) and CLOBs (character LOBs) may require special handling.

For example, the view FEATURE_GEOMETRY_TEXT_VW returns text as a CLOB. A simple query is often sufficient:

select rep_id, text from FEATURE_GEOMETRY_TEXT_VW;

In some cases, a function may only handle small strings that fit into a VARCHAR2 (e.g. DBMS_OUTPUT.PUT_LINE) or a compiled application for performance reasons may want to break the LOBs into smaller chunks. In that case, something like the following may be useful:

DECLARE

text CLOB;

buffer VARCHAR2 (800);

len NUMBER;

offset NUMBER DEFAULT 1;

amount NUMBER DEFAULT 50;

CURSOR get_text_cur IS

SELECT rep_id, text from FEATURE_GEOMETRY_TEXT_VW;

BEGIN

FOR rec IN get_text_cur LOOP

text := rec.text;

len := DBMS_LOB.getlength (text);

DBMS_OUTPUT.put_line (rec.rep_id);

offset := 1;

LOOP

EXIT WHEN offset > len;

amount := 50;

DBMS_LOB.READ (text, amount, offset, buffer);

-- process buffer....

DBMS_OUTPUT.put_line (buffer);

offset := offset + 50;

END LOOP;

END;