Oracle OCI: Query with Date field

Question

Client compiled with OCI: 10.2.0.4.0
Server: Oracle9i Enterprise Edition Release 9.2.0.4.0

The problematic query is:

SELECT CODIGO FROM LOG WHERE TEL = :telnumber AND DATE_PROC = '05-JUL-08'

Table description:

SQL>describe LOG;

TEL NOT NULL VARCHAR2(15)
CODIGO NOT NULL VARCHAR2(20)
DATE_PROC NOT NULL DATE

As simple as it might look, when executed directly on the server with SQLPlus, it returns a result, but when executed from the app that uses OCI, this query returns OCI_NO_DATA always. In the beginning, the date value was also a placeholder, but I found out that even giving a literal like '05-JUL-08' didn't work. I have tried the following:

• I've tried the basics: querying the DB from the client does work. It's this one that gives me trouble

• The following does work:

  SELECT CODIGO FROM LOG WHERE TEL = :telnumber
  

• Executing ALTER SESSION SET NLS_DATE_FORMAT="DD-MM-YYYY"; before the query in both the server and the client. Same result: server returns data, client OCI_NO_DATA

• Tried changing DATE_PROC format, combining this with the use of TO_DATE(). Same result.
• Searched, searched, searched. No answer

I'm a bit desperate to find an answer, would appreciate any help and can provide as many further details as needed. Thanks.

--- Further info ---

update log set DATE_PROC = TO_DATE('20080705162918', 'YYYYMMDDHH24MISS') where CODIGO='BancoOne';

I have tried different combinations using trunc() and "alter session set nls_date_format"... and this is what I get:

SELECT CODIGO FROM LOG WHERE TEL = 11223344 AND DATE_PROC = TO_DATE('20080705162918', 'YYYYMMDDHH24MISS');

In server: Returns: "BancoOne" (good value)
In OCI app: Returns OCI_NO_DATA

SELECT CODIGO FROM LOG WHERE TEL = 11223344 AND trunc(DATE_PROC) = TO_DATE('20080705', 'YYYYMMDD');

In server: Returns: "BancoOne"
In OCI app: Returns "BancoOne"

So the point is, why is the OCI app giving different results if both are accessing the same DB server?

Also, to clarify the purpose of the OCI app: it has a query to be configured by the user. The idea is that the user will adapt the query as desired to fit with the Date field present in the destination DB, that's why I should not include "alter session set nls_date_format" statements in my code, as I will not know the date format. This way I want to provide flexibility to the user, and don't rely on specific date formats. Does this make sense? Any suggestions?

Answer 1

your column DATE_PROC is a DATE, you should always compare it to a date and never rely on the implicit data conversion.

Try this:

SELECT CODIGO FROM LOG WHERE TEL = :telnumber AND DATE_PROC = DATE '2008-07-05'

or this:

SELECT CODIGO 
  FROM LOG 
 WHERE TEL = :telnumber 
   AND DATE_PROC = to_date('05-JUL-08', 'DD-MON-RR')

If you can, refrain from using a date format in your code that uses letters for months (because the code will fail when you change the default language) and only two chars for years (ambiguity of century). I like to use 'YYYY/MM/RR' because this format will be sorted as the original date.