1rem ----------------------------------------------------------------------- 2rem URL: http://www.orafaq.com/scripts/plsql/readlob.txt 3rem Filename: readlob.sql 4rem Purpose: Fetch LOB column values piece-wise from PL/SQL 5rem Date: 12-Jun-2000 6rem Author: Frank Naude (frank@ibi.co.za) 7rem ----------------------------------------------------------------------- 8 9set serveroutput on 10 11DROP TABLE lob_table; -- Create table to hols LOBs 12CREATE TABLE lob_table ( 13 id INTEGER, 14 b_lob BLOB, 15 c_lob CLOB, 16 b_file BFILE ); 17 18INSERT INTO lob_table -- Create sample record 19 VALUES (1, EMPTY_BLOB(), 'abcde', NULL); 20 21DECLARE 22 clob_locator CLOB; 23 charbuf VARCHAR2(20); 24 read_offset INTEGER; 25 read_amount INTEGER; 26BEGIN 27 -- First we need to get the lob locator 28 SELECT c_lob INTO clob_locator FROM lob_table WHERE id = 1; 29 30 DBMS_OUTPUT.PUT_LINE('CLOB Size: ' || 31 DBMS_LOB.GETLENGTH(clob_locator)); 32 33 -- Read LOB field contents 34 read_offset := 1; 35 read_amount := 20; 36 dbms_lob.read(clob_locator, read_amount, read_offset, charbuf); 37 dbms_output.put_line('CLOB Value: ' || charbuf); 38END; 39/ 40