Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/UserGuide/sandbox/gelernter/Extended Stored Procedure and PLSQL"
m |
m |
||
Line 29: | Line 29: | ||
[http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/queries/StoredProcedureCall.html <tt>StoredProcedureCall</tt>] now supports multiple result sets and multiple out cursors. | [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/queries/StoredProcedureCall.html <tt>StoredProcedureCall</tt>] now supports 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 | + | A flag will be added for multiple result sets, '''''////what is that flag? I don't see it in the Javadoc////''''' |
+ | |||
+ | or data queries will be supported, '''''////Does that mean ''either''the flag ''or''the queries will be supported? Which? Both? Again, I don't see anything in the Javadoc that applies to "data queries," whatever that means in this context////''''' | ||
+ | |||
+ | unless both result sets are for the same class. | ||
+ | |||
+ | Specification of multiple out cursors is already supported from JPA, the support will allow their 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. | 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. |
Revision as of 16:18, 25 May 2011
http://wiki.eclipse.org/EclipseLink/DesignDocs/275156
Extended Stored Procedure and Function Support
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. ////existing in previous EL or in PLSQL?////
StoredProcedureCall now supports multiple result sets and multiple out cursors.
A flag will be added for multiple result sets, ////what is that flag? I don't see it in the Javadoc////
or data queries will be supported, ////Does that mean eitherthe flag orthe queries will be supported? Which? Both? Again, I don't see anything in the Javadoc that applies to "data queries," whatever that means in this context////
unless both result sets are for the same class.
Specification of multiple out cursors is already supported from JPA, the support will allow their 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.