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

Latest revision as of 14:16, 21 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 since 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 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 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: e.g. BOOLEAN (pass in argument as JDBC integer 0 or 1

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

• numeric types: PLS_INTEGER, SMALLINT, NUMERIC, BINARY_INTEGER, PLS_INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, DEC, DECIMAL, LONG, LONG_RAW, RAW
  Using the JDBC type with the widest precision java.sql.Types.NUMERIC, a simple assignment will do:

DECLARE
  y_target PLS_INTEGER := :1;
BEGIN
  int_test(y=>y_target);
END;

• OUT arguments

DECLARE
  x_target VARCHAR(20) := :1;
  y_target BOOLEAN;
BEGIN
  some_pkg2.nint_test3(x=>x_target, y=>y_target);
  :2 := SYS.SQLJUTL.BOOL2INT(y_target);
END;

• INOUT arguments: PL/SQL anonymous blocks do not support INOUT arguments; thus an additional computed bind variable is needed:

DECLARE
  x_target VARCHAR(20) := :1;
  y_target BOOLEAN := SYS.SQLJUTL.INT2BOOL(:2);
BEGIN
  test_pkg.test2(x=>x_target, y=>y_target);
  :3 := SYS.SQLJUTL.BOOL2INT(y_target);
END;

Advanced PL/SQL types as arguments to Stored Procedure calls

• Record types - a PL/SQL record is created either explicitly in a package declaration:

CREATE OR REPLACE PACKAGE SOMEPACKAGE AS
  TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER;
  TYPE TBL2 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE ARECORD IS RECORD (
    T1 TBL1,
    T2 TBL2,
    T3 BOOLEAN
  );
  ...
END SOMEPACKAGE;

or implicitly using the %ROWTYPE macro:

PROCEDURE rec_test(z IN emp%ROWTYPE)

In either case, the trick to passing along the required information is to 'stage' it a compatible JDBC Object type whose fields and types are the same as the record's:

CREATE TYPE EMP_TYPE AS OBJECT (
  EMPNO NUMBER(4,0),
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4,0),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2,0)
);

The generated anonymous PL/SQL block can take advantage of this compatibility:

DECLARE
  Z_TARGET emp%ROWTYPE;
  Z_COMPAT EMP_TYPE := :1;
BEGIN
  Z_TARGET.EMPNO := Z_COMPAT.EMPNO;
  Z_TARGET.ENAME := Z_COMPAT.ENAME;
  Z_TARGET.JOB := Z_COMPAT.JOB;
  Z_TARGET.MGR := Z_COMPAT.MGR;
  Z_TARGET.HIREDATE := Z_COMPAT.HIREDATE;
  Z_TARGET.SAL := Z_COMPAT.SAL;
  Z_TARGET.COMM := Z_COMPAT.COMM;
  Z_TARGET.DEPTNO := Z_COMPAT.DEPTNO;
  REC_TEST(Z=>Z_TARGET);
END;

For more complicated nested structures, the anonymous PL/SQL block will contain nested functions that do the compatibility conversion and can be called multiple times at different levels of the nested structure.

• Collection types: in the same way that compatible JDBC Object types are used, compatible JDBC Array types are used to pass along the information into the Stored Procedure's argument that is a PL/SQL collection type.

LOB datatypes

• CLOB: supported; realized in-memory and when marshalled to SOAP messages as inlined strings
• BLOB: supported; realized in-memory as byte[] array's; can be marshalled to SOAP messages inlined as base64binary or can be handled as binary attachments.
• NCLOB: not supported

It is important to note that CLOB's and BLOB's cannot exceed the JVM's memory handling capabilities; i.e. there is no support for streamed attachments.

¹ 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. This link is a case-study of some of the issues.