Database Architecture Manager (NYC) Next Step Systems US-NY-New York Post A Job | Post A Resume

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();

SQL>set SERVEROUPUT ON;
SQL>CALL dbms_java.set_output(1000);
SQL>CALL printHello();

IF you have any questions please post them in the java forum.

DBAsupport.com Home Page