Binary Large Object (BLOB) or DBMS_LOB

The LOB data type allows holding and manipulating unstructured data such as texts, graphic images, video sound files. The dbms_lob package was designed to manipulate LOB data types.  Oracle provides the dbms_lob package which is used to access and manipulate LOB values in both internal or external storage locations.

With this package dbms_lob, it is possible to read and modify given BLOB, CLOB and NLOBtypes as well as effecting operations of reading in BFILEs.  The types of data used for package dbms_lob include:

1.    BLOB

2.    RAW

3.    CLOB

4.    VARCHAR2

5.    INTEGER

6.    BFILE

It is important to remember that the maximum size for a data type LOB is 8 TB for databases with blocks of 8k, and 128 TB for databases configured with blocks of 32k. Package dbms_lob contains procedures that are used to manipulate segments of type LOB (BLOBs, CLOBs and NCLOBs) and BFILEs.

Internal LOBs are stored in the database tablespaces in way that optimizes space and provides efficient access. Internal LOBs participate in the transactional model of the server. Internal LOBs can store binary data (BLOBs), single-byte character data (CLOBs), or fixed-width single-byte or multibyte character data (NCLOBs). An NCLOB consists of character data that corresponds to the national character set defined for the Oracle database. Varying width character data is not supported in Oracle

External LOBs are stored in operating system files outside the database tablespaces as BFILEs, binary data. The database stores a pointer to the LOB's location in the file system. They cannot participate in transactions, and access is read-only.

Below is a list containing some of the main procedures and functions that are presented in this package and what they do.

·         isopen:  This function checks to see if the LOB was already opened using the input locator.

·         createtemporary:  The procedure createtemporary creates a temporary CLOB or BLOB and its corresponding index in the user default temporary tablespace.

·         instr:  Used to return the matching position of the nth occurrence of the pattern in the LOB.

·         getlength:  Used to get the length of specified LOB.

·         copy: Copies part or all of a source internal LOB to a destination internal LOB.

·         writeappend:  Writes a specified amount of data to the end of an internal LOB.

·         trim:  Trims the value of the internal LOB to the length specified by the newlen parameter.

Example of BLOB Package:

/*
define the directory inside Oracle when logged on as SYS
create or replace directory ctemp as 'c: emp\';

grant read on the directory to the Staging schema
grant read on directory ctemp to staging;
*/

-- the storage table for the image file
CREATE TABLE pdm (
dname  VARCHAR2(30),  -- directory name
sname  VARCHAR2(30),  -- subdirectory name
fname  VARCHAR2(30),  -- file name
iblob  BLOB);         -- image file

-- create the procedure to load the file
CREATE OR REPLACE PROCEDURE load_file (
 pdname VARCHAR2,
 psname VARCHAR2,
 pfname VARCHAR2) IS

 src_file BFILE;
 dst_file BLOB;
 lgh_file BINARY_INTEGER;
BEGIN

src_file := bfilename('CTEMP', pfname);

  -- insert a NULL record to lock
  INSERT INTO pdm
  (dname, sname, fname, iblob)
  VALUES
  (pdname, psname, pfname, EMPTY_BLOB())
  RETURNING iblob INTO dst_file;

  -- lock record
  SELECT iblob
  INTO dst_file
  FROM pdm
  WHERE dname = pdname
  AND sname = psname
  AND fname = pfname
  FOR UPDATE;

  -- open the file
  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

  -- determine length
  lgh_file := dbms_lob.getlength(src_file);

  -- read the file
  dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

  -- update the blob field
  UPDATE pdm
  SET iblob = dst_file
  WHERE dname = pdname
  AND sname = psname
  AND fname = pfname;

  -- close file
  dbms_lob.fileclose(src_file);
END load_file;
/