Java Stored Procedures and
Functions are Java methods stored in the
database. They are published to the SQL using call
specifications which map the method names, parameters
and return types to the SQL Context.
An Overview of Java Stored Procedure
/ Function Development
1. Write the procedure in
Java using your favourite editor. I prefer KAWA
since it is very flexible and does not have all
the clutter of GUI designers.
2. Oracle provides a loadjava
utility that can be used to Load Java Classes into
the database.
3. Write Call Specs so that
these Java Procedures are exposed to the SQL Context.
In this step we will write a call spec to the top
level entry point of Java Method.
4. Call Java Stored Procedures
/ Funcations as if they were PL/SQL Stored Procedures.
Lets start by creating a simple
procedure that will print hello world. This example
is very basic and will only introduce the concept
of java stored proc creation in the database.
Create Procedure and save as Dba.java
Step 1: Open Notepad, and
create a Class named Dba with a single method in
the class called printHello. The method printHello
will take no arguments and print a string "Hello
World". Here is the code for the procedure.
JaVa is Case SeNsItIvE, so be
careful.
// Dba.java
// Save the file with the same name as the public
class
import java.util.*;
public class Dba {
public static void printHello ()
{
String prtString="Hello World";
System.out.println(prtString);
}
}
Load the Class Dba
Oracle 8i comes with a handy utility
called loadjava that loads classes into the database.
Load the java source file you just created, using
the loadjava utility. I saved my file as C:\java\Dba.java,
change the following code to your environment. NOTE:
file name should exactly match the class name
C:\ cd java
C:\ loadjava -u scott/tiger -resolve C:\java\Dba.java
Java source code can be
loaded into the database to let the database handle
the compilation or a pre compiled java class can
be loaded using loadjava. In the above case the
java source code is being loaded.
Publish in Oracle Data Dictionary
Publishing a java method
is nothing but declaring a function or a procedure
in PL/SQL using Create function or create procedure
based on the return value of the method. If the
method returns a value, declare it as a function
or else it can be declared as a procedure. Log into
sql plus as scott/tiger and
SQL>CREATE OR REPLACE
PROCEDURE printHello
AS LANGUAGE JAVA
NAME 'Dba.printHello()';
/
Execute the Java
Procedure
After you load and
publish the stored procedure, you are ready to call
it. Since we are using a System.out. and System.err
print to the trace files by default. so, use a simple
work by calling the dbms_java.set_output();