[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);


C.5 DBMS_ JOB		C.7 DBMS_LOCK