Bug in jdbc timestamp escaping in Oracle 9i?

From: Till Kahle (kahle_at_weikatec.com)
Date: 10/05/04 

Date: 5 Oct 2004 03:55:56 -0700

Hi all,

has anybody experienced this in Oracle:

Issue a jdbc query using a timestamp like:
  "select * from mytable where postDate>={ts '2004-10-05 10:00:00'}"
-> oracle 8i: everything works fine
-> oracle 9i: the query result is correct but oracle doesn't use
   an index on the postDate column

The behaviour is similar to a query using the wrong constant type:
  "select * from mytable where postDate>='2004-10-05 10:00:00'"
  (if nls_date_format is set accordingly)

I have reproduced the problem in Oracle 9.2.0.4 with JDBC
versions 9.2.0.3 and 9.2.0.5.
Of course there are possible workarounds like bind variables
and using to_date(...) in the query, but I'd rather see
Oracle 9i JDBC translating the {ts} escapes correctly.

Is this bug fixed in later Oracle versions? Or am I
missing something fundamental about timestamp escaping?

Till

Relevant Pages

  • Re: Views - performance
    ... when u use stored object views oracle has to read the data dictionary ... this includes lots of steps for execution ... semantics of the query and fires it ... you'll possibly have any idea is to use explain plan to provide a base ...
    (comp.databases.oracle.server)
  • RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    ... can pass strings as varchar's and have them inserted ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ...
    (perl.dbi.users)
  • Re: Equilevant of Oracle for optional records
    ... and a normalized t2 could be more useful, to build the query, and once this ... the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • Re: Equilevant of Oracle for optional records
    ... this query is done, then flatten the result? ... example, the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    ... can pass strings as varchar's and have them inserted ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ...
    (perl.dbi.users)