REM lobprint.sql
REM Version 1.0, last updated 8/8/97
REM This procedure demonstrates the DBMS_LOB.READ procedure, as described in
REM Chapter 21 of _Oracle8 PL/SQL Programming_ by Scott Urman.CREATE OR REPLACE PROCEDURE LOBPrint(p_CLOB IN CLOB) AS
v_Buffer VARCHAR2(80);
v_Offset INTEGER := 1;
v_Amount INTEGER := 80;
BEGIN
LOOP
-- Read and output the next 80 characters.
DBMS_LOB.READ(p_CLOB, v_Amount, v_Offset, v_Buffer);
DBMS_OUTPUT.PUT_LINE(v_Buffer); v_Offset := v_Offset + v_Amount;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- End of loop, just return.
NULL;
END LOBPrint;
/
REM Version 1.0, last updated 8/8/97
REM This procedure demonstrates the DBMS_LOB.READ procedure, as described in
REM Chapter 21 of _Oracle8 PL/SQL Programming_ by Scott Urman.CREATE OR REPLACE PROCEDURE LOBPrint(p_CLOB IN CLOB) AS
v_Buffer VARCHAR2(80);
v_Offset INTEGER := 1;
v_Amount INTEGER := 80;
BEGIN
LOOP
-- Read and output the next 80 characters.
DBMS_LOB.READ(p_CLOB, v_Amount, v_Offset, v_Buffer);
DBMS_OUTPUT.PUT_LINE(v_Buffer); v_Offset := v_Offset + v_Amount;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- End of loop, just return.
NULL;
END LOBPrint;
/
fil BFILE;
pos INTEGER;
amt BINARY_INTEGER;
buf RAW(40);
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
dbms_lob.open(fil, dbms_lob.lob_readonly);
amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := '';
dbms_lob.read(fil, amt, pos, buf);
dbms_output.put_line('Read F1 past EOF: '||
utl_raw.cast_to_varchar2(buf));
dbms_lob.close(fil);
exception
WHEN no_data_found
THEN
BEGIN
dbms_output.put_line('End of File reached. Closing file');
dbms_lob.fileclose(fil);
-- or dbms_lob.filecloseall if appropriate
END;
END;
/