Oracle OCI: Problem in 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
• SELECT CODIGO FROM LOG WHERE TEL = :telnumber does work
• 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

How were the records inserted?

Date fields do store time information, and thus when inserting records using sysdate, the date field will contain different "values" for records within that day. When you execute date_proc = '05-JUL-08', you are saying where date_proc equals exactly the 5th of July 2008 at 12:01:00. If you inserted the record at 12:01 with sysdate, it will not be returned. Have you tried using a between or trunc?

Here is an example:

drop table test_date;
create table test_date (id number, ud date);

insert into test_date values (1, '15-jan-10');
insert into test_date values (2, '15-jan-10');
insert into test_date values (3, '15-jan-10');
insert into test_date values (6, sysdate);    -- sysdate as of writing is 15-JAN-2010 08:01:55
insert into test_date values (7, sysdate);    -- sysdate as of writing is 15-JAN-2010 08:01:55
insert into test_date values (8, '16-jan-10');
commit;

select id, ud, to_char(ud, 'dd-MON-yyyy HH:MM:SS') from test_date where ud = '15-jan-10';
---------------------- ------------------------- --------------------
1                      15-JAN-10                 15-JAN-2010 12:01:00                                                        
2                      15-JAN-10                 15-JAN-2010 12:01:00                                                        
3                      15-JAN-10                 15-JAN-2010 12:01:00      

select id, ud, to_char(ud, 'dd-MON-yyyy HH:MM:SS') from test_date where trunc(ud) = '15-jan-2010';
---------------------- ------------------------- --------------------
1                      15-JAN-10                 15-JAN-2010 12:01:00                                                        
2                      15-JAN-10                 15-JAN-2010 12:01:00                                                        
3                      15-JAN-10                 15-JAN-2010 12:01:00                                                        
6                      15-JAN-10                 15-JAN-2010 08:01:55                                                        
7                      15-JAN-10                 15-JAN-2010 08:01:55

Answer 2

Hi Toni,

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.

Answer 3

I'd say you are asking the wrong question.

Oracle relies on sharing SQL for performance and when you stick a string in, like '20080705', the SQL can't be shared. Details here.

So the data value should be a placeholder and should be of the correct data-type (DATE). The example here should help to do that.

That said, if this works

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

but this doesn't

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

I'd be trying

SELECT TO_CHAR(DATE_PROC,'DD-MM-YYYY HH24:MI:SS') FROM LOG 
WHERE TEL = 11223344 AND 
trunc(DATE_PROC) = TO_DATE('20080705', 'YYYYMMDD');