Skip to main content

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.

Jump to: navigation, search

Difference between revisions of "EclipseLink/UserGuide/JPA/Advanced JPA Development/Stored Procedure and PL/SQL Support"

m
Line 1: Line 1:
−
{{EclipseLink_UserGuide
+
{{EclipseLink_UserGuide  
 
|info=y
 
|info=y
 
|toc=n
 
|toc=n
 +
|eclipselink=y
 +
|eclipselinktype=JPA
 
|api=y
 
|api=y
 
|apis=
 
|apis=
−
*[http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/jpa/JpaEntityManager.html JpaEntityManager]
+
* [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/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/PLSQLStoredProcedureCall.html PLSQLStoredProcedureCall]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedPLSQLStoredProcedureQuery.html @NamedPLSQLStoredProcedureQuery]
 +
* [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]
 +
* [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/annotations/Array.html @Array]  
 
|examples=y
 
|examples=y
 
|example=
 
|example=
−
*[http://wiki.eclipse.org/EclipseLink/Examples/JPA/PLSQLStoredFunction PLSQLStoredFunction]
+
*[[EclipseLink/Examples/JPA/PLSQLStoredFunction |PLSQLStoredFuntion]]
−
}}  
+
}}
  
−
= Stored Procedure and PL/SQL Support =
+
=PLSQL Stored Function Support=
−
''These enhancements are new in EclipseLink 2.3.''
+
  
−
''This page is under development''
+
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 as <tt>java.sql.Struct</tt> and VARRAY as <tt>java.sql.Array</tt> types in JDBC.
  
−
EclipseLink 2.3 includes the following extended support for stored procedures:
+
Use either of the following to call a stored function using PLSQL types:
 +
* [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/annotations/NamedPLSQLStoredFunctionQuery.html @NamedPLSQLStoredFunctionQuery]
  
−
* PLSQL stored functions
+
Use either of the following to call a stored procedure:
−
* multiple result sets
+
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.html PLSQLStoredProcedureCall]
−
* multiple cursor output parameters
+
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/annotations/NamedPLSQLStoredProcedureQuery.html @NamedPLSQLStoredProcedureQuery]
−
* 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
+
  
−
{{EclipseLink_JPA
+
Use the following for regular stored functions and procedure that do not return complex PLSQL types:
−
|up=      [[EclipseLink/UserGuide/JPA/Advanced_JPA_Development|Advanced JPA Development]]
+
* [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]  
 +
* [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]
 +
 
 +
To call PLSQL stored functions or procedures, you must define mirror OBJECT/VARRAY types for the RECORD/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.
 +
 
 +
Use the following annotations to map nested OBJECT and VARRAY types:
 +
* [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]
 +
 
 +
 
 +
==Examples==
 +
 
 +
===Oracle Stored Function Returning PLSQL Record Type===
 +
 
 +
 
 +
<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>
 +
 
 +
<source lang="sql">
 +
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>
 +
 
 +
An OBJECT type mirror for the EMP_REC type must also be defined.
 +
 
 +
<source lang="sql">
 +
CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2))
 +
</source>
 +
 
 +
=== Defining a Java Class to Map to OBJECT type ===
 +
 
 +
<source lang="java">
 +
@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;
 +
...
 +
}
 +
</source>
 +
 
 +
=== Using JpaEntityManager createQuery() API to Execute a PLSQL Stored Function ===
 +
 
 +
<source lang="java">
 +
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();
 +
 
 +
</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 named query ===
 +
<source lang="java">
 +
Query query = entityManager.createNamedQuery("getEmployee");
 +
Employee result = (Employee)query.getSingleResult();
 +
</source>
 +
 
 +
 
 +
{{EclipseLink_JPA }}

Revision as of 11:33, 30 June 2011

EclipseLink JPA


PLSQL Stored 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 as java.sql.Struct and VARRAY as java.sql.Array types in JDBC.

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/VARRAY types for the RECORD/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

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 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/JPA/Advanced_JPA_Development/Stored_Procedure_and_PL/SQL_Support&oldid=259499"

Copyright © Eclipse Foundation, Inc. All Rights Reserved.