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>