Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00932: inconsistent datatypes: expected - got CLOB (Oracle 10g, )
ORA-00932: inconsistent datatypes: expected - got CLOB [message #403785] |
Mon, 18 May 2009 12:09  |
akabir77
Messages: 6 Registered: May 2009
|
Junior Member |
|
|
Hi
I am trying to insert and update some CLOB in my table called
comparisons.
Now the valu when inserted/updated can be null.
I was getting the error message at first when i did the insert
ERROR:ORA-00932: inconsistent datatypes: expected CLOB got -
So I took out the XMLTYPE(?) from the sql and put a check on the xml data if its null then creating an null CLOB.
here is my code for INSERT
protected void insertComparisonPairListWithClobs(final List<ComparisonPair> pairList,
Connection conn) throws Exception {
logger.debug("My Method");
CLOB clobDiff = null;
CLOB clobCore = null;
CLOB clobLeg = null;
String insertSQL = " INSERT INTO COMPARISONS(COMPARISON_ID,LEGACY_TRANS_ID,CORE_TRANS_ID,LAST_COMPARISON_DATE,STATE,CUSTOMER,COMMODITY_ID, "
+ " CORE_STATUS,LEGACY_STATUS,CUST_BATCH,CUST_TRANS,DATE_STAMP,DIFFERENCES,CORE_XML,LEGACY_XML) "
+ " VALUES(COMP_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(insertSQL);
for (ComparisonPair pair : pairList) {
ps.setString(1, pair.getLegacyTransactionId());
ps.setString(2, pair.getCoreTransactionId());
Date utilDate = pair.getLastComparisonDate();
if (utilDate != null) {
ps.setTimestamp(3, new java.sql.Timestamp(utilDate.getTime()));
}
else {
ps.setTimestamp(3, null);
}
ps.setString(4, pair.getStateCode());
ps.setString(5, pair.getCustomer());
ps.setString(6, pair.getTransType());
ps.setString(7, pair.getCoreStatus());
ps.setString(8, pair.getLegacyStatus());
ps.setString(9, pair.getCustBatch());
ps.setString(10, pair.getCustTrans());
Date dateStamp = pair.getDateStamp();
if (dateStamp != null) {
java.text.SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
String dateString = format.format(dateStamp);
ps.setString(11, dateString);
}
else {
ps.setString(11, null);
}
clobDiff = getCLOB(pair.getDifferencesXML(), conn);
ps.setObject(12, clobDiff);
clobCore = getCLOB(pair.getCoreTransactionXML(), conn);
ps.setObject(13, clobCore);
clobLeg = getCLOB(pair.getLegacyTransactionXML(), conn);
ps.setObject(14, clobLeg);
ps.addBatch();
}
ps.executeBatch();
}
catch (Exception e) {
logger.error(e);
throw e;
}
finally {
releaseResources(null, null, ps);
}
}
@SuppressWarnings("deprecation")
private CLOB getCLOB(String xmlDATA, Connection conn)throws SQLException{
CLOB tempClob = null;
try{
tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);
tempClob.open(CLOB.MODE_READWRITE);
Writer tempClobWriter = tempClob.getCharacterOutputStream();
if(StringUtils.isNotBlank(xmlDATA))
tempClobWriter.write(xmlDATA);
tempClobWriter.flush();
tempClobWriter.close();
tempClob.close();
}catch(SQLException sqlexp){
tempClob.freeTemporary();
logger.error(sqlexp);
} catch (Exception e) {
tempClob.freeTemporary();
logger.error(e);
}
return tempClob;
}
but Now I am getting this ERROR:
ORA-00932: inconsistent datatypes: expected - got CLOB with my Update.
protected void updateComparisonPairListWithClobs(final List<ComparisonPair> pairList,
Connection conn) throws Exception {
logger.debug("Updating a Set of Comparison Pairs without clob data in Comparisons Table");
String updateSQL = " UPDATE COMPARISONS SET LAST_COMPARISON_DATE = ? , CORE_STATUS = ? , "
+ "LEGACY_STATUS = ? , LEGACY_TRANS_ID = ? , CORE_TRANS_ID = ?, DIFFERENCES = ?, "
+ "LEGACY_XML = ?, CORE_XML = ? "
+ " WHERE COMPARISON_ID = ? ";
PreparedStatement ps = null;
CLOB clobDiff = null;
CLOB clobCore = null;
CLOB clobLeg = null;
try {
ps = conn.prepareStatement(updateSQL);
for (ComparisonPair pair : pairList) {
Date utilDate = pair.getLastComparisonDate();
if (utilDate != null) {
ps.setTimestamp(1, new java.sql.Timestamp(utilDate.getTime()));
}
else {
ps.setTimestamp(1, null);
}
ps.setString(2, pair.getCoreStatus());
ps.setString(3, pair.getLegacyStatus());
ps.setString(4, pair.getLegacyTransactionId());
ps.setString(5, pair.getCoreTransactionId());
ps.setString(6, pair.getComparisonId());
clobDiff = getCLOB(pair.getDifferencesXML(), conn);
ps.setObject(7, clobDiff);
clobCore = getCLOB(pair.getCoreTransactionXML(), conn);
ps.setObject(8, clobCore);
clobLeg = getCLOB(pair.getLegacyTransactionXML(), conn);
ps.setObject(9, clobLeg);
ps.addBatch();
}
ps.executeBatch();
}
catch (Exception e) {
logger.error(e);
throw e;
}
finally {
releaseResources(null, null, ps);
}
}
I have ran out of ideas. please help.
[Updated on: Mon, 18 May 2009 12:18] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 03:41:39 CST 2025
|