MacLochlainns Weblog

If you’re new to writing Java inside the Oracle Database, please read the following two blog posts in sequence first: How to build a Deterministic Java Library and how to build a JDBC-Enabled Java Library.

Implementing the SQLData interface is done by providing a variable definition and three concrete methods in your Java class file. The following are the required components:

• A String data type named sql_type.
• A getSQLTypeName() method that returns a String data type.
• A readSQL() method that takes two formal parameters and returns a void. One formal parameter is a SQLInput that contains a stream. The other is a string that contains a data type name.
• A writeSQL() method that takes one formal parameter, which is a SQLOutput that contains a stream.

Oracle Database 12c forward also provides the OracleData and OracleDataFactory interfaces. Applying the Factory pattern is inherently more complex. Since applying the pattern requires advanced Java coding skills, they’re not covered in this blog post.

The SQLData interface only maps SQL objects. The OracleData interface lets you map SQL objects as well as any other SQL data type. The OracleData interface is necessary when you want to serialize RAW data in Java libraries. Please check the Oracle Database JDBC Developer’s Guide for more information on the OracleData and OracleDataFactory interfaces.

Subsequent examples in this chapter show you how to implement runtime Java classes. The HelloWorld4.java class file shows you a minimalist Java library to support a runtime instance of the HelloWorld4 object.

The source code for the class is as follows (with line numbers added for your convenience
while reading the discussion of the code):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

// Oracle class imports.
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.oracore.*;
import java.math.*; // Needed for BigDecimal maps of NUMBER data type.
 
// Class definition.
public class HelloWorld4 implements SQLData {
 
// Declare class instance variable.
private String whom;
 
  // Declare getter for SQL data type value.
  public String getSQLTypeName() throws SQLException {
    return sql_type; }
 
  // Required interface variable.
  private String sql_type;
 
  // Implements readSQL() method from the SQLData interface.
  public void readSQL(SQLInput stream, String typeName)
    throws SQLException {
    sql_type = typeName;
    this.whom = stream.readString(); }
 
  // Implements writeSQL() method from the SQLData interface.
  public void writeSQL(SQLOutput stream) throws SQLException {
    stream.writeString(whom); }
 
  // Declare a toString method.
  public String toString() {
    String datatype = null;
 
    try {
      datatype = getSQLTypeName(); }
    catch (SQLException e) {}
 
    // Return message.
    return datatype + " says hello [" + this.whom + "]!\n"; } 
}

The Java class implements the SQLData interface. You must create four elements to implement the SQLData interface. The first element requires declaring a private sql_type variable (declared on line 20). The sql_type variable holds an object type name defined in the Oracle Database. The second element, on lines 23 through 26, lets you read data from the database into the Java object instance through a SQLInput stream. The third element, on lines 29 and 30, lets you write any changes to the local copies of the data (held in the Java class instance) into the database. The getSQLTypeName() method lets you read the user-defined data type from the class. The sample code uses the getSQLTypeName() method to print a message from the toString() method.

If you have not built a test schema, you should do so now. The code in this example runs in either a container database (CDB) or a pluggable database (PDB). We’re using a video PDB database with a trivial video password.

The HelloWorld4.java program contains import statements that require you to place the ojdbcX.jar file (where X is the version required for your Oracle Database’s compatibility) in your CLASSPATH environment variable. If you have not done so, you should do so now. The file is found in your $ORACLE_HOME/jdbc/lib directory on Linux or Unix, and %ORACLE_HOME%\jdbc\lib directory on Windows.

You can directly load a Java source file with the loadjava utility as follows into a CDB
schema with the default listener:

loadjava -r -f -o -user video/video HelloWorld4.java

A slight change is required when you load this into a PDB. The following shows you must add the TNS alias to the credentials, which in this case is also videodb:

loadjava -r -f -o -user video/video@videodb HelloWorld4.java

The loadjava utility command behaves slightly differently when you choose this option. It parses, stores the Java source as a text entry, and compiles the stored Java source into a Java byte stream in the Oracle JVM under the videodb schema.

Creating the Java class file is only the first step. Next, you need to create an Oracle object type that wraps the Java class. Object types are declared as SQL data types and are implemented in PL/SQL, or callable languages such as Java.

Just a quick note: After loading the Java class file into the database this way, you won’t be able to use the dropjava utility to remove the HelloWorld4.class file. Instead, use the dropjava utility to remove the HelloWorld4.java file, which also drops the HelloWorld4.class file.

Building the Object Type Wrapper

You’ll need to build a SQL object type to wrap the Java stored object class. That means you have to connect to the Oracle database. You can connect to a CDB or a PDB.

Assuming you have a c##video CDB schema, connect as follows:

sqlplus c##video/password

Assuming you have a videodb PDB schema, you connect as follows:

sqlplus video/password@videodb

The following HelloWorld4.sql script builds the object type as a wrapper to the Java class object:

SQL> CREATE OR REPLACE TYPE hello_world4 AS OBJECT
  2  ( whom VARCHAR2(100)
  3  , MEMBER FUNCTION get_sql_type_name
  4    RETURN VARCHAR2 AS LANGUAGE JAVA
  5    NAME 'HelloWorld4.getSQLTypeName() return java.lang.String';
  6  , MEMBER FUNCTION to_string
  7    RETURN VARCHAR2 AS LANGUAGE JAVA
  8    NAME 'HelloWorld4.toString() return java.lang.String'; )
 10  INSTANTIABLE FINAL;
 11  /

The SQL object type declares a local whom attribute. The whom attribute has a maximum length of 100 characters. Both line 5 and line 8 wrap the name of the Java class name, method, and return type. As signatures to your methods become longer, we recommend that you take advantage of the ability to break these strings across multiple lines for readability.

You can create a table collection of the HELLO_WORLD4 type with this syntax:

SQL> CREATE OR REPLACE
  2  TYPE hello_tab IS TABLE OF HELLO_WORLD4;
  3  /

Now, you can test this in SQL with the following complex query:

SQL> SELECT *
  2  FROM   TABLE(
  3           SELECT CAST(COLLECT(hello_world4('Bilbo')) AS hello_tab));

You must collect the object type into a table collection and cast it to a table collection of the hello_tab base type. Then, you use the TABLE function to return a SQL result set.

The query prints from the toString() method of the HelloeWorld4 class:

VIDEO.HELLO_WORLD4 says hello [Bilbo]!

The SQLData interface allows you to pass a user-defined type (UDT), which means you can use any defined user structure. If you debug the execution of the Java instance, you’ll find that each invocation of the instance method actually reinstantiates the class instance.