Re: Oracle 9 TIMESTAMP ORA-03115 on SELECT



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


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


.

Relevant Pages

  • Re: Timestamp Value - please help
    ... The following topic in MSDN gives an example of converting hex to string. ... Private mintEmpID As Integer ... 'Pass the timestamp to conversion function. ... I retrieve the updated TS for future updates. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: yamp yet another multiline post
    ... start app timestamp ... return string from application ... app complete timestamp ... Mike Dundas ...
    (comp.lang.awk)
  • Re: yamp yet another multiline post
    ... start app timestamp ... return string from application ... app complete timestamp ... moving solaris awk to awk.old and linking xpg4 awk instead? ...
    (comp.lang.awk)
  • Re: yamp yet another multiline post
    ... Mike Dundas wrote: ... start app timestamp ... return string from application ... app complete timestamp ...
    (comp.lang.awk)
  • yamp yet another multiline post
    ... I need to clean up a log file that was produced when the logging level set ... start app timestamp ... return string from application ... app complete timestamp ...
    (comp.lang.awk)