How does one write Java Stored Procedures?

Step 1: Create the Java Class and Methods in a file

In this example we will create a Java class "EMPL" in a file called EMPL.java to implement the get_sal method:

import java.sql.*;

public class EMPL {

  public static int get_sal(Connection conn, int empno) throws Exception
  {
     Statement s = conn.createStatement();
     ResultSet r = s.executeQuery("SELECT sal, nvl(comm,0) FROM EMP WHERE empno="+empno);
     int     sal = 0;

     if (r.next()) {
        sal = r.getInt(1) + r.getInt(2);   /* sal = :sal + :comm */
     } else {
        throw new Exception("Employee "+empno+" is invalid.");
     }

     r.close();  s.close();
     System.out.println("Salary for employee "+empno+" is "+sal);
     return sal;
  }
}

Step 2: Attach the Java Class to the Table

ALTER TABLE EMP ATTACH SOURCE "EMPL" IN '.';

Step 3: Execute the Method

To execute the get_sal method from SQL*Plus:

SELECT EMP."get_sal"(7369) FROM DUAL;

To execute this method from ODBC:

SQLExecDirect(hstm, "SELECT EMP."get_sal"(7369) FROM DUAL");