Oracle OCI：带日期字段的查询
使用 OCI 编译的客户端：10.2.0.4.0
服务器：Oracle9i Enterprise Edition Release 9.2.0.4.0
有问题的查询是：
SELECT CODIGO FROM LOG WHERE TEL = :telnumber AND DATE_PROC = '05-JUL-08'
表描述：
SQL>describe LOG;
TEL NOT NULL VARCHAR2(15)
CODIGO NOT NULL VARCHAR2(20)
DATE_PROC NOT NULL DATE
看起来很简单，当使用 SQLPlus 直接在服务器上执行时，它会返回结果，但是当从使用 OCI 的应用程序执行时，此查询返回OCI_NO_DATA 始终。一开始，日期值也是一个占位符，但我发现即使给出像 '05-JUL-08' 这样的文字也不起作用。我尝试了以下操作：
- 我尝试了基础知识：从客户端查询数据库确实有效。正是这个给我带来了麻烦
以下方法确实有效：
从日志中选择 CODIGO，其中 TEL = :telnumber在服务器和客户端中的查询之前执行
ALTER SESSION SET NLS_DATE_FORMAT="DD-MM-YYYY";。结果相同：服务器返回数据，客户端OCI_NO_DATA- 尝试更改
DATE_PROC格式，并将其与TO_DATE()的使用结合起来。相同的结果。 - 搜索了，搜索了，搜索了。没有答案
我有点渴望找到答案，希望得到任何帮助，并可以根据需要提供尽可能多的进一步详细信息。谢谢。
--- 更多信息 ---
update log set DATE_PROC = TO_DATE('20080705162918', 'YYYYMMDDHH24MISS') where CODIGO='BancoOne';
我尝试了使用 trunc() 和“alter session set nls_date_format”的不同组合...这就是我得到的：
SELECT CODIGO FROM LOG WHERE TEL = 11223344 AND DATE_PROC = TO_DATE('20080705162918', 'YYYYMMDDHH24MISS');
在服务器中：返回：“BancoOne”（物有所值）
在 OCI 应用程序中：返回 OCI_NO_DATA
SELECT CODIGO FROM LOG WHERE TEL = 11223344 AND trunc(DATE_PROC) = TO_DATE('20080705', 'YYYYMMDD');
在服务器中：返回：“BancoOne”
在 OCI 应用程序中：返回“BancoOne”
所以问题是，如果 OCI 应用程序都访问同一数据库服务器，为什么会给出不同的结果？
另外，为了澄清 OCI 应用程序的用途：它有一个由用户配置的查询。这个想法是，用户将根据需要调整查询以适应目标数据库中存在的日期字段，这就是为什么我不应该在代码中包含“alter session set nls_date_format”语句，因为我不知道日期格式。这样我想为用户提供灵活性，并且不依赖于特定的日期格式。这有道理吗？有什么建议吗？
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 = :telnumberExecuting
ALTER SESSION SET NLS_DATE_FORMAT="DD-MM-YYYY";before the query in both the server and the client. Same result: server returns data, clientOCI_NO_DATA- Tried changing
DATE_PROCformat, combining this with the use ofTO_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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您的列 DATE_PROC 是日期，您应该始终将其与日期进行比较，并且从不依赖隐式数据转换。
试试这个：
或者这个：
如果可以的话，请避免在代码中使用使用字母表示月份的日期格式（因为当您更改默认语言时，代码将会失败 ）并且只有两个字符代表年份（世纪的歧义）。我喜欢使用
'YYYY/MM/RR'因为这种格式将按原始日期排序。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:
or this:
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.记录是如何插入的？
日期字段确实存储时间信息，因此当使用 sysdate 插入记录时，日期字段将包含当天记录的不同“值”。当您执行 date_proc = '05-JUL-08' 时，您所说的 date_proc 恰好等于 2008 年 7 月 5 日的 12:01:00。如果你用sysdate插入了12:01的记录，则不会被返回。您是否尝试过使用 Between 或 trunc ？
这是一个例子：
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:
我想说你问错了问题。
Oracle 依靠共享 SQL 来提高性能，当您插入字符串（如“20080705”）时，SQL 无法共享。详细信息此处。
因此数据值应该是占位符并且应该是正确的数据类型（DATE）。 此处的示例应该有助于做到这一点。
也就是说，如果这可行
，但这不行，
我会尝试
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
but this doesn't
I'd be trying
我们刚刚遇到了这个错误（OCI_NO_DATA），这是由于某人更改了电脑上的时间而引起的。当他们将日期/时间调回到正确的时间时，应用程序开始正常工作。
We just had this error (OCI_NO_DATA) get caused by someone changing the hour of the day on their pc. When they put the date/time back to the right time, the application started working ok.