blob: 9480909ceff5b037c53508938843799faf8de8ae [file] [log] [blame]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />
<title>Implementing the Solution | EclipseLink 2.7 Understanding EclipseLink</title>
<meta name="generator" content="Oracle DARB XHTML Converter (Mode = document) - Version 1.0.22 Build 1" />
<meta name="date" content="2014-06-10T10:35:15Z" />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content="Implementing the Solution" />
<meta name="relnum" content="Release 2.7" />
<link rel="stylesheet" type="text/css" href="../../dcommon/style.css" media="screen" />
<link rel="copyright" href="../../dcommon/html/cpyr.htm" title="Copyright" type="text/html" />
<link rel="start" href="../../index.htm" title="Home" type="text/html" />
<link rel="contents" href="toc.htm" title="Contents" type="text/html" />
<link rel="prev" href="oracledb001.htm" title="Previous" type="text/html" />
<link rel="next" href="oracledb003.htm" title="Next" type="text/html" />
<!-- START: Disqus --><script type="text/javascript"> var disqus_developer = 0; </script><!-- END: Disqus --><!-- START: Sharethis --><script type="text/javascript">var switchTo5x=true;</script><script type="text/javascript" src="http://w.sharethis.com/button/buttons.js"></script><script type="text/javascript" src="http://s.sharethis.com/loader.js"></script> <!-- END: Sharethis --></head>
<body bgcolor="#FFFFFF"><iframe id="docheader" frameborder="0" framemargin="0" scrolling="no" src="../../dcommon/header.html"></iframe><script src="http://www.google.com/jsapi" type="text/javascript"></script><script type="text/javascript"> google.load('search', '1', {language : 'en'}); google.setOnLoadCallback(function() { var customSearchOptions = {}; var googleAnalyticsOptions = {}; googleAnalyticsOptions['queryParameter'] = 'q'; googleAnalyticsOptions['categoryParameter'] = ''; customSearchOptions['googleAnalyticsOptions'] = googleAnalyticsOptions; var customSearchControl = new google.search.CustomSearchControl( '016171230611334810008:enkkfjmsqju', customSearchOptions); customSearchControl.setResultSetSize(google.search.Search.FILTERED_CSE_RESULTSET); var options = new google.search.DrawOptions(); options.setSearchFormRoot('cse-search-form'); customSearchControl.draw('cse', options); }, true);</script><link rel="stylesheet" href="http://www.google.com/cse/style/look/default.css" type="text/css" /><div id="cse" style="width:100%;"></div>
<div class="header"><a id="top" name="top"></a>
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td align="left" valign="top"><font face="helvetica, arial, sans-serif"><b>EclipseLink Solutions Guide for EclipseLink</b><br />
Release 2.7</div></td>
<td valign="bottom" align="right" width="144">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td>&nbsp;</td>
<td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/images/contents.png" alt="Go To Table Of Contents" border="0" height="16" width="16" /><br />
</td><td>&nbsp;</td><td align="center"><a href="../../" target="_top" class="external text" title="Search" rel="nofollow"><img src="../../dcommon/images/search.png" alt="Search" style="border:0;" /><br /><span class="mini"></span></a></td><td>&nbsp;</td><td align="center"><a href="../eclipselink_otlcg.pdf" title="PDF" target="_blank"><img src="../../dcommon/images/pdf_icon.png" style="padding-right:5px;border:0" alt="PDF"></a></td>
</tr>
</table>
</td>
</tr>
</table>
<hr />
<table class="navigation simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100" align="center">
<tr>
<td align="center"><a href="oracledb001.htm"><img src="../../dcommon/images/larrow.png" alt="Previous" border="0" height="16" width="16" /></a></td>
<td align="center"><a href="oracledb003.htm"><img src="../../dcommon/images/rarrow.png" alt="Next" border="0" height="16" width="16" /></a></td>
<td>&nbsp;</td>
</tr>
</table>
</div>
<!-- class="header" -->
<div class="ind"><!-- End Header --><a id="CHDJHBIC" name="CHDJHBIC"></a><a id="TLADG1058" name="TLADG1058"></a>
<div class="sect1">
<h1 class="sect1"><font face="arial, helvetica, sans-serif" color="#330099">Implementing the Solution</font></h1>
<p>The solution in this section is organized according to technology. The organization allows developers to easily understand the different parts of the solution and choose specific parts to implement.</p>
<p>This section includes the following topics:</p>
<ul>
<li>
<p><a href="#CHDIBDGJ">Using Oracle Platform-Specific APIs</a></p>
</li>
<li>
<p><a href="#CHDDCIEC">Using Oracle PL/SQL With EclipseLink</a></p>
</li>
<li>
<p><a href="#CHDEFIBH">Using Oracle Virtual Private Database</a></p>
</li>
<li>
<p><a href="#CHDGDDJF">Using Oracle Proxy Authentication</a></p>
</li>
<li>
<p><a href="#CHDIEBBB">Using EclipseLink with Oracle RAC</a></p>
</li>
<li>
<p><a href="#CHDJBFIJ">Using Oracle Spatial and Graph</a></p>
</li>
</ul>
<a id="CHDIBDGJ" name="CHDIBDGJ"></a><a id="TLADG1059" name="TLADG1059"></a>
<div class="sect2">
<h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">Using Oracle Platform-Specific APIs</font></h2>
<p>Oracle Database platform support is provided in the <code>org.eclipse.persistence.platform.database.OraclePlatform</code> class, the <code>org.eclipse.persistence.platform.database.oracle*</code> packages, and the <code>org.eclipse.persistence.mappings.xdb</code> package for Oracle XML Database support. For details on the API, see <em>Java API Reference for EclipseLink</em>. For details on specific Oracle SQL types, see <em>Oracle Database JDBC Java API Reference</em>.</p>
<p>The following support is provided for the Oracle Database:</p>
<ul>
<li>
<p>Batch writing with optimistic locking</p>
</li>
<li>
<p>Native SQL for <code>byte[]</code>, <code>Date</code>, <code>Time</code>, <code>Timestamp</code> and <code>Calendar</code></p>
</li>
<li>
<p>Support for <code>BLOB</code> and <code>CLOB</code> database types using Oracle JDBC specific <code>LOBLocator</code> for large values</p>
<div align="center">
<div class="inftblnote"><br />
<table class="Note oac_no_warn" summary="" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0">
<tbody>
<tr>
<td align="left">
<p class="note"><img src="../../dcommon/images/note_icon.png" width="16" height="16" alt="Note" style="vertical-align:middle;padding-right:5px;" />Note:</p>
<p>For non-Oracle thin JDBC drivers or applications environments where the thin driver is wrapped, it is possible to turn off <code>LOBLocator</code> usage using <code>setShouldUseLocatorForLOBWrite(boolean)</code> on the platform instance.</p>
</td>
</tr>
</tbody>
</table>
<br /></div>
<!-- class="inftblnote" --></div>
</li>
<li>
<p>Native support for outer join syntax <code>(+) =</code></p>
</li>
<li>
<p>Native Sequencing (<code>SELECT SEQ_NAME.NEXTVAL FROM DUAL</code>)</p>
</li>
<li>
<p>Native SQL/ROWNUM support for <code>MaxRows</code> and <code>FirstResult</code> filtering.</p>
</li>
<li>
<p>Hierarchical selects (connect by prior)</p>
</li>
<li>
<p>Returning clause</p>
</li>
<li>
<p>Custom expression functions (<code>REGEXP_LIKE</code>, <code>LOCATE</code>, <code>ATAN2</code>, <code>LOG</code>, <code>CONCAT</code>, <code>SYSDATE</code> (Date, Time, Today), <code>EXCEPT</code>)</p>
</li>
<li>
<p>PLSQL data types, stored functions, stored procedure syntax for invoking and parameter passing, output parameters and output cursors. See <a href="#CHDDCIEC">Using Oracle PL/SQL With EclipseLink.</a></p>
</li>
<li>
<p>Timestamp query for use in optimistic locking using <code>SYSDATE</code> and <code>SYSTIMESTAMP</code></p>
</li>
<li>
<p>Multi-byte support of <code>NCHAR</code>, <code>NSTRING</code>, and <code>NCLOB</code></p>
</li>
<li>
<p>Support of <code>TIMESTAMP</code>, <code>TIMESTAMPTZ</code>, and <code>TIMESTAMPLTZ</code></p>
</li>
<li>
<p>Oracle XML Database support of <code>XMLType</code> field and custom XSQL functions (<code>extract</code>, <code>extractValue</code>, <code>existsNode</code>, <code>isFragment</code>, <code>getStringVal</code>, and <code>getNumberVal</code>)</p>
</li>
<li>
<p>XDK XML parser</p>
</li>
<li>
<p>Flashback Querying in Historical Sessions</p>
</li>
<li>
<p>Object-relational Mappings (ReferenceMapping, StructureMapping, NestedTableMapping, ArrayMapping, ObjectArrayMapping)</p>
</li>
<li>
<p>Oracle AQ</p>
</li>
<li>
<p>Oracle Real Application Clusters. See <a href="#CHDIEBBB">Using EclipseLink with Oracle RAC.</a></p>
</li>
<li>
<p>Virtual Private Database (VPD), including Oracle Label Security. <a href="#CHDEFIBH">Using Oracle Virtual Private Database.</a></p>
</li>
<li>
<p>Proxy Authentication. See <a href="#CHDGDDJF">Using Oracle Proxy Authentication.</a></p>
</li>
</ul>
</div>
<!-- class="sect2" -->
<a id="CHDDCIEC" name="CHDDCIEC"></a><a id="TLADG652" name="TLADG652"></a>
<div class="sect2">
<h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">Using Oracle PL/SQL With EclipseLink</font></h2>
<p>EclipseLink includes APIs for use with Oracle PL/SQL. The APIs are located in the <code>org.eclipse.persistence.platform.database.oracle.plsql</code> package and the <code>org.eclipse.persistence.platform.database.oracle.annotations</code> package.</p>
<p>This Section contains the following topics:</p>
<ul>
<li>
<p><a href="#CHDBBJIG">Executing an Oracle PL/SQL Stored Function</a></p>
</li>
<li>
<p><a href="#CHDDJJBI">Handling PL/SQL arguments for Oracle Stored Procedures</a></p>
</li>
</ul>
<a id="CHDBBJIG" name="CHDBBJIG"></a><a id="TLADG653" name="TLADG653"></a>
<div class="sect3">
<h3 class="sect3"><font face="arial, helvetica, sans-serif" color="#330099">Executing an Oracle PL/SQL Stored Function</font></h3>
<p>Oracle PL/SQL stored functions can be used to return complex PL/SQL data-types such as <code>RECORD</code> types and <code>TABLE</code> types. PL/SQL types are not supported by Oracle JDBC, so these types must be translated to Oracle <code>OBJECT</code> types and <code>VARRAY</code> types. <code>OBJECT</code> types are returned as <code>java.sql.Struct</code> and <code>VARRAY</code> as <code>java.sql.Array</code> types in JDBC.</p>
<p>Executing PL/SQL stored functions or procedures requires defining mirror <code>OBJECT</code> and <code>VARRAY</code> types for the <code>RECORD</code> and <code>TABLE</code> types. <code>OBJECT</code> types can be mapped to classes annotated with either <code>@Entity</code> or <code>@Embeddable</code> using the <code>@Struct</code> annotation. Typically, classes annotated with <code>@Embeddable</code> are used, unless the <code>OBJECT</code> type defines an <code>Id</code> and can be stored in a table. Nested <code>OBJECT</code> and <code>VARRAY</code> types are mapped using the <code>@Structure</code> and <code>@Array</code> annotations.</p>
<p>Use the <code>PLSQLStoredFunctionCall</code> class or the <code>@NamedPLSQLStoredFunctionQuery</code> annotation to call a stored function using PL/SQL types. The <code>PLSQLStoredProcedureCall</code> class and the <code>@NamedPLSQLStoredProcedureQuery</code> annotation also exist for stored procedures. Use the <code>StoredFunctionCall</code> class, the <code>@NamedStoredFunctionQuery</code> annotation, the <code>StoredProcedureCall</code> class, and the <code>@NamedStoredProcedureQuery</code> annotation for stored functions and procedure that do not return complex PL/SQL types.</p>
<a id="TLADG654" name="TLADG654"></a>
<div class="sect4"><a id="sthref238" name="sthref238"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Main Tasks</font></h4>
<p>To execute an Oracle PL/SQL stored function:</p>
<ul>
<li>
<p><a href="#CHDJBJEF">Task 1: Create an Oracle Stored Function That Returns a PL/SQL Record Type</a></p>
</li>
<li>
<p><a href="#CHDIGHDG">Task 2: Define an Object Type Mirror</a></p>
</li>
<li>
<p><a href="#CHDEIABJ">Task 3: Define a Java Class Mapping The OBJECT Type</a></p>
</li>
<li>
<p><a href="#CHDFACEG">Task 4: Execute a PL/SQL Stored Function Using JpaEntityManager</a></p>
</li>
<li>
<p><a href="#CHDHDIAF">Task 5: Define a Stored Function Using @NamedPLSQLStoredFunctionQuery</a></p>
</li>
<li>
<p><a href="#CHDHEIFJ">Task 6: Use the Stored Function in a Query</a></p>
</li>
</ul>
<a id="CHDJBJEF" name="CHDJBJEF"></a><a id="TLADG655" name="TLADG655"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" -->
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 1: Create an Oracle Stored Function That Returns a PL/SQL Record Type</font></h4>
<pre xml:space="preserve" class="oac_no_warn">
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;
</pre></div>
<!-- class="sect4" -->
<a id="CHDIGHDG" name="CHDIGHDG"></a><a id="TLADG656" name="TLADG656"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" -->
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 2: Define an Object Type Mirror</font></h4>
<pre xml:space="preserve" class="oac_no_warn">
CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT (F_NAME VARCHAR2(30),
L_NAME VARCHAR2(30), SALARY NUMBER(10,2))
</pre></div>
<!-- class="sect4" -->
<a id="CHDEIABJ" name="CHDEIABJ"></a><a id="TLADG657" name="TLADG657"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" -->
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 3: Define a Java Class Mapping The OBJECT Type</font></h4>
<pre xml:space="preserve" class="oac_no_warn">
@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;
...
}
</pre></div>
<!-- class="sect4" -->
<a id="CHDFACEG" name="CHDFACEG"></a><a id="TLADG658" name="TLADG658"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" -->
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 4: Execute a PL/SQL Stored Function Using JpaEntityManager</font></h4>
<pre xml:space="preserve" class="oac_no_warn">
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();
</pre></div>
<!-- class="sect4" -->
<a id="CHDHDIAF" name="CHDHDIAF"></a><a id="TLADG659" name="TLADG659"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" -->
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 5: Define a Stored Function Using @NamedPLSQLStoredFunctionQuery</font></h4>
<pre xml:space="preserve" class="oac_no_warn">
@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 {
...
}
</pre></div>
<!-- class="sect4" -->
<a id="CHDHEIFJ" name="CHDHEIFJ"></a><a id="TLADG660" name="TLADG660"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" -->
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 6: Use the Stored Function in a Query</font></h4>
<pre xml:space="preserve" class="oac_no_warn">
Query query = entityManager.createNamedQuery("getEmployee");
Employee result = (Employee)query.getSingleResult();
</pre></div>
<!-- class="sect4" --></div>
<!-- class="sect4" --></div>
<!-- class="sect3" -->
<a id="CHDDJJBI" name="CHDDJJBI"></a><a id="TLADG661" name="TLADG661"></a>
<div class="sect3">
<h3 class="sect3"><font face="arial, helvetica, sans-serif" color="#330099">Handling PL/SQL arguments for Oracle Stored Procedures</font></h3>
<p>The standard way of handling a stored procedure is to build an instance of the <code>StoredProcedureCall</code> class. However, the arguments must be compatible with the JDBC specification. To handle Oracle PL/SQL arguments (for example, <code>BOOLEAN</code>, <code>PLS_INTEGER</code>, PL/SQL record, and so on), use the <code>PLSQLStoredProcedureCall</code> class.</p>
<div align="center">
<div class="inftblnote"><br />
<table class="Note oac_no_warn" summary="" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0">
<tbody>
<tr>
<td align="left">
<p class="note"><img src="../../dcommon/images/note_icon.png" width="16" height="16" alt="Note" style="vertical-align:middle;padding-right:5px;" />Note:</p>
<p>the <code>PLSQLStoredProcedureCall</code> class is only supported on Oracle8 or higher.</p>
</td>
</tr>
</tbody>
</table>
<br /></div>
<!-- class="inftblnote" --></div>
<a id="TLADG662" name="TLADG662"></a>
<div class="sect4"><a id="sthref239" name="sthref239"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Using the PLSQLStoredProcedureCall Class</font></h4>
<p>The following example demonstrates handling PL/SQL arguments using the <code>PLSQLStoredProcedureCall</code> class. The example is based on the following target procedure:</p>
<pre xml:space="preserve" class="oac_no_warn">
PROCEDURE bool_in_test(x IN BOOLEAN)
</pre>
<a id="TLADG663" name="TLADG663"></a>
<p><b><font face="arial, helvetica, sans-serif" color="#330099">Example of Using the PLSQLStoredProcedureCall Class</font></b></p>
<pre xml:space="preserve" class="oac_no_warn">
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 = "<em>username</em>";
public static String DATABASE_PASSWORD = "<em>password</em>";
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);
}
}
</pre>
<p>The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:</p>
<pre xml:space="preserve" class="oac_no_warn">
...[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=&gt;X_TARGET);
END;
bind =&gt; [:1 =&gt; 1]
</pre>
<div align="center">
<div class="inftblnote"><br />
<table class="Note oac_no_warn" summary="" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0">
<tbody>
<tr>
<td align="left">
<p class="note"><img src="../../dcommon/images/note_icon.png" width="16" height="16" alt="Note" style="vertical-align:middle;padding-right:5px;" />Note:</p>
<p>Notice the conversion of the Integer to a PL/SQL <code>BOOLEAN</code> type in the <code>DECLARE</code> stanza (as a similar conversion is used for OUT <code>BOOLEAN</code> arguments).</p>
</td>
</tr>
</tbody>
</table>
<br /></div>
<!-- class="inftblnote" --></div>
</div>
<!-- class="sect4" -->
<a id="TLADG664" name="TLADG664"></a>
<div class="sect4"><a id="sthref240" name="sthref240"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Mixing JDBC Arguments With Non JDBC Arguments</font></h4>
<p>A Stored Procedure may have a mix of regular and non JDBC arguments. Use the <code>PLSQLStoredProcedureCall</code> 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:</p>
<pre xml:space="preserve" class="oac_no_warn">
PROCEDURE two_arg_test(x IN BOOLEAN, y IN VARCHAR)
</pre>
<a id="TLADG665" name="TLADG665"></a>
<p><b><font face="arial, helvetica, sans-serif" color="#330099">Example of Mixing JDBC Arguments With NonJDBC Arguments</font></b></p>
<pre xml:space="preserve" class="oac_no_warn">
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);
</pre>
<p>The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:</p>
<pre xml:space="preserve" class="oac_no_warn">
[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=&gt;X_TARGET, Y=&gt;Y_TARGET);
END;
bind =&gt; [:1 =&gt; 0, :2 =&gt; test]
</pre></div>
<!-- class="sect4" -->
<a id="TLADG666" name="TLADG666"></a>
<div class="sect4"><a id="sthref241" name="sthref241"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Handling IN and OUT Arguments</font></h4>
<p>The following example demonstrates a stored procedure that contain both <code>IN</code> and <code>OUT</code> arguments and is based on the following target procedure:</p>
<pre xml:space="preserve" class="oac_no_warn">
PROCEDURE two_arg_in_out(x OUT BINARY_INTEGER, y IN VARCHAR) AS
BEGIN
x := 33;
END;
</pre>
<a id="TLADG667" name="TLADG667"></a>
<p><b><font face="arial, helvetica, sans-serif" color="#330099">Example of Handling IN and OUT Arguments</font></b></p>
<pre xml:space="preserve" class="oac_no_warn">
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");
}
</pre>
<p>The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:</p>
<pre xml:space="preserve" class="oac_no_warn">
[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=&gt;X_TARGET, Y=&gt;Y_TARGET);
:2 := X_TARGET;
END;
bind =&gt; [:1 =&gt; testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds, X =&gt; :2]
</pre>
<div align="center">
<div class="inftblnote"><br />
<table class="Note oac_no_warn" summary="" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0">
<tbody>
<tr>
<td align="left">
<p class="note"><img src="../../dcommon/images/note_icon.png" width="16" height="16" alt="Note" style="vertical-align:middle;padding-right:5px;" />Note:</p>
<p>The order in which arguments are bound at runtime must be altered. Anonymous PL/SQL blocks must process the ordinal markers (<code>:1</code>,<code>:2</code>) for all the IN arguments first, then the OUT arguments. Inside the block, the arguments are passed in the correct order for the target procedure, but the bind order is managed in the <code>DECLARE</code> stanza and after the target procedure has been invoked.</p>
</td>
</tr>
</tbody>
</table>
<br /></div>
<!-- class="inftblnote" --></div>
</div>
<!-- class="sect4" -->
<a id="TLADG668" name="TLADG668"></a>
<div class="sect4"><a id="sthref242" name="sthref242"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Handling IN OUT Arguments</font></h4>
<p>Anonymous PL/SQL blocks cannot natively handle <code>IN OUT</code> 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:</p>
<pre xml:space="preserve" class="oac_no_warn">
PROCEDURE two_args_inout(x VARCHAR, y IN OUT BOOLEAN) AS
BEGIN
y := FALSE;
END;
</pre>
<a id="TLADG669" name="TLADG669"></a>
<p><b><font face="arial, helvetica, sans-serif" color="#330099">Example of Handling IN OUT Arguments</font></b></p>
<pre xml:space="preserve" class="oac_no_warn">
...
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");
}
</pre>
<p>The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:</p>
<pre xml:space="preserve" class="oac_no_warn">
[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=&gt;X_TARGET, Y=&gt;Y_TARGET);
:3 := SYS.SQLJUTL.BOOL2INT(Y_TARGET);
END;
bind =&gt; [:1 =&gt; test, :2 =&gt; 1, Y =&gt; :3]
</pre>
<div align="center">
<div class="inftblnote"><br />
<table class="Note oac_no_warn" summary="" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0">
<tbody>
<tr>
<td align="left">
<p class="note"><img src="../../dcommon/images/note_icon.png" width="16" height="16" alt="Note" style="vertical-align:middle;padding-right:5px;" />Note:</p>
<p>The <code>Y</code> argument is split in two using the <code>:2</code> and <code>:3</code> ordinal markers.</p>
</td>
</tr>
</tbody>
</table>
<br /></div>
<!-- class="inftblnote" --></div>
</div>
<!-- class="sect4" --></div>
<!-- class="sect3" --></div>
<!-- class="sect2" -->
<a id="CHDEFIBH" name="CHDEFIBH"></a><a id="TLADG1060" name="TLADG1060"></a>
<div class="sect2"><!-- infolevel="all" infotype="General" -->
<h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">Using Oracle Virtual Private Database</font></h2>
<p>EclipseLink supports Oracle Virtual Private Database (VPD). Oracle VPD is a server-enforced, fine-grained access control mechanism. Oracle VPD ties a security policy to a table by dynamically appending SQL statements with a predicate to limit data access at the row level. You can create your own security policies, or use Oracle's custom implementation called Oracle Label Security (OLS). For details about Oracle VPD, see <em>Oracle Database Security Guide</em>. For details about Oracle Label Security, see <em>Oracle Label Security Administrator's Guide</em>.</p>
<p>For details about using Oracle VPD with Multitenancy, see <a href="multitenancy004.htm#CHDJEBAC">Using VPD Multi-Tenancy.</a></p>
<p>To use the Oracle Database VPD feature in an EclipseLink application, an isolated cache should be used. Any entity that maps to a table that uses Oracle VPD should have the descriptor configured as isolated. In addition, you typically use exclusive connections.</p>
<p>To support Oracle VPD, you must implement session event handlers that the are invoked during the persistence context's life cycle. The session event handler you must implement depends on whether or not you are using Oracle Database proxy authentication.</p>
<a id="TLADG1061" name="TLADG1061"></a>
<p><b><font face="arial, helvetica, sans-serif" color="#330099">Oracle VPD with Oracle Database Proxy Authentication</font></b></p>
<p>By using Oracle Database proxy authentication, you can set up Oracle VPD support entirely in the database. That is, rather than session event handlers to execute SQL, the database performs the required setup in an after login trigger using the proxy session_user.</p>
<p>For details on using Oracle proxy authentication, see <a href="#CHDGDDJF">Using Oracle Proxy Authentication.</a></p>
<a id="TLADG1062" name="TLADG1062"></a>
<p><b><font face="arial, helvetica, sans-serif" color="#330099">Oracle VPD Without Oracle Database Proxy Authentication</font></b></p>
<p>If you are not using Oracle Database proxy authentication, implement session event handlers for the following session events:</p>
<ul>
<li>
<p><code>postAcquireExclusiveConnection</code>: used to perform Oracle VPD setup at the time a dedicated connection is allocated to an isolated session and before the isolated session user uses the connection to interact with the database.</p>
</li>
<li>
<p><code>preReleaseExclusiveConnection</code>: used to perform Oracle VPD cleanup at the time the isolated session is released and after the user is finished interacting with the database.</p>
</li>
</ul>
<p>In the implementation of these handlers, you can obtain the required user credentials from the associated session's properties.</p>
</div>
<!-- class="sect2" -->
<a id="CHDGDDJF" name="CHDGDDJF"></a><a id="TLADG1063" name="TLADG1063"></a>
<div class="sect2">
<h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">Using Oracle Proxy Authentication</font></h2>
<p>JPA and EclipseLink are typically used in a middle tier/server environment with a shared connection pool. A connection pool allows database connections to be shared to avoid the cost of reconnecting to the database. Typically, the user logs into the application but does not have their own database login as a shared login is used for the connection pool. The provides a mechanism to set a proxy user on an existing database connection. This allows for a shared connection pool to be used, but to also gives the database a user context.</p>
<p>Oracle proxy authentication is configured using the following persistence unit properties on an <code>EntityManager</code> object:</p>
<ul>
<li>
<p><code>"eclipselink.oracle.proxy-type" : oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME, PROXYTYPE_CERTIFICATE, PROXYTYPE_DISTINGUISHED_NAME</code></p>
</li>
<li>
<p><code>oracle.jdbc.OracleConnection.PROXY_USER_NAME :</code> <em><code>user_name</code></em></p>
</li>
<li>
<p><code>oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD :</code> <em><code>password</code></em></p>
</li>
<li>
<p><code>oracle.jdbc.OracleConnection.PROXY_DISTINGUISHED_NAME</code></p>
</li>
<li>
<p><code>oracle.jdbc.OracleConnection.PROXY_CERTIFICATE</code></p>
</li>
<li>
<p><code>oracle.jdbc.OracleConnection.PROXY_ROLES</code></p>
</li>
</ul>
<div align="center">
<div class="inftblnote"><br />
<table class="Note oac_no_warn" summary="" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0">
<tbody>
<tr>
<td align="left">
<p class="note"><img src="../../dcommon/images/note_icon.png" width="16" height="16" alt="Note" style="vertical-align:middle;padding-right:5px;" />Note:</p>
<p>This connection is only used for writing by default; reads still use the shared connection pool. To force reads to also use the connection, the <code>eclipselink.jdbc.exclusive-connection.mode</code> property should be set to <code>Always</code>, but this depends on if the application wishes to audit writes or reads as well. The <code>eclipselink.jdbc.exclusive-connection.is-lazy</code> property configures whether the connection should be connected up front, or only when first required. If only writes are audited, then lazy connections allow for the cost of creating a new database connection to be avoided unless a write occurs.</p>
</td>
</tr>
</tbody>
</table>
<br /></div>
<!-- class="inftblnote" --></div>
<a id="TLADG1064" name="TLADG1064"></a>
<div class="sect3"><a id="sthref243" name="sthref243"></a>
<h3 class="sect3"><font face="arial, helvetica, sans-serif" color="#330099">Main Tasks:</font></h3>
<p>To setup proxy authentication, create an <code>EntityManager</code> object and set the persistence unit properties. Three examples are provided:</p>
<a id="TLADG1065" name="TLADG1065"></a>
<p><b><font face="arial, helvetica, sans-serif" color="#330099">Task: Audit Only Writes</font></b></p>
<p>To configure proxy authentication when auditing only writes:</p>
<pre xml:space="preserve" class="oac_no_warn">
Map properties = new HashMap();
properties.put("eclipselink.oracle.proxy-type",
oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password);
properties.put("eclipselink.jdbc.exclusive-connection.mode", "Transactional");
properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "true");
EntityManager em = factory.createEntityManager(properties);
</pre>
<a id="TLADG1066" name="TLADG1066"></a>
<p><b><font face="arial, helvetica, sans-serif" color="#330099">Task: Audit Reads and Writes</font></b></p>
<p>To configure proxy authentication when auditing reads and writes:</p>
<pre xml:space="preserve" class="oac_no_warn">
Map properties = new HashMap();
properties.put("eclipselink.oracle.proxy-type",
oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password);
properties.put("eclipselink.jdbc.exclusive-connection.mode", "Always");
properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "false");
EntityManager em = factory.createEntityManager(properties);
</pre>
<a id="TLADG1067" name="TLADG1067"></a>
<p><b><font face="arial, helvetica, sans-serif" color="#330099">Task: Configure Proxy Authentication in Java EE Applications</font></b></p>
<p>If a JEE and JTA managed entity manager is used, specifying a proxy user and password can be more difficult, as the entity manager and JDBC connection is not under the applications control. The persistence unit properties can still be specified on an <code>EntityManager</code> object as long as this is done before establishing a database connection.</p>
<p>If using JPA 2.<em><code>n</code></em>, the <code>setProperty</code> API can be used:</p>
<pre xml:space="preserve" class="oac_no_warn">
em.setProperty("eclipselink.oracle.proxy-type",
oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME);
em.setProperty(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user);
em.setProperty(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password);
em.setProperty("eclipselink.jdbc.exclusive-connection.mode", "Always");
em.setProperty("eclipselink.jdbc.exclusive-connection.is-lazy", "false");
</pre>
<p>Otherwise, the <code>getDelegate</code> API can be used:</p>
<pre xml:space="preserve" class="oac_no_warn">
Map properties = new HashMap();
properties.put("eclipselink.oracle.proxy-type",
oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password);
properties.put("eclipselink.jdbc.exclusive-connection.mode", "Always");
properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "false");
((org.eclipse.persistence.internal.jpa.EntityManagerImpl)em.getDelegate()).
setProperties(properties);
</pre></div>
<!-- class="sect3" -->
<a id="TLADG1068" name="TLADG1068"></a>
<div class="sect3"><a id="sthref244" name="sthref244"></a>
<h3 class="sect3"><font face="arial, helvetica, sans-serif" color="#330099">Caching and security</font></h3>
<p>By default, EclipseLink maintains a shared (L2) object cache. This is fine for auditing, but if Oracle VPD or user based security is used to prevent the reading of certain tables/classes, then the cache may need to be disabled for these secure classes. To disable the shared cache, see <a href="scaling002.htm#CEGEDHAB">"Disabling Entity Caching"</a>.</p>
<p>If the database user is used to check security for reads, then set the <code>eclipselink.jdbc.exclusive-connection.mode</code> property to <code>Isolated</code> to only use the user connection for reads for the classes whose shared cache has been disabled (isolated).</p>
</div>
<!-- class="sect3" -->
<a id="TLADG1069" name="TLADG1069"></a>
<div class="sect3"><a id="sthref245" name="sthref245"></a>
<h3 class="sect3"><font face="arial, helvetica, sans-serif" color="#330099">Using Oracle Virtual Private Database for Row-Level Security</font></h3>
<p>The Oracle Virtual Private Database (VPD) feature allows for row level security within the Oracle database. Typically, database security only allows access privileges to be assigned per table. Row level security allows different users to have access to different rows within each table.</p>
<p>The Oracle proxy authentication features in EclipseLink can be used to support Oracle VPD. The proxy user allows for the row level security to be checked. When using Oracle VPD, it is also important to disable shared caching for the secured objects as these objects should not be shared. To disable the shared cache, see <a href="scaling002.htm#CEGEDHAB">"Disabling Entity Caching"</a>.</p>
</div>
<!-- class="sect3" --></div>
<!-- class="sect2" -->
<a id="CHDIEBBB" name="CHDIEBBB"></a><a id="TLADG1070" name="TLADG1070"></a>
<div class="sect2">
<h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">Using EclipseLink with Oracle RAC</font></h2>
<p>Oracle Real Application Clusters (RAC) extends the Oracle Database so that you can store, update, and efficiently retrieve data using multiple database instances on different servers at the same time. Oracle RAC provides the software that manages multiple servers and instances as a single group. Applications use Oracle RAC features to maximize connection performance and availability and to mitigate down-time due to connection problems. Applications have different availability and performance requirements and implement Oracle RAC features accordingly. For details on Oracle RAC, see the <em>Oracle Real Application Clusters Administration and Deployment Guide</em>.</p>
<p>The Oracle Database and the Oracle WebLogic Server both provide connection pool implementations that can create connections to a RAC database and take advantage of various Oracle RAC features. The features include Fast Connection Failover (FCF), Run-Time Connection Load Balancing (RCLB), and connection affinity. In WebLogic Server, applications create JDBC data sources (Multi Data Source or GridLink Data Source) to connect to a RAC-enabled database. Standalone applications use the Universal Connection Pool (UCP) JDBC connection pool API (<code>ucp.jar</code>) to create data sources. Both connection pool implementations require the Oracle Notification Service library (<code>ons.jar</code>). This library is the primary means by which the connection pools register for, and listen to, RAC events. For those new to these technologies, refer to the <em>Oracle Universal Connection Pool for JDBC Developer's Guide</em> and the Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.</p>
<p>This sections assumes that you have an Oracle JDBC driver and Oracle RAC-enabled database. Make sure that the RAC-enabled database is operational and that you know the connection URL. In addition, download the database Oracle Client software that contains the <code>ons.jar</code> file. The <code>ucp.jar</code> file is included with the Oracle Database.</p>
<a id="TLADG1071" name="TLADG1071"></a>
<div class="sect3"><a id="sthref246" name="sthref246"></a>
<h3 class="sect3"><font face="arial, helvetica, sans-serif" color="#330099">Accessing a RAC-Enabled database from Java EE Applications</font></h3>
<p>The tasks in this section are used to connect to a RAC-enabled database from a persistence application implemented in Oracle WebLogic Server.</p>
<a id="TLADG1072" name="TLADG1072"></a>
<div class="sect4"><a id="sthref247" name="sthref247"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 1: Configure a Multi Data Source or GridLink Data Source</font></h4>
<p>Refer to <a href="tlandwls.htm#BABHCJBG">Chapter 3, "Using EclipseLink with WebLogic Server,"</a> and <em>Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server</em> for details about configuring a data source in WebLogic Server for Oracle RAC.</p>
</div>
<!-- class="sect4" -->
<a id="TLADG1073" name="TLADG1073"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" --><a id="sthref248" name="sthref248"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 2: Configure the Persistence Unit</font></h4>
<p>Edit the <code>persistence.xml</code> file and include the name of the data source within a persistence unit configuration. For example:</p>
<pre xml:space="preserve" class="oac_no_warn">
&lt;persistence-unit name="OrderManagement"&gt;
&lt;jta-data-source&gt;jdbc/MyOrderDB&lt;/jta-data-source&gt;
...
&lt;/persistence-unit&gt;
</pre></div>
<!-- class="sect4" -->
<a id="TLADG1074" name="TLADG1074"></a>
<div class="sect4"><a id="sthref249" name="sthref249"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 3: Include the Required JARs</font></h4>
<p>Ensure that the <code>ons.jar</code> is in the WebLogic Server classpath.</p>
</div>
<!-- class="sect4" --></div>
<!-- class="sect3" -->
<a id="TLADG1075" name="TLADG1075"></a>
<div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref250" name="sthref250"></a>
<h3 class="sect3"><font face="arial, helvetica, sans-serif" color="#330099">Accessing a RAC-Enabled Database from Standalone Applications</font></h3>
<p>The tasks in this section are used to connect to a RAC database from a standalone persistence application. The tasks demonstrate how to use UCP data sources which are required for advanced RAC features.</p>
<a id="TLADG1076" name="TLADG1076"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" --><a id="sthref251" name="sthref251"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 1: Create a UCP Data Source</font></h4>
<p>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 <em>Oracle Universal Connection Pool for JDBC Developer's Guide</em>. The following example creates a data source and enables FCF and configures ONS.</p>
<pre xml:space="preserve" class="oac_no_warn">
PoolDataSource datasource = PoolDataSourceFactory.getPoolDataSource();
datasource.setONSConfiguration(&rdquo;nodes=<em>host1</em>:4200,<em>host2</em>:4200&rdquo;);
datasource.setFastConnectionFailoverEnabled(true);
datasource.setConnectionFactoryClassName(&rdquo;oracle.jdbc.pool.OracleDataSource&rdquo;);
datasource.setURL(&rdquo;jdbc:oracle:thin:@DESCRIPTION=
(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=<em>host1</em>)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=<em>host2</em>)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=<em>host3</em>)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=<em>host4</em>)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service_name)))&rdquo;);
</pre>
<p>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 <code>persistence.xml</code> file for you applications and add the RAC URL connection string for a persistence unit. For example:</p>
<pre xml:space="preserve" class="oac_no_warn">
&lt;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"&gt;
&lt;persistence-unit name="my-app" transaction-type="RESOURCE_LOCAL"&gt;
&lt;provider&gt;org.eclipse.persistence.jpa.PersistenceProvider&lt;/provider&gt;
&lt;exclude-unlisted-classes&gt;false&lt;/exclude-unlisted-classes&gt;
&lt;properties&gt;
&lt;property name="javax.persistence.jdbc.driver"
value="oracle.jdbc.OracleDriver"/&gt;
&lt;property name="javax.persistence.jdbc.url"
value="jdbc:oracle:thin@(DESCRIPTION= "+ "(LOAD_BALANCE=on)"+
"(ADDRESS=(PROTOCOL=TCP)(HOST=<em>rac_node</em>) (PORT=1521))"+
"(ADDRESS=(PROTOCOL=TCP)(HOST=<em>racnode2</em>) (PORT=1521))"+
"(CONNECT_DATA=(SERVICE_NAME=<em>service_name</em>))")"/&gt;
&lt;property name="javax.persistence.jdbc.user" value="<em>user_name</em>"/&gt;
&lt;property name="javax.persistence.jdbc.password" value="<em>password</em>"/&gt;
&lt;/properties&gt;
&lt;/persistence-unit&gt;
&lt;/persistence&gt;
</pre>
<p>To use the persistence unit, instantiate an <code>EntityManagerFactory</code> as follows:</p>
<pre xml:space="preserve" class="oac_no_warn">
Persistence.createEntityManagerFactory("my-app");
</pre></div>
<!-- class="sect4" -->
<a id="TLADG1077" name="TLADG1077"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" --><a id="sthref252" name="sthref252"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 2: Use the UCP Data Source</font></h4>
<p>To use the UCP data source, instantiate an <code>EntityManagerFactory</code> an pass in the data source as follows:</p>
<pre xml:space="preserve" class="oac_no_warn">
Map properties = new HashMap();
properties.add("javax.persistence.nonJtaDataSource", datasource);
Persistence.createEntityManagerFactory(properties);
</pre></div>
<!-- class="sect4" -->
<a id="TLADG1078" name="TLADG1078"></a>
<div class="sect4"><!-- infolevel="all" infotype="General" --><a id="sthref253" name="sthref253"></a>
<h4 class="sect4"><font face="arial, helvetica, sans-serif" color="#330099">Task 3: Include the Required JARs</font></h4>
<p>Ensure that both <code>ucp.jar</code> and <code>ons.jar</code> are in the application classpath.</p>
</div>
<!-- class="sect4" --></div>
<!-- class="sect3" --></div>
<!-- class="sect2" -->
<a id="CHDJBFIJ" name="CHDJBFIJ"></a><a id="TLADG1079" name="TLADG1079"></a>
<div class="sect2">
<h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">Using Oracle Spatial and Graph</font></h2>
<p>EclipseLink provides added support for querying Oracle Spatial and Graph data in the Oracle Database. Oracle Spacial and Graph is used to location-enable applications. It provides advanced features for spatial data and analysis and for physical, logical, network, and social and semantic graph applications. The spatial features provide a schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial features in an Oracle database. For details about developing Oracle Spacial and Graph applications, see <em>Oracle Spatial and Graph Developer's Guide</em>. To use Oracle Spatial and Graph within WebLogic Server, see <a href="tlandwls002.htm#BABHICHE">Chapter 3, "Task 7: Extend the Domain to Use Advanced Oracle Database Features,"</a></p>
<p>EclipseLink applications can construct expressions that use Oracle Spacial and Graph operators. See the <code>org.eclipse.persistence.expressions.spatial</code> API for details. For Example:</p>
<pre xml:space="preserve" class="oac_no_warn">
ExpressionBuilder builder = new ExpressionBuilder();
Expression withinDistance = SpatialExpressions.withinDistance(myJGeometry1,
myJGeometry2, "DISTANCE=10");
session.readAllObjects(GeometryHolder.class, withinDistance);
</pre>
<p>The above expression requires a <code>oracle.spatial.geometry.JGeometry</code> object. Use the EclipseLink <code>org.eclipse.persistence.platform.database.oracle.converters.JGeometryConverter</code> converter to convert the <code>JGeometry</code> object as it is read and written from the Oracle database. The <code>JGeometryConverter</code> object must be added to the Oracle Database platform either with the <code>addStructConverter(StructConverter)</code> method or specified in the <code>sessions.xml</code> file. The <code>JGeometry</code> type must also be available on the classpath.</p>
<p>The following example demonstrates how to use the <code>FUNCTION</code> JPA extension to perform Oracle Spatial queries. For details on the <code>FUNCTION</code> extension, see <em>Java Persistence API (JPA) Extensions Reference for EclipseLink</em>:</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT a FROM Asset a, Geography geo WHERE geo.id = :id AND a.id IN :id_list AND
FUNCTION('ST_INTERSECTS', a.geometry, geo.geometry) = 'TRUE'
SELECT s FROM SimpleSpatial s WHERE FUNCTION('MDSYS.SDO_RELATE', s.jGeometry,
:otherGeometry, :params) = 'TRUE' ORDER BY s.id ASC
</pre></div>
<!-- class="sect2" --></div>
<!-- class="sect1" --></div>
<!-- class="ind" -->
<!-- Start Footer -->
<div class="footer">
<hr />
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="33%" />
<col width="*" />
<col width="33%" />
<tr>
<td valign="bottom">
<table class="navigation simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100" align="center">
<col width="*" />
<col width="48%" />
<col width="48%" />
<tr>
<td>&nbsp;</td>
<td align="center"><a href="oracledb001.htm"><img src="../../dcommon/images/larrow.png" alt="Previous" border="0" height="16" width="16" /></a></td>
<td align="center"><a href="oracledb003.htm"><img src="../../dcommon/images/rarrow.png" alt="Next" border="0" height="16" width="16" /></a></td>
</tr>
</table>
</td>
<td align="center" width="34%"><a href="http://www.eclipse.org/eclipselink/" title="EclipseLink home"><img src="../../dcommon/images/ellogo.png" alt="EclipseLink" width="150" border="0" /></a><br />
<font size="-2">Copyright&nbsp;&copy;&nbsp;2014,&nbsp;Oracle&nbsp;and/or&nbsp;its&nbsp;affiliates.&nbsp;All&nbsp;rights&nbsp;reserved.</font> <a href="../../dcommon/html/cpyr.htm"><br />
<td valign="bottom" align="right">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="225">
<tr>
<td>&nbsp;</td>
<td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/images/contents.png" alt="Go To Table Of Contents" border="0" height="16" width="16" /><br />
</td><td>&nbsp;</td><td align="center"><a href="../../" target="_top" class="external text" title="Search" rel="nofollow"><img src="../../dcommon/images/search.png" alt="Search" style="border:0;" /><br /><span class="mini"></span></a></td><td>&nbsp;</td><td align="center"><a href="../eclipselink_otlcg.pdf" title="PDF" target="_blank"><img src="../../dcommon/images/pdf_icon.png" style="padding-right:5px;border:0" alt="PDF"></a></td>
</tr>
</table>
</td>
</tr>
</table>
</div>
<!-- class="footer" -->
<div id="copyright">Copyright &copy; 2014 by The Eclipse Foundation under the <a href="http://www.eclipse.org/org/documents/epl-v10.php">Eclipse Public License (EPL)</a><br /> <script type="text/javascript">var LastUpdated = document.lastModified;document.writeln ("Updated: " + LastUpdated);</script> </div><!-- START: Analytics --><script type="text/javascript"> var _gaq = _gaq || []; _gaq.push(['_setAccount', 'UA-1608008-2']); _gaq.push(['_trackPageview']); (function() { var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); })(); </script><!-- END: Analytics --><!-- START: Sharethis --><script>var options={ "publisher": "e2fe9e07-fab6-4f84-83ea-0991b429842c", "position": "right", "ad": { "visible": false, "openDelay": 5, "closeDelay": 0}};var st_hover_widget = new sharethis.widgets.hoverbuttons(options);</script><!-- END: Sharethis --></body>
</html>