时间戳

首页 > 解决方案 > 链接服务器“FDMEE_SIMPHONY”的 OLE DB 提供程序“OraOLEDB.Oracle”返回消息“ORA-00903：无效表名”

c# - 链接服务器“FDMEE_SIMPHONY”的 OLE DB 提供程序“OraOLEDB.Oracle”返回消息“ORA-00903：无效表名”

问题描述

我正在尝试在两个链接服务器之间运行此查询，但它返回消息ORA-00903Invalid table。

- 代码

SELECT * FROM OPENQUERY ([Database1],
              'SELECT ''Simphony_ARCS-Operational'', ''Mar'', ''03'', ''2020'', ''DayPlaceholder''AS DAY, 
              SUM((MENU_ITEM_DAY_PART_TOTAL.SALESTOTAL) - (MENU_ITEM_DAY_PART_TOTAL.DISCOUNTTOTAL)) AS NETSALESTOTAL, 
              MENU_ITEM_DAY_PART_TOTAL.ORGANIZATIONID, 
              CONCAT(MENU_ITEM_DAY_PART_TOTAL.REVENUECENTERID, ''_REV'') AS REV_ID, 
              UPPER(CASE WHEN DAY_PART.NAME IS NULL THEN ''DINNER'' 
                       ELSE DAY_PART.NAME END) AS DAYPART, 
              COALESCE(sc.stringtext, scc.stringtext) as MenuItemClassName,
              sm.stringtext as MenuItemName,
              su.stringtext as ZoneORLocation
              FROM 
              --LOCATION_ACTIVITY_DB.MAJOR_GROUP 
              [10.xxx.1.xxx].[Database2].[TRANS].menu_item_definition d
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].string_table sm
              on d.name1id = sm.stringnumberid
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].menu_item_master m
              on d.menuitemmasterid = m.menuitemmasterid
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].hierarchy_structure s
              on d.hierstrucid = s.hierstrucid
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].hierarchy_unit u
              on s.hierunitid = u.hierunitid
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].string_table su
              on u.nameid = su.stringnumberid
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].menu_item_class c
              on d.hierstrucid = c.hierstrucid and d.menuitemclassobjnum = c.objectnumber
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].menu_item_class cc
              on s.parenthierstrucid = cc.hierstrucid and d.menuitemclassobjnum = cc.objectnumber
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].string_table sc
              on c.nameid = sc.stringnumberid
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].string_table scc
              on cc.nameid = scc.stringnumberid
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].sales_itemizer i
              on c.hierstrucid = i.hierstrucid and c.slsitmzrindex = i.slsitmzrindex 
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].sales_itemizer ii
              on cc.hierstrucid = ii.hierstrucid and cc.slsitmzrindex = ii.slsitmzrindex 
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].sales_itemizer pi
              on s.parenthierstrucid = pi.hierstrucid and c.slsitmzrindex = pi.slsitmzrindex  
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].string_table si
              on i.nameid = si.stringnumberid
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].string_table spi
              on pi.nameid = spi.stringnumberid
              left join [10.xxx.1.xxx].[SimphonyTables].[TRANS].string_table sii
              on ii.nameid = sii.stringnumberid
              inner join LOCATION_ACTIVITY_DB.REVENUE_CENTER rc
              on rc.revenuecenterposref = u.objectnumber
              inner join  LOCATION_ACTIVITY_DB.MENU_ITEM mi
              on m.objectnumber = mi.MENUITEMPOSREF
              right outer join LOCATION_ACTIVITY_DB.MENU_ITEM_DAY_PART_TOTAL midpt
              on  mi.menuitemid = midpt.menuitemid
              left outer join LOCATION_ACTIVITY_DB.DAY_PART dp
              on midpt.organizationid = dp.organizationid and midpt.daypartid = dp.daypartid
AND  MENU_ITEM_DAY_PART_TOTAL.DAYPARTID = DAY_PART.DAYPARTID 
              WHERE (midpt.ORGANIZATIONID = ''10260'') 
              AND BUSINESSDATE BETWEEN (TO_DATE(''01-Mar-20'',''DD-MON-YY'')) AND (TO_DATE(''31-Mar-20'',''DD-MON-YY'')) 
              AND mi.MENUITEMPOSREF = ''420000010''
              AND rc.REVENUECENTERPOSREF LIKE ''304%''
              GROUP BY  midpt.ORGANIZATIONID 
                             ,COALESCE(sc.stringtext, scc.stringtext)
                            ,sm.stringtext 
                             ,midpt.REVENUECENTERID 
                             ,dp.NAME
                             ,su.stringtext')

错误信息...

OLE DB provider "OraOLEDB.Oracle" for linked server "FDMEE_SIMPHONY" returned message "ORA-00903: invalid table name".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT 'Simphony_ARCS-Operational', 'Mar', '03', '2020', 'DayPlaceholder'AS DAY, 
              SUM((MENU_ITEM_DAY_PART_TOTAL.SALESTOTAL) - (MENU_ITEM_DAY_PART_TOTAL.DISCOUNTTOTAL)) AS NETSALESTOTAL, 
              MENU_ITEM_DAY_PART_TOTAL.ORGANIZATIONID, 
              CONCAT(MENU_ITEM_DAY_PART_TOTAL.REVENUECENTERID, '_REV') AS REV_ID, 
              UPPER(CASE WHEN DAY_PART.NAME IS NULL THEN 'DINNER' 
                       ELSE DAY_PART.NAME END) AS DAYPART, 
              COALESCE(sc.stringtext, scc.stringtext) as MenuItemClassName,
              sm.stringtext as MenuItemName,
              su.stringtext as ZoneORLocation
              FROM 
              --LOCATION_ACTIVITY_DB.MAJOR_GROUP 
              [10.118.1.135].[SimphonyTables].[TRANS].menu_item_definition d
              left join [10.118.1.135].[SimphonyTables].[TRANS].string_table sm
              on d.name1id = sm.stringnumberid
              left join [10.118.1.135].[SimphonyTables].[TRANS].menu_item_master m
              on d.menuitemmasterid = m.menuitemmasterid
              left join [10.118.1.135].[SimphonyTables].[TRANS].hierarchy_structure s
              on d.hierstrucid = s.hierstrucid
              left join [10.118.1.135].[SimphonyTables].[TRANS].hierarchy_unit u
              on s.hierunitid = u.hierunitid
              left join [10.118.1.135].[SimphonyTables].[TRANS].string_table su
              on u.nameid = su.stringnumberid
              left join [10.118.1.135].[SimphonyTables].[TRANS].menu_item_class c
              on d.hierstrucid = c.hierstrucid and d.menuitemclassobjnum = c.objectnumber
              left join [10.118.1.135].[SimphonyTables].[TRANS].menu_item_class cc
              on s.parenthierstrucid = cc.hierstrucid and d.menuitemclassobjnum = cc.objectnumber
              left join [10.118.1.135].[SimphonyTables].[TRANS].string_table sc
              on c.nameid = sc.stringnumberid
              left join [10.118.1.135].[SimphonyTables].[TR...

标签： c#sqlsql-serveroraclelinked-server

解决方案

我怀疑问题出在你的方括号上（MSSQL 特定的语法；对 Oracle 无效）。

看看这个链接：

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/610dfd96-f9b3-41ee-948c-d5763599397e/oracle-linked-server-ole-db-provider-invalid-table-name-or-表或视图不存在？forum=sqlintegrationservices

Oracle Linked Server Ole DB Provider 无效的表名或表或视图不存在

大家好，我在使用 OLE DB Provider for Oracle 通过 SSIS 连接 Oracle 时遇到了一个大问题

数据流，源 Oracle_Connection，我将 AlwaysUseDefaultCode 固定为是，并检索了表“[Oracle]”。“[Tax]”（顶点自动出现）。我检查了映射并将 Destination Assistant 创建为 SQL 表。它确实有效。

数据流，源 Oracle_Connection，我将 AlwaysUseDefaultCode 固定为是并且...我使用 SQL 命令编写

select * from [Oracle].[Tax] retrieves   (ORA - 00903) invalid table name 

select * from "[Oracle]"."[Tax]" retrieves (ORA - 00942)  table or view does not exist

select * from LinkedSever.."[Oracle]"."[Tax]" retrieves (ORA - 00903) invalid table name 

select * from "LinkedSever".."[Oracle]"."[Tax]" retrieves (ORA - 00903) invalid table name

解决方案：

我知道了。

select * from Oracle.Tax simply without brackets

否则，请尝试此处的故障排除提示：

推荐阅读