StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle OCI: Query with Date field
text
Body
copied!<p>Client compiled with OCI: 10.2.0.4.0<br> Server: Oracle9i Enterprise Edition Release 9.2.0.4.0</p> <p>The problematic query is:</p> <pre><code>SELECT CODIGO FROM LOG WHERE TEL = :telnumber AND DATE_PROC = '05-JUL-08' </code></pre> <p>Table description:</p> <pre><code>SQL>describe LOG; TEL NOT NULL VARCHAR2(15) CODIGO NOT NULL VARCHAR2(20) DATE_PROC NOT NULL DATE </code></pre> <p>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 <code>OCI_NO_DATA</code> <strong>always</strong>. In the beginning, the date value was also a placeholder, but I found out that even giving a literal like <code>'05-JUL-08'</code> didn't work. I have tried the following:</p> <ul> <li>I've tried the basics: querying the DB from the client does work. It's this one that gives me trouble</li> <li><p>The following does work:</p> <pre><code>SELECT CODIGO FROM LOG WHERE TEL = :telnumber </code></pre></li> <li><p>Executing <code>ALTER SESSION SET NLS_DATE_FORMAT="DD-MM-YYYY";</code> before the query in both the server and the client. Same result: server returns data, client <code>OCI_NO_DATA</code></p></li> <li>Tried changing <code>DATE_PROC</code> format, combining this with the use of <code>TO_DATE()</code>. Same result.</li> <li>Searched, searched, searched. No answer</li> </ul> <p>I'm a bit desperate to find an answer, would appreciate any help and can provide as many further details as needed. Thanks.</p> <p>--- <b> Further info </b> ---</p> <pre><code>update log set DATE_PROC = TO_DATE('20080705162918', 'YYYYMMDDHH24MISS') where CODIGO='BancoOne'; </code></pre> <p>I have tried different combinations using trunc() and "alter session set nls_date_format"... and this is what I get:</p> <pre><code>SELECT CODIGO FROM LOG WHERE TEL = 11223344 AND DATE_PROC = TO_DATE('20080705162918', 'YYYYMMDDHH24MISS'); </code></pre> <p>In server: Returns: "BancoOne" (good value)<br> In OCI app: <b>Returns OCI_NO_DATA</b></p> <pre><code>SELECT CODIGO FROM LOG WHERE TEL = 11223344 AND trunc(DATE_PROC) = TO_DATE('20080705', 'YYYYMMDD'); </code></pre> <p>In server: Returns: "BancoOne"<br> In OCI app: Returns "BancoOne"</p> <p>So the point is, why is the OCI app giving different results if both are accessing the same DB server?</p> <p>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?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload