Oracle9i Supplied PL/SQL Packages and Types Reference Release 1 (9.0.1) Part Number A89852-02 |
|
DBMS_LOB , 23 of 26
This function returns amount
bytes or characters of a LOB
, starting from an absolute offset
from the beginning of the LOB
.
For fixed-width n
-byte CLOBs
, if the input amount for SUBSTR
is specified to be greater than (32767/n
), then SUBSTR
returns a character buffer of length (32767/n
), or the length of the CLOB
, whichever is lesser. For CLOBs in a varying-width character set, n
is 2
.
DBMS_LOB.SUBSTR ( lob_loc IN BLOB, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW; DBMS_LOB.SUBSTR ( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET; DBMS_LOB.SUBSTR ( file_loc IN BFILE, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW;
pragma restrict_references(SUBSTR, WNDS, WNPS, RNDS, RNPS);
Return | Description |
---|---|
RAW |
Function overloading that has a |
VARCHAR2 |
|
NULL |
- |
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
.SUBSTR
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_14a IS src_lob CLOB; pos INTEGER := 2147483647; buf VARCHAR2(32000); BEGIN SELECT c_lob INTO src_lob FROM lob_table WHERE key_value = 21; buf := DBMS_LOB.SUBSTR(src_lob, 32767, pos); -- process the data END; CREATE OR REPLACE PROCEDURE Example_14b IS fil BFILE; pos INTEGER := 2147483647; pattern RAW; BEGIN SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21; dbms_lob.fileopen(fil, dbms_lob.file_readonly); pattern := dbms_lob.substr(fil, 255, pos); dbms_lob.fileclose(fil); END;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|