Java Database Connectivity (JDBC)

Hi,

I am facing an issue which I am not able to resolve for almost 2 weeks. I was sure that it should be a version related issue, but am not able to figure out as I am having all as latest version.
i,e JDK 1.4.2 , tomcat 5.0.25 ,JDBC jar is OJDBC14.jar and database is Oracle9i 9.2.0.4

I am trying to insert values in a table which was extracted from excel sheet.

1) The excel sheet includes a field which is formatted to type text but actaully a number(eg: 000145728).In database it is of type VARCHAR.
2) There is a field which is of currency type in excel sheet and its type in database is NUMBER.
3) There are other two fields which has SYSDATE and TIMESTAMP as datatypes.

When I tried to execute the query in database directly throught TOAD it worked fine. i,e 1 row inserted.

But the query when it going through prepared statement, gives INVALID COLUMN TYPE as exception. So the problem exist only when I am trying to insert through code.

one more point tobe noted is it is using statement.setObject in the prepared statement.

here is the code:

try {
connection = DBConnection.getConnection(Constants.CURRENT_DB);

sql = "INSERT INTO FSDT7070 \n" +
"(CLSTR_DSTBR_ID_NO, " +
" DSTBR_ID_NO, " +
" DUNS_NO, " +
" TECH_SEGM_NM, " +
" ISRT_FLG, " +
" ENTR_ID, " +
" DATA_CMNT_TXT, " +
" CMNT_TXT, " +
" Z1_TGT_INCRMTL_AMT, " +
" Z2_TGT_INCRMTL_AMT, " +
" DSTBR_REP_NM, " +
" TXT_COMMENT_1, " +
" TXT_COMMENT_2, " +
" TXT_COMMENT_3, " +
" QUALIFIED, " +
" STOREROOM, " +
" ENTR_TS, " +
" INSERT_TS, \n" +
" DSTBR_TGT_PYR1_AMT) \n" +
"SELECT \n" +
" CLSTR_DSTBR_ID_NO, " +
" DSTBR_ID_NO, " +
" DUNS_NO, " +
" TECH_SEGM_NM, " +
" 'N' ISRT_FLG, " +
" ? ENTR_ID, " +
" ? DATA_CMNT_TXT, " +
" ? CMNT_TXT, " +
" ? Z1_TGT_INCRMTL_AMT, " +
" ? Z2_TGT_INCRMTL_AMT, " +
" ? DSTBR_REP_NM, " +
" ? TXT_COMMENT_1, " +
" ? TXT_COMMENT_2, " +
" ? TXT_COMMENT_3, " +
" ? QUALIFIED, " +
" ? STOREROOM, " +
" SYSDATE ENTR_TS, " +
" CURRENT_TIMESTAMP INSERT_TS, \n" +
" 0 DSTBR_TGT_PYR1_AMT \n" +
"FROM ABCD5360 \n" +
"WHERE DUNS_NO=? AND CLSTR_DSTBR_ID_NO=? AND TECH_SEGM_NM = ? AND NOT EXISTS \n" +
" (SELECT DUNS_NO FROM FSDT7070 WHERE CLSTR_DSTBR_ID_NO=ABCD5360.CLSTR_DSTBR_ID_NO AND \n" +
" DUNS_NO=ABCD5360.DUNS_NO AND TECH_SEGM_NM=ABCD5360.TECH_SEGM_NM AND ISRT_FLG='N')";
statement = connection.prepareStatement(sql);

values = new ArrayList();
values.add(userId);
values.add(record.getDataincfeedback());
values.add(record.getGencomment());
values.add(String.valueOf(record.getZ1Amount()));
values.add(String.valueOf(record.getZ2Amount()));
values.add(record.getDistrepname());
values.add(record.getComment1());
values.add(record.getComment2());
values.add(record.getComment3());
values.add(record.getQualified());
values.add(record.getStoreroom());
values.add(record.getDuns());
values.add(record.getCluster());
values.add(record.getTechseg());

debugSql = StringUtil.combineQueryValues(sql, values);
for (int i=0; i<values.size(); i++)
statement.setObject(i+1, values.get(i));

result = statement.executeUpdate();

}catch (java.sql.SQLException sqle){
logger.log(Level.SEVERE, debugSql, sqle);
throw sqle;
}finally{
closeResources(connection, statement, null);
}
}


Here i am pasting the log information :


Nov 6, 2009 2:18:33 AM com.racs.salesplan.sql.SalesPlanningSql updateRecord
SEVERE: INSERT INTO FSDT7070
(CLSTR_DSTBR_ID_NO, DSTBR_ID_NO, DUNS_NO, TECH_SEGM_NM, ISRT_FLG, ENTR_ID, DATA_CMNT_TXT, CMNT_TXT, Z1_TGT_INCRMTL_AMT, Z2_TGT_INCRMTL_AMT, DSTBR_REP_NM, TXT_COMMENT_1, TXT_COMMENT_2, TXT_COMMENT_3, QUALIFIED, STOREROOM, ENTR_TS, INSERT_TS,
DSTBR_TGT_PYR1_AMT)
SELECT
CLSTR_DSTBR_ID_NO, DSTBR_ID_NO, DUNS_NO, TECH_SEGM_NM, 'N' ISRT_FLG, 'SKANNA' ENTR_ID, NULL DATA_CMNT_TXT, NULL CMNT_TXT, '0' Z1_TGT_INCRMTL_AMT, '5000' Z2_TGT_INCRMTL_AMT, 'Svoboda, Jerry' DSTBR_REP_NM, NULL TXT_COMMENT_1, NULL TXT_COMMENT_2, NULL TXT_COMMENT_3, '' QUALIFIED, '' STOREROOM, SYSDATE ENTR_TS, CURRENT_TIMESTAMP INSERT_TS,
0 DSTBR_TGT_PYR1_AMT
FROM ABCD5360
WHERE DUNS_NO='008679040' AND CLSTR_DSTBR_ID_NO='APR0044' AND TECH_SEGM_NM = 'SEN, SFTY, AND CONN' AND NOT EXISTS
(SELECT DUNS_NO FROM FSDT7070 WHERE CLSTR_DSTBR_ID_NO=ABCD5360.CLSTR_DSTBR_ID_NO AND
DUNS_NO=ABCD5360.DUNS_NO AND TECH_SEGM_NM=ABCD5360.TECH_SEGM_NM AND ISRT_FLG='N')
java.sql.SQLException: Invalid column type
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:6414)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:1354)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2791)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3217)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165)
at com.racs.salesplan.sql.SalesPlanningSql.updateRecord(SalesPlanningSql.java:2310)

Pleae help me in this.

Thanks.