CODEWITHSUNDEEP
c#sqlsql-serveroracle-databaselinked-server
Bryan W
Bryan W

Reputation: 1

OLE DB provider "OraOLEDB.Oracle" for linked server "FDMEE_SIMPHONY" returned message "ORA-00903: invalid table name"

I am trying to run this query between two linked servers, but it is returning the message ORA-00903 Invalid table.

--Code

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')

Error Message...

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...

Upvotes: 0

Views: 1114

Answers (1)

paulsm4
paulsm4

Reputation: 121649

I suspect the problem is your square brackets (MSSQL-specific syntax; invalid for Oracle).

Take look at this link:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/610dfd96-f9b3-41ee-948c-d5763599397e/oracle-linked-server-ole-db-provider-invalid-table-name-or-table-or-view-does-not-exist?forum=sqlintegrationservices

Oracle Linked Server Ole DB Provider invalid table name or table or view does not exist

Hi guys, I am having a big problem tying to connect Oracle by SSIS using the OLE DB Provider for Oracle

Data Flow, Source Oracle_Connection, I fixed the AlwaysUseDefaultCode as yes and I retrieved the table "[Oracle]"."[Tax]" (the apexes came in automatic). I checked the mapping and created the Destination Assistant as a SQL table. IT did works.

Data Flow, Source Oracle_Connection, I fixed the AlwaysUseDefaultCode as yes and...I used the SQL Command writing

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

Solution:

I got it.

select * from Oracle.Tax simply without brackets

Otherwise, try the troubleshooting tips here:

Upvotes: 0

Related Questions