Oracle Database Java Stored Procedure

Oracle database has an user JVM to run Java program on the database server, which is much powerful than normal stored procedure. It can bind with standard stored procedure interface to integrate with other database functions. I will show a quick example here, and full document can be found at Oracle official website: https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdev/lot.html#List-of-Tables

1. Create Java program with your convenience IDE, compile and connect to database with JDBC to test funcitons.

import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import oracle.jdbc.*;

public class TestJavaProgram {
    public static void main(String[] args) throws SQLException {
        //Test from IDE
        modifyTable("Alvin", 18);
    }

    public static void modifyTable(String name, int age) throws SQLException {

// Use commented code to connect to database with your Java IDE

//        String jdbcUrl = "jdbc:oracle:thin:@localhost:1521/orcl";
        Connection conn = DriverManager.getConnection("jdbc:default:connection:");
//        try (Connection conn = DriverManager.getConnection(jdbcUrl, "user", "password")) {
        try {
            
            //Query
            String sql = "SELECT COUNT(*) FROM students where name=? and age=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setString(2, age);
            ResultSet rs = pstmt.executeQuery();
            rs.close();
            pstmt.close();

            //Batch query
            Map<String, Integer> studentMap = new HashMap<String, Integer>();

            sql = "SELECT name, age from students";
            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                studentMap.put(rs.getString(1),rs.getInt(2));
            }

            rs.close();
            pstmt.close();

            //Delete
            sql = "delete from students where name=?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.executeUpdate();

            //Batch update
            sql = "insert into students (name, age) values (?,?)";
            pstmt = conn.prepareStatement(sql);

            conn.setAutoCommit(false);

            pstmt.setString(1, "Alvin");
            pstmt.setInt(2, 18);
            pstmt.addBatch();
            
            pstmt.setString(1, "Liu");
            pstmt.setInt(2, 20);
            pstmt.addBatch();

            pstmt.executeBatch();

            conn.commit();
            
            //Output text
            System.out.println("DONE");
        } catch (Exception e) {
            //Output error
            StringWriter sw = new StringWriter();
            PrintWriter pw = new PrintWriter(sw);
            e.printStackTrace(pw);
            System.out.println(sw.toString());
        }
    }
}

2. Load Java code into Oracle database, use Oracle SQL Developer or loadjava command line tool. Here use SQL developer as example:

3. Define Stored Procedure interface

create or replace PROCEDURE TEST_JAVA_MODIFY_TABLE (name VARCHAR2, age number)
AS LANGUAGE JAVA
NAME 'JavaProcedureTest.modifyTable(java.lang.String, int)';

4. Call Java stored procedure

SET SERVEROUTPUT ON
CALL dbms_java.set_output(2000);
-- Above for receive Java output

exec TEST_JAVA_MODIFY_TABLE('Alvin',18);

5. Receive procedure output

DONE

PL/SQL procedure successfully completed.

6. Receive exception stacktrace

With the print stack trace section in my example code, you can output error stack to SQL output window, which is helpful to address issues.

java.lang.ArithmeticException: / by zero
	at JavaProcedureTest.modifyTable(MODIFYTABLE:111)

PL/SQL procedure successfully completed.