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>

Java Stored Procedure remotely connecting to another database

I very rarely use JavaSP but I had a request to connect to a remote database from a JavaSP. My first thought was to use a database link but the request required a direct connection to the remote database using JDBC/THIN driver. here is how I got it working.

1. Create a class as follows which contains one method to connect to a remote database.

import java.sql.Connection;

import java.sql.SQLException;

import oracle.jdbc.pool.OracleDataSource;

public class RemoteDBTest
{
public static String remoteConnection()
 throws SQLException
{
  StringBuffer sb = new StringBuffer();
  String userId = "scott";
  String password = "tiger";
  String url =
   "jdbc:oracle:thin:@acampanaro-pc2.au.oracle.com:1521:linux102";
  Connection conn = null;

  OracleDataSource ods = new OracleDataSource();
  ods.setUser(userId);
  ods.setPassword(password);
  ods.setURL(url);
  conn = ods.getConnection();

  sb.append("Auto commit = " + conn.getAutoCommit());
  conn.close();

  return sb.toString();
}
}

2. Load the class into the database and create a call specification, I used JDeveloper 10.1.3.4 as this makes light work of the task as shown by the output below. The class was loaded as user SCOTT.

Invoking loadjava on connection 'scott-linux102' with arguments:
-order -resolve -thin
Loadjava finished.
Executing SQL Statement:
CREATE OR REPLACE FUNCTION remoteConnection RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'RemoteDBTest.remoteConnection() return java.lang.String';
Success.
Publishing finished.

3. Now I need to grant some privileges to enable me to remotely connect from my database to another database using JAVA. There 2 are as follows, which must be executed as SYS.

SYS@LINUX10G> exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', 'acampanaro-pc2.au.oracle.com', 'resolve');

PL/SQL procedure successfully completed.

SYS@LINUX10G> exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', '10.187.80.13:1521', 'connect,resolve');

PL/SQL procedure successfully completed.

4. Now we can call our JavaSP and verify if we have successfully connected.

SCOTT@LINUX10G> select remoteconnection from dual;

REMOTECONNECTION
------------------------------------------------------------

Auto commit = true

What you will find is if you don't execute the required privileges then you will be told when you try to run the JavaSP that you don't have required permissions to do this. His an example of what error you may receive. Lucky for me oracle gives me the command I need to run which made this easy to setup.

SCOTT@LINUX10G> select remoteconnection from dual;
select remoteconnection from dual
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
(java.net.SocketPermission 10.187.80.13:1521 connect,resolve) has not been granted to SCOTT. The PL/SQL to grant this
is dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', '10.187.80.13:1521', 'connect,resolve' )