This tutorial shows you how to develop and run Java programs in the Database.
Time to Complete
Approximately 30 minutes
This tutorial covers the following topics:
|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|
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.
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
Before you perform this tutorial, you should:
Install Oracle Database 11g
Download and unzip the java.zip file into your working directory (i.e.wkdir)
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:
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
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
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); /
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)'; /
Execute the procedure TrimLobProc using the following commands:
set serveroutput on call dbms_java.set_output(50000); call TrimLobProc();
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:
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
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:
Examine the ResultSet.sql script.
Run the ResultSet.sql script to m ap REF Cursor to java.sql.ResultSet.
Execute the XVARRAY.sql script to create and populate a table with VARRAY column types.
Execute the NumVarray.sql script to map VARRAY to Scalar SQL Type, Number.
Perform the following steps to invoke a Java application using ojmjava or PL/SQL wrapper:
Execute the Workers.sql script to create and populate a WORKERS table.
Execute the Workers.java script to create and store a Workers java class method in the database.
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.
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');
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:
Invoke Java in database through using the PL/SQL wrapper.
In SQL*plus, execute the BasicObjTyp.sql script:
set echo on @BasicObjTyp
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 SQL*Plus session, execute the JITDemo.sql script:
Notice the timing ratio between interpreted and compiled code
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 SQL*Plus session, execute the memtest.sql script:
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|