Implementing the Solution

Main Tasks

To execute an Oracle PL/SQL stored function:

• Task 1: Create an Oracle Stored Function That Returns a PL/SQL Record Type

• Task 2: Define an Object Type Mirror

• Task 3: Define a Java Class Mapping The OBJECT Type

• Task 4: Execute a PL/SQL Stored Function Using JpaEntityManager

• Task 5: Define a Stored Function Using @NamedPLSQLStoredFunctionQuery

• Task 6: Use the Stored Function in a Query

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;

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

@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;
   ...
}

import javax.persistence.Query;
import org.eclipse.persistence.platform.database.orcle.plsql.
   PLSQLStoredFunctionCall;
import org.eclipse.persistence.queries.ReadAllQuery;

DataReadQuery databaseQuery = new DataReadQuery();
databaseQuery.setResultType(DataReadQuery.VALUE);
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();

@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 {
 ...
}

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

Using the PLSQLStoredProcedureCall Class

The following example demonstrates handling PL/SQL arguments using the PLSQLStoredProcedureCall class. The example is based on the following target procedure:

PROCEDURE bool_in_test(x IN BOOLEAN)

Example of Using the PLSQLStoredProcedureCall Class

import java.util.List;
import java.util.ArrayList;
import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
import org.eclipse.persistence.platform.database.oracle.Oracle10Platform;
import org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes;
import org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall;
import org.eclipse.persistence.queries.DataModifyQuery;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.sessions.Project;
import org.eclipse.persistence.sessions.Session;

public class TestClass {
 
   public static String DATABASE_USERNAME = "username";
   public static String DATABASE_PASSWORD = "password";
   public static String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
   public static String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";
 
   public static void main(String[] args) {
      Project project = new Project();
      DatabaseLogin login = new DatabaseLogin();
      login.setUserName(DATABASE_USERNAME);
      login.setPassword(DATABASE_PASSWORD);
      login.setConnectionString(DATABASE_URL);
      login.setDriverClassName(DATABASE_DRIVER);
      login.setDatasourcePlatform(new Oracle10Platform());
      project.setDatasourceLogin(login);
      Session s = project.createDatabaseSession();
      s.setLogLevel(SessionLog.FINE);
      ((DatabaseSession)s).login();

      PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
      call.setProcedureName("bool_in_test");
      call.addNamedArgument("X", OraclePLSQLTypes.PLSQLBoolean);
      DataModifyQuery query = new DataModifyQuery();
      query.addArgument("X");
      query.setCall(call);
      List queryArgs = new ArrayList();
      queryArgs.add(Integer.valueOf(1));
      s.executeQuery(query, queryArgs);
    }
}

The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:

...[EclipseLink Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)--
   Thread(Thread[main,5,main])-- login successful
[EclipseLink Fine]: 2007.11.23 01:03:23.968--DatabaseSessionImpl(15674464)--
   Connection(5807702)--Thread(Thread[main,5,main])--
DECLARE
   X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
BEGIN
   bool_in_test(X=>X_TARGET);
END;
   bind => [:1 => 1]

Mixing JDBC Arguments With Non JDBC Arguments

A Stored Procedure may have a mix of regular and non JDBC arguments. Use the PLSQLStoredProcedureCall class when at least one argument is a non JDBC type. In addition, some additional information may be required for the JDBC type (length, scale or precision) because the target procedure is invoked from an anonymous PL/SQL block. The example is based on the following target procedure:

PROCEDURE two_arg_test(x IN BOOLEAN, y IN VARCHAR)

Example of Mixing JDBC Arguments With NonJDBC Arguments

import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
...
   PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
   call.setProcedureName("two_arg_test");
   call.addNamedArgument("X", OraclePLSQLTypes.PLSQLBoolean);
   call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40);
   DataModifyQuery query = new DataModifyQuery();
   query.addArgument("X");
   query.addArgument("Y");
   query.setCall(call);
   List queryArgs = new ArrayList();
   queryArgs.add(Integer.valueOf(0));
   queryArgs.add("test");
   boolean worked = false;
   String msg = null;
   s.executeQuery(query, queryArgs);

The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:

[EclipseLink Fine]: 2007.11.23 02:54:46.109--DatabaseSessionImpl(15674464)--
   Connection(5807702)--Thread(Thread[main,5,main])--
DECLARE
   X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
   Y_TARGET VARCHAR(40) := :2;
BEGIN
   two_arg_test(X=>X_TARGET, Y=>Y_TARGET);
END;
   bind => [:1 => 0, :2 => test]

Handling IN and OUT Arguments

The following example demonstrates a stored procedure that contain both IN and OUT arguments and is based on the following target procedure:

PROCEDURE two_arg_in_out(x OUT BINARY_INTEGER, y IN VARCHAR) AS
BEGIN
   x := 33;
END;

Example of Handling IN and OUT Arguments

import static org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes.
   BinaryInteger;
...
   PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
   call.setProcedureName("two_arg_in_out");
   call.addNamedOutputArgument("X", OraclePLSQLTypes.BinaryInteger);
   call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40);
   DataReadQuery query = new DataReadQuery();
   query.setCall(call);
   query.addArgument("Y");
   List queryArgs = new ArrayList();
   queryArgs.add("testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds");
   boolean worked = false;
   String msg = null;
   List results = (List)s.executeQuery(query, queryArgs);
   DatabaseRecord record = (DatabaseRecord)results.get(0);
   BigDecimal x = (BigDecimal)record.get("X");
   if (x.intValue() != 33) {
      System.out.println("wrong x value");
   }

The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:

[EclipseLink Fine]: 2007.11.23 03:15:25.234--DatabaseSessionImpl(15674464)--
   Connection(5807702)--Thread(Thread[main,5,main])--
DECLARE
   Y_TARGET VARCHAR(40) := :1;
   X_TARGET BINARY_INTEGER;
BEGIN
   two_arg_in_out(X=>X_TARGET, Y=>Y_TARGET);
   :2 := X_TARGET;
END;
   bind => [:1 => testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds, X => :2]

Handling IN OUT Arguments

Anonymous PL/SQL blocks cannot natively handle IN OUT arguments. The arguments must be split into two parts: an IN-half and an OUT-half. The following example demonstrates a stored procedure that handles IN OUT arguments and is based on the following target procedure:

PROCEDURE two_args_inout(x VARCHAR, y IN OUT BOOLEAN) AS
BEGIN
   y := FALSE;
END;

Example of Handling IN OUT Arguments

...
   PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
   call.setProcedureName("two_args_inout");
   call.addNamedArgument("X", JDBCTypes.VARCHAR_TYPE, 20);
   call.addNamedInOutputArgument("Y", OraclePLSQLTypes.PLSQLBoolean);
   DataReadQuery query = new DataReadQuery();
   query.addArgument("X");
   query.addArgument("Y");
   query.setCall(call);
   List queryArgs = new ArrayList();
   queryArgs.add("test");
   queryArgs.add(Integer.valueOf(1));
   List results = (List)s.executeQuery(query, queryArgs);
   DatabaseRecord record = (DatabaseRecord)results.get(0);
   Integer bool2int = (Integer)record.get("Y");
   if (bool2int.intValue() != 0) {
      System.out.println("wrong bool2int value");
   }

The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:

[EclipseLink Fine]: 2007.11.23 03:39:55.000--DatabaseSessionImpl(25921812)--
   Connection(33078541)--Thread(Thread[main,5,main])--
DECLARE
   X_TARGET VARCHAR(20) := :1;
   Y_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:2);
BEGIN
   two_args_inout(X=>X_TARGET, Y=>Y_TARGET);
   :3 := SYS.SQLJUTL.BOOL2INT(Y_TARGET);
END;
   bind => [:1 => test, :2 => 1, Y => :3]

Task 1: Configure a Multi Data Source or GridLink Data Source

Refer to Chapter 3, "Using EclipseLink with WebLogic Server," and Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server for details about configuring a data source in WebLogic Server for Oracle RAC.

Task 2: Configure the Persistence Unit

Edit the persistence.xml file and include the name of the data source within a persistence unit configuration. For example:

<persistence-unit name="OrderManagement">
   <jta-data-source>jdbc/MyOrderDB</jta-data-source>
   ...
</persistence-unit>

Task 3: Include the Required JARs

Ensure that the ons.jar is in the WebLogic Server classpath.

Task 1: Create a UCP Data Source

A UCP data source is used to connect to a RAC database. The data source can specify advanced RAC configuration. For details on using advanced RAC features with UCP, see Oracle Universal Connection Pool for JDBC Developer's Guide. The following example creates a data source and enables FCF and configures ONS.

PoolDataSource datasource = PoolDataSourceFactory.getPoolDataSource();
datasource.setONSConfiguration(“nodes=host1:4200,host2:4200”);
datasource.setFastConnectionFailoverEnabled(true);
datasource.setConnectionFactoryClassName(“oracle.jdbc.pool.OracleDataSource”);
datasource.setURL(“jdbc:oracle:thin:@DESCRIPTION=
    (LOAD_BALANCE=on)
    (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=host3)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=host4)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=service_name)))”);

Applications that do not require the advanced features provided by RAC and UCP can connect to a RAC-enabled database using the native connection pool in EclipseLink. In this case, edit the persistence.xml file for you applications and add the RAC URL connection string for a persistence unit. For example:

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
   persistence_1_0.xsd" version="1.0">
   <persistence-unit name="my-app" transaction-type="RESOURCE_LOCAL">
      <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
      <exclude-unlisted-classes>false</exclude-unlisted-classes>
      <properties>
         <property name="javax.persistence.jdbc.driver"
            value="oracle.jdbc.OracleDriver"/>
         <property name="javax.persistence.jdbc.url" 
            value="jdbc:oracle:thin@(DESCRIPTION= "+ "(LOAD_BALANCE=on)"+
           "(ADDRESS=(PROTOCOL=TCP)(HOST=rac_node) (PORT=1521))"+
           "(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2) (PORT=1521))"+
           "(CONNECT_DATA=(SERVICE_NAME=service_name))")"/>
         <property name="javax.persistence.jdbc.user" value="user_name"/>
         <property name="javax.persistence.jdbc.password" value="password"/>
      </properties>
   </persistence-unit>
</persistence>

To use the persistence unit, instantiate an EntityManagerFactory as follows:

Persistence.createEntityManagerFactory("my-app");

Task 2: Use the UCP Data Source

To use the UCP data source, instantiate an EntityManagerFactory an pass in the data source as follows:

Map properties = new HashMap();
       properties.add("javax.persistence.nonJtaDataSource", datasource);
       Persistence.createEntityManagerFactory(properties);

Task 3: Include the Required JARs

Ensure that both ucp.jar and ons.jar are in the application classpath.