Oracle Database - Java Stored Procedure

1 - Oracle JServer

Application developers familiar with procedural programming styles developed business application logic using languages like PL/SQL. The business logic they developed was deployed as stored program units that run in the database as stored procedures, functions or triggers (henceforth referred to as “stored procedures”). A stored procedure is a set of SQL and PL/SQL statements grouped together as an executable unit.

They are defined and stored in the database, thereby providing efficient access to SQL data.

Because stored procedures execute in the database, they minimize the network traffic between applications and the database, increasing application and system performance.

Starting with the Oracle8i release, application developers can now use Java to develop their business logic and deploy it in the database as stored procedures, functions and triggers. Stored procedures developed in Java run in the same address space as SQL and PL/SQL, so they can seamlessly interoperate and invoke business logic that had been written using SQL or PL/SQL.

While PL/SQL shares the same datatypes and is therefore seamless with SQL, Java is an opensystem alternative to PL/SQL — an open, general purpose programming language with which you can program the server.

And Oracle8i Java implementation is fully functional. In other words, you can now use Java in all the contexts in which you traditionally used PL/SQL. Further, database stored procedures can be implemented using either standard Java or SQLJ.

SQLJ is a standard way of embedding static SQL statements in Java programs (similar to how Pro*C™ is used to embed static SQL statements in C programs). SQLJ is an excellent choice for writing stored procedures and triggers because these are typically data-or SQL-intensive and an embedded SQL language is ideal for this purpose. Java stored procedures can execute in three different runtime contexts:

  • First, you can use them to write top-level SQL functions and procedures in Java. This allows users to extend SQL with any business logic written in Java. It is callable both from within SQL DML and at the top level, just like PL/SQL.
  • Second, database triggers can also be implemented in Java. These can run in a variety of contexts related to changes in database rows. Because Oracle8i views are fully updateable, triggers can also augment views to support update, insert or drop operations. You can now write in Java all five types of triggers that Oracle supports.
  • Third, Oracle8 provided object-relational functionality, allowing users to define complex or composite datatypes in SQL. While Oracle8 enabled users to implement methods of these types either in PL/SQL or as external procedures in C or C++, Oracle8i adds the ability to implement

3 - Loading of Java Program

The database supports a variety of different forms in which Java programs can be loaded, including:

  • Java source text,
  • standard Java .class files,
  • or Java archives (.jar).

Java source loaded into the database is automatically compiled by the Java byte-code compiler hosted in the database. The Java programs loaded into the database are stored as “library units” in a database schema similar to how PL/SQL program units are stored in the database.

Each Java source file and Java class is stored in its own library unit. Java library units needed to execute a Java program can be physically located in different database schemas.

Once you have written the Java program, you need to load it into the database. This section describes the different forms used for loading Java into the database, how to load Java and from where you can load Java into the database.

3.1 - Into The Database In Three Forms

First, Java programs can be loaded into the database as Java source and compiled by a byte-code compiler hosted in the database. Second, Java source files that have been compiled outside the database can also be loaded into the database as .class files or .jar files. Finally, a Java resource file (Java data in standard portable format — for example, images) can also be loaded into the database.

Example using a binary file on the operating system level:

-- Create a directory object on the server’s file system
SQL> CREATE DIRECTORY bfile_dir AS '/home/user/com/oracle/employee';
Statement processed.
-- Then load the Java class file using the "CREATE JAVA CLASS …" DDL statement
SQL> CREATE OR REPLACE JAVA CLASS USING BFILE (bfile_dir, 'emp.class');
Statement processed.

3.2 - Into Oracle8i In Two Ways

First, a new DDL command of the form “CREATE JAVA… ” can be issued from SQL*Plus to load Java source, binaries, or resource files into the database. The Oracle8i database accepts two input sources for Java: from a binary file on the operating system level or from a LOB column in the database. The name of the library unit for each Java class loaded into the database is derived from the name of the class that it contains. For example, if the fully qualified name of a Java class is com.oracle.employee.emp, the corresponding library unit name in the database will be com/oracle/employee/emp.

Example using a LOB column in the database:

  1. Load the Java emp.class file into the LOB column of a table in the database.
  2. Create the Java class from the LOB column in the table
SQL> CREATE JAVA CLASS RESOLVER USING BLOB
>(SELECT ALOB FROM LOB_TABLE WHERE ID=123);
Statement processed.

3.3 - Using LOADJAVA

Using LOADJAVA to load Java program units into the database:

loadjava -user scott/tiger@oudelsrv-1:5521:ORCL -r '(("employee.*" SCOTT) (* PUBLIC))' emp.class

You can load Java over the network: Because the LOADJAVA utility uses Oracle’s Java Database Connectivity (JDBC) drivers to communicate with the database and load Java into the database, Java program units can be loaded into the database over the network. By default, the LOADJAVA utility uses the Thin JDBC driver. The user also has the option to specify the JDBC/Oracle® Call Interface driver.

4 - Resolution of Java Class

Similarly to a client execution environment, Java classes need to be resolved before they can beexecuted. Because Java classes in the database are stored as library units, a Java class library unit is resolved when all of its external references to Java names are bound.

Analogous to the CLASSPATH, the RESOLVER is used by the database to resolve/link all the classes needed for server side execution.

The RESOLVER provides a search path, but instead of using file system directories, SQL schemas are specified. When resolution searches for a binary corresponding to a Java full name, it searches that list of schemas in order until a library unit matching that name is found. It also builds the depends-on list for the library units containing each class. The depends-on list has the same purpose here as for PL/SQL: As long as the referenced library units are unchanged, then the Java program is, in effect, “pre-linked” and ready to run. Here is the syntax for a RESOLVER:

RESOLVER ((<match string> <schema name>) ...)

The <match string> is either a Java name or a wildcard name that can appear in a Java language import statement, or the single wildcard '*' that matches any name. The <schema name> specifies a schema to be searched for a corresponding “library unit.” For example:

RESOLVER (
          (“some.particular.class” FRED)
          (“com.oracle.employee.*” SCOTT)
          (* PUBLIC)
          )

5 - Writing Java stored programs

Writing Java stored programs in Oracle8i can be broken into four steps:

  • write your Java code,
  • load and resolve it,
  • publish the code
  • and then run it.

6 - Publish you Java Code

Once the Java program is loaded into the database, you need to register it with SQL. To register the Java program with SQL you need only expose the top-level Java entry point, so that it is callable from SQL.

What does that mean?

Consider a database trigger that is implemented in Java. The Java trigger may have several classes and method calls. Only one of these is initially called from SQL when the DML statement is executed. Subsequent Java-to-Java calls happen within the Java VM. Java classes and methods are not automatically published to the data dictionary. Many Java methods are called only from other Java classes. The reason for publishing Java to SQL, therefore, is to register this top-level Java entry point — that is, the Java class or method that SQL initially calls with SQL.

6.1 - Issues

Three issues need to be addressed before publishing Java to SQL:

6.1.1 - Mapping SQL types To Java Types

When Java programs are invoked from SQL or PL/SQL, data is passed to and from SQL to Java in parameters and results. Because Java and SQL support different datatypes, they need to be mapped from SQL to Java or from Java to SQL during a stored program invocation.

Java native types include BYTE, SHORT, INT, FLOAT and DOUBLE while SQL native types include NUMBER, CHAR, VARCHAR2, DATE.

When a Java stored program is published to SQL, you have to specify how the parameters and return values are mapped between SQL and Java. You can map SQL native types directly to Java native types but there are two problems with doing so:

  • First, you may lose information in some applications when SQL native types are mapped to Java native types. For example, mapping a SQL NUMBER to a Java “int” could result in loss of information in financial applications that use very large numbers.
  • Second, SQL data can have NULL values. Scalar types in Java can not be NULL. When SQL NULL data are passed to parameters of Java scalar types, a NULL CONSTRAINT exception is raised before the call to Java is attempted.

You can handle these problems in two ways:

  • To address the SQL NULL issue, you can use the Java classes corresponding to the Java scalar types.

For example, you can map the SQL NUMBER type to the java.lang.Integer class. SQL NULL values can now be passed to Java because Java “null” represents SQL NULL. However, using these Java classes still does not address the possibility of loss of information.

  • To address both problems mentioned above, Oracle8i provides an Oracle.SQL Java package

It provides Java wrappers for native SQL types. These classes are designed to hold SQL data in SQL format (in byte arrays) rather than converting them to Java native format. Every primitive SQL type has a corresponding class definition in the oracle.sql package. These classes provide conversions from SQL format to primitive Java types when appropriate. Because data is kept in SQL native format, there is no loss of information and it can also store NULL values. In operations that involve moving SQL data from one table to another, using the oracle.sql package also proves to be very efficient because no conversion occurs from SQL native types to Java native types. For example, the oracle.sql.NUMBER class can be used to pass a SQL number to Java programs.

6.1.2 - Mapping SQL Parameter Modes To Java Parameter Modes

SQL supports parameter modes IN, OUT and IN OUT whereas Java methods only support the IN parameter mode. In Java, SQL OUT and IN OUT parameter modes are mapped to Java parameter types by using an array of length one and getting and setting its single element as if it were an IN OUT parameter.

6.1.3 - Privileges Under Which The Java Programs Will Execute

In Oracle 7™ and Oracle8, PL/SQL stored programs execute with “definer’s-privileges.” Such PL/SQL programs bind early to the tables that they name. A definer’s-privileges program executes at the defining site, in the definer’s schema, with the definer's visibility and permissions for accessing SQL names. The caller of a definer’sprivileges program class must have EXECUTE privilege on that class. This requires customers to load all of the (PL/SQL) code for an application into every schema where it will be used, in order to bind early to tables. This unnecessarily stores multiple copies of the program and requires extra compilations. Oracle8i supports a new privilege mechanism called “invoker’s-privileges” that Java programs can use. This privilege mechanism allows late binding to tables using the visibility and authorization of the caller of the procedure. If an invoker’s-privilege program executes a SQL statement that queries a table T, it will access the table T in A’s schema when user A invokes the program and will access the table T in B’s schema when user B invokes the program. When Java programs are invoked from SQL or PL/SQL, they can be declared to execute with definer’s privileges or with invoker’s privileges. However, when Java programs invoke other Java programs, they always execute with invoker’s privileges.

6.2 - Publishing

After you address the preceding three issues, you can publish a Java method to SQL using a CALL-spec. A CALL-spec is a PL/SQL subprogram spec that is annotated to indicate that a Java method implements the subprogram. A CALL-spec is similar to a PL/SQL external-procedure declaration and declares a SQL name and parameters and result types for a method that is implemented in Java. The CALL-spec can either be written by application developers to control the mapping between SQL and Java names and types or be generated by tools that automate the mapping of Java and SQL type mapping. The CALL-spec provides the following information about the program it describes:

  • The language in which the designated subprogram is implemented, here “JAVA”
  • The Java full name of the method, here “com.oracle.employee.emp.sal_grade” or “com.oracle.employee.emp.approve_raise”
  • The mapping of SQL types to Java types of parameters and result
  • The parameter modes
  • Privileges with which the Java program will execute. This is optional. If not specified, the Java program executes with the definer’s privileges
CREATE OR REPLACE FUNCTION SGRADE (SAL IN NUMBER) RETURN
VARCHAR2 IS LANGUAGE JAVA NAME 'com.gerardnico.emp.sal_grade (float) return java.lang.String';
CREATE OR REPLACE
PROCEDURE APPRAISE (SAL IN NUMBER, RAISE IN NUMBER, NSAL OUT NUMBER) AUTHID CURRENT_USER IS LANGUAGE 
JAVA NAME 'com.gerardnico.approve_raise(float, float, float[])';

With a VARCHAR2:

CREATE OR REPLACE FUNCTION hellocmd(cmd IN VARCHAR2)
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'helloWorld.fun(java.lang.String) return java.lang.String';

Datatype Mapping

Java Oracle
java.lang.String varchar2(255)
int number(20,0)
long number(20,0)
float number(20,5)
double number(30,0)
byte number(10,0)
char char(1)
short number(10,0)
boolean number(1,0)
java.util.Date date
java.io.Serializable blob

7 - Run the java code

Java can be called from SQL and PL/SQL in a variety of contexts:

  • traditional database stored functions and procedures,
  • database triggers,
  • object type methods,
  • or from within a PL/SQL package or subprogram (procedure or function).

SQL can invoke Java in two different ways:

7.1 - Using the "CALL" Statement

A new SQL statement, the “CALL” statement, is introduced in Oracle8i. It begins with the keyword “CALL”. It is similar in effect to the EXECUTE command of SQL*PLUS. However, it does not expand to a BEGIN…END. Using the “CALL” statement, you can invoke a Java stored program as a top-level procedure.The “CALL” statement invokes the CALL-spec that is used to publish the Java methods to SQL. The “CALL” statement can also be used with functions. Here is an example of how to use the CALL statement.

Note that SGRADE and APPRAISE were the CALL-specs used to publish the Java methods.

Calling a Java Stored Procedure from SQL as a top-level procedure using the “CALL” statement:

SQL> VARIABLE NEWSAL NUMBER;
SQL> VARIABLE CURSAL NUMBER;
SQL> VARIABLE RAISE NUMBER;
SQL> VARIABLE GRADE VARCHAR2(4);
SQL>
SQL> EXECUTE :CURSAL := 50000;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXECUTE :RAISE := 5000;
 
PL/SQL PROCEDURE successfully completed.
 
SQL>
SQL> CALL APPRAISE(:CURSAL, :RAISE, :NEWSAL);
 
Call completed.
 
SQL>
SQL> PRINT NEWSAL;
 
    NEWSAL
----------
     55000
 
SQL>
SQL> CALL SGRADE(:CURSAL) INTO :GRADE;
 
Call completed.
 
SQL>
SQL> PRINT GRADE;
 
GRAD
----
SMTS

7.2 - Using a SQL Query

Just as you can employ user-defined PL/SQL functions with SQL query statements, you can also use Java stored functions to manipulate the results returned from a SQL query. Again, from SQL, you need to invoke the CALL-spec that was used to publish the Java method to SQL. The following example shows how to use a Java method in a SQL query statement.

SQL> SELECT SGRADE(:CURSAL) FROM dual;
 
SGRADE(:CURSAL)
------------------------------------------
 
SMTS

7.3 - Using a PL/SQL block

Like SQL, PL/SQL can also invoke Java stored procedures and functions. PL/SQL can call Java stored procedures directly. Let us look at how this mechanism works. Because the Java stored program is wrapped with a PL/SQL proxy call descriptor, the calling PL/SQL program can simply invoke the PL/SQL CALL-spec. The syntax to call the proxy is essentially identical to calling another PL/SQL program. When the proxy subprogram is invoked, the Java VM automatically invokes the Java method. Here is an example of calling a Java method directly from PL/SQL:

Calling a Java Stored Procedure from PL/SQL directly:

DECLARE
newsal NUMBER(7,2);
cursal NUMBER(7,2);
RAISE NUMBER(7,2);
BEGIN
…
APPRAISE(CURSAL, RAISE, NEWSAL);END;

8 - Support

8.1 - AUTHID must specify CURRENT_USER or DEFINER

Oracle Database - AUTHID:

CREATE OR REPLACE PROCEDURE APPRAISE (SAL IN NUMBER, RAISE IN NUMBER, NSAL OUT NUMBER) AUTHID INVOKER IS LANGUAGE 
JAVA NAME 'test.emp.approve_raise(float, float, float[])';
Error(2,1): PLS-00160: AUTHID must specify CURRENT_USER or DEFINER

Solution:

CREATE OR REPLACE PROCEDURE APPRAISE (SAL IN NUMBER, RAISE IN NUMBER, NSAL OUT NUMBER) AUTHID CURRENT_USER IS LANGUAGE
JAVA NAME 'test.emp.approve_raise(float, float, float[])';

9 - Documentation / Reference

db/oracle/stored_procedure_java.txt · Last modified: 2017/09/13 16:15 by gerardnico