EclipseLink/Development/DBWS/PLSQLStoredFunctions

From Eclipsepedia

Jump to: navigation, search


Complex PL/SQL datatypes as parameters (and return argument) for StoredFunctions

Requirements

Use of PL/SQL datatypes as parameters for StoredFunctions is required by JDev QA testing (a 'torture test' that tries every datatype). In addition, the issue has been raised on the Oracle TopLink forum.

Fixing DatasourceCall

As described in bug 27516, the specific DatasourceCall behaviour that handles complex PL/SQL datatypes as parameters is in the PLSQLStoredProcedureCall class, making it un-available to any sub-class of StoredFunctionCall (i.e. PLSQLStoredFunctionCall)

Call classdiagram.png

In order to better handle complex PL/SQL datatypes as parameters, the implementation of parameter-handling in DatasourceCall needs to be fixed. In addition to 'starving' StoredFunctionCall's from handling complex parameters, the current code leaks its internal implementation details to surrounding classes: a parameter's value is held in a separate array from the array that holds (at the same index) the integer code that categorizes the parameter. Additionally, the parameter at index 0 is 'special', representing the return argument for StoredFunctionCall's. Finally, handling INOUT PL/SQL parameters have special handling needs as the anonymous PL/SQL blocks used to handle complex PL/SQL datatypes does not natively handle INOUT args. The args must be split into two parameters (dubbed IN_SYNTHETIC and OUT_SYNTHETIC) and argument-order must be re-computed as all OUT args must be moved after the IN args.

The parameter/parameterType implementation has leaked to:

/org.eclipse.persistence.core/src/org/eclipse/persistence/queries

Call
SQLCall

/org.eclipse.persistence.core/src/org/eclipse/persistence/eis/interactions/

EISInteraction
IndexedInteraction
MappedInteraction
QueryStringInteraction
XMLInteraction
XQueryInteraction

/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/databaseaccess

DatasourceCall
DatabaseCall
ParameterizedSQLBatchWritingMechanism
StoredProcedureCall
StoredFunctionCall
DatabasePlatform

/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/expressions

SQLDeleteAllStatement
SQLModifyAllStatementForTempTable
SQLUpdateAllStatement
SQLUpdateAllStatementForOracleAnonymousBlock
SubSelectExpression
SQLDeleteAllStatement
SubSelectExpression

/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/queries

DatabaseQueryMechanism

/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/sessions/factories

ObjectPersistenceRuntimeXMLProject_11_1_1

/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database

OraclePlatform
PostgreSQLPlatform

/org.eclipse.persistence.core/src/org/eclipse/persistence/tools/schemaframework

StoredProcedureGenerator

/org.eclipse.persistence.dbws/src/org/eclipse/persistence/internal/xr

StoredFunctionQueryHandler

Refactoring PLSQLStoredProcedureCall

After all the above re-work is complete (and all the core EclipseLink tests pass - yikes!), the real work begins: refactoring PLSQLStoredProcedureCall so that the logic that handles generating nested conversion functions inside anonymous PL/SQL blocks can be shared between PLSQLStoredProcedureCall and the not-yet-implemented PLSQLStoredFunctionCall