Running Java in the Database

Purpose

This tutorial shows you how to develop and run Java programs in the Database.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Loading and Running a Java Application in the Database
	Mapping a SQL Type to a Java Type
	Invoking Java in the Database
	Illustrating an Object Type Call Spec
	Compiling Java with JIT
	Displaying Java Memory Areas
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Stored procedures allow the exploitation of capabilities of relational database management systems (RDBMSs) to their fullest extent. Stored procedures simplify database programming, improvise performance, provides central management of data logic, and optimizes network traffic.

Java for Stored Procedures

The Java language is by design an object-oriented programming language that has a built-in security mechanism and an efficient garbage collection system. Java also has a rich and very large set of standard libraries which results in quicker and lower-cost applications development. With Java stored procedures, developers have the ability to harness all above stated powers of Java when building database applications.

The following OBE is based on the book Oracle Database Programming using Java and Web Services written by Kuassi Mensah. The description of the book is available at http://db360.blogspot.com/2006/08/oracle-database-programming-using-java_01.html and the code samples available on OTN at http://download.oracle.com/technology/tech/java/jsp/pdf/Code_depot.zip

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.	Install Oracle Database 11g
2.	Download and Install JDK 6.0 from Sun
4	Download and unzip the java.zip file into your working directory (i.e.wkdir)

Back to Topic List

Loading and Running a Java Application in the Database

Using the following steps load TrimLoad.java in the database using the default server-side connection (conn = DriverManager.getConnection("jdbc:default:connection:");) and then execute it using the procedure TrimLobProc:

Open a terminal window and change to the directory where you unzipped the files. Execute the following command:

loadjava -u hr/hr TrimLob.java

Open a terminal window and change to the directory where you unzipped the files. Execute the following command:

drop table basic_lob_table;
create table basic_log_table (x varchar2 (30), b blob, c clob);
/

Execute the procedure TrimLobProc using the following commands:

set serveroutput on
call dbms_java.set_output(50000);
call TrimLobProc();

Back to Topic List

Mapping SQL Type to Java Type

Because data types in SQL and data types in the Java programming language are not identical, mapping transfers data between an application using Java types and a database using SQL types. Using the following steps map various SQL types to/from corresponding Java types:

1.	Map oracle.sql.char to SQL CHAR. Execute the TypesTab.sql and orasqlCHAR.sql scripts: @TypesTab @orasqlCHAR
2.	Mapping CLOB locator to/from java.sql.Clob. Execute the XobTypesTab.sql and ClobMap.sql scripts: @XobTypesTab @ClobMap
3.	Mapping REF Cursor to java.sql.ResultSet. Execute the ResultSet.sql script: @ResultSet
4.	Mapping VARRAY to Scalar SQL Type, Number. Execute the XVARRAY.SQL and NumVarray.sql scripts: @XVARRAY @NumVarray

Back to Topic List

Invoking Java in Database

Perform the following steps to invoke a Java application using ojmjava or PL/SQL wrapper:

Setting up the database table. Execute the Workers.SQL script:

@Workers
exit

Invoking Java in the Database using ojmjava, which is an interactive command-line utility which can run Java classes in the database from the client machine. The ojmjava uses the JDBC connection to create a database session to pass a byte array from ojmjava client to ojmjava server where the main method of the user class is executed. The ojmjava server then passes the output back to the client. From your terminal window, execute the following commands:

ojvmjava -thin -user hr/hr
Java Workers 621 "Senior VP" 650000

Invoking Java in Database using the PL/SQL wrapper. From your terminal window, execute the following commands:

sqlplus hr/hr
create or replace procedure WorkerSp
  (wid IN varchar2, wpos in varchar2, wsal in varchar2) as
  language Java name 
  'Workers.main(java.lang.String[])';
call WorkerSp('621','Senior VP','650000');

Back to Topic List

Illustrating an Object Type Call Spec

Object Type Call Spec can publish public static member methods of an object type using the STATIC keyword. However, unlike any other PL/SQL packaged Call Spec, using the MEMBER keyword Object Type Call Spec can also publish non-static instance methods.Using following steps create and test an Object Type Call spec:

Invoking Java in Database using the PL/SQL wrapper. From your terminal window, execute the @BasicObjTyp.sql script:

@BasicObjTyp

Back to Topic List

Compiling Java with JIT

You can achieve the best execution performance for Java by using binary executables, which are obtained by compiling Java source code using JIT compilers and/or static compilers. Starting with Oracle Database 11g OJVM uses a JIT compiler which allows dynamic selection, native-compilation, and execution of most frequently used Java methods hence leading to better performance and manageability compared to NCOMP used in Oracle Database 10g. You can display the benefits of JIT compilation compared to interpretation of Java application using the following steps:

From your terminal window, execute the @JITDemo.sql script:

@JITDemo

Notice the timing ratio between interpreted and compiled code

Back to Topic List

Displaying Java Memory Areas

In OracleJVM the states of Java objects, used for specific needs, are preserved in various specific memory ares (such as Newspace, Oldspace, Runspace, Stackspace, Sessionspace) called "object memories".Perform the following step to display various Java memory areas

From your terminal window, execute the @memtest.sql script:

@memtest

Back to Topic List

Summary

In this tutorial, you learned about the following:

	Load and Run a Java Application in the Database
	Map a SQL Type to a Java Type
	Invoke Java in the Database
	Illustrate an Object Type Call Spec
	Compile Java with JIT
	Display Java Memory Areas

Back to Topic List