Can I use a Clob as an attribute of a structured type (to map to a Java VO)?

[Graham Wagg]

Hi,

I'm trying to use a Clob as an attribute of a structured type, but I'm having trouble retrieving the Clob from Oracle, resorting to explicitly retrieving it in a seperate query on a database column. Is there a way of retrieving it as part of a Java VO? I haven't found it explicitly printed where it's not possible, but I have a nagging idea that it isn't. Any ideas?

I'm using Java 1.4 (soon to be Java 1.5), and Oracle 10.1.2.

Any help, greatly appreciated.


The code :

My VO (CharacterDataVO) contains 2 attributes - an id (BigDecimal), and data (the Clob). I'm calling a stored procedure, and it bombs out when I hit callable.execute(), and reports a StringIndexOutOfBoundsException:33. (I'm going to convert it to text once it's back - hence the String returned. Not an issue.)

The Clob is only ~10chars long at the moment(for test purposes, will get much bigger).


public String getClobVO(BigDecimal id) {
Connection con = null;
CallableStatement callable = null;
CharacterDataVO charData = new CharacterDataVO();

String clobString = null;
try {

con = getConnection();

Map map;
map = con.getTypeMap();
map.put(SQLObjectTypeNames.OT_CHARACTER_DATA_VO, CharacterDataVO.class);

con.setTypeMap(map);

//set call
callable = con.prepareCall("{? = call " + LARGE_DATA_PACKAGE + ".GET(?)");


//set parameters
callable.setBigDecimal(2, id);

callable.registerOutParameter(1, OracleTypes.STRUCT, SQLObjectTypeNames.OT_CHARACTER_DATA_VO);
callable.execute();

charData = (CharacterDataVO)callable.getObject(1);

} catch (final SQLException e) {
e.printStackTrace();
} catch (final Exception e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
if (callable != null) {
callable.close();
}
con.close();
} catch (final SQLException e) {
e.printStackTrace();
}
}
}

return clobString;
}






The exception : java.lang.StringIndexOutOfBoundsException: String index out of range: 33
at java.lang.String.charAt(Unknown Source)
at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:1066)
at oracle.jdbc.driver.OracleSql.parse(OracleSql.java:984)
at oracle.jdbc.driver.OracleSql.getSql(OracleSql.java:312)
at oracle.jdbc.driver.OracleSql.getSqlBytes(OracleSql.java:557)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:201)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
at com.inpses.vision.common.clobmapspike.dao.ClobMapSpikeDaoImpl.getClobVO(ClobMapSpikeDaoImpl.java:128)

[Graham Wagg]

Argh.

Obviously you can - or at least you can get the Clob locator - a schoolboy error from me, missing the "}" from the stored procedure call prevented progress (and some correcting of the signature call). Whoopsie.

Cheers to anyone that looked.