Loading LOBs and Data Into LOBs

How do I Load a 1Mb File into a CLOB Column?

Question

How do I insert a file of 1Mb which is stored on disk, into a CLOB column of my table. I thought DBMS_LOB.LOADFROMFILE should do the trick, but, the document says it is valid for BFILE only. How do I do this?

Answer

You can use SQL*Loader. See Oracle8i Utilities or in this manual, Chapter 4, "Managing LOBs", Using SQL Loader to Load LOBs on .

You can use loadfromfile() to load data into a CLOB, but the data is transferred from the BFILE as raw data -- i.e., no character set conversions are performed. It is up to you to do the character set conversions yourself before calling loadfromfile().

Use OCILobWrite() with a callback. The callback can read from the operating system (OS) file and convert the data to the database character set (if it's different than the OS file's character set) and then write the data to the CLOB.

How Do We Improve BLOB and CLOB Performance When Using JDBC Driver To Load?

Question

We are facing a performance problem concerning BLOBs and CLOBs. Much time is consumed when loading data into the BLOB or CLOB using JDBC Driver.

Answer

It's true that inserting data into LOBs using JDBC Thin driver is slower as it still uses the DBMS_LOB package and this adds the overhead of a full JDBC CallableStatement execution for each LOB operation.

With the JDBC OCI and JDBC server-side internal drivers, the inserts are faster because native LOB APIs are used. There is no extra overhead from JDBC driver implementation.

It's recommended that you use InputStream and OutputStream for accessing and manipulating LOB data. By using streaming access of LOBs, JDBC driver will handle the buffering of the LOB data properly to reduce the number of network round-trips and ensure that each database operation uses a data size as a multiple of the LOB's natural chunk size.

Here is an example that uses OutputStream to write data to a BLOB:

/*

* This sample writes the GIF file john.gif to a BLOB.

*/

import java.sql.*;
import java.io.*;
import java.util.*;

// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.driver.*;

//needed for new CLOB and BLOB classes
import oracle.sql.*;

public class LobExample
{
  public static void main (String args [])
       throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop table persons");
    }
    catch (SQLException e)
    {
      // An exception could be raised here if the table did not exist already.
    }

    // Create a table containing a BLOB and a CLOB
    stmt.execute ("create table persons (name varchar2 (30), picture blob)");
    
    // Populate the table
    stmt.execute ("insert into persons values ('John', EMPTY_BLOB())");
    
    // Select the BLOB 
    ResultSet rset = stmt.executeQuery ("select picture from persons where name 
= 'John'");
    if (rset.next ())
    {
      // Get the BLOB locator from the table
      BLOB blob = ((OracleResultSet)rset).getBLOB (1);

      // Declare a file handler for the john.gif file
      File binaryFile = new File ("john.gif");

      // Create a FileInputStream object to read the contents of the GIF file
      FileInputStream istream = new FileInputStream (binaryFile);

      // Create an OutputStram object to write the BLOB as a stream
      OutputStream ostream = blob.getBinaryOutputStream ();

      // Create a tempory buffer  
      byte[] buffer = new byte[1024];
      int length = 0;

      // Use the read() method to read the GIF file to the byte 
      // array buffer, then use the write() method to write it to 
      // the BLOB.
      while ((length = istream.read(buffer)) != -1)
        ostream.write(buffer, 0, length);

      // Close the inputstream and outputstream
      istream.close();
      ostream.close();

      // Check the BLOB size
      System.out.println ("Number of bytes written = "+blob.length());
    }

    // Close all resources
    rset.close();
    stmt.close();
    conn.close(); 
  }
}

Note that you'll get even better performance if you use DBMS_LOB.LOADFROMFILE() instead of using DBMS_LOB.WRITE().

In order to be able to use DBMS_LOB.LOADFROMFILE(), the data to be written into the LOB must be in a server-side file.