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

EclipseLink JPA

• EclipseLink Destinations:
• Documentation
• FAQ
• Examples
• Development

Key API

• PLSQLStoredFunctionCall
• @NamedPLSQLStoredFunctionQuery
• PLSQLStoredProcedureCall
• @NamedPLSQLStoredProcedureQuery
• StoredFunctionCall
• StoredProcedureCall
• @NamedStoredFunctionQuery
• @NamedStoredProcedureQuery
• @Struct
• @Structure
• @Array

Examples

• EclipseLink/Examples/JPA/PLSQLStoredFunction PLSQLStoredFuntion

PLSQL Stored Function Support

EclipseLink has 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.

To call a stored function using PLSQL types the PLSQLStoredFunctionCall or @NamedPLSQLStoredFunctionQuery must be used. PLSQLStoredProcedureCall and @NamedPLSQLStoredProcedureQuery also exist for stored procedures. For regular stored functions and procedure that do not return complex PLSQL types the regular StoredFunctionCall, StoredProcedureCall and @NamedStoredFunctionQuery, @NamedStoredProcedureQuery can be used.

To call PLSQL stored functions or procedures mirror OBJECT/VARRAY types must be defined for the RECORD/TABLE types. OBJECT types can be mapped to @Entity or @Embeddable classes using the @Struct annotation. Normally @Embeddable is used, unless the OBJECT type defines an Id and can be stored in a table. Nested OBJECT and VARRAY types can be mapped using the @Structure and @Array annotations.

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 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();

Extended Stored Procedure and Function Support

This document describes the EclipseLink 2.3 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 -- ah, but look below under API:////

• StoredProcedureCall
  • setHasMultipleResultSets(boolean)
  • addNamedCursorOutputArgument(String)
  • addUnamedCursorOutputArgument(String)
  • addOptionalArgument(String)

BUT THESE ARE NOT IN THE JAVADOC. ARE THEY IMPLEMENTED? ////

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.

Headline text

Version: DRAFT
Other versions...

EclipseLink Home JPA User Guide: Table of Contents, Search
How to contribute to this guide...