20.  Oracle

20.1. Using Query Hints with Oracle
20.2. Known issues with Oracle

Example 2.17.  Example properties for Oracle

openjpa.ConnectionDriverName: oracle.jdbc.driver.OracleDriver
openjpa.ConnectionURL: jdbc:oracle:thin:@SERVER_NAME:1521:DB_NAME

20.1.  Using Query Hints with Oracle

Oracle has support for "query hints", which are formatted comments embedded in SQL that provide some hint for how the query should be executed. These hints are usually designed to provide suggestions to the Oracle query optimizer for how to efficiently perform a certain query, and aren't typically needed for any but the most intensive queries.

Example 2.18.  Using Oracle Hints

Query query = em.createQuery(...);
query.setHint("openjpa.hint.OracleSelectHint", "/*+ first_rows(100) */");
List results = query.getResultList();

20.2.  Known issues with Oracle

  • The Oracle JDBC driver has significant differences between different versions. It is important to use the officially supported version of the drivers (10.2.0.1.0/11.2.0.x.0), which is backward compatible with previous versions of the Oracle server. It can be downloaded from http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.

  • Empty string/char values are stored as NULL.

  • Oracle corp's JDBC driver for Oracle has only limited support for batch updates. The result for OpenJPA is that batching of some statements may fail and in some cases, the exact object that failed an optimistic lock check cannot be determined. OpenJPA will throw an OptimisticException with more failed objects than actually failed. This situation may be resolved by disabling statement batching by setting the batchLimit value to zero or by using a more recent Oracle JDBC Driver (11.2.0.1) with batch support improvements. Attempting to resolve the issue with a more current driver is recommended since disabling statement batching can result in a decrease in performance.

    Example 2.19.  Property to disable statement batching for Oracle

    openjpa.jdbc.DBDictionary: oracle(batchLimit=0)
    


  • Oracle cannot store numbers with more than 38 digits in numeric columns.

  • Floats and doubles may lose precision when stored.

  • CLOB columns cannot be used in queries.

  • The use of LOBs with persistent attributes of a streaming data type (ex. java.io.InputStream or java.io.Reader) may require the same connection to be used over the life of the transaction or entity manager. If the same connection is not used for persistent operations a java.io.IOException with message Closed Connection may result. The OpenJPA property openjpa.ConnectionRetainMode can be used to control how OpenJPA uses datastore connections. See Section 8, “ Configuring the Use of JDBC Connections ” for details.

    Example 2.20.  Property to retain connection over the lifetime of the entity manager

    openjpa.ConnectionRetainMode: always
    


  • Mapping persistent attributes to XML columns requires a JDBC 4 compliant driver if XML strings are longer than 4000 bytes, as counted in database. Otherwise an ORA-01461: can bind a LONG value only for insert into a LONG column error may result.

  • If Oracle dictionary property MaxEmbeddedBlobSize or MaxEmbeddedClobSize is set to some limit (i.e. not -1) and embedded collection with BLOB/CLOB attribute is used, a "org.apache.openjpa.persistence.ArgumentException: "x.y.z.EmbedOwner.embedCollection<element:class x.y.z.EmbedValue>" is mapped as embedded, but embedded field "x.y.z.EmbedOwner.embedCollection.x.y.z.EmbedValue.blob" is not embeddable. Embedded element/key/value types are limited to simple fields and direct relations to other persistent types" error may result. To overcome this limitation, either use JDBC driver 11.2.0.x.0 (or later version) or set both MaxEmbeddedBlobSize and MaxEmbeddedClobSize properties to -1.