How to retrieve tables of custom object types with JDBC

July 24, 2012 by Michael

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.

21 comments

  1. Dave O'Neill wrote:

    Thank YOU!!!!!!

    I got the STRUCT, put then got stuck ….

    Posted on March 2, 2013 at 12:27 AM | Permalink
  2. Michael wrote:

    Hi Dave, thank you for your feedback, you’re welcome.

    Posted on March 2, 2013 at 11:09 AM | Permalink
  3. Prasenjit wrote:

    Thanks for sharing !!
    This blog was very helpful 🙂

    Posted on April 5, 2015 at 9:04 PM | Permalink
  4. hicham wrote:

    There is a probleme with oracle 11G, JAVA 1.6

    I get this result:

    SOME_NUMBER = 1
    SOME_STRING = ???

    SOME_NUMBER = 2
    SOME_STRING = ???

    SOME_NUMBER = 3
    SOME_STRING = ???

    SOME_NUMBER = 4
    SOME_STRING = ???

    SOME_NUMBER = 5
    SOME_STRING = ???

    Any idea plz?

    Posted on June 26, 2015 at 11:49 AM | Permalink
  5. Michael wrote:

    Hi,
    what’s the problem? You getting weird encodings?

    Posted on June 26, 2015 at 12:19 PM | Permalink
  6. sande wrote:

    Thanks for the information.

    Can you please share an example to pass Java object which represents t_demo_objects as an input parameter to stored procedure

    Posted on July 3, 2015 at 6:04 PM | Permalink
  7. Michael wrote:

    Sure, sande. You’re welcome. Passing table of objects is also not hard, see updated post, especially the new receive function and that part of the Java class:

    		// 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();
    Posted on July 3, 2015 at 8:53 PM | Permalink
  8. hicham wrote:

    Hi,
    This part of code
    [CODE]// …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(“—“);
    }[/CODE]

    return
    [CODE]
    SOME_NUMBER = 1
    SOME_STRING = ???

    [/CODE]

    Have you any idea

    Posted on July 4, 2015 at 12:28 PM | Permalink
  9. nbey wrote:

    İs there any comment for hicham’s character/encoding problem. I am also getting same result

    Posted on August 17, 2015 at 7:05 AM | Permalink
  10. Bob wrote:

    I have found this code doesn’t work if you use a JNDI connection (as opposed to using JDBC) 🙂

    Posted on November 11, 2015 at 10:27 AM | Permalink
  11. Michael wrote:

    Intersting… First guess would be that you’re getting a wrapped connection, but i’m not using specifics of an Oracle Connection. Whats the exact problem?

    Posted on November 11, 2015 at 8:29 PM | Permalink
  12. Bob wrote:

    Sure – so I can create two kinds of database connections. One using the device driver the other by going into Weblogic console and creating a JNDI and referencing it in the code. If I use the former it works 100% but it’s not practical. Weblogic gives us the options of connection pooling. If I generate the JNDI and then invoke it like this:

    public static Connection getConn() {

    Connection db = null;
    //OracleConnection orConn = null;

    try {
    Context c = new InitialContext();
    DataSource ds = (DataSource) c.lookup(“jdbc/pme”);

    db = ds.getConnection();

    //orConn = (OracleConnection) db;

    }
    catch (SQLException e) {
    e.printStackTrace();
    logger.fatal(e.getMessage() + e.getCause() );
    }
    catch (NamingException n) {
    n.printStackTrace();
    logger.fatal(n.getMessage() + n.getCause() );
    }

    // return orConn;
    return db;
    }

    Then I use this database connection returned from the code into this:

    final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(objectType.toUpperCase(), dbConnection);

    Then it will fail …

    Posted on November 11, 2015 at 9:21 PM | Permalink
  13. ChrisP wrote:

    Hi, I know this is an old blog post but it consistently comes up on searches. I’m currently having an issue I have a stored procedure:
    procedure update_person(p_person in person)

    This seems fine and can use a STRUCT to populate person until you see that person is made up of :
    CREATE OR REPLACE TYPE person as object(
    P_FORENAME VARCHAR2(20)
    ,P_SURNAME VARCHAR2(20)
    ,P_DATE_OF_BIRTH DATE
    ,P_CONTACT_DETAILS db_contact_details_table
    )

    where db_contact_details_table is defined as:
    CREATE OR REPLACE TYPE db_contact_details_table as VARRAY(100) OF db_contact_details,

    CREATE OR REPLACE TYPE db_contact_details as object(
    PHONE_NUMBER VARCHAR2(12)
    ,EMAIL_ADDRESS VARCHAR2(100)
    )

    How is this nesting of custom types handled and how would you model this in Java?

    Posted on March 23, 2016 at 5:22 PM | Permalink
  14. Michael wrote:

    Hi Chris, no problem.

    I’ve done this before, let me check this days… I’ll drop you an email.

    Posted on March 23, 2016 at 5:25 PM | Permalink
  15. Bob wrote:

    Hi Michael,

    Just to let you know I got past that problem in my previous post (using JNDI). Your hunch was correct. I used a wrapped connection and it worked fine!

    Cheers
    Bob

    Posted on March 23, 2016 at 5:30 PM | Permalink
  16. ChrisP wrote:

    Hi Michael,

    Brilliant thanks very much
    Chris

    Posted on March 24, 2016 at 10:25 AM | Permalink
  17. Michael wrote:

    Hi Chris,

    here’s a version that works with nested objects. I’ve added your “db_contact_details” to my t_demo, so that i didn’t have to rewrite all:

    This is the PL/SQL part:

    CREATE OR REPLACE TYPE db_contact_details AS object(
      PHONE_NUMBER VARCHAR2(12),
      EMAIL_ADDRESS VARCHAR2(100)
    )
    /
     
    CREATE OR REPLACE TYPE db_contact_details_table AS VARRAY(100) OF db_contact_details
    /
     
    CREATE OR REPLACE TYPE t_demo_object AS OBJECT (
      some_number  NUMBER,
      some_string  varchar2(32),
      P_CONTACT_DETAILS db_contact_details_table
    )
    /
     
    CREATE TYPE t_demo_objects IS TABLE OF t_demo_object
    /
     
    -- My procedure is now generating nested objects, too:
    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
    	    DECLARE
    		the_details db_contact_details_table;
    	    BEGIN
    		the_details := db_contact_details_table(
    		    db_contact_details('4711', 'foo@bar.baz'),
    		    db_contact_details('23', 'test@test.com')
    		);
    		p_data(i) := t_demo_object(i, 'Index ' || i, the_details);
    	    END;
    	END LOOP;
    END p_generate_demo_objects;
    /
     
    -- So is the receiving part handling them now
    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
    	    DECLARE
    		hlp VARCHAR2(4000) := '';
    	    BEGIN
    		FOR idx2 IN p_data(idx).P_CONTACT_DETAILS.FIRST .. p_data(idx).P_CONTACT_DETAILS.LAST LOOP
    		    hlp := hlp || p_data(idx).P_CONTACT_DETAILS(idx2).PHONE_NUMBER || ' ' || p_data(idx).P_CONTACT_DETAILS(idx2).EMAIL_ADDRESS; 
    		END LOOP;
    		INSERT INTO demo(some_number, some_string) VALUES(p_data(idx).some_number, p_data(idx).some_string || ' (' || hlp || ')');
    	    END;
    	END LOOP;
    	COMMIT;
    END p_receive_demo_objects;
    /

    Now for the changes on the Java Side: Nested object arrays behave exactly the same as on top level, you basically only need the additional ARRAY and STRUCT descriptors:

    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();
     
            	// THIS IS NEW!!!!
            	// And also for the nested object type
        		final StructDescriptor structDescriptorContactDetails = StructDescriptor.createDescriptor("db_contact_details".toUpperCase(), connection);
        		final ResultSetMetaData metaDataContactDetails = structDescriptorContactDetails.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;
    			}
                            // THIS IS NEW!!!!
    			// Nested objects works the same, VARRAYs and TABLE OF types map to ARRAY
    			// from which an Object[] can be retrieved.
    			final Object[] contactDetailsTable = (Object[]) ((Array) row.getAttributes()[2]).getArray();
    			for (Object tmp2 : contactDetailsTable) {
                			Struct contactDetails = (Struct) tmp2;
                			// Attributes are index 1 based...
                			idx = 1;
                			System.out.println("Contact details:");
                			for (Object attribute : contactDetails.getAttributes()) {
                		    		System.out.println("t" + metaDataContactDetails.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);
     
    		// THIS IS NEW!!!!
    		// Also need the descriptor for the details table
    		final ArrayDescriptor arrayDescriptorContactDetails = 		ArrayDescriptor.createDescriptor("db_contact_details_table".toUpperCase(), connection);
    		// Create some contact details
    		final ARRAY detailsTable = new ARRAY(
                    arrayDescriptorContactDetails,
            		connection,
            		new STRUCT[]{
            		    new STRUCT(structDescriptorContactDetails, connection, new Object[]{"0800-NESTED", "nestedobjects@database.de"})
            		}
    		);
     
    		// 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
            		    // THIS IS NEW!!!! See the additional entry
            		    new STRUCT(structDescriptor, connection, new Object[]{23, "Testobject 1", detailsTable}),
            		    new STRUCT(structDescriptor, connection, new Object[]{42, "Testobject 2", detailsTable})
            		}
    		);
    		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();
    	}
    }

    There’s little change in the basics.

    Modeling this in Java: Not directly on the ARRAYs, i guess 🙂 But if you want to access them by hand and not using a framework, the above is the way to go, i think.

    Posted on March 24, 2016 at 11:20 AM | Permalink
  18. Michael wrote:

    Bob, Thanks for your feedback! I appreciate it and I’m happy that your solved your problem!

    Posted on March 24, 2016 at 11:21 AM | Permalink
  19. ChrisP wrote:

    Hi Michael,

    Thanks for the help, this should be of great help to me 🙂

    Chris

    Posted on March 24, 2016 at 12:07 PM | Permalink
  20. bhandi wrote:

    Excellent post Michael.
    I have not used oracle db much, let alone stored procedures with such advanced concepts. I was struggling to develop higher-level utility/driver on top of JDBC to connect to oracle stored procedures. This example of yours, made my day.
    Thank you very much.
    bhandi

    Posted on May 17, 2016 at 6:56 PM | Permalink
  21. Michael wrote:

    Thanks a lot for your friendly feedback!

    Posted on May 17, 2016 at 8:07 PM | Permalink
Post a Comment

Your email is never published nor shared. Required fields are marked *