Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EclipseLink/Development/DBWS/PLSQLStoredFunctions"

(Complex PL/SQL datatypes as parameters (and return argument) for StoredFunctions)
 
(12 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
<css>
 +
  .source-sql {padding:1em;border:1px solid black; background-color: white;}
 +
  .source-java5 {padding:1em;border:1px solid black; background-color: white;}
 +
  .source-xml {padding:1em;border:1px solid black; background-color: white;}
 +
  .source-text {padding:1em;border:1px solid black; background-color: white;}
 +
</css>
 +
__NOTOC__
 
== Complex PL/SQL datatypes as parameters (and return argument) for StoredFunctions ==
 
== Complex PL/SQL datatypes as parameters (and return argument) for StoredFunctions ==
  
As described in [https://bugs.eclipse.org/bugs/show_bug.cgi?id=275156 bug 27516], the specific <code>DatasourceCall</code> behaviour that handles complex PL/SQL datatypes as parameters is in <code>PLSQLStoredProcedureCall</code>, making it un-available to a sub-class of <code>StoredFunctionCall</code>
+
=== 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 [http://forums.oracle.com/forums/thread.jspa?threadID=625502&tstart=0&messageID=2382402#2382402 Oracle TopLink forum].
 +
 
 +
=== Fixing <code>DatasourceCall</code> ===
 +
As described in [https://bugs.eclipse.org/bugs/show_bug.cgi?id=275156 bug 27516], the specific <code>DatasourceCall</code> behaviour that handles complex PL/SQL datatypes as parameters is in the <code>PLSQLStoredProcedureCall</code> class, making it un-available to any sub-class of <code>StoredFunctionCall</code> (i.e. <code>PLSQLStoredFunctionCall</code>)
  
 
[[Image:Call_classdiagram.png]]
 
[[Image:Call_classdiagram.png]]
  
=== Refactoring <code>DatasourceCall</code> Hierarchy ===
+
In order to better handle complex PL/SQL datatypes as parameters, the implementation of parameter-handling in <code>DatasourceCall</code> needs to be fixed. In addition to 'starving' <code>StoredFunctionCall</code>'s from handling complex parameters, the current code <b><i>leaks</i></b> 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 <tt>0</tt> is 'special', representing the return argument for <code>StoredFunctionCall</code>'s. Finally, handling <tt>INOUT</tt> PL/SQL parameters have special handling needs as the anonymous PL/SQL blocks used to handle complex PL/SQL datatypes does not natively handle <tt>INOUT</tt> 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.
In order to better handle complex PL/SQL datatypes as parameters, the <code>DatasourceCall</code> hierarchy needs to be refactored. In addition to 'starving' <code>StoredFunctionCall</code>'s from handling complex parameters, the current code <b><i>leaks</i></b> internal implementation details to the surrounding classes: a parameter's name is held in a separate array from the array that holds (at the same index) the <code>DatabaseFields</code>'s that describe the parameter. Additionally, the parameter at index <tt>0</tt> is 'special', representing the return argument for <code>StoredFunctionCall</code>'s.
+
 
 +
==== 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 <code>PLSQLStoredProcedureCall</code> ===
 +
After all the above re-work is complete (and all the core EclipseLink tests pass - yikes!), the <b>real</b> work
 +
begins: refactoring <code>PLSQLStoredProcedureCall</code> so that the logic that handles generating nested
 +
conversion functions inside anonymous PL/SQL blocks can be shared between <code>PLSQLStoredProcedureCall</code>
 +
and the not-yet-implemented <code>PLSQLStoredFunctionCall</code>

Latest revision as of 10:34, 7 October 2010


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

Back to the top