Simple Java Stored procedure in JDeveloper 11g to Oracle 11g

The steps to create a Java Stored Procedure in JDeveloper 11g although similar to JDeveloper 10g have slightly changed. here is how to do this when deploying to a 11g R2 database. Couple of things to be aware of on the 11g RDBMS side.

Also now when you use a package the specification is created correctly to reference the correct package class name. In JDeveloper 10g you had to fix that manually.

1. Change your project J2SE version to JDK 1.5. can't use default 1.6 as Oracle JVM is not a 1.6 JVM,
it's a 1.5 JVM. By default JDeveloper 11g is using a 1.6 JDK which means the code you compile won't be able to be deployed to the Oracle JVM without switching to 1.5.

2. Create class as follows.

package pas.au.jsp;

public class DemoJSP
{
  public DemoJSP()
  {
  }
  
  public static String sayHello ()
  {
    return "Hello Pas";
  }
}

3. Right click on project node in navigator and select "New"
4. Select "Database Tier -> Database Files -> Load java and Java Stored procedures"
5. Click on "Ok"
6. Click on "Ok" again
7. Right click on profile storedProc1.dbexport and select "Add PLSQL Package"
8. Name it "HelloWorldPKG" and press ok
9. Right click on "HelloWorldPKG" and select "Add Stored procedure"
10. Select method sayHello and press OK
12. Right click on profile storedProc1 and select "Export to -> {your 11g database connection }"

JDeveloper Log window should show something as follows.

[12:00:54 PM] Invoking loadjava on connection 'scott-11gr2' with arguments:

[12:00:54 PM] -order -resolve -thin

[12:00:57 PM] Loadjava finished.

[12:00:58 PM] Executing SQL Statement:

[12:00:58 PM] CREATE OR REPLACE PACKAGE HELLOWORLDPKG

AUTHID CURRENT_USER AS FUNCTION sayHello RETURN VARCHAR2; END HELLOWORLDPKG;

[12:00:58 PM] Success.

[12:00:58 PM] Executing SQL Statement:

[12:00:58 PM] CREATE OR REPLACE PACKAGE BODY HELLOWORLDPKG AS FUNCTION sayHello RETURN VARCHAR2

AS LANGUAGE JAVA NAME 'pas.au.jsp.DemoJSP.sayHello() return java.lang.String'; END HELLOWORLDPKG;

[12:00:58 PM] Success.

[12:00:58 PM] Publishing finished.

[12:00:58 PM] ----  Stored procedure database export finished.  ----

13. From SQL*Plus invoke Java Stored procedure as follows.

d:\temp>sqlplus scott/tiger@linux11gr2

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 19 12:02:13 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@linux11gr2> select HELLOWORLDPKG.sayHello from dual;

SAYHELLO
-------------------------------------------------------------------------------

Hello Pas

SCOTT@linux11gr2>

Jetty 6.x UCP Data Source Setup

Here is how I setup Jetty 6.1 to use UCP (Universal Connection Pool) data source. Like tomcat it was a straight forward process. I have never used Jetty before so no doubt I may have put files in the wrong places but it still did work in the end.

1. Edit etc/jetty-plus.xml to add my data source config as follows.

<!-- Add a UCP DataSource -->
<New id="scott-oracle-ucp" class="org.mortbay.jetty.plus.naming.Resource">
  <Arg>jdbc/UCPPool</Arg>
  <Arg>
   <New class="oracle.ucp.jdbc.PoolDataSourceImpl">
    <Set name="connectionFactoryClassName">oracle.jdbc.pool.OracleDataSource</Set>
    <Set name="inactiveConnectionTimeout">20</Set>
    <Set name="user">scott</Set>
    <Set name="password">tiger</Set>
    <Set name="URL">jdbc:oracle:thin:@beast.au.oracle.com:1523/linux11gr2</Set>
    <Set name="minPoolSize">2</Set>
    <Set name="maxPoolSize">5</Set>
    <Set name="initialPoolSize">2</Set>
   </New>
  </Arg>
 </New> 

2. Copy ojdbc6.jar and ucp.jar into $JETTY_HOME\lib\ext directory. You can download those JAR files from here.

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

Note: We started Jetty using JDK 1.6 so that we could use ojdbc6.jar. 

3. Start Jetty as follows ensuring we use our jetty-plus.xml file on the startup command line.

java -jar start.jar etc/jetty.xml etc/jetty-plus.xml

4.  Now simply deploy an application which will use the data source to ensure it is created. Then use JConsole to verify indeed your using UCP as shown below.

UTL_DBWS : Handling Web Service methods that accept XML Input parameters

Dealing with Web Services callouts using the UTL_DBWS package requires some thought when calling methods which accept XML data such as "org.w3c.dom.Element". In this example I show how to ensure the XML data within your XML file is correctly escaped prior to sending the request to the Web Service.

An example of UTL_DBWS package can be found on steve's blog here. That shows how to set it up at the database level in 11g.

Lets assume we have a Web Service deployed to OAS 10.1.3.x with a class as follows. Basic method which takes a org.w3c.dom.Element parameter for it's only service method testXML.

package pas.au.xml.ws;

import org.w3c.dom.Element;

public class XMLWebService
{
  public XMLWebService()
  {
  }
  
  public String testXML (Element xmlInput)
  {
    return "success";
  }
}

With UTL_DBWS installed we could simply write a PLSQL block as follows to call out Web Service method and the output clearly shows this works fine.

set serveroutput on size 100000
set linesize 130

declare

  service_ sys.utl_dbws.SERVICE;
  call_ sys.utl_dbws.CALL;
  service_qname sys.utl_dbws.QNAME;
  port_qname sys.utl_dbws.QNAME;
  response sys.XMLTYPE;
  request sys.XMLTYPE;

  inputXML varchar2(300) := null;
  myXML varchar2(100) := 'pas';

begin

  dbms_output.put_line('Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor');
  service_qname := sys.utl_dbws.to_qname(null, 'testXMLElement');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_, 'http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPort');
  sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');

  dbms_output.put_line('Preparing request');

  inputXML := '<ns1:testXMLElement xmlns:ns1="http://pas.au.xml.ws/types/"><ns1:xmlInput><pas>'||
              myXML||
              '</pas></ns1:xmlInput>'||
              '</ns1:testXMLElement>';

  request       := sys.XMLTYPE(inputXML);
  response      := sys.utl_dbws.invoke(call_, request);

  dbms_output.put_line('** Soap Response **'||chr(10)||response.getStringVal());

  dbms_output.put_line('Result = '||
        response.extract('//ns0:result/child::text()',
        'xmlns:ns0="http://pas.au.xml.ws/types/"').getstringval());
end;
/
show errors;

Output

SCOTT@linux11gr2> @invoke_xmlws.sql
Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor
Preparing request
** Soap Response **
<ns0:testXMLResponseElement xmlns:ns0="http://pas.au.xml.ws/types/">

<ns0:result>success</ns0:result>
</ns0:testXMLResponseElement>

Result = success

PL/SQL procedure successfully completed.

No errors.
SCOTT@linux11gr2>

The problem here is we can't just assume that the data which we pass into theWeb Service method is not a special character which in the XML world would require that it be escaped. The data we passed in our first test was a simple string such as "pas" as shown below.

myXML varchar2(100) := 'pas';

Lets assume we now want to pass data as follows by introducing a & character. The assumption here is we can't control what is passed to the method so it may be a special character which XML won't accept.

myXML varchar2(100) := 'pas&';

The result when running the PLSQL Block now is a runtime exception as follows.

SCOTT@linux11gr2> @invoke_xmlws.sql
Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor
Preparing request
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &)
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 29


No errors.
SCOTT@linux11gr2>

In fact if you try this outside of PLSQL / Oracle RDBMS itself, such as a J2SE client you would get a runtime exception as follows. The database error was a lot more useful then this.

java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Unable to get header stream in saveChanges. SOAP exception while trying to externalize: Error parsing envelope: (1, 1) Start of root element expected.. java.io.IOException: SOAP exception while trying to externalize: Error parsing envelope: (1, 1) Start of root element expected.

So the solution around this is to use the package method dbms_xmlgen.CONVERT to convert the XML data into the escaped XML equivalent. So now our SQL is as follows

set serveroutput on size 100000
set linesize 130
call dbms_java.set_output(100000);

declare

  service_ sys.utl_dbws.SERVICE;
  call_ sys.utl_dbws.CALL;
  service_qname sys.utl_dbws.QNAME;
  port_qname sys.utl_dbws.QNAME;
  response sys.XMLTYPE;
  request sys.XMLTYPE;

  inputXML varchar2(300) := null;
  myXML varchar2(100) := 'pas&';

begin

  dbms_output.put_line('Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor');
  service_qname := sys.utl_dbws.to_qname(null, 'testXMLElement');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_, 'http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPort');
  sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');

  dbms_output.put_line('Preparing request');

  --add to convert XML with special characters correctly escaped
  inputXML := '<ns1:testXMLElement xmlns:ns1="http://pas.au.xml.ws/types/"><ns1:xmlInput><pas>'||
              dbms_xmlgen.CONVERT(myXML, dbms_xmlgen.ENTITY_ENCODE)||
              '</pas></ns1:xmlInput>'||
              '</ns1:testXMLElement>';

  request       := sys.XMLTYPE(inputXML);
  response      := sys.utl_dbws.invoke(call_, request);

  dbms_output.put_line('** Soap Response **'||chr(10)||response.getStringVal());

  dbms_output.put_line('Result = '||
         response.extract('//ns0:result/child::text()',
          'xmlns:ns0="http://pas.au.xml.ws/types/"').getstringval());
end;
/
show errors;

Note: I added a call to call dbms_java.set_output(100000); prior to running the BLOCK so we could see our REQUEST/RESPONSE XML to verify it has done what we needed it to do.

And the output now shows it works correctly.

Output

SCOTT@linux11gr2> @invoke_xmlws2.sql

Call completed.

Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor
ServiceFacotory: oracle.j2ee.ws.client.ServiceFactoryImpl@7e6efb86
WSDL: null
Service: oracle.j2ee.ws.client.BasicService@917ab4b8
*** Created service: -1261915451 - oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@eec8c59c ***
ServiceProxy.get(-1261915451) = oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@eec8c59c
setProperty(javax.xml.rpc.soap.operation.style, document)
Preparing request
dbwsproxy.add.map: ns1, http://pas.au.xml.ws/types/
Attribute 0: http://pas.au.xml.ws/types/: xmlns:ns1, http://pas.au.xml.ws/types/
dbwsproxy.lookup.map: ns1, http://pas.au.xml.ws/types/
createElement(ns1:testXMLElement,null,http://pas.au.xml.ws/types/)
dbwsproxy.add.soap.element.namespace: ns1, http://pas.au.xml.ws/types/
Attribute 0: http://pas.au.xml.ws/types/: xmlns:ns1, http://pas.au.xml.ws/types/
dbwsproxy.element.node.child.0: 1, null
dbwsproxy.lookup.map: ns1, http://pas.au.xml.ws/types/
createElement(ns1:xmlInput,null,http://pas.au.xml.ws/types/)
dbwsproxy.element.node.child.0: 1, null
createElement(pas,null,null)
dbwsproxy.text.node.child.0: 3, pas&
request:
<ns1:testXMLElement xmlns:ns1="http://pas.au.xml.ws/types/">
<ns1:xmlInput>
<pas>pas&amp;</pas>
</ns1:xmlInput>
</ns1:testXMLElement>
response:
<ns0:testXMLResponseElement xmlns:ns0="http://pas.au.xml.ws/types/">
<ns0:result>success</ns0:result>
</ns0:testXMLResponseElement>
** Soap Response **
<ns0:testXMLResponseElement xmlns:ns0="http://pas.au.xml.ws/types/">

<ns0:result>success</ns0:result>
</ns0:testXMLResponseElement>

Result = success

PL/SQL procedure successfully completed.

No errors.
SCOTT@linux11gr2>

Coherence Extend client from Oracle 11g RDBMS

Given coherence JAR file isn't that large I have always wanted a coherence client to run from the database itself by connecting to extend proxy client. Here is how I did this and what is required on the Oracle DB side to ensure you can run java code within the database to connect to a coherence extend proxy client.

Note: This was done with Oracle 11g r2 (11.2.0.1) RDBMS. The JVM in the DB is a 1.5 JVM version. So you won't be able to use JDK 1.6 for this.

1. First we create a new schema called COHERENCE in our database. The script to create this user is as follows.

drop user coherence cascade;

create user coherence identified by coherence
default tablespace users
temporary tablespace temp;

grant dba to coherence;

execute dbms_java.grant_permission('COHERENCE','SYS:java.util.PropertyPermission','*', 'read,write');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission','getClassLoader','');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission','createClassLoader','');

execute dbms_java.grant_permission('COHERENCE','SYS:java.net.SocketPermission','*','listen,connect,resolve');

execute dbms_java.grant_permission('COHERENCE','SYS:java.util.PropertyPermission','*','read,write');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission','setFactory','');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.net.SocketPermission', 'localhost:8088', 'listen,resolve' );

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.io.FilePermission', '/tangosol-coherence-override-dev.xml', 'read');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.io.FilePermission', '/tangosol-coherence-override.xml', 'read');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.io.FilePermission', '/custom-mbeans.xml', 'read');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.net.SocketPermission', 'papicell-au.au.oracle.com:9099', 'connect,accept,resolve');

execute dbms_java.grant_permission('COHERENCE', 'SYS:java.security.SecurityPermission', 'getDomainCombiner', '' );

prompt
prompt COHERENCE user ready to roll
prompt
prompt all done..

As you can see I granted DBA to the user coherence only because I wanted to get this up and running quickly but in theory the DBA role should not be required. Also all those privileges are required in order to create a socket connection to the extend proxy client.

2. Load the following 3 JAR files into the database using the new user coherence. In order for the database to load/resolve coherence.jar it will require the log4j and sleepycat JAR files. I tried to avoid loading them as coherence outside of the database runs fine with just coherence.jar but inside the database it needed to compile/resolve the class files which meant it needed those 2 JAR files to do that. To me it's not a big issue as there quite small JAR files as well.

loadjava -u coherence/coherence -r -v -f -s -grant public log4j-1.2.15.jar

loadjava -u coherence/coherence -r -v -f -s -grant public je.jar

loadjava -u coherence/coherence -r -v -f -s -grant public coherence.jar

Note: There will be some errors resolving some of the classes but the main ones needed will resolve/compile fine so just ignore the errors. In fact a query as follow should show most of classes as VALID.

COHERENCE@linux11gr2> @check

STATUS    COUNT(*)
------- ----------
INVALID         11
VALID         3200


Java Name                                                    STATUS
------------------------------------------------------------ ----------
com/tangosol/util/internal/SunMiscCounter                    INVALID
com/tangosol/util/internal/SunMiscCounter$1                  INVALID
com/sleepycat/persist/model/ClassEnhancerTask                INVALID
org/apache/log4j/jmx/Agent                                   INVALID
com/tangosol/run/jca/CacheAdapter                            INVALID
com/tangosol/run/jca/CacheAdapter$CacheConnectionSpec        INVALID
com/tangosol/engarde/websphere/SecurityHelper                INVALID
com/tangosol/engarde/StubManagerHookup                       INVALID
com/tangosol/engarde/StubManager                             INVALID
com/tangosol/engarde/EjbRouter                               INVALID
com/tangosol/engarde/DebugStubManagerHookup                  INVALID

11 rows selected.

COHERENCE@linux11gr2> l
  1  select dbms_java.longname(object_name) "Java Name", status
  2  from user_objects
  3  where object_type like '%JAVA%'
  4* and status = 'INVALID'

3.Now we have a proxy client running on "papicell-au.au.oracle.com:9099" which is storage disabled but we have other cluster nodes which are storage enabled. In this setup the extend proxy client  is the node the database client will connect to and hence why we set it up with this privilege.

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.net.SocketPermission', 'papicell-au.au.oracle.com:9099', 'connect,accept,resolve');

4. Now we are ready to actually create a Java Stored procedure which will connect to the extend proxy client and put/get data off the cache. The code for this is as follows and must be loaded into the database.

import com.tangosol.net.CacheFactory;
import com.tangosol.net.NamedCache;

public class CoherenceClient
{
  public CoherenceClient()
  {
  }
  
  public static void getData ()
  {
    System.setProperty("tangosol.coherence.cacheconfig", "jserver:/resource/schema/COHERENCE/client-cache-config.xml");
    System.setProperty("tangosol.coherence.distributed.localstorage", "false");
    
    NamedCache cache = CacheFactory.getCache("test");

    System.out.println("Key 1 = " + cache.get(1));
    
    CacheFactory.shutdown();
  }
  
  public static void putData ()
  {
    System.setProperty("tangosol.coherence.cacheconfig", "jserver:/resource/schema/COHERENCE/client-cache-config.xml");
    System.setProperty("tangosol.coherence.distributed.localstorage", "false");
    
    NamedCache cache = CacheFactory.getCache("test");

    cache.put(1, "Pas Apicella");
    
    CacheFactory.shutdown();   
  }
} 

5. The client cache config file which needs to be loaded into the database with the Java Class is as follows. You can see it connects to "papicell-au.au.oracle.com:9099" which is our extend proxy client host:port. This file is called "client-cache-config.xml" which is loaded into the database itself.

<?xml version="1.0"?>
<?xml version="1.0"?>

<!DOCTYPE cache-config SYSTEM "cache-config.dtd">

<cache-config>
  <caching-scheme-mapping>
    <cache-mapping>
      <cache-name>*</cache-name>
      <scheme-name>remote</scheme-name>
    </cache-mapping>
  </caching-scheme-mapping>
  <caching-schemes>
    <remote-cache-scheme>
      <scheme-name>remote</scheme-name>
      <initiator-config>
        <tcp-initiator>
          <remote-addresses>
            <socket-address>
              <address>papicell-au.au.oracle.com</address>
              <port>9099</port>
              <reusable>true</reusable>
            </socket-address>
          </remote-addresses>
        </tcp-initiator>
      </initiator-config>
    </remote-cache-scheme>
  </caching-schemes>
</cache-config>

6. The Java Stored procedure was created in JDeveloper 10g given we are using JDK 1.5 that made it easier to deploy from JDeveloper itself. The project is as follows. In this setup we simply exposed the Java Stored procedure static methods as 2 PLSQL procedures, but it would make sense to use a PLSQL package here rather then stand alone procedures.

7. You will see we reference the client cache config file as follows in our Java Stored procedure which is how it's done in the Oracle JVM.

System.setProperty("tangosol.coherence.cacheconfig", 

                               "jserver:/resource/schema/COHERENCE/client-cache-config.xml");

8. Finally we are ready to invoke our Java Stored Procedure which was loaded as follows. By creating a Java Stored procedure we are making it available in SQL terms. This means any client can invoke the coherence client , meaning even a PLSQL client if it wanted to or a .NET client. You simple call the stored procedures like any other procedures in the database. Basically when we test this we will use SQL*Plus to verify it works.

JDEV Log window which shows a successful deployment.

Invoking loadjava on connection 'coherence-11gr2' with arguments:
-order -resolve -thin
Loadjava finished.
Executing SQL Statement:
CREATE OR REPLACE PROCEDURE getData AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'CoherenceClient.getData()';
Success.
Executing SQL Statement:
CREATE OR REPLACE PROCEDURE putData AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'CoherenceClient.putData()';
Success.
Publishing finished.
----  Stored procedure deployment finished.  ----

9. Now we can write a very basic SQL file as follows which will put data onto the coherence cache and then retrieve it calling our 2 stored procedures.

set serveroutput on
call dbms_java.set_output(100000);

exec putData;
exec getData;

SQL*PLus Output

COHERENCE@linux11gr2> @run

Call completed.

2010-08-04 14:15:19.750/0.077 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
configuration from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence.xml"
2010-08-04 14:15:19.755/0.082 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
overrides from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence-override-dev.xml"
2010-08-04 14:15:19.756/0.083 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Optional configuration
override "/tangosol-coherence-override.xml" is not specified
2010-08-04 14:15:19.758/0.085 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Optional configuration
override "/custom-mbeans.xml" is not specified
Oracle Coherence Version 3.5.3/465
Grid Edition: Development mode
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
2010-08-04 14:15:19.876/0.203 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded cache
configuration from "jserver:/resource/schema/COHERENCE/client-cache-config.xml"
2010-08-04 14:15:19.975/0.302 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Started:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STARTED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}
2010-08-04 14:15:19.976/0.303 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Opening Socket
connection to 10.187.80.135:9099
2010-08-04 14:15:19.979/0.306 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Connected to
10.187.80.135:9099
2010-08-04 14:15:20.013/0.340 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Stopped:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STOPPED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}

PL/SQL procedure successfully completed.

2010-08-04 14:15:20.031/0.358 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
configuration from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence.xml"
2010-08-04 14:15:20.034/0.361 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
overrides from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence-override-dev.xml"
2010-08-04 14:15:20.035/0.362 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Optional
configuration override "/tangosol-coherence-override.xml" is not specified
2010-08-04 14:15:20.036/0.363 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Optional
configuration override "/custom-mbeans.xml" is not specified
Oracle Coherence Version 3.5.3/465
Grid Edition: Development mode
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
2010-08-04 14:15:20.047/0.374 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded cache
configuration from "jserver:/resource/schema/COHERENCE/client-cache-config.xml"
2010-08-04 14:15:20.062/0.389 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Started:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STARTED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}
2010-08-04 14:15:20.063/0.390 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Opening Socket
connection to 10.187.80.135:9099
2010-08-04 14:15:20.065/0.392 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Connected to
10.187.80.135:9099
Key 1 = Pas Apicella
2010-08-04 14:15:20.082/0.409 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Stopped:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STOPPED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}

PL/SQL procedure successfully completed.

COHERENCE@linux11gr2>

In this example we turn on debug output on the database side using DBMS_JAVA package to get console messages displayed when invoking the Java Stored procedure to verify indeed coherence is being used and what cache config file we are using. The fact that we now can access the coherence cluster from the database in SQL makes this worth the effort.

I also make sure I disconnect from the cluster once done in this simple example using "CacheFactory.shutdown();" in my Java Stored Procedures, assuming that database clients just need to read/put data from the cache and then there done.