Building Internal Server Java Procedures

Building a procedure will follow very similar rules to building functions. PL/SQL procedures have an IN or IN and OUT mode. However, you cannot use an IN and OUT mode in PL/SQL when wrapping a Java method. If you attempt to define a package body with a procedure using IN and OUT modes, it will raise the following exception:

PLS-00235: the external type is not appropriate for the parameter

You’ll now build an IN mode procedure as a wrapper to a Java class method. When you use Java methods in the context of a procedure, you return a void type from the Java method.

Why Would I Use This? We use internal server Java procedures for the same reasons we write PL/SQL procedures, which is to process result sets that may or may not return a result and which involve Data Manipulation Language (DML) commands. Java procedures have the ability to mirror PL/SQL procedures 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.

The following Java source file supports an IN mode PL/SQL procedure:

-- Available online as part of HelloWorld3.java file.
// Oracle class imports.
import java.sql.*;
import oracle.jdbc.driver.*;
// Class definition.
public class HelloWorld3
{
  // Define the doDML() method.
  public static void doDML(String statement
                          ,String name) throws SQLException
  {
    // Define a connection for Oracle.
    Connection conn = new OracleDriver().defaultConnection();
    // Define and initialize a prepared statement.
    PreparedStatement ps = conn.prepareStatement(statement);
    // Assign the cursor return.
    ps.setString(1,name);
    ps.execute();
  } // End of the doDML() method.
  // --------------------------------------/
  // Define the doDQL() method.
  public static String doDQL(String statement) throws SQLException
  {
    // Define and initialize a local return variable.
    String result = new String();
    // Define a connection for Oracle.
    Connection conn = new OracleDriver().defaultConnection();
    // Define and initialize a prepared statement.
    PreparedStatement ps = conn.prepareStatement(statement);
    // Execute a query.
    ResultSet rs = ps.executeQuery();
    // Use a while-loop even though only one row is returned.
    while (rs.next())
    {
      // Assign the cursor return.
      result = rs.getString(1);
    }
    // Return the user name.
    return result;
  } // End of the doDQL() method.
} // End of HelloWorld3 class.

The program does the following:

■ It defines a single class with two static methods. One method returns a void and the other returns a String, which maps to a VARCHAR2 data type. The methods do the following:

• The myDML() method has a signature with two formal parameters. Both parameters are String data types. One takes the SQL statement and the second sends the data to be inserted. It creates a Connection and PreparedStatement with the first formal parameter. Then, it maps the second parameter to the SQL statement and executes the statement. This is the pattern for DML statements.
  
• The myDQL() method has a signature with one formal parameter, which is the SQL query submitted as an actual parameter. It creates a Connection and PreparedStatement with the formal parameter. It returns a String, which is the return value for the last row fetched in the while-loop.

There is no main() method in the HelloWorld3.java class file. Including a main() method to test the program externally to the database would require changing the connection to a client-side or OCI driver. You can refer to Appendix D if you wish to build a test externally to the database instance.

Most likely, you have built the PLSQL schema, but if not, you should 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 using the following:

loadjava -r -f -o -user plsql/plsql HelloWorld2.class

The loadjava utility command loads the Java HelloWorld3 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 mytable table and PL/SQL wrapper to use it.

The mytable table is built by using the following command:

-- Available online as part of HelloWorld3.sql file.
CREATE TABLE mytable (character VARCHAR2(100));

The following HelloWorld3.sql script builds the package and package body as a wrapper to the Java class library:

-- Available online as part of HelloWorld3.sql file.

-- Create a PL/SQL wrapper package to a Java class file.
CREATE OR REPLACE PACKAGE hello_world3 AS

  -- Define a single argument procedure.
  PROCEDURE doDML
  ( dml VARCHAR2
  , input VARCHAR2 );

  -- Define a single argument function.
  FUNCTION doDQL
  ( dql   VARCHAR2 )
  RETURN VARCHAR2;

END hello_world3;
/

-- Create a PL/SQL wrapper package to a Java class file.
CREATE OR REPLACE PACKAGE BODY hello_world3 AS

  -- Define a single argument procedure.
  PROCEDURE doDML
  ( dml   VARCHAR2
  , input VARCHAR2 ) IS
  LANGUAGE JAVA
  NAME 'HelloWorld3.doDML(java.lang.String,java.lang.String)'; 

 -- Define a single argument function.
  FUNCTION doDQL
  ( dql   VARCHAR2 )
  RETURN VARCHAR2 IS
  LANGUAGE JAVA
  NAME 'HelloWorld3.doDQL(java.lang.String) return String';

END hello_world3;
/

The script does the following:

• It creates a package with one procedure and one function, which do the following:
  

  • The doDML procedure takes two formal parameters that are VARCHAR2 data types.

  • The doDQL function takes one formal parameter that is a VARCHAR2 and returns a VARCHAR2 data type.

• It creates a package body with the procedure and function mapped to Java class files and methods. 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.

   

You can verify that all components are present to test by querying the user_ objects view with the following:

-- Available online as part of HelloWorld3.sql file.
SELECT   object_name
,        object_type
,        status
FROM     user_objects
WHERE    object_name IN ('HelloWorld3','HELLO_WORLD3');

The script should output the following results:

-- This output is generated from the online HelloWorld3.sql file.

OBJECT_NAME        OBJECT_TYPE    STATUS
-----------------  -----------    -------
HELLO_WORLD3       PACKAGE        VALID
HELLO_WORLD3       PACKAGE BODY   VALID
HelloWorld3        JAVA CLASS     VALID

If you did not get the same output, you’ll need to see what step you may have skipped. Please do this 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_world3.doDQL('SELECT character FROM mytable')
FROM    dual;

The query returns the following results:

HELLO_WORLD3.DODQL('SELECTCHARACTERFROMMYTABLE')
-----------------
Bobby McGee

You’ve now covered how to build Oracle database instance-stored Java class files that map a Java method to a PL/SQL procedure. The next section discusses how to build real Java objects wrapped by PL/SQL object types.