glenreid

Query Oracle Table -- Need advice

Discussion created by glenreid on Feb 8, 2013
Latest reply on Feb 12, 2013 by jamesfreddyc
Task: query a very large Oracle table, build an XY Event layer, apply symbology with .lyr file, create GP service, and output to web app using web API.

I have this running using ModelBuilder at AGS v10 in a GP service and a JSAPI web app.  I am now trying to upgrade to Python and a Python Toolbox.

ArcGIS Desktop 10.1 SP1
ArcGIS Server Linux 10.1 SP1
ArcSDE 10 SP2
Oracle 11.2.0.2

My biggest question is how best to query the data in Oracle (I'm looking for performance).

I use TableSelect_analysis through an ArcSDE connection file in my older ModelBuilder GP service, but I only want 4 columns from this table and it has 21.

MakeTableView_management through an ArcSDE connection file doesn't honor my where clause or my fieldinfo (ESRI Support is looking into this).

I can execute my SQL query with ArcSDESQLExecute, but I'm not sure how to handle the list to use with MakeXYEventLayer_management.

I have also read that using cx_Oracle is a good way to go.

I was hoping that you gurus out there might give me some advice.

Thanks,
Glen

Outcomes