Archive for October, 2025
Java Subclass with Struct
This blog post relies on the “How to write a Java Class with the SQLData Interface” blog post. Oracle object types support subclasses with the UNDER keyword from Oracle Database 10g forward. Naturally, you can create Java classes that subclass another Java class.
A subclass is a specialized behavior of a parent class. Parent classes are considered more generalized than their subclasses. There are certain rules and behaviors for using Java subclasses as Oracle object subtypes. That’s why we wrote this section.
The two subsections show you how to implement a Java subclass and how to implement an Oracle object subtype.
Creating a Java Subclass
Like Java classes and Oracle object types, you must implement the Java class file first, and the PL/SQL wrapper second. Java subclasses rely on the existence of their parent, and object subtypes rely on the existence of their parent.
The following ItemSt class is a subclass or specialization of the Java Item class. You should take note that it doesn’t redefine the attributes of the parent class. The ItemSt class does add one new attribute, so we can demonstrate how Java subclasses or Oracle subtypes work.
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ItemSt" AS 2 // JAVA LIBRARY imports. 3 import JAVA.SQL.*; 4 import JAVA.io.*; 5 import oracle.SQL.*; 6 import oracle.jdbc.*; 7 import oracle.oracore.*; 8 import oracle.jdbc2.*; 9 import JAVA.math.*; 10 11 PUBLIC class ItemSt EXTENDS Item implements SQLData 12 { 13 // Implement the attributes AND operations FOR this TYPE. 14 PRIVATE String bluray; 15 16 // A getter FOR the rating attribute. 17 PUBLIC String getBluray() { 18 RETURN this.bluray; } 19 20 // A setter FOR the rating attribute. 21 PUBLIC void setBluray(String bluray) { 22 this.bluray = NEW String(bluray); } 23 24 // A setter FOR this object. 25 PUBLIC void setItem(Struct item) throws JAVA.SQL.SQLException { 26 27 // Assign Item instance variables. 28 super.setItem(item); 29 30 // Get the attributes OF the Item object. 31 Object[] attributes = (Object[]) item.getAttributes(); 32 33 // Assign the SUBTYPE element. 34 this.bluray = (String) attributes[6]; } 35 36 // DECLARE an instance toString method. 37 PUBLIC String toString() { 38 RETURN super.toString() + "Blu-ray [" + this.bluray +"]\n"; } 39 40 /* Implement SQLData interface. 41 || -------------------------------------------------------- 42 || Required interface components: 43 || ============================== 44 || 1. String sql_type instance variable. 45 || 2. getSQLTypeName() method returns the sql_type value. 46 || 3. readSQL() method to read from the Oracle session. 47 || 4. writeSQL() method to write to the Oracle session. 48 */ 49 50 // Required INTERFACE variable. 51 PRIVATE String sql_type; 52 53 // Reads the stream FROM the Oracle SESSION. 54 PUBLIC void readSQL(SQLInput stream, String typeName) throws SQLException { 55 56 // CALL TO parent class. 57 super.readSQL(stream, typeName); 58 59 // Map instance variables. 60 sql_type = typeName; 61 bluray = stream.readString(); } 62 63 // Writes the stream TO the Oracle SESSION. 64 PUBLIC void writeSQL(SQLOutput stream) throws SQLException { 65 // CALL TO parent class. 66 super.writeSQL(stream); 67 68 // Map instance variables. 69 stream.writeString(bluray); } 70 71 /* 72 || -------------------------------------------------------- 73 || End Implementation of SQLData interface. 74 */ 75 } 76 / |
Lines 25 through 34 show you how to handle a Struct of the ItemSt object type. This is where we implement a specialized setItem() method. You should note that in line 28 the subclass calls the parent class (or super) setItem() method. The balance of the setItem() method converts the Struct to an array of objects and then assigns the correct element to the
bluray variable.
Lines 38 and 39 show you how to handle the toString() method of a subclass. Like the setItem() method, the toString() method must call super to get the behavior of the parent
before adding on its own behavioral characteristics. Lines 57 and 66 also make super calls to the
readSQL() and writeSQL() methods of the parent class.
You should discover from this example that a subclass depends on the program logic in the parent class. Also, you should see that subclass methods generally override behaviors of equivalent methods in the subclass.
Testing the Java Subclass
Testing subclass behavior is also known as testing polymorphic behavior. Polymorphic behavior occurs when a subclass performs differently from its parent and sibling classes.
We need to slip one more example in here, and that’s a stand-alone function that constructs either a super class or subclass. That’s done with the following get_item function. You should note two things about the get_item function. First, it takes a list of parameters necessary to create an item_obj_st instance. Second, it makes any subtype-only parameters optional parameters in the function’s signature.
SQL> CREATE OR REPLACE FUNCTION get_item 2 ( id NUMBER 3 , title VARCHAR2 4 , subtitle VARCHAR2 5 , rating VARCHAR2 6 , rating_agency VARCHAR2 7 , release_date DATE 8 , bluray VARCHAR2 DEFAULT NULL ) 9 RETURN item_obj IS 10 /* Declare a local variable. */ 11 lv_item ITEM_OBJ; 12 BEGIN 13 /* Check for the subtype attribute. */ 14 IF bluray IS NULL THEN 15 lv_item := item_obj( id 16 , title 17 , subtitle 18 , rating 19 , rating_agency 20 , release_date); 21 ELSE 22 lv_item := item_obj_st( id 23 , title 24 , subtitle 25 , rating 26 , rating_agency 27 , release_date 28 , bluray); 29 END IF; 30 31 /* Return a type. */ 32 RETURN lv_item; 33 END; 34 / |
Line 8 provides the bluray attribute, which only belongs to the item_obj_st object type. The bluray parameter is optional because an item_obj instance can ignore that parameter. The IF statement on line 14 checks whether the subtype’s bluray variable is null. The get_item function creates an item_obj instance when the bluray variable is null and an item_obj_st when the bluray variable isn’t null.
The simplest way to test polymorphic behavior is an anonymous PL/SQL block. The following constructs item_obj and item_obj_st instances by leveraging our get_item function, and it assigns the result from the get_item function to a collection of the generalized item_obj class. Then, the anonymous block reads through the collection in a range FOR loop.
SQL> DECLARE 2 /* Declare a generalized object type. */ 3 lv_item_tab ITEM_TAB := item_tab(); 4 BEGIN 5 /* Assign an object to a generalization. */ 6 lv_item_tab.EXTEND; 7 lv_item_tab(lv_item_tab.COUNT) := 8 get_item( id => 2 9 , title => 'The Hobbit' 10 , subtitle => 'The Desolation of Smaug' 11 , rating => 'PG-13' 12 , rating_agency => 'MPAA' 13 , release_date => '13-DEC-2013'); 14 15 /* Assign an object to a specialization. */ 16 lv_item_tab.EXTEND; 17 lv_item_tab(lv_item_tab.COUNT) := 18 get_item( id => 3 19 , title => 'The Hobbit' 20 , subtitle => 'The Battle of the Five Armies' 21 , rating => 'PG-13' 22 , rating_agency => 'MPAA' 23 , release_date => '17-DEC-2014' 24 , bluray => 'Sony Blu-ray'); 25 26 /* Print header line. */ 27 dbms_output.put_line( 28 '---------------------------------------------'); 29 30 /* Print items in collection. */ 31 FOR i IN 1..lv_item_tab.COUNT LOOP 32 /* Print the contents of the item_obj. */ 33 parse_rows(lv_item_tab(i).to_string()); 34 dbms_output.put_line( 35 '---------------------------------------------'); 36 END LOOP; 37 END; 38 / |
For reference, the parse_rows procedure is in the “How to write a Java Class with the SQLData Interface” blog post.
Lines 8 through 14 assign an item_obj instance to the lv_item_tab collection, and lines 18 through 26 assign an item_obj_st instance to the lv_item_tab collection. Line 35 uses the parse_rows procedure.
It should print:
--------------------------------------------- ID # [2] Title [The Hobbit: The Desolation of Smaug] Rating [MPAA:PG-13] Release [2013-12-13] --------------------------------------------- ID # [3] Title [The Hobbit: The Battle of the Five Armies] Rating [MPAA:PG-13] Release [2014-12-17] Blu-ray [Sony Blu-ray] --------------------------------------------- |
The first element prints the item_obj output, and the second element prints the item_obj_st output. Please take note that Oracle determined which to run based on their base object type, and you didn’t need to write any specialized instanceOf logic.
This collection of articles should have enabled you to learn the necessary skills to implement Java classes inside the Oracle Database. The SQLData Interface affords the ability to read and write to the database and those methods may be replaced by other more direct methods using the JDBC standard approaches.
Java Class with Struct
If you’re new to writing Java classes that use the SQLData interface, inside the Oracle Database, please read the blog post on: “How to build a Java Class with the SQLData Interface”. This is a much larger example that shows you how to implement a Struct as a parameter option and how to work with other input and output data types.
Building the Java Object Class
The Item Java source file provides us with a lot of topics to cover. We debated about breaking the code up for display, but decided against it. In this section, we simply show you a complete Java
example class. We did limit the example to one getter, the getRating() method, and one setter, the setRating() method, for single attribute values.
We also gave you a getName() method that lets you capture the Java class name, and a setItem() method that lets you set the list of class attributes with a Struct object type. The setItem() method shows you how to set the instance values through a direct method call rather than the readSQL() method implementation of the SQLData interface.
The following SQL statement creates the Item class in an Oracle Database as follows:
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Item" AS 2 3 // JAVA LIBRARY imports. 4 import JAVA.SQL.*; 5 import JAVA.io.*; 6 import oracle.SQL.*; 7 import oracle.jdbc.*; 8 import oracle.oracore.*; 9 import oracle.jdbc2.*; 10 import JAVA.math.*; 11 12 PUBLIC class Item implements SQLData 13 { 14 // Implement the attributes AND operations FOR this TYPE. 15 PRIVATE BigDecimal id; 16 PRIVATE String title; 17 PRIVATE String subtitle; 18 PRIVATE String rating; 19 PRIVATE String ratingAgency; 20 PRIVATE DATE releaseDate; 21 22 // A getter FOR the rating attribute. 23 PUBLIC String getRating() { 24 RETURN this.rating; } 25 26 // A getter FOR the class instance name. 27 PUBLIC String getName() { 28 RETURN this.getClass().getName(); } 29 30 // A getter FOR the rating attribute. 31 PUBLIC String getUserName() { 32 33 // DECLARE local variables. 34 String userName = NEW String(); 35 String query = "SELECT user"; 36 37 // CREATE a connection discover a USER name. 38 try { 39 // DECLARE an Oracle connection. 40 OracleConnectionWrapper conn = 41 (oracle.jdbc.OracleConnectionWrapper) 42 DriverManager.getConnection("jdbc:default:connection:"); 43 44 // Prepare AND EXECUTE a statement. 45 JAVA.SQL.PreparedStatement ps = conn.prepareStatement(query); 46 ResultSet rs = ps.executeQuery(); 47 48 // Read the result SET. 49 WHILE (rs.next()) 50 userName = rs.getString(1); } 51 catch (SQLException e) {} 52 // RETURN a USER name. 53 RETURN userName; } 54 55 // A setter FOR this object. 56 PUBLIC void setItem(Struct item) throws JAVA.SQL.SQLException { 57 58 // Get the attributes OF the Item object. 59 Object[] attributes = (Object[]) item.getAttributes(); 60 61 // Assign Item instance variables. 62 this.id = (BigDecimal) attributes[0]; 63 this.title = (String) attributes[1]; 64 this.subtitle = (String) attributes[2]; 65 this.rating = (String) attributes[3]; 66 this.ratingAgency = (String) attributes[4]; 67 this.releaseDate = NEW DATE(((TIMESTAMP) attributes[5]).getTime()); } 68 69 // A setter FOR the rating attribute. 70 PUBLIC void setRating(String rating) { 71 this.rating = rating; } 72 73 // DECLARE an instance toString method. 74 PUBLIC String toString() { 75 RETURN "ID # [" + this.id + "]\n" + "Title [" + this.title + ": " + this.subtitle +"]\n" + 76 "Rating [" + this.ratingAgency + ":" + this.rating + "]\n" + 77 "Release [" + this.releaseDate + "]\n"; } 78 79 /* Implement SQLData interface. 80 || -------------------------------------------------------- 81 || Required interface components: 82 || ============================== 83 || 1. String sql_type instance variable. 84 || 2. getSQLTypeName() method returns the sql_type value. 85 || 3. readSQL() method to read from the Oracle session. 86 || 4. writeSQL() method to write to the Oracle session. 87 */ 88 89 // Required INTERFACE variable. 90 PRIVATE String sql_type; 91 92 // Returns the INTERFACE required variable VALUE. 93 PUBLIC String getSQLTypeName() throws SQLException { 94 RETURN this.sql_type; } 95 96 // Reads the stream FROM the Oracle SESSION. 97 PUBLIC void readSQL(SQLInput stream, String typeName) throws SQLException { 98 99 // Map instance variables. 100 this.sql_type = typeName; 101 this.id = stream.readBigDecimal(); 102 this.title = stream.readString(); 103 this.subtitle = stream.readString(); 104 this.rating = stream.readString(); 105 this.ratingAgency = stream.readString(); 106 this.releaseDate = stream.readDate(); } 107 108 // Writes the stream TO the Oracle SESSION. 109 PUBLIC void writeSQL(SQLOutput stream) throws SQLException { 110 111 // Map instance variables. 112 stream.writeBigDecimal(this.id); 113 stream.writeString(this.title); 114 stream.writeString(this.subtitle); 115 stream.writeString(this.rating); 116 stream.writeString(this.ratingAgency); 117 stream.writeDate(this.releaseDate); } 118 119 /* 120 || -------------------------------------------------------- 121 || End Implementation of SQLData interface. 122 */ 123 } 124 / |
The generic getter on lines 23 and 24 lets you get the instance value of the rating attribute, and the generic setter on lines 70 and 71 lets you override the rating instance variable. Lines 56 through 67 contain the logic for the setItem method. The setItem method takes a Struct parameter, which illustrates how you can use a composite data type. Line 57 converts the fields of the Struct parameter to an array of objects. Most of the conversions are straightforward and map through simple casting operations based on the Oracle Database rules. A date data type in the Oracle database is a DATE data type. While you may think that an Oracle DATE data type should convert to a Java Date class, it doesn’t. An Oracle DATE inside a Struct converts to a Timestamp class. You must cast the Java Timestamp class to a Java Date class through a two-step method. First, use the getTime() method to capture the long value of the Timestamp. Second, construct a new Date class by using the long value result from the getTime() method.
Line 67 from the preceding Item class example demonstrate the required Oracle DATE to Java Timestamp technique:
67 | this.releaseDate = new Date(((Timestamp) attributes[5]).getTime()); } |
Line 90 declares the sql_type variable. Lines 93 and 94 implement the getSQLTypeName() method for the SQLData interface. Lines 97 through 106 implement the readSQL() method, and lines 109 through 117 implement the writeSQL() method for the SQLData interface.
After creating and compiling the Item class, you need to create an object type for it. The object type wraps access to the Java class file.
Creating the PL/SQL Object Type
The Item object type provides us with the syntax for mapping object methods against native Java classes. As a refresher, you create object types with a CREATE TYPE statement. Then, you can deploy them as persistent or transient objects. Persistent objects can be either stand-alone or embedded objects. Stand-alone objects are stored in a database table and have a unique identifier. Embedded objects are not stored in a database table but are embedded in another Oracle structure, like a stored program parameter, return type, or as an attribute of another object type. Transient objects are instances of objects that aren’t stored in the database. They have a lifetime limited to the duration of their use in a PL/SQL block.
You create an instance of an object type and use an INSERT or UPDATE statement to assign it as a persistent object in a table. Alternatively, you can retrieve an object type from an IN OUT or OUT-only mode parameter, or get one as the output parameter of a function.
The following CREATE TYPE statement creates the object declaration in the session scope. You should note that each object type’s methods point to valid Java methods from the same Java class.
SQL> CREATE OR REPLACE TYPE item_obj IS OBJECT 2 ( id NUMBER 3 , title VARCHAR2(60) 4 , subtitle VARCHAR2(60) 5 , rating VARCHAR2(8) 6 , rating_agency VARCHAR2(4) 7 , release_date DATE 8 , MEMBER FUNCTION get_rating RETURN VARCHAR2 9 AS LANGUAGE JAVA 10 NAME 'Item.getRating() return java.lang.String' 11 , MEMBER FUNCTION get_name RETURN VARCHAR2 12 AS LANGUAGE JAVA 13 NAME 'Item.getName() return java.lang.String' 14 , MEMBER FUNCTION get_sql_type RETURN VARCHAR2 15 AS LANGUAGE JAVA 16 NAME 'Item.getSQLTypeName() return java.lang.String' 17 , MEMBER FUNCTION get_user_name RETURN VARCHAR2 18 AS LANGUAGE JAVA 19 NAME 'Item.getUserName() return java.lang.String' 20 , MEMBER PROCEDURE set_item (item ITEM_OBJ) 21 AS LANGUAGE JAVA 22 NAME 'Item.setItem(java.sql.Struct)' 23 , MEMBER PROCEDURE set_rating (rating VARCHAR2) 24 AS LANGUAGE JAVA 25 NAME 'Item.setRating(java.lang.String)' 26 , MEMBER FUNCTION to_string RETURN VARCHAR2 27 AS LANGUAGE JAVA 28 NAME 'Item.toString() return java.lang.String') 29 INSTANTIABLE NOT FINAL; 30 / |
Lines 2 through 7 declare a list of attributes. The physical size assigned to the VARCHAR2 data types limits the size of the Java String attributes. Any attempt to pass a variable-length string longer than the imposed limit in the PL/SQL object type definition raises a “character string buffer too small” error.
Line 20 shows a set_item procedure that passes a SQL item_obj object type, which maps to a java.sql.Struct on line 22. All the other parameters and return types use Java String classes.
The next section shows you how to test the Java class instance by calling the PL/SQL object type in various contexts.
Testing the Java Class File
This section shows you how to insert instances of the item object type into an item_struct table, and how to query the persistent item_obj object instance from the item_struct table. Then, this section shows you how to work with transient object instances in an anonymous PL/SQL block.
The following CREATE TABLE statement creates the item_struct sample table:
SQL> CREATE TABLE item_struct 2 ( item_struct_id NUMBER DEFAULT item_struct_id_seq.NEXTVAL 3 , item_struct ITEM_OBJ); |
You can construct and insert an item_obj object instance to the item_struct table with the following syntax:
SQL> INSERT INTO item_struct VALUES 2 ( item_struct_s.NEXTVAL 3 , item_obj( 1 4 ,'The Hobbit' 5 ,'An Unexpected Journey' 6 ,'PG-13' 7 ,'MPAA' 8 ,'14-DEC-2012')); |
Lines 3 through 8 create an instance of (instantiate) the item_obj type. The instance is assigned as a flattened object to the VALUES clause of the INSERT statement, and is inserted into the item_struct table.
Any attempt to access a single object type from a table in a query returns
ORA-22905: cannot ACCESS ROWS FROM a non-nested TABLE item |
To avoid this error, you create an item_tab or table collection of item_obj with the following syntax:
SQL> CREATE OR REPLACE 2 TYPE item_tab IS TABLE OF item_obj; 3 / |
Now, you can query persistent copies of the item_obj data type like this:
SQL> COLUMN title FORMAT A36 HEADING "Title" SQL> COLUMN rating FORMAT A6 HEADING "Rating" SQL> COLUMN rating_agency FORMAT A6 HEADING "Rating|Agency" SQL> COLUMN release_date FORMAT A9 HEADING "Release|Date" SQL> SELECT title||': '||subtitle AS title 2 , rating 3 , rating_agency 4 , release_date 5 FROM item_struct 6 CROSS JOIN 7 TABLE( 8 SELECT CAST(COLLECT(item_struct) AS item_tab)); |
The combination of the SQL COLLECT, CAST, and TABLE built-in functions on lines 7 and 8 let us query persistent objects. Combining these SQL functions with a CROSS JOIN lets us write a SELECT statement to query persistent object types. The preceding query prints the following when you have one or more rows in the item_struct table:
Rating Release Title Rating Agency Date ------------------------------------ ------ ------ --------- The Hobbit: An Unexpected Journey PG-13 MPAA 14-DEC-12 |
An anonymous PL/SQL block provides a more robust test case solution. The following shows you how to create and assign an object type to a local variable, and shows you how to test the getter functions and setter procedures:
SQL> DECLARE 2 /* Create an object type instance. */ 3 lv_item ITEM_OBJ := 4 item_obj( 1 5 ,'The Hobbit' 6 ,'An Unexpected Journey' 7 ,'PG-13' 8 ,'MPAA' 9 ,'14-DEC-2012'); 10 BEGIN 11 /* Print the getter rating result. */ 12 dbms_output.put_line( 13 '---------------------------------------------'); 14 dbms_output.put_line( 15 'Rating Value: ['||lv_item.get_rating()||']'); 16 17 /* Set the value of the rating. */ 18 lv_item.set_rating('PG'); 19 20 /* Print the getter rating result. */ 21 dbms_output.put_line( 22 'Rating Value: ['||lv_item.get_rating()||']'); 23 dbms_output.put_line( 24 '---------------------------------------------'); 25 26 /* Print user name and sql_type value. */ 27 dbms_output.put_line( 28 'User Name: ['||lv_item.get_user_name()||']'); 29 dbms_output.put_line( 30 'Class Name: ['||lv_item.get_name()||']'); 31 dbms_output.put_line( 32 'Object Name: ['||lv_item.get_sql_type()||']'); 33 34 /* Print the toString value. */ 35 dbms_output.put_line( 36 '---------------------------------------------'); 37 parse_rows(lv_item.to_string()); 38 dbms_output.put_line( 39 '---------------------------------------------'); 40 END; 41 / |
Lines 3 through 9 create an instance of the item_obj object type and assign the instance to the lv_item local variable. Lines 14 and 15 print the current instance’s rating value, line 18 resets the instance’s rating value, and lines 21 and 22 print the modified rating value. These three activities demonstrate a call to the getter of an object type’s attribute before and after the call to the setter to reset the value of an object type’s attribute.
Lines 27 through 32 show how you can call several of the other getter functions, like the get_user function. The get_userto_string procedure of the lv_item local variable as a parameter to the parse_rows procedure.
The toString() methods of Java classes often return long strings. The parse_rows procedure lets you avoid the forced line breaks imposed by the dbms_output package. Wrapping text in a call to the following parse_rows procedure lets you avoid the 80-character limit of the put_line procedures:
SQL> CREATE OR REPLACE PROCEDURE parse_rows 2 ( pv_text VARCHAR2 ) IS 3 /* Declare parsing indexes. */ 4 lv_start NUMBER := 1; 5 lv_end NUMBER := 1; 6 lv_length NUMBER; 7 BEGIN 8 /* Assign an end value by parsing line return. */ 9 IF INSTR(pv_text,CHR(10),lv_start) = 0 THEN 10 lv_end := LENGTH(pv_text) + 1; 11 ELSE 12 lv_end := INSTR(pv_text,CHR(10),lv_start); 13 END IF; 14 15 /* Assign a length value to parsed strings. */ 16 lv_length := lv_end - lv_start; 17 18 /* Print first line. */ 19 dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length)); 20 21 /* Print the rows of a multiple line string. */ 22 WHILE (lv_end < LENGTH(pv_text)) LOOP 23 24 /* Assign a new start and end value. */ 25 lv_start := lv_end + 1; 26 IF INSTR(pv_text,CHR(10),lv_start + 1) = 0 THEN 27 lv_end := LENGTH(pv_text) + 1; 28 ELSE 29 lv_end := INSTR(pv_text,CHR(10),lv_start + 1); 30 END IF; 31 lv_length := lv_end - lv_start; 32 33 /* Print the individual rows. */ 34 dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length)); 35 36 END LOOP; 37 END; 38 / |
It prints the following:
--------------------------------------------- Rating Value: [PG-13] Rating Value: [PG] --------------------------------------------- User Name: [VIDEO] Class Name: [Item] Object Name: [VIDEO.ITEM_OBJ] --------------------------------------------- ID # [1] Title [The Hobbit: An Unexpected Journey] Rating [MPAA:PG] Release [2012-12-14] --------------------------------------------- |
The SELECT statement and anonymous PL/SQL blocks have shown you how to work with and test Oracle object types, that insert and query data. The next step would require you to learn how to use object subclasses, which are in the “How to write a Java Subclass with the SQLData Interface” blog post.
Build SQLData Java Class
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.
JDBC-Enabled Java Library
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.
Deterministic Java Library
In this post, you build an internal server Java function by building a Java class file that will use the server-side internal connection or JDBC thick connection. As qualified earlier in the chapter, the JDBC thick connection depends on 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 the class file is built and loaded into the server, you build a PL/SQL wrapper to the Java class library. Deployment requires that you load the compiled Java class file into the database, which you do with the loadjava utility.
Building a Java Library
To build a Java library for the Oracle Database server, the CLASSPATH must resolve to an ojdbcX.jar file (where X maps to the version for the Oracle Database Server). You can modify the earlier javaenv.bat file to include a CLASSPATH reference to the directory that contains the ojdbcX.jar file:
C:>copy con javaenv.bat SET PATH=%ORACLE_HOME%\jdk\bin;%PATH% SET CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc6.jar;. ^Z 1 file(s) copied. |
You’ll get the following prompt when the javaenv.bat file already exists:
Overwrite javaenv.bat? (Yes/No/All): Yes |
Naturally, you have the alternative of using any text editor of your choice. Several free or inexpensive editors are available, but we recommend Sublime as an inexpensive editor or Notepad++ as a free editor.
The Linux or Unix operating system would require the following changes for a javaenv.sh Bash shell environment:
export set PATH=%ORACLE_HOME%/jdk/bin:%PATH% export set CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc6.jar:. |
You need to source the javaenv.bat or javaenv.sh file before compiling the Java source file. You source the Windows batch file by simply running it from the command line, and you must use the dot (.) in the Bash or Korn shell or the keyword source in Tcsh.
Java Database Connectivity (JDBC) lets you build connections by using the DriverManager class. This is a change over the defaultConnection() method for internal Java class files and external connections. They now both use the getConnection() static method from the DriverManager class. The only difference between a thin client and a thick client is the actual parameter provided to the method. Examples in this chapter use the internal syntax, and examples in the appendix use the external thin client syntax.
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.
// Oracle class imports. import oracle.jdbc.driver.*; // Class definition. public class HelloWorld2 { public static String hello() { return "Hello World."; } public static String hello(String name) { return "Hello " + name + "."; } public static void main(String args[]) { System.out.println(HelloWorld2.hello()); System.out.println(HelloWorld2.hello("Larry")); } } |
The program defines two overloaded hello methods. One takes no formal parameters and the other takes one. You can compile HelloWorld2.java with the following syntax:
javac HelloWorld2.java |
After you compile and run this program with this syntax:
java HelloWorld2 |
the method without any formal parameters always prints
Hello World. |
while the one that takes one formal parameter always prints
Hello Larry. |
This happens because the static main() method always sends either no parameter or the same actual parameter to the dynamic method. As a rule, you want to remove testing components like the main() method before loading them into the database and pass actual parameters to dynamic methods. You can leave the static main() method in the program because it harms nothing and enables your testing of the program with the ojvmjava interactive utility.
Deploying a Java Library
You deploy a Java library with the loadjava utility, which is run from the operating system’s command-line interface. This step requires the HelloWorld2.class file from the prior section. You should note that sometimes problems can occur with the loadjava utility. If you run into problems with the loadjava utility, check the “Building, Loading, and Dropping Java Class Library Objects” below in this blog post.
Deploying a Java library also requires a demo schema, which may be a container database (CDB) or a pluggable database (PDB). If you’re unfamiliar with the difference between a CDB and PDB, the former refers to the base container and the latter are pluggable from one container to another.
After creating the demo schema in a PDB and compiling the Java class file, you load it into the Oracle JVM with the loadjava utility as follows:
loadjava -r -f -o -user video/video@video HelloWorld2.class |
You can verify that the loadjava command worked with the following query:
SQL> SELECT object_name 2 FROM user_objects 3 WHERE object_name = 'HelloWorld2' 4 AND object_type = object_type LIKE 'JAVA%'; |
It displays:
OBJECT_NAME -------------- HelloWorld2 |
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 demo schema. After loading the Java class file into the database, you’ll need to build a
PL/SQL wrapper to use it.
Wrapping a Java Library
PL/SQL lets you wrap Java libraries. This section shows you how to wrap the previously created HelloWorld2 Java library, which differs from wrapping a PL/SQL stored program unit. The following HelloWorld2.sql script (included in the online resources file) builds the hello_world2 package specification and a package body. The package body serves as a wrapper to the two methods implemented in the HelloWorld2 Java class.
SQL> CREATE OR REPLACE PACKAGE hello_world2 AS 2 FUNCTION hello 3 RETURN VARCHAR2; 4 5 FUNCTION hello 6 ( who VARCHAR2 ) 7 RETURN VARCHAR2; 8 END hello_world2; 9 / |
The following package body builds wrapper methods in the HelloWorld2 Java class library:
SQL> CREATE OR REPLACE PACKAGE BODY hello_world2 AS 2 FUNCTION hello 3 RETURN VARCHAR2 IS 4 LANGUAGE JAVA 5 NAME 'HelloWorld2.hello() return java.lang.String'; 6 7 FUNCTION hello 8 ( who VARCHAR2 ) 9 RETURN VARCHAR2 IS 10 LANGUAGE JAVA 11 NAME 'HelloWorld2.hello(java.lang.String) return java.lang.String'; 12 END hello_world2; 13 / |
Line 5 and line 11 designate a lowercase hello method. It is critical that the case-sensitive string description in the PL/SQL wrapper exactly match the case-sensitive method names that are used in the Java code. You raise the following exception if they don’t:
SELECT hello_world2.hello() FROM dual * ERROR AT line 1: ORA-29531: no method hello IN class HelloWorld2 |
When you ensure a case-sensitive match and run this in your schema, it creates a wrapper to the HelloWorld2.class file that you previously loaded. The return type of your PL/SQL wrapper is a VARCHAR2 data type. You map it to a java.lang.String class, and it must be that fully qualified path.
You can verify that all components are present to test by querying the user_objects view with the following:
COLUMN object_name FORMAT A30 SQL> SELECT object_name 2 , object_type 3 , status 4 FROM user_objects 5 WHERE object_name IN ('HelloWorld2','HELLO_WORLD2') 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 query will return the following results:
Method Call ----------------------------------- Hello Paul McCartney. |
This section has shown you how to wrap and identify Java class libraries and related PL/SQL packages. Next you’ll see how to compile, deploy, and wrap a Java library.
Compiling, Deploying and Wrapping a Java Library
As an alternative to using the loadjava utility, you can also build the entire Java source, publishing specification, and implementation in a single SQL file. Then, you can run it directly from the SQL*Plus command line. This is done by using a single Data Definition Language (DDL)
command.
The prototype for the DDL command is
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED <java_class_name> AS <java_source> / |
You must use a forward slash shown in the prototype to execute a DDL command that builds an internal Java class. If you substitute a semicolon, you’ll raise an ORA-29536 exception.
The Java class file name uniquely identifies the Java class in the Oracle JVM’s CLASSPATH. You can treat a class with multiple methods like a package equivalent and define a package as a wrapper to complete the Java class. Alternatively, you can ignore the similarity to a package and implement individual Java methods as functions or procedures. Java methods that return a static value act like PL/SQL procedures, and Java methods that return a value act like PL/SQL functions.
Overloading doesn’t work when you implement a schema-level function or procedure for each Java method in a Java class. You must implement PL/SQL wrappers inside a package when you want to leverage object-oriented overloading in Oracle’s PL/SQL programming language.
You create the Java HelloWorldSQL class with the following DDL command:
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS 2 // Class Definition. 3 PUBLIC class HelloWorldSQL { 4 5 PUBLIC static String hello() { 6 RETURN "Hello World."; } 7 8 PUBLIC static String hello(String name) { 9 RETURN "Hello " + name + "."; } 10 } 11 / |
RESOLVE JAVA SOURCE NAMED targets the class file name and parses and compiles the Java source into the instance. You can then publish individual methods as functions or procedures, or the class as a package.
The following publishes the class as a package specification:
SQL> CREATE OR REPLACE PACKAGE hello_world_sql AS 2 FUNCTION hello 3 RETURN VARCHAR2; 4 5 FUNCTION hello 6 ( who VARCHAR2 ) 7 RETURN VARCHAR2; 8 END hello_world_sql; 9 / |
And this wraps the class as a package body:
SQL> CREATE OR REPLACE PACKAGE BODY hello_world_sql AS 2 FUNCTION hello 3 RETURN VARCHAR2 IS 4 LANGUAGE JAVA 5 NAME 'HelloWorldSQL.hello() return String'; 6 7 FUNCTION hello 8 ( who VARCHAR2 ) 9 RETURN VARCHAR2 IS 10 LANGUAGE JAVA 11 NAME 'HelloWorldSQL.hello(java.lang.String) return String'; 12 END hello_world_sql; 13 / |
The package provides an overloaded hello function. You can call it with or without an actual parameter. You can then publish only a single method of the class as a function, like this:
SQL> CREATE OR REPLACE FUNCTION hello 2 ( who VARCHAR2) RETURN VARCHAR2 IS 3 LANGUAGE JAVA 4 NAME 'HelloWorldSQL.hello(java.lang.String) return String'; 5 / |
You can query the function by using
SQL> SELECT hello('Nathan') AS SALUTATION; |
and it returns
SALUTATION ----------------------------------- Hello Nathan. |
You have now covered how to build Oracle database instance-stored Java class files that map
methods to functions. The next blog post will examine how you build components to deliver
procedure DML behaviors.
Subscribe via RSS