How to call Java stored procedures/Classes from PL/SQL

There is lot of requirements where we need to use Java like zipping the files stored in database, deleting files from server.

 Oracle provides the way to access Java stored Procedure and classes from PL/SQL, we can use according to our need

Following steps are required:

1. Create Java class according to your requirement
2. Compile and load it into database
3. Write one PL/SQL Wrapper Program to call this.

Simple Demonstration:

Requirement: To delete file from server, file name will be provided as input parameter

Steps to achieve this:

1. Write Java class to delete the file and compile it. 

 public class TestDelete {

   public static int delete ...
   public static void main (String args[]) {
      System.out.println (
         delete (args[0])
         );
    }
}
2. Load java class into database
 
C:\oracle9i\bin>loadjava -user scott/tiger -oci8 -resolve
TestDelete.class
 
3. Write PL/SQL Wrapper Program:
CREATE OR REPLACE FUNCTION fDelete (
   file IN VARCHAR2)
   RETURN NUMBER
AS LANGUAGE JAVA
   NAME 'TestDelete.delete (
            java.lang.String) 
            return int';
 
LoadJava utility: The loadjava utility (Oracle 8.1.5 and up) loads
Java source and class files into the database.When class files are
created in a conventional manner, outside the database,
loadjava is used to get them into the database.
Figure 1: Loading Java elements into Oracle

 

Here is the syntax:

loadjava {-user | -u} username/password[@database]
  [-option_name [-option_name] ...] filename [filename ]...

where option_name stands for the following syntax:

{  {andresolve | a}
 | debug
 | {definer | d}
 | {encoding | e} encoding_scheme_name
 | {force | f}
 | {grant | g} {username | role_name}[,{username | role_name}]...
 | {oci8 | o}
 | oracleresolver
 | {resolve | r}
 | {resolver | R} "resolver_spec"
 | {schema | S} schema_name
 | {synonym | s}
 | {thin | t}
 | {verbose | v} }

loadjava requires two database privileges to load java objects into your own schema: CREATE PROCEDURE and CREATE TABLE.  To load Java objects into a schema other than the currently connected user, CREATE ANY PROCEDURE and CREATE ANY TABLE privileges are required.

This example will use a simple Java program that will be compiled outside of Oracle and then loaded into the database.

 public class SimpleJava {

   public void main(String[] args) {

      System.out.println(“Here we are”);

   }

From DOS or  UNIX :

C:\oracle9i\bin>javac SimpleJava.java

C:\oracle9i\bin>loadjava -user scott/tiger SimpleJava.class

The class file is now loaded into the database and visible from the dba_objects view with an object type of JAVA CLASS.

From SQL*Plus, create the PL/SQL wrapper to invoke the newly loaded Java class:

SQL> create or replace procedure call_simplejava

  2  as language java

  3  name ‘SimpleJava.showMessage()’;

  4  /

Execute the code from SQL*Plus:

SQL> set serveroutput on;

SQL> call dbms_java.set_output(50);

Call completed.

SQL> execute call_simplejava;

Here we are

PL/SQL procedure successfully completed.

In this example, the Java class file was loaded into the database.  The Java source file can also be loaded.  But, both the source and class files cannot be loaded at the same time.

 C:\oracle9i\bin>loadjava -user scott/tiger SimpleJava.java

If loading many Java class files at one time, it is advisable to put them in a JAR file and load them into the database at one time, since the loadjava program will also load JAR files.   A JAR file is a group of Java class files lumped into one file, a format similar to TAR (on  UNIX ) and WinZip (on Windows).   The contents of a JAR file can be viewed using these popular utilities.  Java developers prefer to distribute a few JAR files rather than many individual Java class files.

Oracle8i has created two new roles to support Java security. For many Java-based operations within the database, you will not have to work with these roles. If, on the other hand, you want to interact with the operating system (to access or modify operating system files, for example), you need to be granted one of the following roles:

JAVASYSPRIV
JAVAUSERPRIV

You grant these roles as you would any other database role. For example, if I want to allow SCOTT to perform any kind of Java-related operation, I would issue this command from a SYSDBA account:

GRANT JAVASYSPRIV TO SCOTT;

If I want to place some restrictions on what the user can do with Java, I might execute this grant instead:

GRANT JAVAUSERPRIV TO SCOTT;
To access Java class methods from within Oracle, you must take the following steps:

1. Create the Java code elements. You can do this in Oracle’s JDeveloper, or in any other Java Integrated Development Environment. Load the Java class(es) into Oracle using the loadjava command-line utility or the CREATE JAVA statement.
2. Publish the Java class methods inside PL/SQL by writing wrapper programs in PL/SQL around the Java code.
3. Grant privileges as required on the PL/SQL wrapper programs and the Java class referenced by the PL/SQL wrapper.
4. Call the PL/SQL programs from any one of a number of environmentsOracle8i offers a variety of components and commands to work with Java following table summarizes these different elements:

Table 1: Oracle Components and Commands for Java
Component	Description
Aurora JVM	The Java Virtual Machine (JVM) that Oracle implemented in its database server
loadjava	An operating system command-line utility that loads your Java code elements (classes, .jar files, etc.) into the Oracle database
dropjava	An operating system command-line utility that drops your Java code elements (classes, .jar files, etc.) from the Oracle database
CREATE JAVA DROP JAVA ALTER JAVA	New DDL statements that perform some of the same tasks as loadjava and dropjava
DBMS_JAVA	A built-in package that offers a number of utilities to set options and other aspects of the JVM
DBMS_JAVA_TEST	A built-in package you can use to more easily test your JSPs
JPublisher	A utility used to build Java classes around object types and REFs defined in the Oracle database

 

Figure 9.1: Accessing JSPs from within the Oracle database

Data Type Mapping Between Java and SQL:

SQL Type	Java Class
CHAR, NCHAR, LONG, VARCHAR2, NVARCHAR2	oracle.sql.CHAR java.lang.String java.sql.Date java.sql.Time java.sql.Timestamp java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal byte, short, int, long, float, double
DATE	oracle.sql.DATE java.sql.Date java.sql.Time java.sql.Timestamp java.lang.String
NUMBER	oracle.sql.NUMBER java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal byte, short, int, long, float, double
RAW, LONG RAW	oracle.sql.RAW byte[]
ROWID	oracle.sql.CHAR oracle.sql.ROWID java.lang.String
BFILE	oracle.sql.BFILE
BLOB	oracle.sql.BLOB oracle.jdbc2.Blob
CLOB, NCLOB	oracle.sql.CLOB oracle.jdbc2.Clob
OBJECT	oracle.sql.STRUCT oracle.SqljData oracle.jdbc2.Struct
REF	oracle.sql.REF oracle.jdbc2.Ref
TABLE, VARRAY	oracle.sql.ARRAY oracle.jdbc2.Array
Any of the above SQL types	oracle.sql.CustomDatum oracle.sql.Datum