Alvin Liu

  • Home
  • About
  • Privacy Policy
  1. Main page
  2. Database
  3. Main content

Oracle Database Java Stored Procedure

2023-06-16 1481hotness 0likes 0comments

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.

相关文章

  • SQL Developer Code Templates Autocomplete

  • Remote Debug Oracle Procedure by SQL Developer

This article is licensed with Creative Commons Attribution-NonCommercial-No Derivatives 4.0 International License
Tag: Nothing
Last updated:2023-06-16

Alvin Liu

Software Developer in Toronto

Like
< Last article
Next article >

Comments

razz evil exclaim smile redface biggrin eek confused idea lol mad twisted rolleyes wink cool arrow neutral cry mrgreen drooling persevering
Cancel

COPYRIGHT © 2024 Alvin Liu. alvingoodliu@[email protected] ALL RIGHTS RESERVED.

Theme Made By Seaton Jiang