EclipseLink/Development/DBWS/PLSQLStoredFunctions
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)
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