Extending PL/SQL with Java Libraries - Building Internal Server Java Functions (Page 5 of 5 )
You build an internal server Java function by building a Java class file that will use the server-side internal or JDBC thick connection. As qualified earlier in the chapter, the JDBC thick connection depends on Oracle Call Interface (OCI) libraries. All OCI libraries are directly accessible from your Java class file when you’ve loaded it into the Oracle JVM.
Java internal or server-side class files are built and accessed by a three-step process. You use Java to build and compile the class file. Then, you use the Oracle loadjava utility to load the compiled class file into the server. Once built and loaded into the server, you build a PL/SQL wrapper to the Java class library.
The following assumes you have the correct CLASSPATH and PATH to use Java. If you are unable to compile or test the Java programs, it’s possible your environment is configured incorrectly. As mentioned earlier, you should use Appendix D to ensure you have correctly configured your environment.
The example builds a Java class library with two methods. These methods are overloaded, which means they have different signatures or formal parameter lists. They each return a variable length character array or Java string. Both of the overloaded methods will map to two overloaded PL/SQL functions that return VARCHAR2 native Oracle data types. The code for HelloWorld2.java follows:
Why Would I Use This? We use internal server Java functions for the same reasons we write PL/SQL functions, which is to process and return a result that does not involve Data Manipulation Language (DML) commands. Java functions have the ability to mirror PL/SQL functions and call external libraries to leverage Java Archive Repository (JAR) files. This feature is very effective when we have an application written in Java and want to enable the development team to write their server-side code in the same language. We have found that enabling them to stay in Java minimizes errors. |
-- Available online as part of HelloWorld2.java file.
// Oracle class imports.
import oracle.jdbc.driver.*;
// Class definition.
public class HelloWorld2
{
// --------------------------------------/
// The Hello method.
public static String hello()
{
// Call overloaded method with a null String.
return "Hello World.";
} // End of hello() method.
// --------------------------------------/ // The hello method.
public static String hello(String name)
{
// Call overloaded method with a null String.
return "Hello " + name + ".";
} // End of hello() method.
// --------------------------------------/
// Static main to test instance outside of Oracle.
public static void main(String args[])
{
// Print the return message to console.
System.out.println(HelloWorld2.hello());
// Print the return message to console.
System.out.println(HelloWorld2.hello("Larry"));
} // End of static main.
// --------------------------------------/
} // End of HelloWorld2 class.
The program does the following:
- It defines a single class with two Hello() class methods. The methods are overloaded, which means they have different signatures or formal parameter lists. They are both static methods because only static methods can be referenced by a PL/SQL wrapper package. Static methods do not require an instance of the class to run. They function much like a function in C or a PL/SQL stored package function.
- It defines a main() method, which you can use to test the program before loading it into the Oracle database instance. The main() method will be ignored when the class file is loaded into the Oracle instance. In the main method, both static Hello() and Hello(String name) methods are called and the result passed as an actual parameter to the console.
As a rule, you want to remove testing components like the main() method before loading them into the database. If they are left, they have no effect on the stored Java class library.
Use the following syntax to test the program with the Java utility:
java HelloWorld2
The following output will be displayed to the console:
Hello World.
Hello Larry.
If you have not built the PLSQL schema, please run the create_user.sql script now. When you have the PLSQL schema built, compile it with the javac utility, as covered earlier in the chapter. Once compiled you’ll load it into the Oracle JVM with the loadjava utility as follows:
loadjava -r -f -o -user plsql/plsql HelloWorld2.class
NOTE
On the Microsoft platform, you may get a message that states “The procedure entry point kpuhhalo could not be located in the dynamic link library OCI.dll.” If you receive this error, it means you don’t have %ORACLE_HOME\bin% in your PATH environment variable.
The loadjava utility command loads the Java HelloWorld2 class file into the Oracle JVM under the PLSQL schema. After loading the Java class file into the database, you’ll need to build a PL/SQL wrapper to use it. The following HelloWorld2.sql script builds the package and package body as a wrapper to the Java class library:
-- Available online as part of HelloWorld2.sql file.
-- Create a PL/SQL wrapper package to a Java class file.
CREATE OR REPLACE PACKAGE hello_world2 AS
-- Define a null argument function.
FUNCTION hello
RETURN VARCHAR2;
-- Define a one argument function.
FUNCTION hello
( who VARCHAR2 )
RETURN VARCHAR2;
END hello_world2;
/
-- Create a PL/SQL wrapper package to a Java class file.
CREATE OR REPLACE PACKAGE BODY hello_world2 AS
-- Define a null argument function.
FUNCTION hello
RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'HelloWorld2.Hello() return String';
-- Define a null argument function.
FUNCTION hello
( who VARCHAR2 )
RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'HelloWorld2.Hello(java.lang.String) return String';
END hello_world2;
/
The script does the following:
- It creates a package with two overloaded Hello functions that return VARCHAR2 data types. One is a null argument signature and the other has one formal parameter.
- It creates a package body with two overloaded Hello functions that implement a stored Java class file. The PL/SQL NAME keyword provides a reference to the stored Java class file and the return value. You must fully qualify formal parameters by using the complete package path to the defined class, like the java.lang.String reference. The return type can be shortened to String because Oracle understands it as the external data type.
You can verify all components are present to test by querying the user_ objects view with the following:
-- Available online as part of HelloWorld2.sql file.
SELECT object_name
, object_type
, status
FROM user_objects
WHERE object_name IN ('HelloWorld2','HELLO_WORLD2');
The script should output the following results:
-- This output is generated from the online HelloWorld2.sql file.
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------ ------
HELLO_WORLD2 PACKAGE VALID HELLO_WORLD2 PACKAGE BODY VALID HelloWorld2 JAVA CLASS VALID
If you did not get the same output, you’ll need to see what step you may have skipped. Please do that before attempting to proceed. If you did get the same output, you can now test the Java class library in SQL and PL/SQL. You can test it in SQL with a query or in PL/SQL with the DBMS_OUTPUT.PUT_LINE statement. The following illustrates a SQL query of the wrapper, which uses the internal Java class file:
SELECT hello_world2.hello('Paul McCartney') FROM dual;
The query will return the following results:
HELLO_WORLD2.HELLO('PAULMCCARTNEY')
-----------------------------------
Hello Paul McCartney.
You have now covered how to build Oracle database instance-stored Java class files that map methods to functions. The next section will examine how you build components to deliver procedure behavior.
/ 6 
