OracleAS TopLink 10g (9.0.4)


Date: April 2004

How-To: Map Large Objects (LOBs) to Oracle Databases with OracleAS TopLink


  • How the Oracle8i and Oracle9i Database Server handle database fields that contain LOB values.
  • How the Oracle JDBC OCI driver (type 2) and server driver (type 3) differ from the Oracle JDBC thin driver in the way they handle LOB values.
  • Why OracleAS TopLink 9.0.4 throws an exception when attempting to write a LOB value greater than 4k bytes using the Oracle JDBC thin driver.
  • How to use OracleAS TopLink to map LOB values to an Oracle8i or Oracle9i Database Server using the Oracle JDBC OCI driver.
  • How to use OracleAS TopLink 9.0.4.1 (or higher) to map LOB values greater than 4k to an Oracle8i or Oracle9i Database Server using the Oracle JDBC thin driver.

Software Requirements

  • OracleAS TopLink 10g (9.0.4.1) or higher available from Metalink.
  • Oracle8i Database Server or higher

Introduction

The Oracle8i DatabaseServer and higher provide two LOB data types:

  • Binary Large Object (BLOB) type for large amounts of unstructured binary data (such as audio or video)
  • Character Large Object (CLOB) type for large amounts of character data (such as Base64 encoded data or scripts)

Using these types appropriately allows the database server to handle large amounts of binary or character data as efficiently as possible.

Data stored in a LOB is called a value. In an Oracle database, the value of a LOB can be stored inline within a table row or outside of a table. If you do not set DISABLE STORAGE IN ROW, and a LOB value is less than approximately 4k bytes, then the value is stored inline. If the LOB value is greater than 4k (or 5.9k after Oracle8i Database Server version 8.1.7), the value is stored outside of the table.

In a Java (JDBC) application, Oracle mandates manipulating LOB values through a LOB locator. A LOB locator is a pointer to the actual location of the LOB value. A locator is always stored in a row, regardless of whether the value is stored inline or not. A client (such as OracleAS TopLink) can always read a LOB value without the locator but the locator must be used to write a LOB value if the LOB value is stored outside of the row.

This how-to explains how to map LOBs:

Using the Oracle JDBC OCI Driver (type 2) or Server Driver (type 3)

When using Oracle JDBC OCI driver (type 2) or server driver (type 3), the driver encapsulates accessing the locator in the native call, so the client (such as OracleAS TopLink) does not have to explicitly acquire the locator before writing a LOB value. When using the OCI driver, you can use OracleAS TopLink to read and write a LOB value up to the theoretical maximum size of 4Gb and you can use any type of mapping.

Use this procedure to map LOB values using the Oracle JDBC OCI driver:

  1. Configure the descriptors of classes that contain LOB values. You can use any type of mapping if you are using the Oracle JDBC OCI driver.

    In this example, the picture attribute contains a BLOB value and the script attribute contains a CLOB values.

    DirectToField pictureMapping = new DirectToField();
    pictureMapping.setAttributeName("picture");
    pictureMapping.setFieldName("IMAGE.PICTURE");
    descriptor.addMapping(pictureMapping);

    DirectToField scriptMapping = new DirectToField();
    scriptMapping.setAttributeName("script");
    scriptMapping.setFieldName("IMAGE.SCRIPT");
    descriptor.addMapping(scriptMapping);

  2. Get the DatabaseLogin from the session.

    DatabaseLogin login = session.getLogin();

  3. Configure parameter bindings to accommodate the type of LOB values you are mapping.

    If you are mapping BLOB values, enable byte array binding to ensure that byte arrays are bound in case shouldBindAllParameters is set to false and enable the use of streams for binding to ensure that stream binding is used for byte arrays.

    login.setUsesByteArrayBinding(true);
    login.setUsesStreamsForBinding(true);

  4. If you are mapping CLOB values, enable the use of string binding to ensure that long string values (greater than 255 characters) are bound as character streams.

    login.useStringBinding();

Using the Oracle JDBC Thin Driver

When using the Oracle JDBC thin driver, the client (such as OracleAS TopLink) is responsible for explicitly acquiring the locator before writing a LOB value. For more information on this behavior, see Oracle bug 1273826.

Prior to 9.0.4.1, OracleAS TopLink did not retrieve the locator during the commit and its straight INSERT or UPDATE, irrelevant to the bindings, would fail with an exception if the size of the LOB value was greater than 4k (or 5.9k after Oracle8i Database Server version 8.1.7). OracleAS TopLink would throw one of the following exceptions in this case:

In 9.0.4.1 and above, by using a type conversion mapping, you can configure OracleAS TopLink to access the locator during commit. This allows OracleAS TopLink to read and write a LOB value up to the theoretical maximum size of 4Gb when using the Oracle JDBC thin driver.

Use this procedure to map LOB values using the Oracle JDBC thin driver:

  1. Configure the descriptors of classes that contain LOB values. You must use a type conversion mapping if you are using the Oracle JDBC thin driver.

    In this example, the picture attribute contains a BLOB value and the script attribute contains a CLOB values.

    TypeConversionMapping pictureMapping = new TypeConversionMapping();
    pictureMapping.setAttributeName("picture");
    pictureMapping.setFieldName("IMAGE.PICTURE");
    pictureMapping.setFieldClassification(java.sql.Blob.class);
    descriptor.addMapping(pictureMapping);

    TypeConversionMapping scriptMapping = new TypeConversionMapping();
    scriptMapping.setAttributeName("script");
    scriptMapping.setFieldName("IMAGE.SCRIPT");
    scriptMapping.setFieldClassification(java.sql.Clob.class);
    descriptor.addMapping(scriptMapping);

  2. Get the DatabaseLogin from the session and configure a platform that provides locator support.

    DatabaseLogin login = session.getLogin();

  3. Set the appropriate platform.

    If you are using an Oracle8i Database Server, use class oracle.toplink.oraclespecific.Oracle8Platform:

    login.usePlatform(new Oracle8Platform());

  4. If you are using an Oracle9i Database Server, use class oracle.toplink.oraclespecific.Oracle9Platform:

    login.usePlatform(new Oracle9Platform());

Summary

In this document you should have learned:

The API for this feature includes oracle.toplink.mappings.TypeConversionMapping and oracle.toplink.sessions.DatabaseLogin.

For other LOB options, see oracle.toplink.oraclespecific.Oracle8Platform and oracle.toplink.oraclespecific.Oracle9Platform.