Oracle 9 TIMESTAMP ORA-03115 on SELECT

mdwoolley

unread,

Dec 29, 2005, 4:40:24 AM12/29/05

to

Hi

I am struggling to access rows in a table which contains a TIMESTAMP
column. I get "ORA-03115: unsupported network datatype or
representation" when executing a select statement. I am trying to
isolate the problem by testing against a very simple table and with a
simple test application.

Here's the table:

Name Null? Type
----------------------------------------- --------
----------------------------
NOW TIMESTAMP(0)

Here's the test application:

import java.text.SimpleDateFormat;
import java.util.*;
import java.io.*;
import java.sql.*;
import oracle.sql.TIMESTAMP;

public class OracleDateTime {

public static void main(String args[]) throws Exception {
System.out.println("OracleDateTime V1.06");
Connection conn = null;
try {
String driverClassname = "oracle.jdbc.driver.OracleDriver";
String jdbcURL = "jdbc:oracle:thin:@localhost:1521:GANDALF";
String username = "seleniumsms";
String password = "password";
Class.forName(driverClassname);
conn = DriverManager.getConnection(jdbcURL, username, password);
System.out.println("Got connection....");
} catch (Exception e) {
System.out.println(e);
e.printStackTrace();
}
String insert = "insert into test values(?)";
String select = "select * from test";
PreparedStatement psinsert = conn.prepareStatement(insert);
System.out.println("About to set timestamp in prepared statement");
psinsert.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
System.out.println("Done");
PreparedStatement psselect = conn.prepareStatement(select);
psinsert.executeUpdate();
conn.commit();
System.out.println("Inserted record");
SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss");
System.out.println("About to execute SELECT");
ResultSet rs = psselect.executeQuery();
System.out.println("Done");
while (rs.next()) {
System.out.println("About to get date/time");
java.sql.Date d = rs.getDate(1);
System.out.println("Done");
System.out.println("Timestamp is : "+df.format(d));
}
}

}

And here's the output:

OracleDateTime V1.06
Got connection....
About to set timestamp in prepared statement
Done
Inserted record
About to execute SELECT
Exception in thread "main" java.sql.SQLException: ORA-03115:
unsupported network datatype or represe
ntation

at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.v8Odscrarr.receive(v8Odscrarr.java:191)
at
oracle.jdbc.ttc7.TTC7Protocol.describe(TTC7Protocol.java:586)
at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:647)
at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1674)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1870)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363
)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:314)

at OracleDateTime.main(OracleDateTime.java:38)

And running the same SELECT from SQL*PLUS yields:

NOW
---------------------------------------------------------------------------
29-DEC-05 09.27.00

So in summary:

I can insert a record containing a valid TIMESTAMP, complete with
hours, minutes and seconds. But as soon as I try to retrieve the record
I get the ORA-03115.

I'm using the thin JDBC driver.

Hope someone can help

Thanks

Joe Weinstein

unread,

Dec 29, 2005, 11:09:24 AM12/29/05

to mdwoolley

mdwoolley wrote:
> Hi
>
> I am struggling to access rows in a table which contains a TIMESTAMP
> column. I get "ORA-03115: unsupported network datatype or
> representation" when executing a select statement. I am trying to
> isolate the problem by testing against a very simple table and with a
> simple test application.

Hi. I suspect your problem is that you're using a very old
version of the thin driver. Try downloading a new one from
Oracle. Also, don't call getDate() to get the data unless
you specifically want to lose the time portion of the data.
GetTimeStamp() is the right call.
HTH,
Joe Weinstein at BEA Systems

mdwoolley

unread,

Dec 30, 2005, 5:22:28 AM12/30/05

to

Thanks Joe. You're probably right. After more
banging-of-head-against-wall I spotted the usual "schoolboy error". I
was using the thin driver for Java 1.3 rather than 1.4. I was getting
various other strange results, over and above the date related stuff.
These miscellaneous issues have now gone away but I have yet to retest
the original problem, having given up and decided to store my dates as
milliseconds since 1970 values.

I'll dig out the test program again and see if I can now handle dates
as expected.

Thanks for posting.

Martin

mdwoolley

unread,

Dec 30, 2005, 5:41:37 AM12/30/05

to

OK, I re-ran the test app, having modified it to use getTimestamp().
Unfortunately it now fails on executing the select * from test
statement with:

About to execute SELECT
Exception in thread "main" java.sql.SQLException: Bigger type length
than Maximum

Any idea why it would do this?

Thanks in anticipation

Martin

Joe Weinstein

unread,

Dec 30, 2005, 11:59:08 AM12/30/05

to mdwoolley

mdwoolley wrote:

These are all internal Oracle problems. Hmmmm....
I just ran this code:

System.out.println("The driver is " + c.getMetaData().getDriverVersion() );
System.out.println("The DBMS is " + c.getMetaData().getDatabaseProductVersion() );

Statement s = c.createStatement();
try{s.executeUpdate("drop table joetest");} catch (Exception ignore){}
s.executeUpdate("create table joetest(bar timestamp )");

Timestamp t = new java.sql.Timestamp((new java.util.Date()).getTime());
System.out.println( t );

PreparedStatement p = c.prepareStatement("insert into joetest values(?)");
p.setTimestamp(1, t);
p.executeUpdate();
ResultSet r = s.executeQuery("select * from joetest");
while (r.next()) System.out.println( r.getTimestamp(1) );

I got:

The driver is 10.1.0.4.0
The DBMS is Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
2005-12-30 08:56:36.069
2005-12-30 08:56:36.069

mdwoolley

unread,

Dec 30, 2005, 12:37:50 PM12/30/05

to

Hi Joe

good news.....

I ran your code and got:

The driver is 9.2.0.1.0
The DBMS is Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
2005-12-30 17:31:09.968
2005-12-30 17:31:09.968

And of course the key thing is that I didn't get the "Bigger type.."
error!

So I took a closer look at your code versus mine and noticed that:

Your create table... results in a column of type TIMESTAMP(6) whereas
my schema defination has TIMESTAMP(0). So I changed yours to create a
column as TIMESTAMP(0) and hey presto, the error occurs.

Perhaps I misunderstood the meaning of the parameter in brackets, but I
thought it meant the number of decimal places for fractions of a
second. I wanted accuracy to within a second, so "0" seemed sensible.

Anyway, making mine TIMESTAMP(6) makes things work as I'd originally
wanted.

Thanks for taking the time to help, Joe.

Regards

Martin

Joe Weinstein

unread,

Dec 30, 2005, 1:10:50 PM12/30/05

to mdwoolley

mdwoolley wrote:

I'm glad to help. I tried the timestamp(0), and that
worked for me too (with the latest driver and DBMS),
they must have fixed the issue recently. The (0) means
what you expected it to, but the 9.0 DBMS wasn't truncating
the incoming datum to fit the table column:

s.executeUpdate("create table joetest(bar timestamp(0) )");

The driver is 10.1.0.4.0
The DBMS is Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

2005-12-30 10:06:17.975
2005-12-30 10:06:18.0

mdwoolley

unread,

Dec 31, 2005, 1:47:39 AM12/31/05

to

Excellent!

I'll be testing my application with Oracle 10 next, so it's nice to
know things are probably as they should be at that release. At least
where this issue is concerned! I look forward to uncovering some other
issues instead to drive me a little crazy ;-)

All the best

Martin