Community
Participate
Working Groups
PL/SQL code may use %ROWTYPE to create a PL/SQL type that mixes PL/SQL types and JDBC Advanced types. For example: PROCEDURE SOMEPROC(PARAM1 IN INTEGER, PARAM2 OUT SOME_TABLE%ROWTYPE); SOME_TABLE%ROWTYPE will auto-gen a PL/SQL record type that is field-for-field compatible with the columns of SOME_TABLE. If SOME_TABLE uses a JDBC Advanced type - Object, Varray - for a column, then that field in the PL/SQL record will be that advanced type.
The current impl's of PublisherVisitor/PublisherListener PLSQLHelperObjectsBuilder PLSQLORDescriptorBuilder PLSQLOXDescriptorBuilder do not handle this mix of types (AdvancedJDBCQueryBuilder handles the JDBC Advanced types, but never in combination with PL/SQL types)
Use cases: i) %ROWTYPE points to table that has an Object type nested in another Object type: CREATE TYPE T1 AS OBJECT ( FIELD1 VARCHAR2(30), FIELD2 VARCHAR2(30) ); CREATE TYPE T2 AS OBJECT ( FIELD1 VARCHAR2(30), FIELD2 T1 ); CREATE TABLE TABLE1 ( FIELD1 NUMBER, FIELD2 T2, PRIMARY KEY (FIELD1) ); CREATE PACKAGE PACKAGE1 AS PROCEDURE P1(PARAM1 IN INTEGER, PARAM2 OUT TABLE1%ROWTYPE); END PACKAGE1; ii) %ROWTYPE points to table that has an Array type of Object types: CREATE TYPE T3 AS OBJECT ( FIELD1 VARCHAR2(30), FIELD2 VARCHAR2(30) ); CREATE TYPE T4 AS VARRAY(2) OF T3; CREATE TABLE TABLE2 ( FIELD1 NUMBER, FIELD2 T4, PRIMARY KEY (FIELD1) ); CREATE PACKAGE PACKAGE2 AS PROCEDURE P2(PARAM1 IN INTEGER, PARAM2 OUT TABLE2%ROWTYPE); END PACKAGE2;
Some changes to PLSQLHelperObjectsBuilder where checked-in to avoid NPE's and ClassCastException's. Remaining DBWSBuilder work (est few weeks) - PLSQLORDescriptorBuilder: handle (nested) SQLObject types . similar code is in AdvancedJDBCORDescriptorBuilder - how to share? - PLSQLOXDescriptorBuilder: handle (nested) SQLObject types . similar code is in AdvancedJDBCOXDescriptorBuilder - how to share? Last, need to test core EclipseLink anonymous PL/SQL block handling for the above use cases.
Created attachment 209972 [details] Proposed fix and supporting tests Adds support for PL/SQL Records with ObjectType, VArray and ObjectTableType fields.
Created attachment 209979 [details] Removed instanceof checks Removes unnecessary instanceof checks for ObjectType and ObjectTableType - use isJDBCType() method call instead.
Fix part# 1 checked in - revision: 10733 - Adds support for PL/SQL Records with ObjectType, VArray and ObjectTableType fields. Reviewed by: michael.norman@oracle.com Tests: all unit tests pass as expected; ComplexPLSQLSFTestSuite, ComplexPLSQLSPTestSuite
NOTE: EclipseLink does not support collections of collections, hence DBWS cannot support PL/SQL Collections containing VArray, Object or ObjectTable types, even though they are supported in the database.
re-purpose this bug to be more specific about handling Oracle Anchored types - identifiers with %TYPE or %ROWTYPE. Resolving these as a 'special' case of UnresolvedType needs work
Created attachment 212311 [details] Contains proposed fix + supporting test cases The fix basically involves sorting and pushing the unresolved types onto the stack in a particular order, such that the %Type and %ROWType ones are resolved AFTER the type they reference. In addition, local types in packages as well as field types should be handled in the 'findField' logic. Lastly, each UnresolvedType in the initial list should be updated such that a single instance of a given type is referenced by everyone.
Fixed.
Anchor types need to be resolved
Created attachment 214545 [details] For ROWTYPE support - don't add ROWTYPEType to list of Types
Created attachment 214546 [details] Support for %ROWTYPE
Reviewed by: matt.macivor@oracle.com Tests: All unit tests pass as expected; RowTypeTestSuite Revision: 11218
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
