Difference between revisions of "EclipseLink/Development/DBWS/OracleUseCases"

Revision as of 16:32, 17 December 2009

Supported Use-Cases for Oracle Platforms

All of the use-cases support for non-Oracle Platforms are supported on Oracle

The design-time utility DBWSBuilder uses custom Data Dictionary queries for the Oracle Platform (the java.sql.DatabaseMetaData API suffers from well-documented problems of accuracy and scope¹). By doing so, additional use-cases can be supported:

• Advanced JDBC types as arguments to Stored Procedure calls
  • Object Types

java.sql.Struct's are realized in Oracle using proprietary types (oracle.sql.STRUCT) and APIs that are encapsulated in EclipseLink's DatabasePlatform (specifically org.eclipse.persistence.platform.database.oracle.Oracle8Platform or higher)

• • Array Types

java.sql.Array's are realized in Oracle using proprietary types (oracle.sql.VARRAY) and APIs that are encapsulated in EclipseLink's DatabasePlatform (specifically org.eclipse.persistence.platform.database.oracle.Oracle8Platform or higher)

An extreme (!) use-case: an object type wrapped in an array type, wrapped in another object type which is again wrapped in an array type and (finally!) wrapped in a third object type.

• Scalar PL/SQL datatypes - some PL/SQL datatypes have no JDBC equivalent and must be converted. The strategy is to use an anonymous PL/SQL block and handle the type conversion before the invocation of the Stored Procedure:
  • BOOLEAN

DECLARE
  x_target BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
BEGIN
  bool_test(x=>x_target);
END;

• • PLS_INTEGER
  • SMALLINT
  • NUMERIC
  • BINARY_INTEGER
  • PLS_INTEGER
  • NATURAL
  • NATURALN
  • POSITIVE
  • POSITIVEN
  • SIGNTYPE
  • DEC
  • DECIMAL
  • LONG
  • LONG_RAW
  • RAW

• Advanced PL/SQL types as arguments to Stored Procedure calls
  • Record types

this is something

• • Collection types

And again, something more

¹ Compliance to the meta-data APIs available through the java.sql.DatabaseMetaData is weak - no vendor implements the APIs fully, nor in a standard fashion. For example, the simple act of getting the names of available databases is different across platforms: on Oracle the getSchemas method is used while for DB2/Sybase/MS SQLServer the getCatalogs method is used. Another example of cross-platform meta-data issues is the getColumns method: for PostgreSQL, the name of the table must be lower-case; for Oracle, it must be upper-case; others support mixed-case. link is a case-study of some of the issues.