Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages


How to use utl_http
Transform query to XML
Scheduling in 10g
dbms_profiler explained


DBMS_LOB package

With the DBMS_LOG package you can handle Large OBjects or LOB's. There are 2 basic types of LOB's:
  • internal LOBs These are LOB's stored in the database such as BLOB ( unstructured binary ("raw") data), CLOB (character data with same character set as database) and NCLOB (character data that corresponds to the national character set of the database).
    Internal LOBs are divided into persistent and temporary LOBs.
  • external LOBs These are LOB's stored as an operating system file, such as BFILE.
The DBMS_LOB package provides API's to read and write to these large objects.

LOB Locators

To work with a LOB you need a LOB locator. A LOB locator is a pointer to the actual location of the LOB (in the database or the external filesystem).

program units

The DBMS_LOB package contains the following program units. These are grouped by function.

PL/SQL Functions/Procedures To Modify BLOB, CLOB, and NCLOB Values

APPEND() Appends the LOB value to another LOB
COPY() Copies all or part of a LOB to another LOB
ERASE() Erases part of a LOB, starting at a specified offset
LOADFROMFILE() Load BFILE data into an internal LOB
LOADCLOBFROMFILE() Load character data from a file into a LOB
LOADBLOBFROMFILE() Load binary data from a file into a LOB
TRIM() Trims the LOB value to the specified shorter length
WRITE() Writes data to the LOB at a specified offset
WRITEAPPEND() Writes data to the end of the LOB

PL/SQL Functions/Procedures To Read or Examine Internal and External LOB Values

COMPARE() Compares the value of two LOBs
GETCHUNKSIZE() Gets the chunk size used when reading and writing. This only works on internal LOBs and does not apply to external LOBs (BFILEs).
GETLENGTH() Gets the length of the LOB value
INSTR() Returns the matching position of the nth occurrence of the pattern in the LOB
READ() Reads data from the LOB starting at the specified offset
SUBSTR() Returns part of the LOB value starting at the specified offset

PL/SQL Functions/Procedures To Operate on Temporary LOBs

CREATETEMPORARY() Creates a temporary LOB
ISTEMPORARY() Checks if a LOB locator refers to a temporary LOB
FREETEMPORARY() Frees a temporary LOB

PL/SQL Read-Only Functions/Procedures for BFILEs

FILECLOSE() Closes the file. Use CLOSE() instead.
FILECLOSEALL() Closes all previously opened files
FILEEXISTS() Checks if the file exists on the server
FILEGETNAME() Gets the directory alias and file name
FILEISOPEN() Checks if the file was opened using the input BFILE locators. Use ISOPEN() instead.
FILEOPEN() Opens a file. Use OPEN() instead.

PL/SQL Functions/Procedures To Open and Close Internal and External LOBs

OPEN() Opens a LOB
ISOPEN() Sees if a LOB is open
CLOSE() Closes a LOB