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 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 the TrimLoad.java routine into the database using the default server-side connection ( conn = DriverManager.getConnection("jdbc:default:connection:");) and then execute it by calling it from the PL/SQL TrimLobProc procedure:

1.

Open a terminal window and change to the directory where you unzipped the files.

Execute the following command to load the TrimLob java routine into the database:

                               
                                 
cd wkdir
loadjava -u hr/hr TrimLob.java     
                              
                            

 

2.

Start SQL*Plus and connect as hr/hr. To verify that the TrimLob java routine is loaded into the database, execute the following statements:

                               
set heading off

select text
from user_source
where name = 'TrimLob';

set heading on
                            

 

3.

Execute the following commands to create a table (and remove the table if it existed previously). This table is used by the stored TrimLob java routine to manipulate LOB data:

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

 

4.

Publish the Java class method by creating the TrimLobProc PL/SQL Call specification that references the Java class methods. Within the TrimLobProc PL/SQL specification, you identify the name of the Java class method and its parameters. This is called "Publishing" the Java class method.

                               
create or replace procedure TrimLobProc
  as language java
  name 'TrimLob.main(java.lang.String[])';
/
                            


5.

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 the data types enables you to transfer data between an application using Java types and a database using SQL types. You can use the following steps to map various SQL types to/from corresponding Java types:

1.

Execute the TypesTab.sql and orasqlCHAR.sql scripts. The TypesTab.sql script creates a table that holds several different Oracle data types. The orasqlCHAR.sql script creates the mapping between the oracle.sql.CHAR data type to the SQL CHAR data type.

                               
                                 
set echo on
@TypesTab
                              
                            

                               
                                 
@orasqlCHAR
                              
                            



2.

Examine the XobTypesTab.sql script. You need the CREATE DIRECTORY privilege to complete this step. Follow the directions in the script. Copy and paste the statements in the script into your SQL*Plus session. Ensure that your directory structure is valid for your setup.

Map the CLOB locator to/from java.sql.Clob by executing the ClobMap.sql script:

@ClobMap

 

3.

Examine the ResultSet.sql script.

Run the ResultSet.sql script to m ap REF Cursor to java.sql.ResultSet.

                               
                                 
                                   
@ResultSet
                                
                              
                            

 

4.

Execute the XVARRAY.sql script to create and populate a table with VARRAY column types.

                               
                                 
@XVARRAY
                              
                            

Execute the NumVarray.sql script to map VARRAY to Scalar SQL Type, Number.

                               
                                 
                                                            
                                 
                                                            
                                 
@NumVarray
                              
                            

 

Back to Topic List

Invoking Java in Database

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

1.

Execute the Workers.sql script to create and populate a WORKERS table.

                               
                                 
                                   
@Workers
                                
                              
                            

Execute the Workers.java script to create and store a Workers java class method in the database.

                               
@Workers.java
                            

 

2.

Invoke Java in the Database using ojmjava, which is an interactive command-line utility that 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.

Exit SQL*Plus and from your terminal window, execute the following commands:

                               
                                 
                                   
exit

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

Exit the OJVMJAVA utility by typing exit at the command prompt.

                               
exit
                            

 

3.

You can also invoke Java in Database through your SQL*Plus session using a PL/SQL wrapper that identifies the Java class and its parameters.

From your terminal window, start SQL*Plus, then create the PL/SQL wrapper using the code shown below:

                               
                                 
                                   
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[])';
                                
                              
                              
                                 
/

set serveroutput on
call dbms_java.set_output(50000);

call WorkerSp('621','Senior VP','650000');
                                
                              

                            

 

Back to Topic List

Illustrating an Object Type Call Spec

An object type call specification can publish public static member methods of an object type using the STATIC keyword. However, unlike any other PL/SQL packaged call specification, using the MEMBER keyword, the object type call specification can also publish non-static instance methods.Use following steps to create and test an object type call specification:

1.

Invoke Java in database through using the PL/SQL wrapper.

In SQL*plus, execute the BasicObjTyp.sql script:

                               
                                 
                                   
set echo on
@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:

1.

From your SQL*Plus session, 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

1.

From your SQL*Plus session, execute the memtest.sql script:

                               
                                 
                                   
@memtest
                                
                              
                            

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 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
 Use JavaNet Tracing

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document