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 |
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> empty_clob - sets the initial column value |
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> empty_clob - sets the initial column value |
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. |
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> empty_blob - sets the initial column value |
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> empty_blob - sets the initial column value |
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. |
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 -- Assign a value to the bfile column |
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