Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)
ISBN: 0596514468
EAN: 2147483647
EAN: 2147483647
Year: 2004
Pages: 234
Pages: 234
Authors: Steven Feuerstein, Bill Pribyl
| Appendix C Built-In Packages |
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).
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);
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;
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);
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);
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);
The filecloseall procedure closes all BFILEs which have previously been opened in your session. The specification is:
PROCEDURE DBMS_LOB.FILECLOSEALL;
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;
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);
The fileisopen function returns 1 if the BFILE is already open. The specification is:
FUNCTION DBMS_LOB.FILEISOPEN (file_loc IN BFILE) RETURN INTEGER;
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);
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;
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;
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);
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;
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);
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.