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
- 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.
Comments