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