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 Go to next message
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

Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #403787 is a reply to message #403785] Mon, 18 May 2009 13:02 Go to previous message
akabir77
Messages: 6
Registered: May 2009
Junior Member
ok found my problem. It had nothing to do with CLOB. my SQL was wrong.
Previous Topic: Composite range-range table
Next Topic: How to make output from shah to s***
Goto Forum:
  


Current Time: Fri Dec 09 23:24:03 CST 2016

Total time taken to generate the page: 0.09065 seconds