August 24, 2015 at 2:36 pm
Perhaps there is a way to complete the query and surpress the error message?
Background:
The GIS Group provided a new Oracle View today. Modified previous scripts to create a Linked Server to Oracle.
The Linked Server can be expanded in SSMS to the Catalogs, Default, Views and the target View is there.
With this view, Right Click. Script As, Select To - the Select Statement shows up in the new Query.
Note: Have done this dozens of times with other Oracle Linked Servers on this exact same SQL Server DB.
The Select statement looks like this:
SELECT [OBJECTID]
,[PRIMOID_NB]
, dozen more field names
FROM [PSDEC.City.CO.COM]..[WELL].[SHL_Description_VW]
NOTE: a field name of "SHAPE" is NOT one of the field names.
Error:
Unlike the other Oracle Linked Servers, all the views available from here get this error:
Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "PSDEC.City.CO.CO"
supplied invalid metadata for column "SHAPE". The data type is not supported.
The SHAPE field is not returned in this view.
A SHAPE is more than likely some GIS propritary data type in Oracle for mapping.
Message Below in status bar: Query Completed with Errors
August 24, 2015 at 2:45 pm
based on the name, you are querying a view (_VW)
so it doesn't matter what columns are included in your query, it's the underlying tables that are assembled in the view that seems to be affecting you. using the four part naming convention(Server.Db.Schema.Object)s on linked server objects like that are end up copying ALL the data into tempdb before they are filtered to just the columns and rows you request.
[SHL_Description_VW]
if it really is something geospatial related, it might be the ODBC driver form SQL to oracle doesn't support the custom data type.
try getting the data via openquery instead first.
SELECT *
FROM OPENQUERY([PSDEC.City.CO.COM],'SELECT [OBJECTID]
,[PRIMOID_NB]
, dozen more field names
FROM [WELL].[SHL_Description_VW]')
you could also look into getting the definition of the underlying view, and use the joins to raw tables via openquery instead of the view
SELECT * FROM OPENQUERY([SHL_Description_VW]
Lowell
August 24, 2015 at 3:38 pm
SELECT * FROM OPENQUERY([PSDEC.City.CO.COM], 'Select [PrimoID_NB] from [Well.Den_Well_SHL_VW]')
Or
SELECT * FROM OPENQUERY([PSDEC.City.CO.COM], 'Select [PrimoID_NB] from [Well].[Den_Well_SHL_VW]')
Same error:
OLE DB provider "OraOLEDB.Oracle" for linked server "PSDEC.City.CO.COM"
returned message "ORA-00936: missing expression".
Msg 7321, Level 16, State 2,
Line 3
An error occurred while preparing the query "Select [PrimoID_NB] from [Well.Den_Well_VW]"
for execution against OLE DB provider "OraOLEDB.Oracle"
for linked server "PSDEC.City.CO.COM".
August 24, 2015 at 3:43 pm
Used a script to create the Oracle Linked Server.
It provides about 8 views from the same server.
A few of the views with only numeric fields work just fine.
Can't post the script - IE just bombs even when using Code Tags.
August 24, 2015 at 4:01 pm
Can you use SQL*Plus to connect to Oracle and select from the views you're trying to query from SQL Server?
You're getting an ORA-00936, which tells me that the error is on the Oracle side. Unless the underlying query works in Oracle, you aren't going to be able to read the data in SQL Server.
August 24, 2015 at 5:30 pm
Thank you for your comment.
I wanted to be sure before asking the Oracle DBA that.
Was just given a server to link to.
I found that many of the tables with no mapping (index or text only) data work just fine.
This is true for either table or view.
The tables designed for ESRI all have this exact problem. They can create a Select statement, but all have the same error.
Other tables work just fine. This probably means it isn't my server connection.
Looking at some technical documents from ESRI, the "SHAPE" appears to be a filter. So, it might actually be one of those index.
One article indicated the SHAPE index was a private system table.
August 25, 2015 at 8:06 am
Think I found the reason and wanted to run this up the flagpole for others to comment. Am I on the right track?
ESRI for Oracle, Shared Data:
http://support.esri.com/em/knowledgebase/techarticles/detail/35658
Cause : The Oracle parse error occurs because the shape.area or shape.len attribute is not fully qualified with its table name or a table alias in the SQL statement being executed in the database.
Without fully qualifying the attribute, the shape.area or shape.len attributes are not valid attributes for the table being queried. The area and len attributes are properties of the st_geometry type and therefore must be fully qualified with the table alias when referenced within the SQL statement.
Solution or Workaround
The solution to the error is to fully qualify the shape.area or shape.len attribute in the definition queries 'where' clause.
For example when adding a definition query for a layer named 'water_bodies' in ArcMap, an Oracle parse error with the following syntax is encountered:
"NATION" = 52 AND "SHAPE.AREA" >= .000010
By fully qualifying the attribute with the table name, no error is encountered.
"NATION" = 52 AND "WATER_BODIES.SHAPE.AREA" >= .000010
My further reading indicates that the SHAPE is an ESRI Oracle System file. My ODBC rights do not provide me with access to this. So, the Error as translated by ODBC is shown above.
On this same SQL Server Linked Server to Oracle, I am able to read many views and tables that are pure data. These views and tables do not use the SHAPE to determine geograpical areas. Those tables and views named to indicate geopatial data filters have the same "SHAPE" error.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply