Skip to main content

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.

Jump to: navigation, search

Difference between revisions of "EclipseLink/UserGuide/sandbox/gelernter/Extended Stored Procedure and PLSQL"

m
m
 
(17 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
http://wiki.eclipse.org/EclipseLink/DesignDocs/275156
+
{{EclipseLink_UserGuide
 +
|info=y
 +
|toc=n
 +
|eclipselink=y
 +
|eclipselinktype=JPA
 +
|api=y
 +
|apis=
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/Array.html @Array]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedPLSQLStoredFunctionQuery.html @NamedPLSQLStoredFunctionQuery]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedPLSQLStoredProcedureQuery.html @NamedPLSQLStoredProcedureQuery]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedStoredFunctionQuery.html @NamedStoredFunctionQuery]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedStoredProcedureQuery.html @NamedStoredProcedureQuery]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/Struct.html @Struct]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/Structure.html @Structure]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredFunctionCall.html PLSQLStoredFunctionCall]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.html PLSQLStoredProcedureCall]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/queries/StoredFunctionCall.html StoredFunctionCall]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/queries/StoredProcedureCall.html StoredProcedureCall]
  
−
<div style="margin:5px;float:right;border:1px solid #000000;padding:5px">__TOC__</div>
+
|examples=y
−
= Design Specification: Extended Stored Procedure and Function Support =
+
|example=
 +
*[[EclipseLink/Examples/JPA/PLSQLStoredFunction |PLSQLStoredFuntion]]
 +
}}
  
−
[http://bugs.eclipse.org/275156 ER 275156]
+
'''''SANDBOX VERSION'''''
  
−
[[Talk:EclipseLink/DesignDocs/275156 | Feedback]]
+
''This page has not yet been reviewed''
  
−
= Document History =
+
= Extended Stored Procedure and Function Support =
−
{|{{BMTableStyle}}
+
−
|-{{BMTHStyle}}
+
−
! Date
+
−
! Author
+
−
! Version Description & Notes
+
−
|-
+
−
| 2011-03-31
+
−
| James
+
−
| 0.1 Draft
+
−
|-
+
−
|}
+
  
−
= Project overview =
+
EclipseLink provides support for calling Oracle PLSQL stored functions that return complex PLSQL data types such as RECORD types and TABLE types. PLSQL types are not supported by Oracle JDBC, so these types must be translated to Oracle OBJECT types and VARRAY types. OBJECT types are returned in JDBC  as <tt>java.sql.Struct</tt> types and VARRAY types are returned as <tt>java.sql.Array</tt> type.
−
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
+
  
 +
'''''//REVIEWERS: Should the following lists include just the annotations? We can leave the full list in the “Key API” list on the right side of the page.//'''''
  
−
= Concepts =
+
Use either of the following to call a stored function using PLSQL types:
−
PL/SQL is Oracle's SQL base language for defining stored procedures and functions.
+
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedPLSQLStoredFunctionQuery.html @NamedPLSQLStoredFunctionQuery]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredFunctionCall.html PLSQLStoredFunctionCall] 
  
 +
Use either of the following to call a stored procedure:
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedPLSQLStoredProcedureQuery.html @NamedPLSQLStoredProcedureQuery]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.html PLSQLStoredProcedureCall]
  
−
= Requirements =
+
Use the following for regular stored functions and procedure that do not return complex PLSQL types:
−
* PLSQL stored functions
+
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedStoredFunctionQuery.html @NamedStoredFunctionQuery]
−
* multiple result sets
+
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedStoredProcedureQuery.html @NamedStoredProcedureQuery]
−
* multiple cursor output parameters
+
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/queries/StoredFunctionCall.html StoredFunctionCall]
−
* output cursors of record types
+
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/queries/StoredProcedureCall.html StoredProcedureCall]
−
* optional arguments
+
−
* JPA annotation and XML support for stored functions
+
−
* JPA annotation and XML support for PLSQL procedures and functions
+
  
−
= Design Constraints =
+
To call PLSQL stored functions or procedures, you must define mirror OBJECT and VARRAY types for the RECORD and TABLE types. Use the [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/Struct.html @Struct] annotation  to map OBJECT types to <tt>@Entity</tt> or <tt>@Embeddable</tt> classes.  Normally, you can use <tt>@Embeddable</tt>, unless the OBJECT type defines an ID and can be stored in a table.
−
Must be usable by a variety of different usages, DBWS, BPEL, JPA.
+
  
−
= Functionality =
+
Use the following annotations to map nested OBJECT and VARRAY types:
−
A PLSQLStoredFunctionCall subclass of PLSQLStoredProcedureCall will be added to provide the existing PLSQL data-type support for stored functions.
+
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/Structure.html @Structure]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/Array.html @Array]
  
−
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.
+
==Examples==
  
−
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.
+
===Oracle Stored Function Returning PLSQL RECORD Type===
  
−
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.
+
The following stored function returns the PLSQL <tt>RECORD</tt> type:
  
−
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.
+
<source lang="sql">
 +
CREATE OR REPLACE PACKAGE EMP_PKG AS
 +
TYPE EMP_REC IS RECORD (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2));
 +
FUNCTION GET_EMP RETURN EMP_REC;
 +
END EMP_PKG;
 +
</source>
  
−
= Testing =
+
<source lang="sql">
−
Extend existing foundation and JPA stored procedure tests for new types of procedure being supported.
+
CREATE OR REPLACE PACKAGE BODY EMP_PKG AS
 +
FUNCTION GET_EMP RETURN EMP_REC AS
 +
  P_EMP EMP_REC;
 +
  BEGIN P_EMP.F_NAME := 'Bob'; P_EMP.F_NAME := 'Smith'; P_EMP.SALARY := 30000;
 +
  RETURN P_EMP;
 +
END;
 +
END EMP_PKG;
 +
</source>
  
−
= API =
+
An OBJECT type mirror for the EMP_REC type must also be defined.
  
−
* StoredProcedureCall
+
<source lang="sql">
−
** setHasMultipleResultSets(boolean)
+
CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2))
−
** addNamedCursorOutputArgument(String)
+
</source>
−
** 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
+
=== Defining a Java Class to Map to OBJECT type ===
−
* @Array
+
−
* @Structure
+
  
−
= Config files =
+
<source lang="java">
−
* orm.xml
+
@Embeddable
−
<source lang="xml">
+
@Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"})
−
<name-stored-function-query function-name="CHECK_EMP">
+
public class Employee {
−
    <parameter name="EMP_ID"/>
+
@Column(name="F_NAME")
−
    <return-parameter name="STATUS" type="java.lang.Boolean"/>
+
private String firstName;
−
</name-stored-function-query>
+
@Column(name="L_NAME")
−
<plsql-name-stored-function-query function-name="GET_EMP">
+
private String lastName;
−
    <parameter name="EMP_ID" database-type="NUMERIC"/>
+
@Column(name="SALARY")
−
    <return-parameter name="EMP" database-type="EMP"/>
+
private BigDecimal salary;
−
</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>
+
 
</source>
 
</source>
  
−
= Documentation =
+
=== Using JpaEntityManager createQuery() API to Execute a PLSQL Stored Function ===
−
Should be documented under query stored procedure section.
+
  
−
= Open Issues  =
+
<source lang="java">
 +
import javax.persistence.Query;
 +
import org.eclipse.persistence.platform.database.orcle.plsql.PLSQLStoredFunctionCall;
 +
import org.eclipse.persistence.queries.ReadAllQuery;
  
−
{| class="FCK__ShowTableBorders"
+
DataReadQuery databaseQuery = new DataReadQuery();
−
|-
+
PLSQLrecord record = new PLSQLrecord();
−
! Issue #
+
record.setTypeName("EMP_PKG.EMP_REC");
−
! Owner
+
record.setCompatibleType("EMP_TYPE");
−
! Description / Notes
+
record.setJavaType(Employee.class);
−
|-
+
record.addField("F_NAME", JDBCTypes.VARCHAR_TYPE, 30);
−
| 1
+
record.addField("L_NAME", JDBCTypes.VARCHAR_TYPE, 30);
−
|
+
record.addField("SALARY", JDBCTypes.NUMERIC_TYPE, 10, 2);
−
| What package to add JPA annotations to?
+
PLSQLStoredFunctionCall call = new PLSQLStoredFunctionCall(record);
−
|-
+
call.setProcedureName("EMP_PKG.GET_EMP");
−
| 2  
+
databaseQuery.setCall(call);
−
|
+
−
|
+
  
−
|}
+
Query query = ((JpaEntityManager)entityManager.getDelegate()).createQuery(databaseQuery);
 +
Employee result = (Employee)query.getSingleResult();
  
−
= Decisions =
+
</source>
 +
 
 +
=== Using @NamedPLSQLStoredFunctionQuery to Define a Stored Function ===
 +
 
 +
<source lang="java">
 +
@NamedPLSQLStoredFunctionQuery(name="getEmployee", functionName="EMP_PKG.GET_EMP",
 +
    returnParameter=@PLSQLParameter(name="RESULT", databaseType="EMP_PKG.EMP_REC"))
 +
@Embeddable
 +
@Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"})
 +
@PLSQLRecord(name="EMP_PKG.EMP_REC", compatibleType="EMP_TYPE", javaType=Employee.class,
 +
    fields={@PLSQLParameter(name="F_NAME"), @PLSQLParameter(name="L_NAME"), @PLSQLParameter(name="SALARY", databaseType="NUMERIC_TYPE")})
 +
public class Employee {
 +
  ...
 +
}
 +
</source>
 +
 
 +
=== Using a Named Query ===
 +
<source lang="java">
 +
Query query = entityManager.createNamedQuery("getEmployee");
 +
Employee result = (Employee)query.getSingleResult();
 +
</source>
  
−
{| class="FCK__ShowTableBorders"
 
−
|-
 
−
! Issue
 
−
! Description / Notes
 
−
! Decision
 
−
|}
 
  
−
= Future Considerations =
+
{{EclipseLink_JPA }}
−
* Other procedure functionality.
+

Latest revision as of 09:56, 11 July 2011

EclipseLink JPA


SANDBOX VERSION

This page has not yet been reviewed

Extended Stored Procedure and Function Support

EclipseLink provides support for calling Oracle PLSQL stored functions that return complex PLSQL data types such as RECORD types and TABLE types. PLSQL types are not supported by Oracle JDBC, so these types must be translated to Oracle OBJECT types and VARRAY types. OBJECT types are returned in JDBC as java.sql.Struct types and VARRAY types are returned as java.sql.Array type.

//REVIEWERS: Should the following lists include just the annotations? We can leave the full list in the “Key API” list on the right side of the page.//

Use either of the following to call a stored function using PLSQL types:

Use either of the following to call a stored procedure:

Use the following for regular stored functions and procedure that do not return complex PLSQL types:

To call PLSQL stored functions or procedures, you must define mirror OBJECT and VARRAY types for the RECORD and TABLE types. Use the @Struct annotation to map OBJECT types to @Entity or @Embeddable classes. Normally, you can use @Embeddable, unless the OBJECT type defines an ID and can be stored in a table.

Use the following annotations to map nested OBJECT and VARRAY types:

Examples

Oracle Stored Function Returning PLSQL RECORD Type

The following stored function returns the PLSQL RECORD type: 

CREATE OR REPLACE PACKAGE EMP_PKG AS
TYPE EMP_REC IS RECORD (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2));
FUNCTION GET_EMP RETURN EMP_REC;
END EMP_PKG;
CREATE OR REPLACE PACKAGE BODY EMP_PKG AS
FUNCTION GET_EMP RETURN EMP_REC AS
  P_EMP EMP_REC;
  BEGIN P_EMP.F_NAME := 'Bob'; P_EMP.F_NAME := 'Smith'; P_EMP.SALARY := 30000;
  RETURN P_EMP;
END;
END EMP_PKG;

An OBJECT type mirror for the EMP_REC type must also be defined. 

CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2))

Defining a Java Class to Map to OBJECT type

@Embeddable
@Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"})
public class Employee {
 @Column(name="F_NAME")
 private String firstName;
 @Column(name="L_NAME")
 private String lastName;
 @Column(name="SALARY")
 private BigDecimal salary;
 ...
}

Using JpaEntityManager createQuery() API to Execute a PLSQL Stored Function

import javax.persistence.Query;
import org.eclipse.persistence.platform.database.orcle.plsql.PLSQLStoredFunctionCall;
import org.eclipse.persistence.queries.ReadAllQuery;
 
DataReadQuery databaseQuery = new DataReadQuery();
PLSQLrecord record = new PLSQLrecord();
record.setTypeName("EMP_PKG.EMP_REC");
record.setCompatibleType("EMP_TYPE");
record.setJavaType(Employee.class);
record.addField("F_NAME", JDBCTypes.VARCHAR_TYPE, 30);
record.addField("L_NAME", JDBCTypes.VARCHAR_TYPE, 30);
record.addField("SALARY", JDBCTypes.NUMERIC_TYPE, 10, 2);
PLSQLStoredFunctionCall call = new PLSQLStoredFunctionCall(record);
call.setProcedureName("EMP_PKG.GET_EMP");
databaseQuery.setCall(call);
 
Query query = ((JpaEntityManager)entityManager.getDelegate()).createQuery(databaseQuery);
Employee result = (Employee)query.getSingleResult();

Using @NamedPLSQLStoredFunctionQuery to Define a Stored Function

@NamedPLSQLStoredFunctionQuery(name="getEmployee", functionName="EMP_PKG.GET_EMP",
    returnParameter=@PLSQLParameter(name="RESULT", databaseType="EMP_PKG.EMP_REC"))
@Embeddable
@Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"})
@PLSQLRecord(name="EMP_PKG.EMP_REC", compatibleType="EMP_TYPE", javaType=Employee.class,
    fields={@PLSQLParameter(name="F_NAME"), @PLSQLParameter(name="L_NAME"), @PLSQLParameter(name="SALARY", databaseType="NUMERIC_TYPE")})
public class Employee {
 ...
}

Using a Named Query

Query query = entityManager.createNamedQuery("getEmployee");
Employee result = (Employee)query.getSingleResult();


Eclipselink-logo.gif
Version: DRAFT
Other versions...

Retrieved from "https://wiki.eclipse.org/index.php?title=EclipseLink/UserGuide/sandbox/gelernter/Extended_Stored_Procedure_and_PLSQL&oldid=260684"

This page was last modified 09:56, 11 July 2011 by Eclipsepedia anonymous user Unnamed Poltroon.

Back to the top