Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 329435

Summary:

DBWS should handle Anchored types (%ROWTYPE, %TYPE)

Product:

z_Archived

Reporter:

Mike Norman <michael.norman>

Component:

Eclipselink

Assignee:

Nobody - feel free to take it <nobody>

Status:

RESOLVED FIXED

QA Contact:

Severity:

major

Priority:

CC:

david.mccann, david.twelves, eclipselink.dbws-inbox

Version:

unspecified

Target Milestone:

---

Hardware:

All

OS:

All

Whiteboard:

Attachments:

Description	Flags
Proposed fix and supporting tests	none
Removed instanceof checks	none
Contains proposed fix + supporting test cases	none
For ROWTYPE support - don't add ROWTYPEType to list of Types	none
Support for %ROWTYPE	none

Description Mike Norman

2010-11-04 09:19:25 EDT

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.

Comment 1 Mike Norman

2010-11-04 09:22:45 EDT

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)

Comment 2 Mike Norman

2010-11-16 10:43:53 EST

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;

Comment 3 Mike Norman

2010-11-16 10:50:56 EST

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.

Comment 4 David McCann

2012-01-24 10:12:34 EST

Created attachment 209972 [details]
Proposed fix and supporting tests

Adds support for PL/SQL Records with ObjectType, VArray and ObjectTableType fields.

Comment 5 David McCann

2012-01-24 10:45:22 EST

Created attachment 209979 [details]
Removed instanceof checks

Removes unnecessary instanceof checks for ObjectType and ObjectTableType - use isJDBCType() method call instead.

Comment 6 David McCann

2012-01-24 14:25:24 EST

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

Comment 7 David McCann

2012-02-06 10:18:06 EST

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.

Comment 8 Mike Norman

2012-02-17 11:09:53 EST

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

Comment 9 David McCann

2012-03-08 12:43:14 EST

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.

Comment 10 David McCann

2012-03-20 11:38:03 EDT

Fixed.

Comment 11 David Twelves

2012-04-17 10:21:08 EDT

Anchor types need to be resolved

Comment 12 David McCann

2012-04-25 12:52:47 EDT

Created attachment 214545 [details]
For ROWTYPE support - don't add ROWTYPEType to list of Types

Comment 13 David McCann

2012-04-25 12:53:13 EDT

Created attachment 214546 [details]
Support for %ROWTYPE

Comment 14 David McCann

2012-04-25 14:15:52 EDT

Reviewed by:  matt.macivor@oracle.com 
Tests:  All unit tests pass as expected;  RowTypeTestSuite
Revision: 11218

Comment 15 Eclipse Webmaster

2022-06-09 10:36:09 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink