我直接作用于表的查询在写入视图时不起作用

本文介绍了我直接作用于表的查询在写入视图时不起作用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

Oracle 12c，ColdFusion 2018

Oracle 12c, ColdFusion 2018

我在两个不同的数据库中有两个相同的表.有一个视图使用 UNION 将数据连接到一个数据集，使用数据库链接.该视图有效并且没有错误.

I have two identical tables in two different databases. There is a view that uses UNION to join the data into one data set, using a database link. The view works and has no errors.

当我从 ColdFusion 查询视图时，我收到无效月份"错误.

When I query the view from ColdFusion I get a "not a valid month" error.

当我只查询其中一个表(而不是视图)时，我没有得到同样的错误.

When I query just one of the tables (and not the view), I do not get the same error.

知道为什么会发生这种情况吗?

Any idea why this would happen?

Table1 in database1 EmployeeID (number) ShiftCode (varchar2) ShiftTime (date) Table2 in database2 EmployeeID (number) ShiftCode (varchar2) ShiftTime (date) ViewBothTables - Table1 and table2 created in database1 SELECT EmployeeID, ShiftCode, ShiftTime FROM Table1 UNION SELECT EmployeeID, ShiftCode, ShiftTime FROM USER.Table2@databaseConnection2 Query1 in ColdFusion - This works - returns 48 rows SELECT employeeID, ShiftCode, ShiftTime FROM USER.table1@databaseConnection1 Where ShiftTime <= #thisSchedWeekEnd# AND ShiftTime >= #thisSchedWeekStart# Query2 in ColdFusion - This works - returns 10 rows SELECT employeeID, ShiftCode, ShiftTime FROM USER.table2@databaseConnection2 Where ShiftTime <= #thisSchedWeekEnd# AND ShiftTime >= #thisSchedWeekStart# Query 3 in ColdFusion - this gives me the error SELECT employeeID, ShiftCode, ShiftTime FROM USER.viewBothTables@databaseConnection1 Where ShiftTime <= #thisSchedWeekEnd# AND ShiftTime >= #thisSchedWeekStart# [Macromedia][Oracle JDBC Driver][Oracle]ORA-01843: not a valid month ORA-02063: preceding line from databaseConnection1

结构相同，Table1和Table2是两个不同数据库中相同表(不同人)的副本.该视图有效，并且是可见的(哈哈)我正在从第三个数据库查询.ColdFusion 日期变量在所有三个示例中都是相同的，因为它三次都是相同的变量.前两个查询有效.第三个说无效月份.

The structures are identical, Table1 and Table2 are copies of the same tables (with different people) in two separate databases. The view works, and is viewable (ha ha) I am querying from a third database. The ColdFusion date variable is identical in all three examples, because it's the same variable all three times. The first two queries work. The third says invalid month.

我在页面上连续进行了所有三个查询，与上面完全一样.前两个返回数据，第三个错误输出.

I have all three queries in a row on the page, exactly as above. The first two return data, the third errors out.

是的，设置很愚蠢.我对此无能为力，它们是继承的系统，我无法更改，只能处理.

Yeah, the setup is stupid. I can't do much about it, they are inherited systems that I can't change and just have to deal with.

推荐答案

我认为 databaseConnection1 和 databaseConnection2 的日期格式是不同的.

I think the date format is different between databaseConnection1 and databaseConnection2.

这就是为什么对两个不同数据库的单独查询有效而对一个数据库的组合查询无效的原因.

That's why the individual query on the two different databases work and the combined query on one database doesn't work.

能否请您检查两个数据库中NLS_DATE_FORMAT 的下表.

Can you please check the below table in both databases for NLS_DATE_FORMAT.

select * from nls_session_parameters;

更新 1:-由于两个数据库的日期格式相同，请尝试对如下参数进行日期格式的显式转换

Update 1:-Since the date format are same for the two databases try explicit conversion of date format for the parameter like below

SELECT employeeID, ShiftCode, ShiftTime FROM USER.viewBothTables@databaseConnection1 Where ShiftTime <= #dateFormat(thisSchedWeekEnd, "mm/dd/yyyy")# AND ShiftTime >= #dateFormat(thisSchedWeekStart, "mm/dd/yyyy")#