(23) Working with Large Objects


  • Let you store text, images, music, and video in the database.
  • You can store character large objects in CLOB columns, and binary large objects inside the database as BLOB columns or outside the database as BFILE (binary file) columns.
  • BFILE columns: store a locator that points to the physical location of an external file.

  • LOBs can hold up to a maximum of 8 to 128 terabytes, depending on how you configure your database.
  • A call to the DBMS_LOB.GET_STORAGE_LIMIT function tells you your database maximum LOB size.

set serveroutput on
declare 
 var1 clob :=  'some string'; 
 var2 blob := hextoraw( '43'||'421'|| '52');
 result number;
begin
 result := dbms_lob.get_storage_limit(var1);
 dbms_output.put_line('CLOB maximum limit: '|| 
                        to_char(result/1024/1024/1024, '999,999') || ' Gigabytes');
 dbms_output.put_line('CLOB length: ' || dbms_lob.getlength(var1));
 
 result := dbms_lob.get_storage_limit(var2);
 dbms_output.put_line('BLOB maximum limit: '|| 
                       to_char(result/1024/1024/1024, '999,999') || ' Gigabytes');
 dbms_output.put_line('BLOB length: ' || dbms_lob.getlength(var2));
end;

anonymous block completed
CLOB maximum limit:   16,264 Gigabytes
CLOB length: 11
BLOB maximum limit:   32,528 Gigabytes
BLOB length: 4

what for?
  • simple structured data => relational tables
  • complex structured data => object-relational constructs (collections, references, UDTs)
  • semi-structured data => logical structure not typically interpreted by the database. LOBs can be used here.
  • Examples of semi-structured data include document files such as XML documents or word processor files.
  • unstructured data => photos, videos. LOBs can be used here.


On 11g R1: SecureFiles. LOBs reengineered
  • You can now define BLOB, CLOB, or NCLOB columns as SecureFiles when you create a table or alter it.
  • SecureFiles are declared with special storage parameters that let you encrypt, compress, and deduplicate them.
  • Oracle 11g lets you store SecureFiles in BLOB, CLOB, and NCLOB columns.
  • They work on a principle of Transparent Data Encryption (TDE) and use an Oracle Wallet as the encryption key.


  • BLOB, CLOB and NCLOB can be stored in the database (internal) or in external files.
    Internal LOBs can be either persistent or temporary.
    • Persistent LOB: Exists in a table row in the database.
    • Temporary LOB: Created when you instantiate a LOB only within the scope of your local application.
  • External LOBs are data objects stored in operating system files, outside the database tablespaces.
  • The database accesses external LOBs using the SQL data type BFILE, which is the only external LOB data type.
  • BFILEs are read-only data types.
  • External LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying file system as governed by the operating system.

COPY vs. REFERENCE semantics
  • Internal LOBs use copy semantics.: both the LOB locator and LOB value are logically copied during insert, update, or assignment operations.
  • External LOBs use reference semantics.: only the LOB locator is copied during insert operations.

A LOB (BLOB, CLOB, NCLOB) column can be in one of three states
NULLThe table cell is created, but the cell holds no locator or value. (The column contains a NULL value)
EmptyThe column contains a LOB locator that has no value. Length of LOB is zero.
PopulatedThe column contains a LOB locator and a value. A call to dbms_lob.getlength here returns a postive value.

CLOB and NCLOB
The CLOB datatype is an object type, therefore it requires implicit or explicit construction of an object instance.

Declaring a CLOB:
var1 CLOB;                  -- Declare a null reference to a CLOB.
var1 CLOB := empty_clob();  -- Declare an empty CLOB.
var2 CLOB := 'some_string'; -- Declare a CLOB with a string literal.

Manipulating a CLOB column
create table item ( 
 item_id number, 
 item_title varchar2(20), 
 item_desc  clob,
 item_photo bfile);

desc item
Name       Null Type         
---------- ---- ------------ 
ITEM_ID         NUMBER       
ITEM_TITLE      VARCHAR2(20) 
ITEM_DESC       CLOB         
ITEM_PHOTO      BFILE()    


INSERT into item values
  (1, 'Ulysses', empty_clob(), null);
1 row inserted.

-- This approach limits the amount of data that can be 
-- inserted into a CLOB column to is limited to 4K (SQL) or 32K (PL/SQL)

UPDATE item
SET item_desc = 'The Lord of the Rings is a high fantasy epic written by '||  
'philologist and University of Oxford professor J. R. R. Tolkien. The story '||
'began as a sequel to Tolkien's earlier, less complex children's fantasy '||
'novel The Hobbit (1937), but eventually developed into a much larger work. '||
'It was written in stages between 1937 and 1949, much of it during the '||
'Second World War.[1] It is the second best-selling novel ever written, '||
'with over 150 million copies sold.'
WHERE item_id =1;

Alternatively, you can use the DBMS_LOB package

Using DBMS_LOB, you can:

(a) Insert a new row and initialize the CLOB column with a call to empty_clob()
(b) Read chunks from the file and insert into the CLOB Column, appending each new chunk until copying the entire file (up to 128Tb).
(c) To open and read the file at the OS level from within the PLSQL Package, you need to create a DB Directory object and grant permission for the user to read from that directory.
(d) You may use conditional compilation directives to insert debut information..

-- You need to connect as SYSTEM to perform the following two steps: 
-- (1) create a virtual directory. should be conn as system
create directory generic as '/tmp/clob-file'; 

-- grant read permissions on the directory to the user;
grant read on directory generic to dev2;

-- Create procedure to load the file into the CLOB column.
CREATE OR REPLACE PROCEDURE load_clob_from_file
 (src_file_name in varchar2,
  table_name in varchar2,
  column_name in varchar2,
  pk_name in varchar2,
  pk_value in varchar2) is
  
  -- define local vars for dbms_lob.loadclobfromfile proc
  des_clob clob;
  -- the BFILENAME function secures the directory path for 
  -- the GENERIC directory from the database dictionary and 
  -- returns the absolute filename
  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;
  -- declare var to hold the size of the file to be imported.
  src_clob_size number;
  stmt varchar2(2000);
BEGIN
  -- open source file and reads it into a BFILE datatype
  if dbms_lob.fileexists(src_clob) = 1 and 
     NOT dbms_lob.isopen(src_clob)= 1 THEN
     src_clob_size := dbms_lob.getlength(src_clob);
     $IF $$DEBUG $THEN
       dbms_output.put_line('Opening CLOB. Size: '|| src_clob_size);
     $END
    dbms_lob.open(src_clob, DBMS_LOB.LOB_READONLY);
  end if;
  -- assign a dynamic string to the stmt var
  -- Initializes the CLOB column to an empty_clob()
  -- and returns a reference to the column into an 
  -- output variable
  stmt :=  'UPDATE ' ||table_name||' '
       ||  'SET    '||column_name||' = empty_clob()'
       ||  'WHERE  '|| pk_name||' = '||''''||pk_value||''' '
       ||  'RETURNING '||column_name||' INTO :locator';
  -- execute dynamic stmt
  -- the bind variable :locator is assigned to the output var
  $IF $$DEBUG $THEN
    dbms_output.put_line('Preparing to initialize CLOB column');
  $END
  EXECUTE IMMEDIATE stmt USING OUT des_clob;
  $IF $$DEBUG $THEN
    dbms_output.put_line('Initialization successful..');
  $END
  -- read and write file to CLOB, close source file and commit;
  -- the call to DBMS_LOB.LOADCLOBFROMFILE. The procedure 
  -- copy the contents of the file into the table column.
  -- This R/W operation bypasses the 32Kb handling limitation of PL/SQL
  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 the OS file.   
  dbms_lob.close(src_clob);
  
  IF src_clob_size = dbms_lob.getlength(des_clob) THEN
     $IF $$DEBUG $THEN
       dbms_output.put_line('Success!');
     $END
     commit;
  ELSE
    $IF $$DEBUG $THEN
      dbms_output.put_line('Failure.');
    $END
    RAISE dbms_lob.operation_failed;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error stack: '|| dbms_utility.format_error_stack());
    dbms_output.put_line('Error Backtrace: '|| dbms_utility.format_error_backtrace());
END load_clob_from_file;
/

PROCEDURE load_clob_from_file compiled

-- Alter session to set conditional compilation directive 
-- and turn on debugging 
SQL> alter session set plsql_ccflags = 'DEBUG:TRUE';


SQL> set serveroutput on 
SQL> BEGIN
 load_clob_from_file (
   src_file_name    => 'ulysses.txt',
   table_name       => 'item',
   column_name      =>  'item_desc',
   pk_name          => 'item_id',
   pk_value         => to_char(1));
END;
/
anonymous block completed
Opening CLOB. Size: 11989153
Preparing to initialize CLOB column
Initialization successful..
Success!


















No comments:

Post a Comment