Summary of Oracle Java Stored Procedure

 

1. Overview of Oracle Java Stored Procedure

Java is an object-oriented programming language efficient for application-level programs. Oracle provides all types of JDBC drivers and enhances database access from Java applications. Java Stored Procedures are portable and secure in terms of access control, and allow non-Java and legacy applications to transparently invoke Java.

Stored procedures are Java methods published to SQL and stored in the database for general use. To publish Java methods, you write call specifications, which map Java method names, parameter types, and return types to their SQL counterparts.

When called by client applications, a stored procedure can accept arguments, reference Java classes, and return Java result values. Figure 1-1 shows a stored procedure being called by various applications.

 

 

2. Java Stored Procedure Configuration

To configure the database to run Java stored procedures, you must decide on which of the following modes the database should run:

■ Dedicated server mode

You must configure the database and clients in dedicated server mode using Oracle Net Services connections.

■ Shared server mode

You must configure the server for shared server mode with the DISPATCHERS parameter.

Java, SQL, or PL/SQL clients, which run Java stored procedures on the server, connect to the database over an Oracle Net Services connection.

3. Java Stored Procedures Steps

You can run Java stored procedures in the same way as PL/SQL stored procedures. Normally, a call to a Java stored procedure is a result of database manipulation, because it is usually the result of a trigger or SQL DML call. To call a Java stored procedure, you must publish it through a call specification.

Before you can call Java stored procedures, you must load them into the Oracle Database instance and publish them to SQL. Loading and publishing are separate tasks. Many Java classes, which are referenced only by other Java classes, are never published.

To load Java stored procedures automatically, you can use the loadjava command-line utility. It loads Java source, class, and resource files into a system-generated database table, and then uses the SQL CREATE JAVA {SOURCE | CLASS | RESOURCE} statement to load the Java files into the Oracle Database instance. You can upload Java files from file systems, popular Java IDEs, intranets, or the Internet.

The following steps are involved in creating, loading, and calling Java stored procedures:

 

Step 1: Create or Reuse the Java Classes

import java.math.BigDecimal;

 

public class Paymaster {

                public static BigDecimal wages(BigDecimal  sal, BigDecimal comm) throws java.sql.SQLException {

                                BigDecimal pay = sal;

                                if (comm != null)

                                                pay = pay.add(comm);

                                return pay;

                }

}

 

Save the class as Oscar.java. Using a Java compiler(For Oracle 11g, the version of Java should be 1.5), compile the .java file on your client system, as follows:

javac Oscar.java

The compiler outputs a Java binary file, in this case, Oscar.class.

In a call specification, the corresponding SQL and Java parameters and function results must have compatible data types. Table 1–1 lists the legal data type mappings. Oracle Database converts between the SQL types and Java classes automatically.

Table 1–1 Legal Data Type Mappings SQL Type Java Class

CHAR, LONG, VARCHAR2   oracle.sql.CHAR

java.lang.String

java.sql.Date

java.sql.Time

java.sql.Timestamp

java.lang.Byte

java.lang.Short

java.lang.Integer

java.lang.Long

java.lang.Float

java.lang.Double

java.math.BigDecimal

byte, short, int, long, float, double

DATE                 oracle.sql.DATE

java.sql.Date

java.sql.Time

java.sql.Timestamp

java.lang.String

NUMBER               oracle.sql.NUMBER

java.lang.Byte

java.lang.Short

java.lang.Integer

java.lang.Long

java.lang.Float

java.lang.Double

java.math.BigDecimal

byte, short, int, long, float, double

OPAQUE               oracle.sql.OPAQUE

RAW, LONG RAW         oracle.sql.RAW

byte[]

ROWID                oracle.sql.CHAR

oracle.sql.ROWID

java.lang.String

BFILE                oracle.sql.BFILE

BLOB                 oracle.sql.BLOB

oracle.jdbc2.Blob

CLOB, NCLOB oracle.sql.CLOB

oracle.jdbc2.Clob

OBJECT               oracle.sql.STRUCT

Object types                            java.sql.Struct

java.sql.SqlData

oracle.sql.ORAData

REF                  oracle.sql.REF

Reference types                       java.sql.Ref

oracle.sql.ORAData

TABLE, VARRAY         oracle.sql.ARRAY

Nested table types                                  

and VARRAY types oracle.sql.ORAData

java.sql.Array

any of the preceding SQL   oracle.sql.CustomDatum

types                                        oracle.sql.Datum

 

Step 2: Load and Resolve the Java Classes

Using the loadjava utility, you can load Java source, class, and resource files into an Oracle Database instance, where they are stored as Java schema objects. You can run loadjava from the command line or from an application, and you can specify several options including a resolver.

Step 3: Publish the Java Classes

 

Step 4: Call the Stored Procedures

 

 

More introductions could be found in Oracle Database Java Developer’s Guide.pdf