Home » Developer & Programmer » Precompilers, OCI & OCCI » Oracle OCI: Problem in Query with Date field (OCI: 10.2.0.4.0)
Oracle OCI: Problem in Query with Date field [message #439254] Fri, 15 January 2010 06:52 Go to next message
toniwantstoknow
Messages: 2
Registered: January 2010
Junior Member
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.
Re: Oracle OCI: Problem in Query with Date field [message #439281 is a reply to message #439254] Fri, 15 January 2010 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ "I've tried the basics: querying the DB from the client does work. It's this one that gives me trouble"
Both have not the same NLS settings

2/ "SELECT CODIGO FROM LOG WHERE TEL = :telnumber does work"
OK

3/ "Executing ALTER SESSION SET NLS_DATE_FORMAT="DD-MM-YYYY"; "
'05-JUL-08' does not satisfy the format DD-MM-YYYY

4/ "Tried changing DATE_PROC format, combining this with the use of TO_DATE(). Same result."
If you use no format or the same format than in 3, it is normal

5/ "Searched, searched, searched. No answer"
Just use the correct format in your date string or in your TO_DATE format parameter.
Datetime Format Models

Regards
Michel


Re: Oracle OCI: Problem in Query with Date field [message #439560 is a reply to message #439281] Mon, 18 January 2010 06:44 Go to previous messageGo to next message
toniwantstoknow
Messages: 2
Registered: January 2010
Junior Member
Hi,

I narrowed down the problem a bit. Also changed the field names. I find this very annoying and need to know how I should proceed to get this properly done. Would really appreciate your help.

The DB:

describe VTA_LOG;
Name Null? Type
----------------------------------------- -------- ----------------------------
TELEFONO NOT NULL VARCHAR2(15)
CODIGO_BANCO NOT NULL VARCHAR2(20)
FECHA_PROCESO NOT NULL DATE



The Data:

ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDHH24MISS';
select * from VTA_LOG;
TELEFONO CODIGO_BANCO FECHA_PROCESO
--------------- -------------------- --------------
11223344 BancoOne 20080705162918
11223344 BancoTwo 20080705062918



In the OCI App I:

- Bind :msisdn as SQLT_STR
- Bind :datetime as SQLT_STR.

Then the query is:

SELECT CODIGO_BANCO FROM VTA_LOG WHERE TELEFONO = :msisdn AND FECHA_PROCESO = TO_DATE(:datetime, 'YYYYMMDDHH24MISS')



Now, if I pass the string "20080705162918" to the bind function, I get the correct value returned.
If I pass the string "20080705062918" to the bind function, I get EMPTY result set. What's wrong with the date format? 06 is a valid value for HH24, right??

Again, this only happens from the OCI App, If I use SQLPlus directly on the server, I get the correct value every time. WHY?
Re: Oracle OCI: Problem in Query with Date field [message #439791 is a reply to message #439560] Tue, 19 January 2010 07:22 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe what is wrong is in the way you fetch the results.
Try to make your tests in the opposite order: first the value that does not work then the other one.

Regards
Michel

[Updated on: Tue, 19 January 2010 07:23]

Report message to a moderator

Previous Topic: .arr and .len
Next Topic: Pro*C Precompiler Hell (merged)
Goto Forum:
  


Current Time: Fri Mar 29 09:10:09 CDT 2024