Monday, July 26, 2010

Calling Java from PL/SQL inside Oracle database

How to call java classes inside PL/SQL:

Senario: In this example I have implemented Digital Signature Verfication funtion inside PL/SQL using java classes.

Step 1: Create a java class in any text editor or IDE, i have created this class in NetBeans 6.


import java.math.BigInteger;

public class OracleVerifySignature {

public static String VerifySignature(String pubkey, String datatoverify, String sigdata)
PublicKey pubver;
String status = null;
String pubkeyver = pubkey;
byte[] signature= new byte[4000];
byte[] bdtsver = new byte[4000];
signature = getBytes(sigdata);
try {
X509EncodedKeySpec pks = new X509EncodedKeySpec(getBytes(pubkeyver));
KeyFactory kf = KeyFactory.getInstance("RSA");
pubver = kf.generatePublic(pks);
pubkey = getHexString(pubver.getEncoded());
Signature sig = Signature.getInstance("SHA1withRSA");
bdtsver = datatoverify.getBytes();
status = "Signature Verified Successfully";
status = "Signature Unverified";
return status;
} catch (Exception ioe) {
return status;

public static byte[] getBytes( String str ) {
byte[] bts = new BigInteger(str, 16).toByteArray();
return bts;

public static String getHexString(byte[] b) {
String result = "";
for (int i = 0; i < b.length; i++) {
result += Integer.toString((b[i] & 0xff) + 0x100, 16).substring(1);
return result;


Step 2: Load Java class in Oracle Database using loadjava tool.

Note: Because we've yet to compile, we'll ask loadjava to do this as part of the loading process. This is achieved by specifying the -resolve attribute on the utility. We are going to load this class in scott schema.

copy this file in your \bin directory and run loadjava utility :

loadjava -u scott/tiger@orcl10g -v -resolve

You can verify the status of the compilation and load with a simple query against USER_OBJECTS. If done correctly, the status is 'VALID'.

Step 3: Publish Java classes in Oracle database.

The third step in this process is to publish the Java class. Any class that will be directly called from SQL or PL/SQL must be published. A Java class is published by creating and compiling a call specification for it. The call specification, often referred to as a call spec or even a PL/SQL wrapper, maps a Java method's parameters and return type to Oracle SQL types. Here's the call specification for the VerifySignature method.

CREATE OR REPLACE FUNCTION verify_sig (varchar2 pubkey, varchar2 datatoverify, varchar2 sigdata)
   NAME 'OracleVerifySignature.VerifySignature(java.lang.String, java.lang.String, java.lang.String)
return String';

Step 4: Calling the PL/SQL procedure.

We have developed, loaded, and published our Java classes. The final step is to execute them. By default, Java output is written to trace files. The DBMS_JAVA package, an Oracle-supplied package with utilities for managing server-side Java, has a method for redirecting output to SQL*Plus.

NOTE: This implementation is configured with JDK version 1.6, loadjava tool uses default version of JDK (1.4) shipped with Oracle database. If you want to use newer version of JDK, please follow these steps:

1. Copy from (ORACLE_HOME)\jdbc\lib\ to your JDK directory C:\Program Files\Java\jdk1.6.0\jre\lib\ext\

2. Copy from (ORACLE_HOME)\javavm\lib\ to to JDK directory C:\Program Files\Java\jdk1.6.0\jre\lib\ext\

3.  Edit loadjava.bat file located in (ORACLE_HOME)\BIN and set jreclasspath accordingly to to point JDK 1.6 files such as: rt.jar,

Oracle provides a good deal of documentation on Java stored procedure development. If desired, you can download Oracle Java Developer Guide .

1 comment:

Anonymous said...

Wow impressive