oracle stored procedure calls java

In Oracle database, a stored procedure is a piece of pre-compiled PL/SQL code that can be used to perform and manage database operations. However, when dealing with some specific business requirements, the stored procedure itself may not fully meet the requirements, and Java code needs to be called to solve the problem. In this article, we will introduce how to call Java in Oracle stored procedures.

1. Oracle JVM Overview

Oracle provides a feature called JVM (Java Virtual Machine) that allows Java code to be embedded in the Oracle database. After enabling the JVM feature, Oracle will be able to run Java source code and treat it as part of a stored procedure.

When enabling the Oracle JVM function, you need to install Java Development Kit (JDK) and Java Virtual Machine (JVM). Before Oracle 11g, JDK and JVM needed to be installed manually, but starting from Oracle 11g, JVM will be installed in the Oracle database by default. In addition, some environment variables need to be set to ensure that Oracle can correctly find the location of the JDK and JVM.

2. Create Java stored procedures

To create a Java stored procedure in Oracle, you need to write the corresponding Java source code. In the sample code, we created a simple Java class that contains two methods: one returns the sum of two integers, and the other returns the product of two integers.

import oracle.jdbc.*;

import java.sql.*;

public class JavaProc {

   public static int add(int a, int b) {
      return a + b;
   }

   public static int multiply(int a, int b) {
      return a * b;
   }
}

After saving the Java source code, you need to use the Java compiler to compile it into a .class file. After compilation is completed, save the .class file to a directory on the Oracle server.

Next, we need to create a Java stored procedure in the Oracle database. Create a stored procedure using the CREATE PROCEDURE statement, which includes the location and class name of the Java source code file, as shown below:

CREATE OR REPLACE PROCEDURE java_proc (
   a IN NUMBER,
   b IN NUMBER,
   op IN VARCHAR2,
   result OUT NUMBER
)
AS LANGUAGE JAVA
NAME 'JavaProc.calculate(int, int, java.lang.String, oracle.jdbc.OracleTypes.NUMBER)'
LIBRARY java_proc_jar
/

In this stored procedure, we define 4 parameters: a, b and op are Input parameters, result is the output parameter. a and b are integers, and op is a string representing the operation to be performed. result is an output parameter used to store calculation results.

In the stored procedure, we reference the location and class name of the Java class we compiled before: JavaProc.calculate. When specifying Java class and method names, you should note that Java class names and method names are case-sensitive. In addition, we also specify the data type of the Oracle return parameter: oracle.jdbc.OracleTypes.NUMBER.

Before executing the stored procedure, you also need to package the Java class into a JAR file and load it into the Oracle database. In this example, we package the Java class into a JAR file named java_proc_jar and load it into the Oracle database.

3. Calling Java Stored Procedures

To call a Java stored procedure, just use the CALL statement like any other stored procedure. In this example, we will pass two integers and an operation string and store the result in the output parameters. The sample code is as follows:

DECLARE
   a NUMBER;
   b NUMBER;
   op VARCHAR2(1);
   result NUMBER;
BEGIN
   a := 10;
   b := 20;
   op := '+';
   
   java_proc(a, b, op, result);
   
   DBMS_OUTPUT.PUT_LINE('Result: ' || result);
END;

After calling the stored procedure, we use the DBMS_OUTPUT.PUT_LINE statement to output the results to the console.

4. Summary

Oracle JVM functionality allows Java code to be called within stored procedures in order to extend the functionality of the Oracle database. Combining Java with PL/SQL provides higher level functionality and greater power. By writing Java source code and packaging it into a JAR file, and then loading it into an Oracle database, we can create Java stored procedures and call them when needed to solve specific business needs.

The above is the detailed content of oracle stored procedure calls java. For more information, please follow other related articles on the PHP Chinese website!