Invalid column index exception while inserting into the DB

Question

I am getting this exception while inserting into the DB. Have attached the trace.

Caused by: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setTimestampInternal(OraclePreparedStatement.java:7256)
at oracle.jdbc.driver.OraclePreparedStatement.setTimestamp(OraclePreparedStatement.java:7240)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setTimestamp(OraclePreparedStatementWrapper.java:302)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.setTimestamp(WrappedPreparedStatement.java:554)

My Query is

INSERT INTO SP_OPS_TEAM(
PERSON_ID
,FROM_DT
,THRU_DT
,REPORTS_TO
,CASE_MAX_ALLOWED
,PAY_AUTH_MAX
,LUMP_SUM_MAX,DAYS_MAX
,HAS_DENIAL_AUTH_IND
,HAS_MEDICAL_AUTH_IND
,OPS_PERS_TYPE_CD
,CR_TS
,CR_USER
,LST_UPDT_TS
,LST_UPDT_USER) 
VALUES (
?
,to_date(to_char(sysdate,'yyyy/MM/dd'),'yyyy/MM/dd')
,to_date('12/31/2099','MM/DD/YYYY')
,?
,?
,?
,?
,?
,?
,?
,?
,CURRENT_DATE
,?
,CURRENT_DATE
,?)

And the params as as follows

[6804
, Fri Aug 17 10:59:24 IST 2012
, Fri Aug 17 10:59:24 IST 2012
, 6803
, null
, null
, null
, null
, N
, N
, TM
, Fri Aug 17 10:59:24 IST 2012
, 6803
, Fri Aug 17 10:59:24 IST 2012
, 6803]

Im getting this exception at the 14th index which is LST_UPT_TS the data type of this column in db is SYSTIMESSTAMP.

desc SP_OPS_TEAM
Name                 Null     Type              
-------------------- -------- ----------------- 
PERSON_ID            NOT NULL NUMBER            
FROM_DT              NOT NULL DATE              
THRU_DT              NOT NULL DATE              
REPORTS_TO           NOT NULL NUMBER(22)        
CASE_MAX_ALLOWED              NUMBER(5)         
PAY_AUTH_MAX                  NUMBER(10,4)      
LUMP_SUM_MAX                  NUMBER(10,4)      
DAYS_MAX                      NUMBER(5)         
HAS_DENIAL_AUTH_IND  NOT NULL CHAR(1 CHAR)      
HAS_MEDICAL_AUTH_IND NOT NULL CHAR(1 CHAR)      
OPS_PERS_TYPE_CD     NOT NULL VARCHAR2(25 CHAR) 
CR_TS                NOT NULL TIMESTAMP(6)      
CR_USER              NOT NULL NUMBER(22)        
LST_UPDT_TS          NOT NULL TIMESTAMP(6)      
LST_UPDT_USER        NOT NULL NUMBER(22)        

Any help would be appreciated

Answer 1

You said you're 'getting this exception at the 14th index', and it seems to be from something like stmt.setTimestamp(14, ...). You are inserting 15 columns, but you only have 11 positional parameters (?), the other 4 are being set within the query - with the to_date() and CURRENT_DATE clauses. So, 14 is indeed not going to be a valid index, you would get that error with anything above 11, and you'd probably have some data conversion issues with others.

Based on what you've shown, you don't need to attempt to set any date/timestamp values, so you should not have any stmt.setDate() or stmt.setTimestamp() calls at all.

Also, as APC noted you're using CURRENT_DATE for two TIMESTAMP columns; you could use CURRENT_TIMESTAMP instead if you need the precision. And if the idea behind the to_date(to_char(sysdate,...),...) is just to lose the time portion you could use trunc(sysdate) instead to make it clearer and simpler.

Those aren't causing any immediate issues though. I have more of a problem with using 12/31/2099 as a magic date; that's going to cause someone a headache one day, and looks like a repeat of Y2K waiting to happen. (Of course we'll all hopefully be retired before it becomes an issue, but still...)

Answer 2

CURRENT_DATE is a date datatype not a timestamp. However, they should be freely convertible.

I'm a bit puzzle as to how your parameters line up with the ? in your query. Why are you passing data values when you're using system built-in values to populate them?

Anyhow Fri Aug 17 10:59:24 IST 2012 is not in one of Oracle's defaultt formats. So unless your NLS settings are different you will need to include an explicit format mask.

And you ddon't need that TO_DATE(TO_CHAR()) hoopla with sysdate. All dates are stored in Oracle's canonical format: masks are just for formatting input or output.