THREADS - ESRI Discussion Forums

You are here: Home > User Forums > arcgis desktop discussion forums > Thread Replies

ArcGIS Desktop Discussion Forums

ArcGIS Desktop - Data Management (General) forum

XY Data from Oracle not importing correctly	Steve Halko	Mar 01, 2007
Re: XY Data from Oracle not importing corre...	Carly Lambert	Oct 09, 2007
Re: XY Data from Oracle not importing corre...	Carly Lambert	Oct 09, 2007
Re: XY Data from Oracle not importing corre...	Duane Bays	Oct 19, 2007
Re: XY Data from Oracle not importing corre...	Steve Halko	Oct 19, 2007
Re: XY Data from Oracle not importing corre...	Duane Bays	Oct 19, 2007
Re: XY Data from Oracle not importing corre...	Steve Halko	Oct 19, 2007
Re: XY Data from Oracle not importing corre...	Duane Bays	Oct 25, 2007
Re: XY Data from Oracle not importing corre...	Robert MEIER	Feb 22, 2008
Re: XY Data from Oracle not importing corre...	Calum Johnston	Mar 17, 2008

Report Inappropriate Content • Top • Print • Reply
Subject	XY Data from Oracle not importing correctly
Author	Steve Halko
Date	Mar 01, 2007
Message	We're trying to use lat/long data from an Oracle 9i table in ArcMap. In Oracle the lat/long columns are defined as NUMBER(10,7). When we bring the data into ArcMap, the minus sign is lost on all of the negative quantities, and many of the other values become 0 or some other small quantity like 0.01. If we bring the data into MS Access first, then point ArcMap to the MS Access database, all the data is fine. The data is corrupted only when we connect directly from ArcMap to Oracle (using ODBC). Whats going on here?

Report Inappropriate Content • Top • Print • Reply
Subject	Re: XY Data from Oracle not importing correctly
Author	Carly Lambert
Date	Oct 09, 2007
Message	c

Report Inappropriate Content • Top • Print • Reply
Subject	Re: XY Data from Oracle not importing correctly
Author	Carly Lambert
Date	Oct 09, 2007
Message	c

Report Inappropriate Content • Top • Print • Reply
Subject	Re: XY Data from Oracle not importing correctly
Author	Duane Bays
Date	Oct 19, 2007
Message	Hello, We have this type of problem here, also. I suggested that instead of an export of data from Oracle to Excel, then conversion to a Shapefile or Feature Class, we connect directly to the updated tables in the Oracle database. The ODBC connection in ArcCatalog is not producing accurate information from the Oracle tables. Our coordinate fields (Geographic, latitude longitude, in decimal degrees) in Oracle tables are Number data type, and the ArcCatalog ODBC connection converts the fields to Long Integer, 4 characters. The result is a set of rounded integer values, e.g., 114 longitude and 45 latitude, with 0 decimal places, in the coordinate fields. Not very useful for precise locational information! I've found another Thread on this topic. Scale and precision seem to be at the root of the problem, and apparently it's a common issue: http://forums.esri.com/Thread.asp?c=2&f=1720&t=205589#618001 I have not yet found a solution! In one post, someone suggested the creation of a View in Oracle, but I do not have the means to do that (at least, not yet; I'm new here), and it seems like an unnecessarily cumbersome solution. Must we create a View for each and every Oracle table, simply to interpret the coordinates properly? Thanks very much in advance for any workable suggestions. I saw a post stating that ArcSDE does interpret the Oracle Number data types to produces decimal values, but we do not have ArcSDE here at present. Best wishes, Duane Bays GIS Programmer / Analyst Montana Department of Agriculture Helena, Montana

Report Inappropriate Content • Top • Print • Reply
Subject	Re: XY Data from Oracle not importing correctly
Author	Steve Halko
Date	Oct 19, 2007
Message	We found a solution to our problem here. It turns out that when creating a new OLE DB connection, you must choose "Microsoft OLE DB Provider for Oracle". Any other OLE DB provider prevents decimal numbers from being transferred correctly. It makes you wonder why ESRI even gives you those other providers.

Report Inappropriate Content • Top • Print • Reply
Subject	Re: XY Data from Oracle not importing correctly
Author	Duane Bays
Date	Oct 19, 2007
Message	Hello, Thanks very much for the quick response. I haven't been able to make the "Microsoft OLE DB Provider for Oracle" connect yet, but possibly I need to specify the IP address or the full pathway to that machine. It's useful information to know that it can work; ODBC apparently does not. I'd tried this option previously without success, but that could be because I entered a data source name, and not the actual server name. Best wishes, Duane Bays GIS Programmer / Analyst Montana Department of Agriculture Helena, Montana

Report Inappropriate Content • Top • Print • Reply
Subject	Re: XY Data from Oracle not importing correctly
Author	Steve Halko
Date	Oct 19, 2007
Message	Where it asks for Server Name, we just put the database name - I'm not sure how the actual server name would work, since we typically have several Oracle databases on each server.

Report Inappropriate Content • Top • Print • Reply
Subject	Re: XY Data from Oracle not importing correctly
Author	Duane Bays
Date	Oct 25, 2007
Message	Thanks very much - we found that this worked! Instead of the server name, the user should enter the database name in the text box below the label, "Enter a Server Name: ". By doing that, we were able to connect to the Oracle tables and read the XY data in the number fields correctly in ArcGIS, with all the decimal places. Our database specialist immediately became alarmed, because we had opened a "back door" into the production area and thereby circumvented the error-trapping routines built into the non-GIS interface. However, that's another story! Best wishes, Duane Bays

Report Inappropriate Content • Top • Print • Reply
Subject	Re: XY Data from Oracle not importing correctly
Author	Robert MEIER
Date	Feb 22, 2008
Message	I had a similar issue - state plane coords are stored as a whole number in an Oracle Table and I needed to divide them by 100 to get a decimal and then map the coords. I wanted the data in an Oracle View. I created the oracle view, dividing the coords, but to see the coords in the correct format in ArcMap I had to make an unusual connection, in ArcCatalog. First I created a user dsn on my pc using the oracle driver, then in ArcCatalog I created the database connection using the Microsoft OLE DB Provider for ODBC Drivers and specified the user dsn. This worked great. Robert Meier City of Overland Park, Kansas

Report Inappropriate Content • Top • Print • Reply
Subject	Re: XY Data from Oracle not importing correctly
Author	Calum Johnston
Date	Mar 17, 2008
Message	I am just beginning in the exploration of Oracle db integration with my spatial data. I have a pre-existing Oracle table, not spatially referenced. I need to display the data on a plan, spatially. Further, I then need the capacity for edits to propogate throughout my system, whether done directly on the db, or on the spatial plan. In other words, I need a live link between the Oracle data base and my mxd. I have had no luck looking through help topics and tutorials, any assistance would be greatly appreciated.