Oracle OCI: Problem in Query with Date field [message #439254] |
Fri, 15 January 2010 06:52  |
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 #439560 is a reply to message #439281] |
Mon, 18 January 2010 06:44   |
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?
|
|
|
|