Large Objects

These are my notes from studying the Dummies PL/SQL book on Advanced Datatypes. (In the works are my notes from the Oracle online documentation.)

Storage

8-128Tb of binary/textual data
The environment might restrict to 4Gb

Types

CLOB - internally stored character data
BLOB - internally stored binary data (audio/video)
BFILE - externally stored file

Internal storage: (entire file stored in db)

Can be retrieved quickly

No concern for individual file management

Without a good backup utility, it can take hours/days to do a complete db backup

Lob locator, not the data is stored in the table; the actual data is stored elsewhere in the database.

External storage: (only file names stored in db)

Might be slow if many files in a directory

Files might be moved/deleted or changed by user

Read-only access

Lob locator is stored in a BFILE column

See Oracle Database Application Developers Guide - Large Objects for complete details.

---

Create Table Lob_Table
    ( Row_Id    Number        Primary Key
    , File_Name Varchar2(2000)
    , Sample_Cl Clob
    , Sample_Bl Blob
    , Sample_Bf Bfile  );

SQL> desc lob_table
 Name         Null?    Type
 ------------ -------- ----------------
 ROW_ID       NOT NULL NUMBER
 FILE_NAME             VARCHAR2(2000)
 SAMPLE_CL             CLOB
 SAMPLE_BL             BLOB
 SAMPLE_BF             BINARY FILE LOB

---

Populating BFILE

To refer to a directory (i.e., c:\data\lob), use the name assigned in 'Create Directory' statement.

Connect System/??????
Create Directory LOB_Dir as 'C:\Data\Lob';
Grant Read, Write On Directory LOB_Dir To Public;
Connect Scott/Tiger

Create a pointer to a file and insert into table.

Declare
    v_Bf Bfile;                       --stores pointer
    v_Fn Varchar2(100) := 'LOB.TXT';  --stores filename
Begin
    /*-----------------------------------------------*/
    /* Creates a pointer to the file                 */
    /*-----------------------------------------------*/
    v_Bf := BFileName ('LOB_DIR', v_Fn);
    
    /*-----------------------------------------------*/
    /* Inserts a row with the pointer.               */
    /*-----------------------------------------------*/
    Insert Into Lob_Table                  
               ( Row_Id , File_Name , Sample_Bf)
        Values (   1    , v_Fn      , v_Bf     ); 
End;
/
Select Row_Id , File_Name from Lob_Table;

    ROW_ID FILE_NAME
---------- ---------------
         1 LOB.TXT

Load Data To A Clob
Declare
    v_File_Bf   Bfile  ;
    v_Sample_Cl Clob   ;
    Warning     Number ;
    /*-----------------------------------------------*/
    /* You can usually ignore the following four     */
    /*-----------------------------------------------*/
    Lang_Ctx    Number := Dbms_Lob.Default_Lang_Ctx;
    Charset_Id  Number := 0;
    Src_Offset  Number := 1;
    Dst_Offset  Number := 1;
Begin
    /*-----------------------------------------------*/
    /* This creates the actual CLOB with a length of */
    /* 0 bytes.                                      */
    /*-----------------------------------------------*/
    Update Lob_Table   
       Set Sample_Cl = Empty_Clob() 
     Where Row_Id  = 1; 

    /*-----------------------------------------------*/
    /* Put pointers into the variable Clob and Bfile.*/
    /* -Sample Bfile has a pointer to file 'LOB.TXT' */
    /* -Sample Clob is null, but that can't be used. */
    /*-----------------------------------------------*/
    Select Sample_Bf ,   Sample_Cl 
      Into v_File_Bf , v_Sample_Cl
      From Lob_Table
     Where Row_Id  = 1; 

    Dbms_Lob.FileOpen
        ( v_File_Bf , Dbms_Lob.File_ReadOnly);
    
    /*-----------------------------------------------*/
    /* Read the file content into the clob.          */
    /*-----------------------------------------------*/
    Dbms_Lob.LoadClobFromFile( v_Sample_Cl
                             , v_File_Bf
                             , Dbms_Lob.GetLength (v_File_Bf)
                             , Src_Offset /* These    */
                             , Dst_Offset /* are      */
                             , Charset_Id /* usually  */
                             , Lang_Ctx   /* ignored. */
                             , Warning);

    Dbms_Lob.FileClose (v_File_Bf); 
End;
/
PL/SQL procedure successfully completed.

The variable is a pointer to the real CLOB.

Any changes to the CLOB made by using the local pointer go directly to the table -- no 'update' is necessary.

---

Loading a page to a BLOB

Declare
    v_File_Bf     Bfile:= BFileName ('LOB_DIR','TEST.JPG');
    v_Sample_Bl   Blob;
    Src_Offset_Nr Number := 1;
    Dst_Offset_Nr Number := 1;
Begin
    Update Lob_Table
       Set Sample_Bl = Empty_Blob()
     Where Row_Id  = 1;

    Select   Sample_Bl
      Into v_Sample_Bl
      From Lob_Table
     Where Row_Id  = 1;

    Dbms_Lob.FileOpen (v_File_Bf, Dbms_Lob.File_ReadOnly);

    Dbms_Lob.LoadBlobFromFile ( v_Sample_Bl
                              , v_File_Bf
                              , Dbms_Lob.GetLength(v_File_Bf)
                              , Dst_Offset_Nr
                              , Src_Offset_Nr ) ;

    Dbms_Lob.FileClose (v_File_Bf);
End;
/
PL/SQL procedure successfully completed.
Performing basic string operations on CLOBs
You can use many regular string operations on CLOBs.
search for patterns
get length
get part of the code
etc.Declare
    v_Sample_Cl Clob;
    v_Nr        Number;
    v_Tx        Varchar2 (2000);
    v_Add_Tx    Varchar2 (2000) :='Loaded: '
                      ||To_Char(Sysdate,'Mm/Dd/Yyyy Hh24:Mi');
Begin
    Select   Sample_Cl
      Into v_Sample_Cl
      From Lob_Table
     Where Row_Id  = 1
     For Update; 

    /*-----------------------------------------------*/
    /* write at the end                              */
    /*-----------------------------------------------*/
    Dbms_Lob.WriteAppend ( v_Sample_Cl      
                         , Length (v_Add_Tx)
                         , v_Add_Tx);     

    v_Nr := Instr  (v_Sample_Cl, 'Loaded:', -1);
    v_Tx := Substr (v_Sample_Cl, v_Nr);
    
    Dbms_Output.Put_Line (v_Tx);
End;
/
PL/SQL procedure successfully completed.

Note: A COMMIT could make the LOB pointer invalid.