15-2. Loading a Java Database Class into a Database
Problem
You want to load a Java class into a schema within your Oracle Database.
Solution #1
You can use the CREATE JAVA command to load the Java source into the database by copying and pasting the Java source into a SQL file. This is the easiest way to create a Java class and then load it into the database if you are not working directly on the database server but rather remotely using an editor or SQL*Plus. The following lines of SQL code will load the Java class that was created in Recipe 15-1 into the database using the CREATE JAVA command:
CREATE OR REPLACE JAVA SOURCE NAMED "Employees" AS
import java.sql.*;
import oracle.jdbc.*;
public class Employees {
public static void getItEmps(){
String firstName = null;
String lastName = null;
String email = null;
try {
Connection conn = DriverManager.
getConnection("jdbc:default:connection:");
String sql = "SELECT FIRST_NAME, LAST_NAME, EMAIL " +
"FROM EMPLOYEES " +
"WHERE DEPARTMENT_ID = 60";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rset = pstmt.executeQuery();
while(rset.next()) {
firstName = rset.getString(1);
lastName = rset.getString(2);
email = rset.getString(3);
System.out.println(firstName + " " + lastName + " " +
email);
}
pstmt.close();
rset.close();
} catch (SQLException ex){
System.err.println("ERROR: " + ex);
}
}
};
Next, you need to compile the code. To do so, use the ALTER JAVA CLASS <name> RESOLVE command. The following line of code compiles the Employees Java source:
ALTER JAVA CLASS "Employees" RESOLVE;
Solution #2
You can use the loadjava utility that is provided by Oracle in order to load Java code into the database. This situation works best if you are working directly on the database server and have access to the loadjava utility that is installed in the Oracle Database home. This utility is also nice to use if you already have the Java code stored in a file and do not want to copy and paste code into an editor or SQL*Plus. The following code demonstrates loading a Java source file named Employees.java using the loadjava utility:
loadjava –user dbuser Employees.java
After the command is issued, you will be prompted for the password to the user who you named using the –user option. By issuing the –resolve option, you will be loading the Java into the database and compiling at the same time. This saves you the step of issuing the ALTER JAVA CLASS <name> RESOLVE command.
How It Works
You can load Java source code into the database directly using the CREATE JAVA SOURCE command. This will load the source and make it accessible to the schema in which it was loaded. Once loaded, you can create a call specification for any of the class methods that you want to make into a stored procedure or function. The call specification maps the Java method names, parameter types, and return types to their SQL counterparts. You will learn more about creating call specifications in Recipe 15-4. We recommend compiling the source using the RESOLVE command before attempting to invoke any of its methods. However, if you do not issue the RESOLVE command, then Oracle Database will attempt to compile the Java source dynamically at runtime.
Note A class name can be a maximum of 30 characters in length. If the specified name is more than 30 characters in length, then Oracle will automatically shorten it for you and create and use a map to correlate the long name with the shortened name. You can still specify the long name in most cases, and Oracle will automatically convert that name to the shortened name for you. However, in some cases you will need to use the DBMS_JAVA.SHORTNAME('long_classname') function to map the name for you. Conversely, if you want to retrieve the long name by using its corresponding short name, you can use the DBMS_JAVA.LONGNAME('short_classname') function.
The loadjava utility, which is the tool you use to implement the second solution, uses the CREATE JAVA command to load the Java into the database. It also allows you to specify the –resolve option, which will compile the code once it has been loaded. The advantage to using loadjava is that you can load Java source files directly into the database without the need to create a separate SQL file containing the CREATE JAVA command or copy and paste the Java class into SQL*Plus. The downside is that you must have access to the loadjava binary executable that resides on the Oracle Database server. This utility can also be used to load files of type .class, .sqlj., .properties, and .ser.
If your code is unable to compile because of errors, then it will not execute if you attempt to invoke one of its methods. You must repair the error(s) and ensure that the code compiles successfully before it can be used. If your code does not compile, then you can check the USER_ERRORS table to see what issue(s) are preventing the code from compiling successfully. The USER_ERRORS table describes the current errors on all the objects that are contained within the user's schema. To learn more about querying the USER_ERRORS table, please refer to Recipe 15-16.