[Appendix C] C.6 DBMS_LOB (PL/SQL8 Only)

C.6 DBMS_LOB (PL/SQL8 Only)

Use the DBMS_LOB package to manipulate LOBs (large objects) from within a PL/SQL program and SQL statements. With DBMS_LOB you can read and modify BLOBs (binary LOBs), CLOBs (single-byte character data), and NCLOBs (fixed-width single-byte or multibyte character data), and you can perform read-only operations on BFILEs (file-based LOBs).

C.6.1 The APPEND procedure

Call the APPEND procedure to append the contents of a source LOB to a destination LOB. The specifications are:

PROCEDURE DBMS_LOB.APPEND
   (dest_lob IN OUT BLOB,
    src_lob IN BLOB);

PROCEDURE DBMS_LOB.APPEND
   (dest_lob IN OUT CLOB CHARACTER SET ANY_CS,
    src_lob IN CLOB CHARACTER SET DEST_LOB%CHARSET);

C.6.2 The COMPARE function

Use the compare function to compare two LOBs in their entirety, or compare just parts of two LOBs. The specifications are:

FUNCTION DBMS_LOB.COMPARE
   (lob_1 IN BLOB,
    lob_2 IN BLOB,
    amount IN INTEGER := 4294967295,
    offset_1 IN INTEGER := 1,
    offset_2 IN INTEGER := 1)
RETURN INTEGER;

FUNCTION DBMS_LOB.COMPARE
   (lob_1 IN CLOB CHARACTER SET ANY_CS,
    lob_2 IN CLOB CHARACTER SET LOB_1%CHARSET,
    amount IN INTEGER := 4294967295,
    offset_1 IN INTEGER := 1,
    offset_2 IN INTEGER := 1)
RETURN INTEGER;

FUNCTION DBMS_LOB.COMPARE
   (file_1 IN BFILE,
    file_2 IN BFILE,
    amount IN INTEGER,
    offset_1 IN INTEGER := 1,
    offset_2 IN INTEGER := 1)
RETURN INTEGER;

C.6.3 The COPY procedure

The copy procedure copies all or part of a source LOB to a destination LOB. The specifications are:

PROCEDURE DBMS_LOB.COPY
   (dest_lob IN OUT BLOB,
    src_lob IN BLOB,
    amount IN OUT INTEGER,
    dest_offset IN INTEGER := 1,
    src_offset IN INTEGER := 1);

PROCEDURE DBMS_LOB.COPY
   (dest_lob IN OUT CLOB CHARACTER SET ANY_CS,
    src_lob IN CLOB CHARACTER SET DEST_LOB%CHARSET,
    amount IN OUT INTEGER,
    dest_offset IN INTEGER := 1,
    src_offset IN INTEGER := 1);

C.6.4 The ERASE procedure

The erase procedure erases an entire LOB or part of a LOB. The specifications are:

PROCEDURE DBMS_LOB.ERASE
   (lob_loc IN OUT BLOB,
    amount IN OUT INTEGER,
    offset IN INTEGER := 1);

PROCEDURE DBMS_LOB.ERASE
   (lob_loc IN OUT CLOB CHARACTER SET ANY_CS,
    amount IN OUT INTEGER,
    offset IN INTEGER := 1);

C.6.5 The FILECLOSE procedure

Call the fileclose procedure to close a BFILE which has previously been opened in your session or PL/SQL block. The specification is:

PROCEDURE DBMS_LOB.FILECLOSE (file_loc IN OUT BFILE);

C.6.6 The FILECLOSEALL procedure

The filecloseall procedure closes all BFILEs which have previously been opened in your session. The specification is:

PROCEDURE DBMS_LOB.FILECLOSEALL;

C.6.7 The FILEEXISTS function

The fileexists function returns 1 if the file you have specified via a BFILE locator exists. The specification is:

FUNCTION DBMS_LOB.FILEEXISTS (file_loc IN BFILE) RETURN INTEGER;

C.6.8 The FILEGETNAME procedure

Use the filegetname procedure to translate a BFILE locator into its directory alias and filename components. The specification is:

PROCEDURE DBMS_LOB.FILEGETNAME
   (file_loc IN BFILE,
    dir_alias OUT VARCHAR2,
    filename OUT VARCHAR2);

C.6.9 The FILEISOPEN function

The fileisopen function returns 1 if the BFILE is already open. The specification is:

FUNCTION DBMS_LOB.FILEISOPEN (file_loc IN BFILE) RETURN INTEGER;

C.6.10 The FILEOPEN procedure

The fileopen procedure opens a BFILE with the specified mode. The specification is:

PROCEDURE DBMS_LOB.FILEOPEN
   (file_loc IN OUT BFILE,
    open_mode IN BINARY_INTEGER := FILE_READONLY);

C.6.11 The GETLENGTH function

Use the getlength function to return the length of the specified LOB in bytes or characters, depending on the type of LOB. The specifications are:

FUNCTION DBMS_LOB.GETLENGTH (lob_loc IN BLOB) RETURN INTEGER;

FUNCTION DBMS_LOB.GETLENGTH(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;

FUNCTION DBMS_LOB.GETLENGTH (file_loc IN BFILE) RETURN INTEGER;

C.6.12 The INSTR function

The instr function returns the matching location of the nth occurrence of the specified pattern in the LOB. The specifications are:

FUNCTION DBMS_LOB.INSTR
   (lob_loc IN BLOB,
    pattern IN RAW,
    offset IN INTEGER := 1,
    nth IN INTEGER := 1)
RETURN INTEGER;

FUNCTION DBMS_LOB.INSTR
   (lob_loc IN CLOB CHARACTER SET ANY_CS,
    pattern IN VARCHAR2 CHARACTER SET LOB_LOC%CHARSET,
    offset IN INTEGER := 1,
    nth IN INTEGER := 1)
RETURN INTEGER;

FUNCTION DBMS_LOB.INSTR
   (file_loc IN BFILE,
    pattern IN RAW,
    offset IN INTEGER := 1,
    nth IN INTEGER := 1)
RETURN INTEGER;

C.6.13 The READ procedure

Call the read procedure to read a portion of a LOB into a buffer variable. The specifications are:

PROCEDURE DBMS_LOB.READ
   (lob_loc IN BLOB,
    amount IN OUT BINARY_INTEGER,
    offset IN INTEGER,
    buffer OUT RAW);

PROCEDURE DBMS_LOB.READ
   (lob_loc IN CLOB CHARACTER SET ANY_CS,
    amount IN OUT BINARY_INTEGER,
    offset IN INTEGER,
    buffer OUT VARCHAR2 CHARACTER SET LOB_LOC%CHARSET);

PROCEDURE DBMS_LOB.READ
   (file_loc IN BFILE,
    amount IN OUT BINARY_INTEGER,
    offset IN INTEGER,
    buffer OUT RAW);

C.6.14 The SUBSTR function

The substr function returns the specified number of bytes or characters from a LOB. The specifications are:

FUNCTION DBMS_LOB.SUBSTR
   (lob_loc IN BLOB,
    amount IN INTEGER := 32767,
    offset IN INTEGER := 1)
RETURN RAW;

FUNCTION DBMS_LOB.SUBSTR
   (lob_loc IN CLOB CHARACTER SET ANY_CS,
    amount IN INTEGER := 32767,
    offset IN INTEGER := 1)
RETURN VARCHAR2;

FUNCTION DBMS_LOB.SUBSTR
   (file_loc IN BFILE,
    amount IN INTEGER := 32767,
    offset IN INTEGER := 1)
RETURN RAW;

C.6.15 The TRIM procedure

Use the trim procedure to trim the LOB value to the length you specify. The specifications are:

PROCEDURE DBMS_LOB.TRIM
   (lob_loc IN OUT BLOB,
    newlen IN INTEGER);

PROCEDURE DBMS_LOB.TRIM
   (lob_loc IN OUT CLOB CHARACTER SET ANY_CS,
    newlen IN INTEGER);

C.6.16 The WRITE procedure

Call the write procedure to write a specified number of bytes or characters from a buffer variable into a LOB at a specified position. The specifications are:

PROCEDURE DBMS_LOB.WRITE
   (lob_loc IN OUT BLOB,
    amount IN OUT BINARY_INTEGER,
    offset IN INTEGER,
    buffer IN RAW);

PROCEDURE DBMS_LOB.WRITE
   (lob_loc IN OUT CLOB CHARACTER SET ANY_CS,
    amount IN OUT BINARY_INTEGER,
    offset IN INTEGER,
    buffer IN VARCHAR2 CHARACTER SET LOB_LOC%CHARSET);