Java Stored Procedures

1
Java Stored Procedures

2
Java in the Database Objectives

• To learn more about the Java infrastructure in
  the Database
• To look at techniques for
• coding,
• deploying
• debugging Java Stored Procedures/Functions
• To look at some performance numbers
• To look at some pros and cons of this technology
• To assess its potential and provide a balanced
  perspective

3
What is a Java Stored Procedure?

• Java methods published to SQL and stored in a
  database are refered to
• as Java Stored Procedures. There are two
  available APIs to choose from,
• JDBC and SQLj.
• Java Stored Procedures are facilitated by a JVM
  in the DB server, e.g. the
• Aurora JVM (VVM) in Oracle 8i.

VVM
VVM
VVM
Shared Code and Data
4
Why Java in the Database?

• JDBC and SQLj have wide industry support and
  provide an alternative way to do DB programming.
  Some core benefits are
• Binary Portability - Write Once, Run Anywhere
• Logical Portability Business Logic in SPs can
  be easily taken out of the DB and placed in
  Business Objects e.g. using EJBs
• DB Independence SPs / SFs can be coded in
  Java. Bye Bye PL/SQL!
• Power of Java, OO SPs and SFs and a familiar
  dev environment

5
How do we code a Java Stored Procedure?

• The basic infrastructure including Java Bytecode,
  JDBC Driver, SQLj Runtime is already available in
  the Database
• There are two available options JDBC and SQLj,
  which is a new API for
• SQL programming in Java built on top of JDBC
• Java methods are exposed with CREATE FUNCTION,
  PROCEDURE,
• PACKAGE BODY statements, refered to as Call
  Specs
• Java classes are loaded into the database by the
  loadjava utility (use
• the dropjava utility to purge bytecode and
  source)

6
How do we code a Java Stored Procedure? Cont'd

• The JDBC syntax remains unchanged. SQLj
  encompasses a set of
• specifications namely
• Embedded SQL (Part 0) - Specs for embedding SQL
  statements in Java methods
• SQL Routines (Part 1) - Specs for calling Java
  static methods as stored procs/functions
• SQL Types (Part 2) - Specs for using Java classes
  as SQL user-defined data types

• There are pros and cons with using either of the
  two.
• SQLj is an ANSI standard but is not part of J2EE
• SQLj needs an additional Runtime library on top
  of JDBC
• SQLj provides more concise code and compile time
  sql validation
• SQLj provides typed resultsets, called named
  iterators

7
The SQLj Development Process
SQLj Translator
Java Compiler
SQLj Translator
Java Compiler
Load Java Utility
.sqlj
.java
.java
.class
8
The Java Stored Procs Development Process
Internals
.java
.jar
.class
loadjava
Java Sources
Java Classes
Java Resources
Java Compiler
Serialized profiles contain Text of SQL
Execution info Parameter/Result SQL, Java types
9
The Java Stored Procs Development Process Cont'd
Applications call the Java method through its
call spec. The run-time system looks up the
call-spec definition in the Oracle data
dictionary, then executes the corresponding Java
method.
Application
JDBC
Call Spec
Java Method
Application
SQLj
Data Dictionary
10
Let's write a Java Stored Function
SQLj public static int getCountSQLJ() int
count 0 try sql
SELECT (count()) into count
FROM employee
catch(SQLException excp) return
count
JDBC public static int getCountJDBC() int
count 0 Connection conn null try
conn new OracleDriver().defaultConnect
ion() String sql "SELECT
COUNT() FROM employee" Statement stmt
conn.createStatement() ResultSet
rset stmt.executeQuery(sql) while
(rset.next()) count
rset.getInt(1) rset.close()
stmt.close() catch (SQLException
e) return count
11
Lets write a Java Stored Function Contd
Write Call Spec
Package Header
Package Body
CREATE OR REPLACE package rda_java_pkg as function
get_count_sf return number end rda_java_pkg /
CREATE OR REPLACE package body rda_java_pkg
as function get_count_sf return number as
language java name Count.getCount() return
int' end rda_java_pkg /
12
Lets write a Java Stored Function Nitty Gritty

• Code and Compile Call Spec
• Compile .sqlj file
• C\SQLjgtsqlj Demo.sqlj
• Create .jar file
• C\SQLjgtjar cvf0 demo.jar Demo.class Demo.ser
• Login to DB (via SQLPlus?)
• drop java source Demo"
• drop java resource Demo_SJProfile0.ser
• call dbms_java.grant_permission('SCOTT',
• 'SYSoracle.aurora.security.JServerPermission',
  'Debug' ,null)
• Load class file
• C\SQLjgtloadjava -oci8 -resolve -force -user
  rda/rdaxuser_at_oracleee demo.jar

13
Another Java Stored Function
SQLj sql iterator count_iter (String
lastname) public static int getCursorCount()
int count 0 count_iter myIter null
try sql myIter SELECT lastname
FROM employee while(myIter.next())
count String strName
myIter.lastname() myIter.close()
catch(SQLException excp)
return count
PL/SQL function get_count_cursor_plsql_sf return
number is cursor count_cursor is
SELECT lastname FROM employee
v_return_count number 0 v_name
employee.lastnametype begin open
count_cursor loop fetch
count_cursor into v_name exit when
count_cursornotfound v_return_count
v_return_count 1 end loop --Return
the variable return v_return_count end
14
Demo with JDeveloper 3.1.1.2

• Deploy a Java Stored Procedure
• Debug a Java Stored Procedure via Remote
  Debugging
• Easily move code to application-layer from
  Database via minimal changes
• Some comparative performance numbers for JDBC,
  SQLj and PL/SQL

15
Where is the bad news ?

• While choosing Java as a stored procedure
  development alternative - one must be aware of
  some of its limitations
• Performance issues - PL/SQL appears to be
  faster, but Oracle is making investments in
  JDBC/SQLj to improve. JAccelerator native
  compilation in 8.1.7 and more coming in Oracle
  9i.
• Immaturity of Features - Oracle 8i cannot return
  a cursor (ResultSet) from a Java Stored
  Procedure/Function (DB2 can!). This should be
  available in Oracle 9i.
• Ownership - SQLj is an ANSI standard sponsored by
  IBM, Oracle, Informix, Sybase amongst others. Sun
  does NOT own the SQLj spec, thus its interest
  appears to be limited.

16
How to get started

• Download and install Oracle 8i Release 2 (8.1.6)
• http//otn.oracle.com
• Download and install JDeveloper 3.x and go
  through online demo
• http//technet.oracle.com/products/jdev/info/j
  dev/sqlj/s000.html
• Oracle 8i Java Stored Procedures Developer's
  Guide
• http//oradoc.photo.net/ora81/DOC/java.815/a6
  4686/toc.htm
• Remote Debugging Oracle 8i Java Stored Procedures
  
• http//technet.oracle.com/products/jdev/info/j
  dev/rdjsproc/s000.html
• Oracle 8i Java Stored Procedures Roadmap
• http//technet.oracle.com/tech/java/jroadmap/j
  sproc/listing.htm

17
Some information sites

• SQLj home
• http//www.sqlj.org
• Oracle 8i Java Stored Procedures Application
  Scenario's
• http//www.sys-con.com/java/archives/0412/ash
  more/index.html
• Performance related information
• http//www.trivadis.com/publikationen/d/JavaPerf.
  htm
• http//www.interealm.com/orageeks/technotes/la
  rry/languages.html

18
Summary and Thanks!

• The JDBC and SQLj are alternatives to using
  proprietary DB languages for developing stored
  procedures
• Cross platform software development is a reality
  though it comes with a performance price
• Multi-language skills could be avoided on
  development teams
• Is SQLj enterprise ready? Will it remain a
  marginal player?