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

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.