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.

34 comments

Post a comment | Trackback URI | RSS 2.0 feed for those 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
  22. bedware wrote:

    Anybody who has ??? instead original string or null value for strings – you need to add orai18n.jar in your classpath.

    Posted on April 5, 2018 at 8:09 AM | Permalink
  23. Anver wrote:

    Can the adding part be made dynamic.. Like i have a list instead of hard coded 23 and 42 .. i need to loop and add these values to the array ..
    new STRUCT(structDescriptor, connection, new Object[]{42, “Testobject 2”})

    Any help is appreciated 🙂

    Posted on May 6, 2018 at 2:56 PM | Permalink
  24. nickfoo wrote:

    Hi bedware,
    Even though i have orai18n.jar in my classpath i’m facing with the same problem . How did you solve it?

    Posted on May 9, 2018 at 2:06 PM | Permalink
  25. Kamalnath wrote:

    Hi

    if i added one more object

    CREATE TYPE t_demo_object_two AS OBJECT (
    some_number_two number,
    some_string_two varchar2(32),
    t_demo_objects t_demo_objects
    )
    /

    CREATE TYPE t_demo_objects_two IS TABLE OF t_demo_object_two
    /

    can you please provide the example for this

    Posted on June 22, 2018 at 6:17 PM | Permalink
  26. Kamalnath B wrote:

    Hi

    if i added one more object

    CREATE TYPE t_demo_object_two AS OBJECT (
    some_number_two number,
    some_string_two varchar2(32),
    t_demo_objects t_demo_objects
    )
    /

    CREATE TYPE t_demo_objects_two IS TABLE OF t_demo_object_two
    /

    can you please provide the example for this

    Posted on June 22, 2018 at 6:20 PM | Permalink
  27. JoaoMira wrote:

    Hi,

    Do you whether this works getting the connection from a DataSource configured inside WebLogic Server?

    I’m facing the error below:

    Exception in thread “main” weblogic.rmi.extensions.RemoteRuntimeException: Unexpected Exception
    at weblogic.jdbc.rmi.internal.ArrayImpl_oracle_sql_ARRAY_1036_WLStub.getArray(Unknown Source)
    at weblogic.jdbc.rmi.internal.ArrayStub_weblogic_jdbc_rmi_internal_ArrayImpl_oracle_sql_ARRAY_1036_WLStub.getArray(Unknown Source)
    at OracleTableOfResult.main(OracleTableOfResult.java:56)
    Caused by: java.rmi.MarshalException: error marshalling return; nested exception is:
    java.io.NotSerializableException: oracle.jdbc.driver.T4CConnection
    at weblogic.rjvm.ResponseImpl.unmarshalReturn(ResponseImpl.java:237)
    at weblogic.rmi.internal.BasicRemoteRef.invoke(BasicRemoteRef.java:223)
    … 3 more
    Caused by: java.io.NotSerializableException: oracle.jdbc.driver.T4CConnection
    at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1164)
    at java.io.ObjectOutputStream.defaultWriteFields(ObjectOutputStream.java:1518)
    at java.io.ObjectOutputStream.writeSerialData(ObjectOutputStream.java:1483)
    at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1400)
    at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1158)
    at java.io.ObjectOutputStream.writeArray(ObjectOutputStream.java:1346)
    at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1154)
    at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:330)
    at weblogic.rjvm.MsgAbbrevOutputStream.writeObject(MsgAbbrevOutputStream.java:618)
    at weblogic.utils.io.ChunkedObjectOutputStream.writeObject(ChunkedObjectOutputStream.java:62)
    at weblogic.jdbc.rmi.internal.ArrayImpl_oracle_sql_ARRAY_WLSkel.invoke(Unknown Source)
    at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:667)
    at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:522)
    at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
    at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:146)
    at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:518)
    at weblogic.rmi.internal.wls.WLSExecuteRequest.run(WLSExecuteRequest.java:118)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)

    Posted on June 3, 2019 at 4:10 PM | Permalink
  28. Michael wrote:

    Sorry, I don’t know.

    Posted on June 4, 2019 at 9:03 AM | Permalink
  29. Shashank wrote:

    Hi Michael,

    I am using the approach suggested by you. Below is my code for SQL
    ————
    CREATE OR REPLACE PACKAGE TXN_CONSOLIDATION_PKG AS

    TYPE TRAN_DETAIL IS RECORD
    (
    col1 VARCHAR2(40),
    col2 VARCHAR2(40),
    col3 VARCHAR2(40),
    );

    TYPE tran_details IS TABLE OF TRAN_DETAIL;

    PROCEDURE TXN_CONSOLIDATION_PROC (tableName IN VARCHAR2, tableType IN VARCHAR2, startDate IN VARCHAR2, endDate IN VARCHAR2,
    cardNumber IN VARCHAR2, rowCount IN INTEGER, detail OUT tran_details);

    END TXN_CONSOLIDATION_PKG;

    ———————————————-
    Java Code
    ———————————————-

    Connection c = DriverManager.getConnection(“url”, “username”,”pwd”);

    String proc = “{call TXN_CONSOLIDATION_PKG.TXN_CONSOLIDATION_PROC(?,?,?,?,?,?,?)}”;

    final String typeName = “TXN_CONSOLIDATION_PKG.TRAN_DETAIL”;
    final String typeTableName = “TXN_CONSOLIDATION_PKG.tran_details”;

    final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), c);
    final ResultSetMetaData metaData = structDescriptor.getMetaData();

    OracleCallableStatement s = (OracleCallableStatement)c.prepareCall(proc);
    s.setString(1, “a”);
    s.setString(2, “b”);
    s.setString(3, “01/01/2019 00:00:00”);
    s.setString(4, “11/01/2019 00:00:00”);
    s.setString(5, “5000888823566634”);
    s.setInt(6, 100);
    s.registerOutParameter(7, Types.ARRAY, typeTableName);
    s.execute();

    —————————————————
    Error received at line s.registerOutParameter(7, Types.ARRAY, typeTableName);

    Exception in thread “main” java.sql.SQLException: ORA-06550: line 1, column 83:
    PLS-00103: Encountered the symbol “;” when expecting one of the following:

    ( begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with

     << continue close current delete fetch lock
    insert open rollback savepoint set sql execute commit forall
    merge pipe purge
    The symbol "exit" was substituted for ";" to continue.

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1041)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
    at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4739)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)

    Posted on September 20, 2019 at 1:50 PM | Permalink
  30. Eric Hunter wrote:

    Hi Michael,

    This code absolutely saved my bacon! Oracle JDBC with tables of objects appears to be a bit of a lost art.

    I much appreciate you sharing your knowledge.

    Eric

    Posted on June 11, 2020 at 1:13 AM | Permalink
  31. Michael wrote:

    Thanks for your kind feedback, Eric!

    Posted on June 11, 2020 at 6:38 PM | Permalink
  32. Sreenivasulu Gadige wrote:

    Hi Michael…

    Your code has saved my day. Found Very helpful after googling for a week on JDBC, collections and arrays in java n oracle.

    How to avoid hardcoding of values for the object columns ?

    new STRUCT(structDescriptor, connection, new Object[] {23, “Testobject 1”}),
    new STRUCT(structDescriptor, connection, new Object[] {42, “Testobject 2”})

    //nested
    new STRUCT(structDescriptor, connection, new Object[]{23, “Testobject 1”, detailsTable}),
    new STRUCT(structDescriptor, connection, new Object[]{42, “Testobject 2”, detailsTable})

    Your help is much appreciated!!
    — Sreeni

    Posted on June 22, 2020 at 3:10 AM | Permalink
  33. VijayaSuresh wrote:

    Dear Sir/Madam,
    I am really happy for sharing knowledge and it was very helpful to me.
    Requirement:
    I am calling stored procedure using mybatis(ibatis) in java spring boot project.
    Input: String
    And procedure is returing 2 out param values. 1 o_batch_size(i am getting in the response). 2. o_Message_data_array(i am getting as empty object).

    I am getting empty object since which i was written Typehandler is not correct. I am guessing.
    Can you please help me with this.
    Procedure snippet:
    Procedure Poll_File_Transmission
    (in_trans_type IN btps.transmission_header.th_trans_type%type,
    o_message_data_array OUT btps.Obj_File_Trans_Poll_Array,
    o_batch_size OUT NUMBER) IS
    v_state_agency_obj btps.obj_payer_state_data_array;
    CURSOR pension_ptf_cur IS
    SELECT th_admin_id admin_id,
    —-
    FROM btps.transmission_header,
    —
    btps.participant
    WHERE td_transmission_id = th_transmission_id
    —-
    AND eptr_return_address_id = adr2.addr_address_id(+);
    CURSOR pension_pmt_cur IS
    SELECT th_admin_id admin_id,
    tdf_service_id service_id,
    —
    FROM btps.transmission_header,
    —
    benefits.product_service_ref
    WHERE td_transmission_id = th_transmission_id
    —
    AND cpy_admin_id = ad_admin_id;

    END IF; — in_trans_type = BTPS.BTPS_CONSTANTS_PACKAGE.C_RPS_TYPE

    — All done.
    —
    END Poll_File_Transmission;

    Sir/Madam: If you don’t mind I would be pay if any charge applicable. I am in bit in difficult situatuion. Please do the needful.
    I am happy If you can able to spend 5 of minutes your valuable time.
    Mail Id: brahmasuresh@gmail.com
    Ph No: 9739744111
    I am waiting for your reply. Thank you.
    Regards
    BrahmaSuresh. S

    Posted on December 19, 2020 at 9:29 AM | Permalink
  34. Michael wrote:

    Sorry Vijaya, i have never used MyBatis, but I’m gonna share this on my twitter, maybe someone has an idea.

    Posted on December 20, 2020 at 3:35 PM | Permalink
Post a Comment

Your email is never published. We need your name and email address only for verifying a legitimate comment. For more information, a copy of your saved data or a request to delete any data under this address, please send a short notice to michael@simons.ac from the address you used to comment on this entry.
By entering and submitting a comment, wether with or without name or email address, you'll agree that all data you have entered including your IP address will be checked and stored for a limited time by Automattic Inc., 60 29th Street #343, San Francisco, CA 94110-4929, USA. only for the purpose of avoiding spam. You can deny further storage of your data by sending an email to support@wordpress.com, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *

Meta

This entry (permalink) was posted on July 24, 2012 at 2:42 PM by Michael, tagged with , and categorized in English posts, Oracle.

Related posts

More from my site