EclipseLink/DesignDocs/275156

Design Specification: Extended Stored Procedure and Function Support

ER 275156

Feedback

Document History

Date	Author	Version Description & Notes
2011-03-31	James	0.1 Draft

Project overview

The goal of this project is to extend our stored procedure support, including the following:

• PLSQL stored functions
• multiple result sets
• multiple cursor output parameters
• output cursors of record types
• optional arguments
• JPA annotation and XML support for stored functions
• JPA annotation and XML support for PLSQL procedures and functions

Concepts

PL/SQL is Oracle's SQL base language for defining stored procedures and functions.

Requirements

• PLSQL stored functions
• multiple result sets
• multiple cursor output parameters
• output cursors of record types
• optional arguments
• JPA annotation and XML support for stored functions
• JPA annotation and XML support for PLSQL procedures and functions

Design Constraints

Must be usable by a variety of different usages, DBWS, BPEL, JPA.

Functionality

A PLSQLStoredFunctionCall subclass of PLSQLStoredProcedureCall will be added to provide the existing PLSQL data-type support for stored functions.

StoredProcedureCall will be extended to support multiple result sets, and multiple out cursors. A flag will be added for multiple result sets, or data queries will be supported, unless both result sets are for the same class. Specification of multiple out cursors is already supported from JPA, the support will allow there execution. Only data queries are supported, each out cursor will contains a List of DatabaseRecords stored in the output row the same as other output parameters.

Queries will support an optional argument for stored procedures that have parameters that define a default value. If the query argument is null, and the StoredProcedureCall defines the argument to be optional, then the query's SQL will be regenerate to omit the parameter.

JPA annotation and XML support will be added for stored functions the same as stored procedures, as well as the new options to the existing stored procedure annotation and XML.

JPA annotation and XML support will be added for PLSQL procedures and functions, this will require adding annotations for Record, Table, Struct, and Array types.

Testing

Extend existing foundation and JPA stored procedure tests for new types of procedure being supported.

API

• StoredProcedureCall
  • setHasMultipleResultSets(boolean)
  • addNamedCursorOutputArgument(String)
  • addUnamedCursorOutputArgument(String)
  • addOptionalArgument(String)
• DatabaseQuery
  • addArgument(String argumentName, Class type, boolean nullable)
• @NamedStoredProcedureQuery
  • callByIndex : boolean
  • multipleResultSets : boolean
• @StoredProcedureParameter
  • optional : boolean
• PLSQLStoredFunctionCall
  • setResult(DatabaseType)
• @NamedStoredFunctionQuery
  • returnParameter : @StoredProcedureParameter
• @NamedPLSQLStoredProcedureQuery (org.eclipse.persistence.platform.oracle.annotations)
• @NamedPLSQLStoredFunctionQuery (org.eclipse.persistence.platform.oracle.annotations)
• @PLSQLParameter (org.eclipse.persistence.platform.oracle.annotations)
• @PLSQLRecord (org.eclipse.persistence.platform.oracle.annotations)
• @PLSQLTable (org.eclipse.persistence.platform.oracle.annotations)

• @Struct
• @Array
• @Structure

Config files

• orm.xml

<name-stored-function-query function-name="CHECK_EMP">
    <parameter name="EMP_ID"/>
    <return-parameter name="STATUS" type="java.lang.Boolean"/>
</name-stored-function-query>
<plsql-name-stored-function-query function-name="GET_EMP">
    <parameter name="EMP_ID" database-type="NUMERIC"/>
    <return-parameter name="EMP" database-type="EMP"/>
</plsql-name-stored-function-query>
<plsql-record type-name="EMP" compatible-type="EMP_TYPE" java-type="org.acme.Employee">
    <field name="EMP_ID" database-type="NUMERIC"/>
    <field name="NAME" database-type="VARCHAR"/>
</plsql-record>

Documentation

Should be documented under query stored procedure section.

Open Issues

Issue #	Owner	Description / Notes
1		What package to add JPA annotations to?
2

Decisions

Issue	Description / Notes	Decision

Future Considerations

• Other procedure functionality.