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 scope1. 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
- Array Types -
java.sql.Array's are realized in Oracle using proprietary types (
oracle.sql.VARRAY) and APIs that are encapsulated in
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.
- CLOB: supported; realized in-memory and when marshalled to SOAP messages as inlined strings
- BLOB: supported; realized in-memory as
bytearray'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.
1 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.