MacLochlainns Weblog

This section shows you how to build Java libraries that interact with data in an Oracle Database and relies on this earlier blog post. Here you build Java methods to support both functions and procedures. Building a procedure follows a very similar rule to how you build functions.

While PL/SQL functions and procedures have IN, IN OUT, and OUT parameter modes, you can’t use IN OUT mode in PL/SQL when wrapping a scalar parameter in a Java method. All formal parameters are defined as IN mode only. When you want output back, you should write a PL/SQL function, not a procedure.

The Oracle Database raises the following exception when you attempt to define a packagebody with a procedure using IN OUT modes:

PLS-00235: the external TYPE IS NOT appropriate FOR the parameter

In this section you create functions and procedures with IN-only mode parameters. The difference between how you implement a Java method to support a function or procedure is simple. A Java method returns any data type except a void to support a PL/SQL function wrapper, and a Java method returns only a void data type when supporting a PL/SQL procedure.

There are two subsections. The first shows you how to work with scalar data types, such as strings. The second shows you how to work with Attribute Data Types (ADTs), which are collections of scalar data types.

JDBC Enabled Using Scalar Data Types

This section revisits how you will create a Java library externally and use the loadjava utility to deploy a Java class file into the database. You should note that we’ve tried to conserve space and show how to bind variables, which means the code passes a SQL statement and parameter to each of these Java methods.

The following Java source file supports both a function and a procedure:

// Oracle class imports.
import java.sql.*;
import oracle.jdbc.driver.*;
 
  // Class definition.
  public class HelloWorld3 {
  public static void doDML(String statement
                          ,String name) throws SQLException {
 
    // Declare an Oracle connection.
    Connection conn =
      DriverManager.getConnection("jdbc:default:connection:");
 
    // Declare prepared statement, run query and read results.
    PreparedStatement ps = conn.prepareStatement(statement);
    ps.setString(1,name);
    ps.execute(); }
 
  public static String doDQL(String statement) throws SQLException {
 
    // Define and initialize a local return variable.
    String result = new String();
 
    // Declare an Oracle connection.
    Connection conn =
      DriverManager.getConnection("jdbc:default:connection:");
 
    // Declare prepared statement, run query and read results.
    PreparedStatement ps = conn.prepareStatement(statement);
    ResultSet rs = ps.executeQuery();
 
    while (rs.next())
      result = rs.getString(1);
    return result; }
}

This program creates a Java Connection instance using the Oracle Database’s internal process connection. The
process calls a static getConnection() method of the DriverManager class. The program implements two methods—one to insert records and another to query records. While these methods don’t use explicit cursors, statements and result sets persist across calls, and their finalizers do not release database cursors. You should remember to always close explicitly opened
cursors.

The INSERT statement returns a void, and the query returns a string.

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. As with the other Java libraries, HelloWorld3.java is a library file. Assuming you’re using a demo schema, you could load the Java class file into the Oracle JVM with the loadjava utility using the following:

loadjava -r -f -o -user video/video@video HelloWorld3.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 need to build an example table and PL/SQL wrapper to use it.

The following HelloWorld3.sql script builds the package and package body as a wrapper to the Java class library. The definition of the package specification is

SQL> CREATE OR REPLACE PACKAGE hello_world3 AS
  2    PROCEDURE doDML
  3    ( dml VARCHAR2
  4    , input VARCHAR2 );
  5
  6    FUNCTION doDQL
  7    ( dql VARCHAR2 )
  8    RETURN VARCHAR2;
  9  END hello_world3;
 10  /

The definition of the package body is

SQL> CREATE OR REPLACE PACKAGE BODY hello_world3 AS
  2    PROCEDURE doDML
  3    ( dml VARCHAR2
  4    , input VARCHAR2 ) IS
  5    LANGUAGE JAVA
  6    NAME 'HelloWorld3.doDML(java.lang.String,java.lang.String)';
  7
  8    FUNCTION doDQL
  9    ( dql VARCHAR2 )
 10    RETURN VARCHAR2 IS
 11    LANGUAGE JAVA
 12    NAME 'HelloWorld3.doDQL(java.lang.String) return String';
 13  END hello_world3;
 14  /

This program defines two methods:

• The doDML procedure takes two formal parameters that are VARCHAR2 data types and returns nothing as a stored procedure.
• The doDQL function takes one formal parameter that is a VARCHAR2 and returns a VARCHAR2 data type as a stored function.

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

SQL> COLUMN object_name FORMAT A30
SQL> SELECT object_name
  2  ,      object_type
  3  ,      status
  4  FROM user_objects
  5  WHERE object_name IN ('HelloWorld3','HELLO_WORLD3')
  6  ORDER BY CASE
  7   WHEN object_type = 'JAVA CLASS' THEN 1
  8   WHEN object_type = 'PACKAGE' THEN 2
  9   ELSE 3
 10  END;

The script should output the following results:

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ----------------------- -------
HelloWorld3                    JAVA CLASS              VALID
HELLO_WORLD3                   PACKAGE                 VALID
HELLO_WORLD3                   PACKAGE BODY            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 the doDML procedure inside an anonymous block program to write a string value to the example table, which you can create with the following command:

SQL> CREATE TABLE example
  2  (character VARCHAR2(100));

Now, you can insert the “Bobby McGee” string with the following anonymous PL/SQL block:

SQL> BEGIN
  2    hello_world3.dodml('INSERT INTO example VALUES (?)','Bobby McGee');
  3  END;
  4  /

After inserting a row into the example table, you can test the doDQL function. Running a query is the easiest way. The following illustrates a SQL query of the wrapper, which uses the internal Java class file:

SQL> SELECT hello_world3.doDQL('SELECT character FROM example') AS "Name";

The query returns the following results:

Name
-----------------------------------
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.

JDBC Enabled Using ADT Collections

This section shows you how to create a Java library that receives and sorts an Attribute Data Type (ADT) collection of a scalar VARCHAR2 data type. It introduces how you can map an ADT collection into a Java collection, and how you can map the Java collection back into a PL/SQL collection type.

The following are four key classes available in Oracle’s JDBC implementation:

• oracle.sql.ARRAY
• oracle.sql.ArrayDescriptor
• oracle.sql.Datum
• oracle.sql.STRUCT

The ARRAY class lets you implicitly cast an ADT or user-defined type (UDT) collection into a Java context without explicit conversion. You can transfer the contents of an ADT by calling the getArray() method of the ARRAY class. To transfer an Oracle collection of a scalar data type to a generic Object collection requires you to use the java.util.Map class. You map the Oracle data type to its equivalent Java data type, assuming that the Java library designates the collection’s formal parameter as a list, like

Object[] object = (Object[])list.getArray(map);

Alternatively, you can avoid the expense of converting the object type by using the Oracle-
specific Datum class, like

Datum[] arrayOracle = list.getOracleArray();

A subsequent blog post shows you the more complex approach required to map UDT collections to Java structures. The ArrayDescriptor classes let you map a Java data type into an Oracle data type. You can find more about Oracle collections that other blog post, where you will work with UDT collections, or you can check Chapter 16 of the Oracle Database JDBC Developer’s Guide.

The first step requires that you create an ADT collection. An ADT collection uses a scalar data type as its base type. Scalar data types in Oracle are like primitive data types in Java, and they’re more or less numbers, dates, and strings.

The following syntax creates a table of strings:

SQL> CREATE OR REPLACE
  2    TYPE stringlist IS TABLE OF VARCHAR2(4000);
  3  /

While it would have been straightforward to create this without the use of Java generics, we wanted to make sure you knew they’re available to you. Actually, you can use Java generics from Oracle Database 11g forward.

The following SQL statement creates the SortList Java class, which has a single sortTitleCaseList() method. The Java long name is the same as the short name because of its length.

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortList" AS
  2
  3     // Import required classes.
  4     import java.io.*;
  5     import java.security.AccessControlException;
  6     import java.sql.*;
  7     import java.util.Arrays;
  8     import java.util.Comparator;
  9     import oracle.sql.driver.*;
 10     import oracle.sql.ArrayDescriptor;
 11     import oracle.sql.ARRAY;
 12
 13     // Define class.
 14     public class Sorting {
 15       public static ARRAY sortTitleCaseList(oracle.sql.ARRAY list)
 16         throws SQLException, AccessControlException {
 17
 18     // Convert Oracle data type to Java data type.
 19     String[] unsorted = (String[])list.getArray();
 20
 21     // Sort elements.
 22     Arrays.sort(unsorted, new Comparator<String>() {
 23       public int compare(String s1, String s2) {
 24
 25       // Declare a sorting key integer for the return value.
 26       int sortKey;
 27
 28       // Check if lowercase words match and sort them.
 29       if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0)
 30         sortKey = s1.substring(0,1).compareTo(s2.substring(0,1));
 31       else
 32         sortKey = s1.toLowerCase().compareTo(s2.toLowerCase());
 33
 34       // Return the sorting index.
 35       return sortKey; }});
 36
 37       // Define a connection (this is for Oracle 11g).
 38       Connection conn =
 39         DriverManager.getConnection("jdbc:default:connection:");
 40
 41       // Declare a mapping to the schema-level SQL collection type.
 42       ArrayDescriptor arrayDescriptor =
 43         new ArrayDescriptor("STRINGLIST",conn);
 44
 45       // Translate the Java String{} to the Oracle SQL collection type.
 46       ARRAY sorted =
 47         new ARRAY(arrayDescriptor,conn,((Object[])unsorted));
 48
 49       // Return the sorted list.
 50       return sorted; }
 51  }
 52  /

Lines 10 and 11 import two key Oracle Java classes—the ARRAY and ArrayDescriptor classes. As discussed earlier in this section, the ARRAY class lets you transfer a collection of numbers, dates, strings, or any other scalar data type to a Java data type. Line 19 avoids using the java.util.Map class, shown earlier in this section, by explicitly designating the Java String data type as the natural equivalent of a VARCHAR2 data type. Line 22 implements a Java generic Comparator method that performs a natural language sort of String data type.

The ArrayDescriptor class implements a mediator pattern, and the declaration on lines 42 and 43 creates a map to the Oracle stringlist data type. Lines 46 and 47 construct an instance of the Oracle ARRAY class with a collection of the Java Object type, which is the most generalized class in the single-inheritance Java tree.

The PL/SQL wrapper for this Java library is

SQL> CREATE OR REPLACE
  2    FUNCTION sortTitleCaseList(list STRINGLIST) RETURN STRINGLIST IS
  3    LANGUAGE JAVA NAME
  4      'Sorting.sortTitleCaseList(oracle.sql.ARRAY) return oracle.sql.ARRAY';
  5  /

You can test this configuration with the following anonymous PL/SQL block:

SQL> DECLARE
  2    /* Declare a counter. */
  3    lv_counter NUMBER := 1;
  4    /* Declare a unordered collection of fruit. */
  5    lv_list STRINGLIST := stringlist('Oranges'
  6                                    ,'apples'
  7                                    ,'Apples'
  8                                    ,'Bananas'
  9                                    ,'Apricots'
 10                                    ,'apricots');
 11  BEGIN
 12    /* Read through an element list. */
 13    FOR i IN (SELECT column_value
 14              FROM TABLE(sortTitleCaseList(lv_list))) LOOP
 15      dbms_output.put_line('['||lv_counter||']['||i.column_value||']');
 16      lv_counter := lv_counter + 1;
 17    END LOOP;
 18  END;
 19  /

Line 14 has a call to the TABLE function that translates the returned collection type into a SQL result set. The anonymous PL/SQL block returns

[1][Apples]
[2][apples]
[3][Apricots]
[4][apricots]
[5][Bananas]
[6][Oranges]

The program also demonstrates how to accept Oracle collections into a Java program and how to return collections from a Java program to an Oracle collection.