1 Reply Latest reply on May 27, 2003 10:35 AM by clayroach

    How to: Oracle BLOB/CLOBs and JBoss

    Stephen Coy Novice

      This question seems to keep cropping up from time to time. Maybe Dain
      can add the following information as an addendum or something to the
      CMP doco.

      This information applies to JBoss 3.0.4 and newer.

      Before attempting to store LOBs in Oracle, I've found it's useful to know a few things:

      1. Oracle allows you to store four kinds of large data fields in a
      table:

      a) LONG RAW
      b) LONG VARCHAR
      c) BLOB
      d) CLOB

      The LONG data types work with earlier versions of JBoss, but you are
      limited to one LONG column per table. It's my understanding that
      Oracle may phase out this type in the future.

      The LOB data types need current JBoss releases, but you can have as
      many LOB columns as you like.

      2. There are two primary versions of the JDBC driver:
      i) classes12.jar supports 1.2 and 1.3 JREs;
      ii) ojdbc14.jar supports the 1.4 JRE and it's newer JDBC spec.

      You will also see the old "classes12.zip" around too. I recommend
      that you use the jar because amongst other things, Oracle have
      started using jar manifests with version information.
      [Sigh. I've just checked the Oracle web site and classes12.jar
      has disappeared, and the zip file has acquired a manifest, BUT the
      version info in the manifests does not seem to match the web page -
      I can't win :-( ]

      If you download a full JDBC distribution from Oracle, you will get
      "debug" versions as well, which can be useful. See the associated
      release notes for more information.

      The current version of the Oracle JDBC driver is 9.2.0.1. We have
      found that this resolves a significant memory leak that is present
      in 9.2.0.0.

      3. Within the jar, there are two types of Oracle JDBC driver:

      a) The "thin" driver
      This is the 100% java JDBC implementation. Therefore it is
      portable and will run in any version compliant JRE;

      b) The "oci" driver
      This driver uses JNI to make use of native Oracle client
      libraries on the local host. Therefore, it will only work on
      platforms that support the Oracle client software.

      For more information on this, please see:

      <http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/java.920/a96654/toc.htm>

      You will need a (free) OTN account to see this.

      4. I recommend using the Oracle 9.2 client software (especially the
      jar files), even if the database itself is an earler release, such
      as 8.1.7. Earlier versions of the JDBC drivers in particular were
      problematical when trying to use LOBs.



      As many users have discovered, it is not possible to store LOB data
      that is larger than 4-5k using the "thin" driver and the standard JDBC
      apis. I think that it might be possible using Oracle specific
      interfaces, but we can't/won't do that in JBoss (and I've not tried it
      either).

      Therefore, in order to access any reasonably sized LOBs using Oracle
      and JBoss, you MUST use the "oci" drivers. A corrollary of this is that
      your JBoss deployment platform must also support the native Oracle
      client software.

      JBoss/LOB support is not automatic. You need to have a
      jbosscmp-jdbc.xml file and specify the mappings explicitly:

      <cmp-field>
      <field-name>whatever</field-name>
      <column-name>whatever</column-name>
      <jdbc-type>BLOB</jdbc-type>
      <sql-type>BLOB</sql-type>
      </cmp-field>

      For binary data, JBoss stores byte array data directly to the database.
      All other java types are serialised using a java.rmi.MarshalledObject,
      therefore your object must be serialisable.


      I think that's about all there is to it.

      Steve Coy
      (returning to mundane life landscaping the back yard while on vacation)

        • 1. Re: How to: Oracle BLOB/CLOBs and JBoss
          clayroach Newbie

          Having struggled with this LOB issue for a bit here, I have come to the same conclusions as Steve. However, since our application here requires storage of large and multi-dimensional arrays, there is little choice except to either split these types into separate tables using LONG RAWs (which is a dog for performance reasons), or to try to make the BLOB datatype work.

          After a bit of trial-and-error, and searching through forums like this, we are considering using a third-party driver from Data Direct. The Data Direct driver (though a bit expensive) conforms nicely to the JDBC spec, and best of all, it allows me to use LOBs of any size in my object model. This is nice, since we are using Toplink to abstract out our JDBC layer, and want to be able to ship our product on a number of different databases (including Oracle).


          It's a shame that there are outside vendors who can fix this Oracle BLOB issue, but Oracle seems unable to do so, FOR THEIR OWN DATABASE!!!


          Clay Roach