Java, JDBC driver 9201 causes ORA-30081

From: RandomWinds <jdx80_at_hotmail.com>
Date: 16 Jul 2002 02:05:00 -0700
Message-ID: <46bdc4cd.0207160104.642045c3@posting.google.com>

Hi all,

I noticed the following with the latest JDBC 9.2.0.1 (http://technet.oracle.com/software/tech/java/sqlj_jdbc/content.html)

The following SQL fails with
SQLException: ORA-30081: invalid data type for datetime/interval arithmetic

The same SQL works OK with older JDBC drivers, including 9.0.1

The SQL:
  update TABLE_NAME set NUM_COL=NUM_COL + (? - DATE_COL)*86400   where KEY_COL=1

I'm binding one java.sql.Timestamp argument.

anyone noticed this behaviour? am I doing something illegal?

I know that there are several ways to rewrite the SQL so it will work (e.g. use of to_date()) but I mainly need to know if this behaviour can be considered as a bug of the JDBC driver.

The table:

create table TABLE_NAME (

    KEY_COL NUMBER NOT NULL,
    NUM_COL NUMBER DEFAULT 0,
    DATE_COL DATE
    )
/
insert ito TABLE_NAME (KEY_COL,NUM_COL,DATE_COL) values (1,0,SYSDATE)

Full stack trace
java.sql.SQLException: ORA-30081: invalid data type for datetime/interval arithmetic

 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
 at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
 at oracle.jdbc.ttc7.Oall7.receive(Oall7.java, Compiled Code)
 at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java, Compiled

Code)
 at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)  at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java, Compiled Code)

 at oracle.jdbc.driver.OracleStatement.doExecuteOtherOracleStatement.java:1940)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
 at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)

Thanks

Avi Algazy
avi.algazy_at_viryanet.com
www.ViryaNet.com Received on Tue Jul 16 2002 - 04:05:00 CDT