EclipseLink/UserGuide/sandbox/gelernter/Extended Stored Procedure and PLSQL

http://wiki.eclipse.org/EclipseLink/DesignDocs/275156

Extended Stored Procedure and Function Support

ER 275156

overview

This document describes the EclipseLink 2.4 extended stored procedure and function support.

The extended stored procedure support includes:

• 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

These enhancements can be used by JPA, DBWS, and BPEL. Any other? Why is BPEL in here?

Functionality

The PLSQLStoredFunctionCall class (a subclass of PLSQLStoredProcedureCall) provides 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.