Display CLOB data in Sql*Plus

If you have the session ID (982) and the session is actively running, you use following code to display the content in a CLOB field.

DECLARE

clob_field CLOB;
charbuf VARCHAR2(4000);
read_offset PLS_INTEGER :=1;
lob_length PLS_INTEGER;

BEGIN
-- for screen output
DBMS_OUTPUT.ENABLE (1000000);
-- Make sure that only one row gets returned!!
SELECT b.sql_fulltext INTO clob_field
FROM v$sqlarea b,v$session a
WHERE a.sid=982 and a.sql_address=b.address;

--get length of the clob field
lob_length := DBMS_LOB.GETLENGTH(clob_field);

--loop through clob and print in 255 length segments
WHILE (read_offset <= lob_length) LOOP
charbuf := DBMS_LOB.SUBSTR(clob_field, 255, read_offset);
dbms_output.put_line(trim(charbuf));
read_offset := read_offset + 255;
END LOOP;

END;
/