Why are only SOME of the Oracle database tables in my Database Connection importing into ArcMap?

1815
2
11-30-2016 08:18 AM
ShannonGrumbly
New Contributor II

I made a connection to an Oracle database using 'Database Connection' in ArcCatalog. There are several hundred tables in the database. I tried to drag and drop a table from the ArcCatalog window pane (in ArcMap) into the Data Frame view. After entering the Unique ID Field, I get an error message saying "Could not add the specified data object to the map. Underlying DBMS error." The table is also blank in ArcCatalog, despite other tables in the same database being populated when viewed in Table View.

Any ideas of what's causing this? Any help would be greatly appreciated!!

0 Kudos
2 Replies
ChristianWells
Esri Regular Contributor

If the table is blank in ArcCatalog, I would check for field name issues. Often times a space or special character can cause this issue. 

Field Name Rules:

FAQ: What characters should not be used in ArcGIS for field names and table names? 

ShannonGrumbly
New Contributor II

Thanks for the tip! I did some poking around with Python and came up with the following script to write the table to a CSV file:

>>> import os

>>> import cx_Oracle

>>> import csv

>>> table = ‘TABLE_NAME’

>>> SQL = ‘select * from TABLE_NAME’

>>> FILE = open((table+’.csv’), “wb”)

>>> output = csv.writer(FILE, dialect = ‘excel’)

>>> connection = cx_Oracle.connect(‘USER/PASS@DATABASECONNECTION’)

>>> cursor = connection.cursor()

>>> printHeader = True

>>> cursor.execute(SQL)
>>> for row in cursor:

                output.writerow(row)

>>> cursor.close()

>>>connection.close()

>>> FILE.close()

It's a temporary fix, but I will definitely check out those field names. Thanks again.

0 Kudos