Oracle Large Objects

LOB's can store text, images, music and video inside the database. You can define BLOB, CLOB and NCLOB columns, you can also secure these with securefiles.

LOB's can store a maximum of 8 to 128 terabytes, depending on how you configure the database, you can call get_storage_limit using the dbms_lob package to get the maximum size.

get maximum LOB size

declare
   var1 clob := 'some string';
   var2 blob := hextoraw('43'||'41'||'52');
begin
   result := dbms_lob.get_storage_limit(var1);
   dbms_output.put_line('CLOB maximum limit: ' || result);
   dbms_output.put_line('CLOB length: ' || dbms_lob.getlength(var1));

   result := dbms_lob.get_storage_limit(var2);
   dbms_output.put_line('BLOB maximum: ' || result);
   dbms_output.put_line('CLOB length: ' || dbms_lob.getlength(var2));
end;
/

A CLOB, NCLOB or BLOB column can be in one of three states

NULL The column will contain a NULL value
empty The column contains a LOB locator that is an empty instance
Populated The column contains a LOB locator and a call to dbms_lob.getlength returns a postive value (see above for example).

CLOB and NCLOB

You can define CLOB or NCLOB in a table or a nested table, it allows you to store very large text files. CLOB columns are usually stored separately from the rest of the row in the table, only the descriptor or locator (pointer) is physically stored in the column. The locator points to where the physical contents of a CLOB are stored and provides a reference to a private work area in the SGA. This work area allows us to scroll through the contents and write new chunks of data. SQL allows you to convert streams upto 4,000 bytes whereas PL/SQL lets you convert 32,767 bytes of characters.

declaring a CLOB var1 clob;                                    -- declare a null ref to a clob
var1 clob := empty_clob();                    -- declare an empty clob       
var2 clob := 'some string';                   -- declare a clob with a string literal

A INSERT statement initializes a CLOB column, and then it returns the locator through the returning into clause into a local variable, the local variable is passed by reference and has a OUT mode of operation.

insert prototype example

insert into <table_name>
(column_name1 [,column_name2[,column_name(n+1)]] )
values
(empty_clob() [,column_name2[,column_name(n+1)]] )
returning column_name1 into <local_variable>;

empty_clob - sets the initial column value
column_name1 - inherits the datatype of the referenced column (clob in this case)
local_variable - sets the local variable. will contain the locator (pointer) of the clob

A update statement set a CLOB column value with empty_clob function, then it returns the column locator through the returning into clause into a local variable

update prototype example

update <table_name>
set ( column_name1 = column_value1,
      column_name2 = empty_clob,
      column_name(n+1) = column_value(n+)
    )
returning column_value2 into <local_variable>;

empty_clob - sets the initial column value
column_name1 - inherits the datatype of the referenced column (clob in this case)
local_variable - sets the local variable. will contain the locator (pointer) of the clob

Usually you would read/write in chunks that are hundreds of megabytes, gigabytes or terabytes in size but not for files that are less than 100MB. Some developers resort to using C, C++ or Java to accomplish reading and writing small clob files.

The dbms_lob package provides all the tools required to load large objects directly when they exceed the byte stream limitations of SQL or PL/SQL. Below is a complete example using this package.

CLOB Example
-- Create stored procedure to load a CLOB datatype.
CREATE OR REPLACE PROCEDURE load_clob_from_file
( src_file_name     IN VARCHAR2
, table_name        IN VARCHAR2
, column_name       IN VARCHAR2
, primary_key_name  IN VARCHAR2
, primary_key_value IN VARCHAR2 ) IS

  -- Define local variables for DBMS_LOB.LOADCLOBFROMFILE procedure.
  des_clob   CLOB;
  src_clob   BFILE := BFILENAME('GENERIC',src_file_name);
  des_offset NUMBER := 1;
  src_offset NUMBER := 1;
  ctx_lang   NUMBER := dbms_lob.default_lang_ctx;
  warning    NUMBER;
  
  -- Define a pre-reading size.
  src_clob_size NUMBER;
  
  -- Define local variable for Native Dynamic SQL.
  stmt VARCHAR2(2000);

BEGIN

  -- Opening source file is a mandatory operation.
  IF dbms_lob.fileexists(src_clob) = 1 AND NOT dbms_lob.isopen(src_clob) = 1 THEN
    src_clob_size := dbms_lob.getlength(src_clob);
    dbms_lob.open(src_clob,DBMS_LOB.LOB_READONLY);
  END IF;
  
  -- Assign dynamic string to statement.
  -- We are going to obtain the clob locator (pointer) to use later stmt := 'UPDATE '||table_name||' ' || 'SET '||column_name||' = empty_clob() ' || 'WHERE '||primary_key_name||' = '||''''||primary_key_value||''' ' || 'RETURNING '||column_name||' INTO :locator'; -- Run dynamic statement.
  -- the locator (pointer) to the clob will assigned to des_clob EXECUTE IMMEDIATE stmt USING OUT des_clob; -- Read and write file to CLOB, close source file and commit. dbms_lob.loadclobfromfile( dest_lob => des_clob , src_bfile => src_clob , amount => dbms_lob.getlength(src_clob) , dest_offset => des_offset , src_offset => src_offset , bfile_csid => dbms_lob.default_csid , lang_context => ctx_lang , warning => warning ); -- Close open source file. dbms_lob.close(src_clob); -- Commit write and conditionally acknowledge it. IF src_clob_size = dbms_lob.getlength(des_clob) THEN $IF $$DEBUG = 1 $THEN dbms_output.put_line('Success!'); $END COMMIT; ELSE $IF $$DEBUG = 1 $THEN dbms_output.put_line('Failure.'); $END RAISE dbms_lob.operation_failed; END IF; END load_clob_from_file; / -- Check before load. SELECT item_id , item_title , dbms_lob.getlength(item_desc) AS "SIZE" FROM item WHERE dbms_lob.getlength(item_desc) > 0; -- Insert description in all matching rows. BEGIN FOR i IN (SELECT item_id FROM item WHERE item_title = 'The Lord of the Rings - Fellowship of the Ring' AND item_type IN (SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'ITEM' AND common_lookup_column = 'ITEM_TYPE' AND REGEXP_LIKE(common_lookup_type,'^(dvd|vhs)*','i'))) LOOP -- Call procedure for matching rows. load_clob_from_file( src_file_name => 'LOTRFellowship.txt' , table_name => 'ITEM' , column_name => 'ITEM_DESC' , primary_key_name => 'ITEM_ID' , primary_key_value => TO_CHAR(i.item_id) ); END LOOP; END; / -- Check after load. SELECT item_id , item_title , dbms_lob.getlength(item_desc) AS "SIZE" FROM item WHERE dbms_lob.getlength(item_desc) > 0;

BLOB

You can define BLOB in a table or a nested table, it allows you to store images, music and video. BLOB columns are usually stored separately from the rest of the row in the table, only the descriptor or locator (pointer) is physically stored in the column. The locator points to where the physical contents of a BLOB are stored and provides a reference to a private work area in the SGA. This work area allows us to write new chunks of data.

declaring a BLOB var1 blob;                                    -- declare a null ref to a blob
var1 blob := empty_blob();                    -- declare an empty blob       
var2 blob := hextoraw('43'||'41'||'52');      -- declare a blob with a string literal

A INSERT statement initializes a BLOB column, and then it returns the locator through the returning into clause into a local variable, the local variable is passed by reference and has a OUT mode of operation.

insert prototype example

insert into <table_name>
(column_name1 [,column_name2[,column_name(n+1)]] )
values
(empty_blob() [,column_name2[,column_name(n+1)]] )
returning column_name1 into <local_variable>;

empty_blob - sets the initial column value
column_name1 - inherits the datatype of the referenced column (blob in this case)
local_variable - sets the local variable. will contain the locator (pointer) of the clob

A update statement set a BLOB column value with empty_blob function, then it returns the column locator through the returning into clause into a local variable

update prototype example

update <table_name>
set ( column_name1 = column_value1,
      column_name2 = empty_blob,
      column_name(n+1) = column_value(n+)
    )
returning column_value2 into <local_variable>

empty_blob - sets the initial column value
column_name1 - inherits the datatype of the referenced column (blob in this case)
local_variable - sets the local variable. will contain the locator (pointer) of the blob

The dbms_lob package provides all the tools required to load large objects directly when they exceed the byte stream limitations of SQL or PL/SQL. Below is a complete example using this package

 
-- Create stored procedure to load a BLOB datatype.
CREATE OR REPLACE PROCEDURE load_blob_from_file
( src_file_name     IN VARCHAR2
, table_name        IN VARCHAR2
, column_name       IN VARCHAR2
, primary_key_name  IN VARCHAR2
, primary_key_value IN VARCHAR2 ) IS

  -- Define local variables for DBMS_LOB.LOADCLOBFROMFILE procedure.
  des_blob      BLOB;
  src_blob      BFILE := BFILENAME('GENERIC',src_file_name);
  des_offset    NUMBER := 1;
  src_offset    NUMBER := 1;
  
  -- Define a pre-reading size.
  src_blob_size NUMBER;
  
  -- Define local variable for Native Dynamic SQL.
  stmt VARCHAR2(2000);

BEGIN

  -- Opening source file is a mandatory operation.
  IF dbms_lob.fileexists(src_blob) = 1 AND NOT dbms_lob.isopen(src_blob) = 1 THEN
    src_blob_size := dbms_lob.getlength(src_blob);
    dbms_lob.open(src_blob,DBMS_LOB.LOB_READONLY);
  END IF;
  
  -- Assign dynamic string to statement.
  -- We are going to obtain the clob locator (pointer) to use later  stmt := 'UPDATE '||table_name||' ' || 'SET '||column_name||' = empty_blob() ' || 'WHERE '||primary_key_name||' = '||''''||primary_key_value||''' ' || 'RETURNING '||column_name||' INTO :locator'; -- Run dynamic statement.
 -- the locator (pointer) to the blob will assigned to des_blob EXECUTE IMMEDIATE stmt USING OUT des_blob; -- Read and write file to BLOB, close source file and commit. dbms_lob.loadblobfromfile( dest_lob => des_blob , src_bfile => src_blob , amount => dbms_lob.getlength(src_blob) , dest_offset => des_offset , src_offset => src_offset ); -- Close open source file. dbms_lob.close(src_blob); -- Commit write and conditionally acknowledge it. IF src_blob_size = dbms_lob.getlength(des_blob) THEN $IF $$DEBUG = 1 $THEN dbms_output.put_line('Success!'); $END COMMIT; ELSE $IF $$DEBUG = 1 $THEN dbms_output.put_line('Failure.'); $END RAISE dbms_lob.operation_failed; END IF; END load_blob_from_file; / -- Check before load. SELECT item_id , item_title , dbms_lob.getlength(item_blob) AS "SIZE" FROM item WHERE dbms_lob.getlength(item_blob) > 0; -- Insert description in all matching rows. BEGIN FOR i IN (SELECT item_id FROM item WHERE item_title = 'Harry Potter and the Sorcerer''s Stone' AND item_type IN (SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'ITEM' AND common_lookup_column = 'ITEM_TYPE' AND REGEXP_LIKE(common_lookup_type,'^(dvd|vhs)*','i'))) LOOP -- Call procedure for matching rows. load_blob_from_file( src_file_name => 'HarryPotter1.png' , table_name => 'ITEM' , column_name => 'ITEM_BLOB' , primary_key_name => 'ITEM_ID' , primary_key_value => TO_CHAR(i.item_id) ); END LOOP; END; / -- Check after load. SELECT item_id , item_title , dbms_lob.getlength(item_blob) AS "SIZE" FROM item WHERE dbms_lob.getlength(item_blob) > 0;

Binary Files: BFILES

The bfile datatype works differently that CLOB's or BLOB's, they are read-only LOB datatypes and are stored externally from the database, the column stores an external locator (pointer) in the bfile column, which points to the external file.

First you must create a directory where your data will be kepted, then you can use this directory along with the file name to store a bfile

bfile example

-- Create the directories
create directory images as '/var/www/html/images';                                   -- Unix
create directory images as 'c:\program files\apache group\apache2\htdocs\images';    -- Wndows

-- Assign a value to the bfile column
update item item_photo = bfilename('IMAGES', 'School_Photo.png');

-- Commit the change
commit;

-- Check that the update worked.
declare
   file_locator bfile;
begin
   -- obtain the locator (pointer) of the file, which we will use with the dbms_lob package
   select item_photo into file_locator from item where item_id = 5555;

   if dbms_lob.fileexists(file_locator) = 1
   then
      dbms_output.put_line('File is: [' || dbms_job.getlength(file_locator) || ']' );
   else
      dbms_output.put_line('File does not exists');
   endif;
end;
/

DBMS_LOB package

The dbms_lob package contains a number of constants, methods, here is a short list below but you may want to consult the oracle documentation regarding the full explanations.

There are some bfile methods as well