Calling Java from PL/SQL

You can call Java stored procedures from any PL/SQL block, subprogram, or package. For example, assume that the executable for the following Java class is stored in the RDBMS:

import java.sql.*;
import oracle.jdbc.driver.*;

public class Adjuster {
  public static void raiseSalary (int empNo, float percent)
  throws SQLException { 
    Connection conn = new OracleDriver().defaultConnection();
    String sql = "UPDATE emp SET sal = sal * ? WHERE empno = ?";
    try {
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setFloat(1, (1 + percent / 100));
      pstmt.setInt(2, empNo);
      pstmt.executeUpdate();
      pstmt.close();
    } catch (SQLException e) {System.err.println(e.getMessage());}
  }
}

The class Adjuster has one method, which raises the salary of an employee by a given percentage. Because raiseSalary is a void method, you publish it as a procedure, as follows:

CREATE OR REPLACE PROCEDURE raise_salary (empno NUMBER, pct NUMBER)
AS LANGUAGE JAVA
NAME 'Adjuster.raiseSalary(int, float)';

In the following example, you call the procedure raise_salary from an anonymous PL/SQL block:

DECLARE
   emp_id  NUMBER;
   percent NUMBER;
BEGIN
   -- get values for emp_id and percent
   raise_salary(emp_id, percent);
   ...
END;

In the next example, you call the function row_count (defined in "Example 3") from a stand-alone PL/SQL stored procedure:

CREATE PROCEDURE calc_bonus (emp_id NUMBER, bonus OUT NUMBER) AS
  emp_count NUMBER;
  ...
BEGIN
  emp_count := row_count('emp');
  ...
END;

In the final example, you call the raise_sal method of object type Employee (defined in "Examples") from an anonymous PL/SQL block:

DECLARE
  emp_id NUMBER(4);
  v emp_type;
BEGIN
  -- assign value to emp_id
  SELECT VALUE(e) INTO v FROM emps e WHERE empno = emp_id;
  v.raise_sal(500);
  UPDATE emps e SET e = v WHERE empno = emp_id;
  ...
END;

---