The idea of storing large objects in the database can raise objections from some developers, but it’s an effective technique to manage images and documents. The DBMS_LOB package lets you store and manage large objects (LOBs) in the Oracle Database.

This article shows you how to load large objects into the Oracle database with the DBMS_LOB package. It shows you how to load a character large object (CLOB) and then how to load a binary large object (BLOB) to the Oracle Database. Oracle stores CLOB and BLOB data inside the Oracle Database. Oracle also supports a binary large file (BFILE), which the Oracle Database stores externally.

 LOBs can hold up to a maximum of 8 to 128 terabytes, depending on how you configure your database’s db_block_size parameter. You can set the db_block_size parameter to a 2KB to 32KB. The valid values are multiples of two – 2KB, 4KB, 8KB, 16KB, and 32KB. The default value of the db_block_size parameter is 8KB. Oracle uses the following formula to set the maximum size of large objects in an instance:

Maximum size = (4GB - 1) * db_block_size

The DBMS_LOB package requires you to write PL/SQL blocks to load CLOB and BLOB columns. This article has three sections. The first section shows you how to create a virtual directory. The second shows you how to load a physical file as a CLOB column. The third shows you how to load a physical file as a BLOB column.

How to Setup a Virtual Directory

You need to create virtual directories before you can use the DBMS_LOB package to read files from the operating system. A virtual directory acts like as an alias to an external directory.

The system user typically creates virtual directories. The syntax differs only slightly between Linux and Windows, as you can see below:

Linux or Unix

SQL> CREATE DIRECTORY upload AS '/tmp';

Windows

SQL> CREATE DIRECTORY upload AS 'C:\Windows\temp';

After you create the virtual directory, you need to grant read permissions on the directory to a sample student user. The syntax is:

SQL> GRANT READ ON DIRECTORY upload TO student;

The next two sections cover how you upload external files into CLOB or BLOB columns. They also include full code that you can copy and test in your environments.

How to Load a Physical File as a CLOB Column

This section shows you how to read a physical file from the operating system and how to load it into a CLOB column. The solution is a custom load_clob_from_file procedure that leverages the DBMS_LOB package’s loadclobfromfile procedure.

Writing a wrapper function lets you simplify how to use the DBMS_LOB functions and procedures to load external files. At least, it simplifies things when you leverage Native Dynamic SQL (NDS) with the DBMS_LOB functions and procedures.

The load_clob_from_file procedure takes the file name and key information necessary to look up a unique row and return a CLOB column. The load_clob_from_file procedure takes a table and column name for the SELECT-list, and a primary key column name and value for the WHERE clause. The primary key column and value in the WHERE clause let you find a unique row. The combination of these parameters lets you use an NDS statement in the load_clob_from_file procedure to load an external file to any CLOB column.

The following is the load_clob_from_file procedure:

SQL> CREATE OR REPLACE PROCEDURE load_clob_from_file

  2  ( src_file_name     IN VARCHAR2

  3  , table_name        IN VARCHAR2

  4  , column_name       IN VARCHAR2

  5  , primary_key_name  IN VARCHAR2

  6  , primary_key_value IN VARCHAR2 ) IS

  7 

  8    /* Define local variables. */

  9    des_clob   CLOB;

 10    src_clob   BFILE := BFILENAME('UPLOAD',src_file_name);

 11    des_offset NUMBER := 1;

 12    src_offset NUMBER := 1;

 13    ctx_lang   NUMBER := dbms_lob.default_lang_ctx;

 14    warning    NUMBER;

 15 

 16    /* Define a pre-reading size. */

 17    src_clob_size NUMBER;

 18 

 19    /* Define local variable for Native Dynamic SQL. */

 20    stmt VARCHAR2(2000);

 21  BEGIN

 22    /* Opening source file is a mandatory operation. */

 23    IF      dbms_lob.fileexists(src_clob) = 1

 24    AND NOT dbms_lob.isopen(src_clob) = 1 THEN

 25        src_clob_size := dbms_lob.getlength(src_clob);

 26        dbms_lob.open(src_clob,DBMS_LOB.LOB_READONLY);

 27    END IF;

 28 

 29    /* Assign dynamic string to statement. */

 30    stmt := 'UPDATE '||table_name||' '

 31           || 'SET    '||column_name||' = empty_clob() '

 32           || 'WHERE  '||primary_key_name

 33           ||' = '||''''||primary_key_value||''' '

 34           || 'RETURNING '||column_name||' INTO :locator';

 35 

 36    /* Run dynamic statement. */

 37    EXECUTE IMMEDIATE stmt USING OUT des_clob;

 38 

 39    /* Read and write file, close file and commit. */

 40    dbms_lob.loadclobfromfile(

 41        dest_lob     => des_clob

 42      , src_bfile    => src_clob

 43      , amount       => dbms_lob.getlength(src_clob)

 44      , dest_offset  => des_offset

 45      , src_offset   => src_offset

 46      , bfile_csid   => dbms_lob.default_csid

 47      , lang_context => ctx_lang

 48      , warning      => warning );

 49 

 50    /* Close open source file. */

 51    dbms_lob.close(src_clob);

 52 

 53    /* Commit write and conditionally acknowledge it. */

 54    IF src_clob_size = dbms_lob.getlength(des_clob) THEN

 55      $IF $$DEBUG = 1 $THEN

 56        dbms_output.put_line('Success!');

 57      $END

 58      COMMIT;

 59    ELSE

 60      $IF $$DEBUG = 1 $THEN

 61        dbms_output.put_line('Failure.');

 62      $END

 63      RAISE dbms_lob.operation_failed;

 64    END IF;

 65  END load_clob_from_file;

 66  /

Line 10 declares the external file by using the BFILENAME function and assigns it to the local src_clob variable. The BFILENAME function takes two parameters. The first parameter is a virtual directory and the second is the physical file name.

Line 13 declares the local ctx_lang variable and assigns the result from the DBMS_LOB package’s default_lang_ctx function. The default_lang_ctx function assigns the NLS_LANG value to the ctx_lang variable.

Line 23 checks whether the physical file exists with the DBMS_LOB fileexists function. You must check for the physical file to avoid the DBMS_LOB package throwing an exception.

Line 24 then checks whether the physical file is open before trying to open the external file. This check is required so opening the file doesn’t raise an exception.

Line 25 calls the getlength function of the DBMS_LOB package. The getlength function returns the size of the external physical file.

Line 26 opens the external physical file with the open procedure of the DBMS_LOB package. The open procedure uses the local src_clob variable to open the physical file.

The NDS statement on lines 30 to 34 returns a reference to the CLOB column of a table. Line 37 executes the NDS statement and provides the src_des variable as a reference to the UPDATE statement.

The loadclobfromfile procedure of the DBMS_LOB package on lines 40 to 48, and it reads and loads the external file into a local des_clob variable. Line 51 closes the external file. Line 54 checks whether the original external file is the same size as the destination variable.

The load_clob_from_file procedure closes the open file on line 58 when the destination variable is the same size as the external file. The load_clob_from_file procedure raises an operation_failed exception when the size of the local variable differs from the external file.

The following anonymous block acts as a unit test for the load_clob_from_file procedure, and uses the Video Store model available with my Oracle Database 12c PL/SQL Programming book. You can download the full Video Store model from McGraw-Hill’s web site.

SQL> BEGIN

  2    FOR i IN (SELECT item_id

  3              FROM   item

  4              WHERE  item_title = 'The Lord of the Rings ...'

  5              AND    item_type IN

  6               (SELECT common_lookup_id

  7                FROM   common_lookup

  8                WHERE  common_lookup_table = 'ITEM'

  9                AND    common_lookup_column = 'ITEM_TYPE'

 10                AND    REGEXP_LIKE(

 11                           common_lookup_type

 12                         ,'^(dvd|vhs)*','i'))) LOOP

 13

 14      /* Call procedure for matching rows. */

 15      load_clob_from_file(

 16          src_file_name     => 'LOTRFellowship.txt'

 17        , table_name        => 'ITEM'

 18        , column_name       => 'ITEM_DESC'

 19        , primary_key_name  => 'ITEM_ID'

 20        , primary_key_value => TO_CHAR(i.item_id));

 21    END LOOP;

 22  END;

 23  /

The call to the load_clob_from_file procedure on lines 15 through 20 provides the file name, the SELECT-list column name, the table name, and the primary key column name and value for the WHERE clause. Line 20 casts the surrogate key value to a string before calling the load_clob_from_file procedure.

This section has shown you how to upload a large character object into a CLOB column. The next section shows you how to modify the load_clob_from_file procedure.

How to Load a Physical File as a BLOB Column

The load_clob_from_file procedure only requires subtle changes to enable it to work with BLOB columns. It is tempting to economize space by only showing the changes but it would be too hard to follow.

The following is the load_blob_from_file procedure.

SQL> CREATE OR REPLACE PROCEDURE load_blob_from_file

  2  ( src_file_name     IN VARCHAR2

  3  , table_name        IN VARCHAR2

  4  , column_name       IN VARCHAR2

  5  , primary_key_name  IN VARCHAR2

  6  , primary_key_value IN VARCHAR2 ) IS

  7 

  8    /* Define local variables. */

  9    des_blob   BLOB;

 10    src_blob   BFILE := BFILENAME('UPLOAD',src_file_name);

 11    des_offset NUMBER := 1;

 12    src_offset NUMBER := 1;

 13    ctx_lang   NUMBER := dbms_lob.default_lang_ctx;

 14    warning    NUMBER;

 15 

 16    /* Define a pre-reading size. */

 17    src_blob_size NUMBER;

 18 

 19    /* Define local variable for Native Dynamic SQL. */

 20    stmt VARCHAR2(2000);

 21  BEGIN

 22    /* Opening source file is a mandatory operation. */

 23    IF      dbms_lob.fileexists(src_blob) = 1

 24    AND NOT dbms_lob.isopen(src_blob) = 1 THEN

 25        src_blob_size := dbms_lob.getlength(src_blob);

 26        dbms_lob.open(src_blob,DBMS_LOB.LOB_READONLY);

 27    END IF;

 28 

 29    /* Assign dynamic string to statement. */

 30    stmt := 'UPDATE '||table_name||' '

 31           || 'SET    '||column_name||' = empty_clob() '

 32           || 'WHERE  '||primary_key_name

 33           ||' = '||''''||primary_key_value||''' '

 34           || 'RETURNING '||column_name||' INTO :locator';

 35 

 36    /* Run dynamic statement. */

 37    EXECUTE IMMEDIATE stmt USING OUT des_blob;

 38 

 39    /* Read and write file, close file and commit. */

 40    dbms_lob.loadclobfromfile(

 41        dest_lob     => des_blob

 42      , src_bfile    => src_blob

 43      , amount       => dbms_lob.getlength(src_blob)

 44      , dest_offset  => des_offset

 45      , src_offset   => src_offset

 46      , bfile_csid   => dbms_lob.default_csid

 47      , lang_context => ctx_lang

 48      , warning      => warning );

 49 

 50    /* Close open source file. */

 51    dbms_lob.close(src_blob);

 52 

 53    /* Commit write and conditionally acknowledge it. */

 54    IF src_blob_size = dbms_lob.getlength(des_blob) THEN

 55      $IF $$DEBUG = 1 $THEN

 56        dbms_output.put_line('Success!');

 57      $END

 58      COMMIT;

 59    ELSE

 60      $IF $$DEBUG = 1 $THEN

 61        dbms_output.put_line('Failure.');

 62      $END

 63      RAISE dbms_lob.operation_failed;

 64    END IF;

 65  END load_blob_from_file;

 66  /

Only the des_clob, src_clob, src_clob_size variable names change between the load_clob_from_file to load_blob_from_file procedures. The variables become des_blob, src_blob, and src_blob_size respectively. Unfortunately, the changes in the variable names occur on too many lines.

Only the file name and column name change in the unit testing anonymous block. The item_desc column is a CLOB column and the item_blob is a BLOB column.

This article has shown you how to effectively use the DBMS_LOB package to load large character and binary objects.