Tuesday, June 22, 2010

Access your Oracle Database from Python

cx_Oracle is a free, downloadable Python extension module that enables Python to access an Oracle database. This can come in handy when you need to streamline or automate tasks that require the use of SQL statements to access components of your spatial database, such as feature classes and domains. The module is available for all combinations of a variety of Operating Systems, Oracle versions (9.2.0, 10.2.0, and 11.1.0), and Python versions (2.0 and up). *Note: An Oracle client (or server) installation is required in order to use cx_Oracle. If you do not require the tools that come with a full client installation, it is recommended to install the Instant Client which is far easier to install.

I found this module to be useful when deleting feature classes with domains from the database. In ArcGIS 9.3.1 and earlier versions, unused domains are left lingering in the database after feature classes have been deleted. What’s more, default domains have to be unregistered before they can be deleted. I wrote a script in Python using the cx_Oracle module to loop through all feature classes in an ArcSDE database, remove default domains from fields, delete each feature class, and then delete the domains remaining in the database. I’ve added this script to the Geospatial Database. If you would like to see an example of how to use cx_Oracle or are looking to delete feature classes with domains from a database like I described above, you can download the script here.

The script takes four arguments:
1) Owner: Data owner.
2) Password: password@NET SERVICE NAME
3) Workspace: The folder location of the sde database (and feature dataset, if applicable) containing feature classes to run the script on (ex. C:\Documents and Settings\user\Application Data\ESRI\ArcCatalog\example.sde)
4) SDE Owner: Owner of the SDE repository in the database (ex. sde)