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.
Difference between revisions of "EclipseLink/UserGuide/sandbox/gelernter/Extended Stored Procedure and PLSQL"
< EclipseLink | UserGuide | sandbox/gelernter
m |
m |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 5: | Line 5: | ||
|eclipselinktype=JPA | |eclipselinktype=JPA | ||
|api=y | |api=y | ||
| − | |apis= | + | |apis= |
| − | * [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/ | + | * [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/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/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/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/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/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/annotations/Structure.html @Structure] | ||
| − | * [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/ | + | * [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] | ||
| + | |||
|examples=y | |examples=y | ||
|example= | |example= | ||
*[[EclipseLink/Examples/JPA/PLSQLStoredFunction |PLSQLStoredFuntion]] | *[[EclipseLink/Examples/JPA/PLSQLStoredFunction |PLSQLStoredFuntion]] | ||
}} | }} | ||
| + | |||
| + | '''''SANDBOX VERSION''''' | ||
''This page has not yet been reviewed'' | ''This page has not yet been reviewed'' | ||
| Line 26: | Line 29: | ||
= Extended Stored Procedure and Function Support = | = Extended Stored Procedure and Function Support = | ||
| − | EclipseLink provides support for calling Oracle PLSQL stored functions that return complex PLSQL data | + | 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. |
| − | Use either of the following to call a stored function using PLSQL types: | + | '''''//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: | ||
* [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/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: | 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/annotations/NamedPLSQLStoredProcedureQuery.html @NamedPLSQLStoredProcedureQuery] | ||
| + | * [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.html PLSQLStoredProcedureCall] | ||
| − | Use the following for regular stored functions and procedure that do not return complex PLSQL types: | + | Use the following for regular stored functions and procedure that do not return complex PLSQL types: |
| − | + | ||
| − | + | ||
* [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/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/NamedStoredProcedureQuery.html @NamedStoredProcedureQuery] | ||
| + | * [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] | ||
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. | 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. | ||
| Line 47: | Line 52: | ||
* [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/Structure.html @Structure] | ||
* [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/Array.html @Array] | ||
| − | |||
==Examples== | ==Examples== | ||
| Line 53: | Line 57: | ||
===Oracle Stored Function Returning PLSQL RECORD Type=== | ===Oracle Stored Function Returning PLSQL RECORD Type=== | ||
| − | The following | + | The following stored function returns the PLSQL <tt>RECORD</tt> type: |
| − | + | ||
| − | + | ||
<source lang="sql"> | <source lang="sql"> | ||
Latest revision as of 09:56, 11 July 2011
EclipseLink JPA
| EclipseLink | |
| Website | |
| Download | |
| Community | |
| Mailing List • Forums • IRC • mattermost | |
| Issues | |
| Open • Help Wanted • Bug Day | |
| Contribute | |
| Browse Source |
Key API
Examples
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 Home
JPA User Guide: |
||
| How to contribute to this guide... | ||
