Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem using Oracle Object Types and Arrays.

571699Apr 10 2007 — edited Apr 10 2007
I'm currently trying to work with oracle object types in java and I'm running into some issues when trying to add an item to an array.
The basic idea is that I have a header object and a detail object (both only containing an ID and a description). Inside of my java code I'm trying to add a new detail line to the header that has been retrieved from the database.

Here's what I'm working with.

--Oracle Objects:
CREATE OR REPLACE TYPE dtl_obj AS OBJECT
(
    detail_id INTEGER,
    header_id INTEGER,
    detail_desc VARCHAR2(300)
)
;
/
CREATE TYPE dtl_tab AS TABLE OF dtl_obj;
/
CREATE OR REPLACE TYPE hdr_obj AS OBJECT
(
    header_id INTEGER,
    src VARCHAR(30),
    details dtl_tab
)
;
/
CREATE TYPE hdr_tab AS TABLE OF hdr_obj;
/
--Java test methods
	public static void main(String[] args) throws SQLException,
			ClassNotFoundException
	{

		// Initialize the objects
		Test t = new Test();
		t.connect(); //Connects to the database

		//The oracle connection will be accessible through t.conn
		
		// Create the oracle call
		String query = "{? = call get_header(?)}";
		OracleCallableStatement cs = (OracleCallableStatement) t.conn.prepareCall(query);

		cs.registerOutParameter(1, OracleTypes.ARRAY, "HDR_TAB"); //Register the out parameter and associate it with our oracle type

		int[] hdrs = { 240 }; //we just want one for testing.
		ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(
				"ARRAY_T", t.conn);
		oracle.sql.ARRAY oHdrs = new ARRAY(descriptor, t.conn, hdrs);

		cs.setARRAY(2, oHdrs); //Set the headers to retrieve

		// Execute the query
		cs.executeQuery();

		try
		{
			ARRAY invArray = cs.getARRAY(1);

			// Start the retrieval process
			Class cls = Class.forName(Header.class.getName());
			Map<String, Class<?>> map = t.conn.getTypeMap();
			map.put(Header._SQL_NAME, cls);

			Object[] invoices = (Object[]) invArray.getArray();
			ArrayList<Header> invs = new ArrayList(
					java.util.Arrays.asList(invoices));

			if (invs != null)
			{
				for (Header inv : invs)
				{
					System.out.println(inv.getHeaderId() + " " + inv.getSrc());
					t.addDetail(inv, "new line");
					
					for (Detail dtl : inv.getDetails().getArray()) // Exception thrown here
//						java.sql.SQLException: Fail to construct descriptor: Invalid arguments
//						at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
//						at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
//						at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:861)
//						at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:128)
//						at oracle.jpub.runtime.MutableStruct.toDatum(MutableStruct.java:109)
//						at com.pcr.tst.Detail.toDatum(Detail.java:40)
//						at oracle.jpub.runtime.Util._convertToOracle(Util.java:151)
//						at oracle.jpub.runtime.Util.convertToOracle(Util.java:138)
//						at oracle.jpub.runtime.MutableArray.getDatumElement(MutableArray.java:1102)
//						at oracle.jpub.runtime.MutableArray.getOracleArray(MutableArray.java:550)
//						at oracle.jpub.runtime.MutableArray.getObjectArray(MutableArray.java:689)
//						at oracle.jpub.runtime.MutableArray.getObjectArray(MutableArray.java:695)
//						at com.pcr.tst.DetailTable.getArray(DetailTable.java:76)
//						at com.pcr.tst.Test.main(Test.java:91)
					{
						System.out.println(dtl.getDetailDesc());
					}
				}
			}
		}
		catch (Exception ex)
		{
			System.out.println("Error while retreiving header");
			ex.printStackTrace();
		}
	}
	
		public void addDetail(Header hdr, String desc) throws Exception
	{
		if (hdr == null)
		{
			throw new Exception("header not initialized");
		}
		// Convert the current list to an ArrayList so we can easily add to it.
		ArrayList<Detail> dtlLst = new ArrayList<Detail>();
		dtlLst.addAll(java.util.Arrays.asList(hdr.getDetails().getArray()));

		// Create the new detail
		Detail dtl = new Detail();
		dtl.setDetailDesc(desc);

		// add the new detail
		dtlLst.add(dtl);
		Detail[] ies = new Detail[dtlLst.size()];

		ies = dtlLst.toArray(new Detail[0]);

		DetailTable iet = new DetailTable(ies);

		hdr.setDetails(iet);
	}
I know its the addDetail method causing the issue because if I comment out the
t.addDetail(inv, "new line");
call it works fine.

Message was edited by:
pcristini
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 8 2007
Added on Apr 10 2007
0 comments
3,511 views