Some solution requirements are best addressed with Java code in the Oracle database. This article shows you use cases where Java methods in Oracle are useful, how Oracle lets you store Java methods and how they can be called from SQL or PL/SQL.
Java stored procedures in Oracle: Why do you need them?
A common reason why you would want Java stored procedures in Oracle is that the equivalent PL/SQL is not feasible, performant or economical to build. A few use cases:
- Application-specific code that’s already in use in Java and needs to be migrated to the database: it might make more sense to make use of the working Java code than to recreate it from scratch in PLSQL.
- Out-of-the-box Java functions: Java packages might have pre-built functions for the problem at hand, and the cost of building their PL/SQL equivalent is prohibitively high. This could range from simple stuff such as primality testing with BigInteger or advanced cryptographic operations with javax.crypto.
How to store and invoke Java methods in Oracle
The steps are outlined below. A working example follows in the next section.
1. Create the Java source
Write the code (java_source_code) as you normally would in Java. Give the Java source code a name (java_source_name) and create it in the database, with the syntax:
create or replace and resolve java source named
"<java_source_name>" as
<java_source_code>;
2. Publish the Java method through a call specification
To publish the Java method, create a “call specification” that maps the Java method name, parameter types and return types to their SQL counterparts. The syntax:
create or replace function <plsql_function_name>
(<plsql_parameters>)
return <plsql_return_type>
as language java
name ‘<java_source_name>.<method>
(<java_parameters>)
return <java_return_type>’;
There should be a 1:1 correspondence between the java parameters/return types and the SQL counterparts. For more on mappings and data conversion considerations, see the Oracle documentation for mappings between SQL Types and Java Types (12c).
A call specification is not a wrapper. A wrapper adds another layer of execution, whereas a call specification only publishes the existence of a Java method. So when you call the Java method via its call specification, this happens at runtime with minimal overhead.
3. Invoke the Java method via the call specification
The stored Java method can now be called using <plsql_function_name> like any regular PL/SQL function. The Java stored procedure can accept arguments, reference Java classes, and return Java result values.
Java in Oracle: Demo
The method used for this demo is BigInteger‘s nextProbablePrime, a pre-built solution that returns the first probable prime integer greater than the current value.
Before moving on to the demo, a little introduction to BigInteger: this is a custom Java class for handling very large numbers, with pre-built methods for mathematical operations. Read more about it in the Java 8 documentation. Note that the class BigInteger does not have a direct datatype mapping in PL/SQL, and so we need to convert the method’s input/output to a Java primitive type, which can be then mapped to the PL/SQL datatype NUMBER.
Let’s see in action, the steps to store and invoke a Java method in Oracle.
1. Create the Java source
Java source MathFunction with method nextProbablePrime — this is a wrapper for BigInteger.nextProbablePrime, with a switch of datatype: int from/to BigInteger.
-- Java source with method which returns the first
-- integer greater than the input that's probably prime.
create or replace and resolve java source named
"MathFunction" as
import java.math.*;
/**
* Java class to calculate math functions
*/
public class MathFunction {
/**
* Wrapper for BigInteger's nextProbablePrime
* function. Input as well as return type are
* are mapped from int to/from BigInteger.
*
* @param int n
* @return int next probable prime > n
*/
public static int nextProbablePrime(int n) {
return BigInteger.valueOf(n)
.nextProbablePrime().intValue();
}
};
/
When run:
SQL> -- Java source with method which returns the first
SQL> -- integer greater than the input that's probably prime.
SQL> create or replace and resolve java source named
2 "MathFunction" as
3 import java.math.*;
4
5 /**
6 * Java class to calculate math functions
7 */
8 public class MathFunction {
9
10 /**
11 * Wrapper for BigInteger's nextProbablePrime
12 * function. Input as well as return type are
13 * are mapped from int to/from BigInteger.
14 *
15 * @param int n
16 * @return next probable prime > n
17 */
18 public static int nextProbablePrime(int n) {
19
20 return BigInteger.valueOf(n)
21 .nextProbablePrime().intValue();
22 }
23 };
24 /
Java created.
SQL>
2. Publish the Java method through a call specification
The call specification for the source created in step 1 - MathFunction.nextProbablePrime(int) with return type int.
-- Call specification for the Java source -- MathFunction.nextProbablePrime create or replace function nextProbablePrime (n number) return number as language java name 'MathFunction.nextProbablePrime(int) return int';
When run:
SQL> -- Call specification for the Java source SQL> -- MathFunction.nextProbablePrime SQL> create or replace function nextProbablePrime 2 (n number) 3 return number 4 as language java 5 name 'MathFunction.nextProbablePrime(int) 6 return int'; 7 / Function created. SQL>
3. Invoke the Java method via the call specification
The Java stored method is now ready for use via its call specification.
Examples:
SQL> select nextProbablePrime(36) from dual;
NEXTPROBABLEPRIME(36)
---------------------
37
SQL> select nextProbablePrime(2147) from dual;
NEXTPROBABLEPRIME(2147)
-----------------------
2153
Summary
Java methods can be stored in Oracle and invoked via a call specification. This article shows you how this can be done, with an example using the Java method BigInteger.nextProbablePrime in the Oracle database.
