#

| Appendix C Built-In Packages |

## 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

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.