Working Around Oracle's SQL Where Clause 1000 Item Limit with ArcPy

One option for working in an ArcSDE environment is to utilize Oracle databases. Oracle databases has an unfortunate limitation: they only allow for up to 1000 items when an SQL where clause is applied to them. When I create ArcGIS Python scripts, I will oftentimes perform a series of geoprocesses and throw field values (GUIDs perhaps) into a python list for features that meet certain criteria. I'll then want to filter my analyzed feature class by these values by means of a selection or through the creation of a feature layer. Both ArcPy's SelectLayerByAttribute_management and MakeFeatureLayer_management allow for the inclusion of an SQL where clause. In a large feature class, however, the 1000 item limit can be easily breached. To work around this limitation, I created a function that creates a feature layer from a list of values, even if the number of values is greater than 1000. The code first creates a new feature layer, and then performs multiple selections in 1000 item chunks. Once all the desired features have been selected, a final output feature layer is made and returned based on the selected features.

The code:

import arcpy

def LayerFromList (inLyr, inField, inList):

    """
    Returns an arcpy feature layer of the 
     features matching the values in 
     the inList in field inField
    inLyr can be a feature layer or 
     feature class
    inField is the field name in the 
     inLyr used for selection
    inList is a python list of values 
     to match to values in inField of inLyr
    Created to work around 1000 item 
     limit in Oracle database SQL querry
    """

    #convert list to strings
    mapList = map (str, inList)

    #Get Field Type
    fldType = [field.type for field
               in arcpy.ListFields (inLyr)
               if field.name == inField][0]
    
    #Make temporary feature layer to apply selections to
    tempLyr = "lyr0"
    num = 0
    #Get available layer name
    while arcpy.Exists (tempLyr):
        num += 1
        tempLyr = "lyr" + str (num)
    #Make layer
    arcpy.MakeFeatureLayer_management (inLyr, tempLyr)

    #features selected counter
    selected = 0
    #iterate
    while True:
        #Get next 1000 rows
        partial = mapList[selected:selected + 1000]
        #Exit while loop if there are no more items in the list
        if not partial:
            break

        #Create the SQL where cluase
        if fldType in ["String", "Guid"]:
            joinStr = "'{0}'".format ("', '".join (partial))
        else:
            joinStr = ", ".join (partial)
        sql = "{0} IN ({1})".format (arcpy.AddFieldDelimiters
                                     (tempLyr, inField), joinStr)

        #Add next 1000 rows to selection
        arcpy.SelectLayerByAttribute_management (tempLyr, 
                                                 "ADD_TO_SELECTION", 
                                                 sql)
        #Add 1000 to selected varaible
        selected += 1000


    #Make output feature layer
    num += 1
    outLyr = "lyr" + str (num)
    while arcpy.Exists (outLyr):
        num += 1
        outLyr = "lyr" + str (num)
    arcpy.MakeFeatureLayer_management (tempLyr, outLyr)

    #Delete temporary layer
    arcpy.Delete_management (tempLyr)
    
    return outLyr