Oracle9i Supplied PL/SQL Packages and Types Reference Release 1 (9.0.1) Part Number A89852-02 |
|
DBMS_LOB , 22 of 26
This procedure reads a piece of a LOB
, and returns the specified amount into the buffer
parameter, starting from an absolute offset from the beginning of the LOB
.
The number of bytes or characters actually read is returned in the amount
parameter. If the input offset
points past the End of LOB
, then amount
is set to 0, and a NO_DATA_FOUND
exception is raised.
DBMS_LOB.READ ( lob_loc IN BLOB, amount IN OUT NOCOPY BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW); DBMS_LOB.READ ( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN OUT NOCOPY BINARY_INTEGER, offset IN INTEGER, buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET); DBMS_LOB.READ ( file_loc IN BFILE, amount IN OUT NOCOPY BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW);
The form of the VARCHAR2
buffer must match the form of the CLOB
parameter. In other words, if the input LOB
parameter is of type NCLOB
, then the buffer must contain NCHAR
data. Conversely, if the input LOB
parameter is of type CLOB
, then the buffer must contain CHAR
data.
When calling DBMS_LOB
.READ
from the client (for example, in a BEGIN
/END
block from within SQL*Plus), the returned buffer contains data in the client's character set. Oracle converts the LOB
value from the server's character set to the client's character set before it returns the buffer to the user.
CREATE OR REPLACE PROCEDURE Example_13a IS src_lob BLOB; buffer RAW(32767); amt BINARY_INTEGER := 32767; pos INTEGER := 2147483647; BEGIN SELECT b_col INTO src_lob FROM lob_table WHERE key_value = 21; LOOP dbms_lob.read (src_lob, amt, pos, buffer); -- process the buffer pos := pos + amt; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('End of data'); END; CREATE OR REPLACE PROCEDURE Example_13b IS fil BFILE; buf RAW(32767); amt BINARY_INTEGER := 32767; pos INTEGER := 2147483647; BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21; dbms_lob.fileopen(fil, dbms_lob.file_readonly); LOOP dbms_lob.read(fil, amt, pos, buf); -- process contents of buf pos := pos + amt; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN dbms_output.putline ('End of LOB value reached'); dbms_lob.fileclose(fil); END; END;
Example for efficient I/O on OS that performs better with block I/O rather than stream I/O:
CREATE OR REPLACE PROCEDURE Example_13c IS fil BFILE; amt BINARY_INTEGER := 1024; -- or n x 1024 for reading n buf RAW(1024); -- blocks at a time tmpamt BINARY_INTEGER; BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99; dbms_lob.fileopen(fil, dbms_lob.file_readonly); LOOP dbms_lob.read(fil, amt, pos, buf); -- process contents of buf pos := pos + amt; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN dbms_output.putline ('End of data reached'); dbms_lob.fileclose(fil); END; END;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|