How to retrieve tables of custom object types with JDBC
The following is not only a personal reminder for me but also a first try to answer some questions on Stack Overflow…
You can create custom object types in an Oracle database like so:
CREATE TYPE t_demo_object AS OBJECT ( some_number NUMBER, some_string varchar2(32) ) / |
Those types can have member functions, custom constructors and some pretty neat features inside oracle.
There also exists tables of them like so
CREATE TYPE t_demo_objects IS TABLE OF t_demo_object / |
They can be used to do stuff like pipelined functions.
Often those types are used as results of stored functions and procedures, probably like so:
CREATE OR REPLACE PROCEDURE p_generate_demo_objects(p_num IN NUMBER, p_data OUT t_demo_objects) AS BEGIN p_data := t_demo_objects(); p_data.extend(p_num); FOR i IN 1..p_num LOOP p_data(i) := t_demo_object(i, 'Index ' || i); END LOOP; END p_generate_demo_objects; / |
There was a comment by sande, asking for an example on how to pass ARRAYs to stored procedures, so here is a stored procedure receiving data as well as a table to store it:
CREATE TABLE demo ( some_number NUMBER, some_string varchar2(32) ); / CREATE OR REPLACE PROCEDURE p_receive_demo_objects(p_data IN t_demo_objects) AS BEGIN FOR idx IN p_data.first .. p_data.last LOOP INSERT INTO demo(some_number, some_string) VALUES(p_data(idx).some_number, p_data(idx).some_string); END LOOP; COMMIT; END p_receive_demo_objects; / |
As long as this is used only within the database, everything is fine with Oracle Object Types… But often a procedure like this will be called from the outside through jdbc.
How to retrieve this object type? As an java.sql.Array of java.sql.Structs.
I’m doing it like so with minimal dependencies on the Oracle JDBC driver:
package tests.jdbc; import java.sql.Array; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSetMetaData; import java.sql.Struct; import java.sql.Types; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.sql.STRUCT; import oracle.sql.StructDescriptor; public class OracleTableOfResult { public static void main(String...a) throws Exception { Class.forName("oracle.jdbc.OracleDriver"); Connection connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>"); final String typeName = "T_DEMO_OBJECT"; final String typeTableName = "T_DEMO_OBJECTS"; // Get a description of your type (Oracle specific) final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection); final ResultSetMetaData metaData = structDescriptor.getMetaData(); // Call the procedure (or whatever else) that returns the table of a custom type CallableStatement cs = connection.prepareCall("{call p_generate_demo_objects(?, ?)}"); cs.setInt(1, 5); // Result is an java.sql.Array... cs.registerOutParameter(2, Types.ARRAY, typeTableName); cs.execute(); // ...who's elements are java.sql.Structs Object[] data = (Object[]) ((Array) cs.getObject(2)).getArray(); for(Object tmp : data) { Struct row = (Struct) tmp; // Attributes are index 1 based... int idx = 1; for(Object attribute : row.getAttributes()) { System.out.println(metaData.getColumnName(idx) + " = " + attribute); ++idx; } System.out.println("---"); } cs.close(); // See sandes comment, passing arrays of Objects // We need the array descriptor as well final ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(typeTableName.toUpperCase(), connection); // Create generic object array // Create ARRAY from array of STRUCTS final ARRAY demoObjectsFromJava = new ARRAY( arrayDescriptor, connection, new STRUCT[] { // STRUCTS are created with the struct descriptor and a generic object array containing the values of the // attributes of the T_DEMO_OBJECT new STRUCT(structDescriptor, connection, new Object[] {23, "Testobject 1"}), new STRUCT(structDescriptor, connection, new Object[] {42, "Testobject 2"}) } ); cs = connection.prepareCall("{call p_receive_demo_objects(?)}"); // setObject with the designated sql type cs.setObject(1, demoObjectsFromJava, Types.ARRAY); cs.execute(); // no output here, have a look in table demo cs.close(); connection.close(); } } |
In the original question is also room for improving the pl/sql procedure.
You can fill the resulting object procedural with a loop but you can also use a “bulk collect into”:
SELECT t_demo_object(colum_a, column_b) bulk collect INTO p_data FROM some_table; |
which should be considerable faster on large datasets.
Note that it is not possible to use package level array types of record types in JDBC:
Oracle JDBC does not support RAW, DATE, and PL/SQL RECORD as element types.
Accessing PL/SQL Index-by Tables
Sadly, we have a legacy application that makes extensive use of such types… We had to rewrite most of the procedures.