Adding Java Procedures to Oracle

1. Create your Java file (import java.sql.* and oracle.jdbc.*, then just code as usual)

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

public class ErrorTest {

//Add an employee to the database.
public static void addErr(String err_errorobjectname,
String err_dberrorcode,String err_dberrortext, String err_errormessage, String dbuser) {

System.out.println("Creating new error log entry...");

try {
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");

String sql =
"INSERT INTO err_errorlog " +
"(ERR_ERRORLOGID, ERR_ERROROBJECTNAME, ERR_DBERRORCODE, ERR_DBERRORTEXT, ERR_ERRORMESSAGE, ERR_INSERTDATE, ERR_INSERTUSER, ERR_LASTUPDATEDATE, ERR_LASTUPDATEUSER)" +
"VALUES(ERR_ERRORLOGID.NEXTVAL,?,?,?,?,SYSDATE,?,SYSDATE,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,err_errorobjectname);
pstmt.setString(2,err_dberrorcode);
pstmt.setString(3,err_dberrortext);
pstmt.setString(4,err_errormessage);
pstmt.setString(5,dbuser);
pstmt.setString(6,dbuser);
pstmt.executeUpdate();
pstmt.close();
}
catch(SQLException e) {
System.err.println("ERROR! Adding Error: "
+ e.getMessage());
}
}
}

2. Load the java file into Oracle

bash-3.00$ loadjava -u acceadmin/acceadmin -v -resolve ErrorTest.java

3. Check that your java loaded successfully (if the response says 'VALID', you're good to go)

SELECT object_name, object_type, status
FROM user_objects WHERE object_type LIKE 'JAVA%';

4. Create a wrapper for the Java (in Toad, you have to use the lightning icon to run this code)

 
CREATE OR REPLACE PROCEDURE add_error_log_entry 
(err_errorobjectname VARCHAR2,err_dberrorcode VARCHAR2,err_dberrortext VARCHAR2, err_errormessage VARCHAR2, dbuser VARCHAR2)
AS LANGUAGE JAVANAME 'ErrorTest.addErr(java.lang.String, java.lang.String, java.lang.String,java.lang.String, java.lang.String, java.lang.String)'
 (for functions that return values...)

CREATE OR REPLACE FUNCTION add_error_log_entry (...)
  RETURN NUMBER AS
  LANGUAGE JAVA NAME 'ErrorTest.addErr(java.lang.String,...) return int'
5. Now run the procedure
 
add_error_log_entry ('test_object','9999','test of java procedure', 'message', 'fakeuser')
 
Check the err_errorlog table