ERROR 999999 underlying DBMS error Failed to execute SelectLayerByLocation

[KatieGodding]

I wrote a script, and when I run it, it fails at the same spot for me and my colleagues every time. Here is a screen shot of the error: 

The script is working with features located in a SDE environment, and given the part of the error "Underlying DBMS error [ORA-25402: transaction must roll back]" I'm thinking it has something to do with trying to access that database?

Additionally, if I copy the line of code that includes the SelectLayerByLocation function and enter that into the python window manually, it works fine. I can run the rest of the script, and there are no issues. It's only when the script is run from the beginning that it fails.

ArcGIS Pro 2.4.1 is being used.

I'm wondering if anyone has any experience or insight with this function and errors with working with data in an SDE.

I attached images of the script itself.

[Robert_LeClair]

I wonder.  Since the Python code works in the Python window, I'd check the *.sde connection file you're using to the Oracle DB to see if that's working or failing.  I'll keep digging on this one.  I don't see anything on the internals for ORA-25402 but see a lot on the non-Esri pages about it.

[KatieGodding]

If I copy the whole script and run it in the python window, it will fail at the SelectLayerByLocation line of code. Immediately after failing, however, if I manually copy, paste, and execute that one line of code in the python window, the SelectLayerByLocation function will work. I'm not sure why it wouldn't work at first when the whole script is run and then would immediately work when executed separately.

[Robert_LeClair]

I'm seeing a bug - BUG000127016 - The Select Layer By Location tool in ModelBuilder forwards all objects if an inline variable substitution is used in the input data path - that was present in ArcGIS Pro 2.4.2 and fixed in ArcGIS Pro 2.6.x.  I know it's a ModelBuilder bug but many users create a Python script by exporting the model out to a *.py script.  Is it possible to upgrade to ArcGIS Pro 2.6.x or later for testing purposes?

[KatieGodding]

I've had coworkers with ArcGIS Pro 2.6 try and they still get the same error on SelectLayerByLocation.

[JoeBorgione]

Perhaps you could use the 'Insert/Edit Code Sample' tool in the expanded tool bar and copy and past your code up to an including the line that fails.  

(choose python)

[KatieGodding]

import arcpy, os
from arcpy import env
    
env.workspace = r'C:\Users\goddik\Working\Summary_LMC\Workspace'
    
#create variables for output excel files
Proposed_LMCs = os.path.join(outputFolder,"Proposed_LMCs.xls")
Proposed_LMCs_DML = os.path.join(outputFolder,"Proposed_LMCs_DML.xls")
Existing_LMCs = os.path.join(outputFolder,"Existing_LMCs.xls")
Existing_LMCs_DML = os.path.join(outputFolder,"Existing_LMCs_DML.xls")
    
env.overwriteOutput = True

# choose sde instance
sde_instance = input('PROD, DEV, LOCAL? '
    
#connect to sde (production)
if sde_instance == 'PROD':
    sde_edit = r'\\central\RESPLAN\GIS\Database_Connections\LF_GIS_EDIT\Production\oracle11g - dnr_sde - LF_GIS_EDIT - LFEdit15.sde'
        
    sde_read = r'\\central\RESPLAN\GIS\Database_Connections\LF_GIS_READ\Production\oracle11g - dnr_sde - LF_GIS_READ - LFCarto15.sde'
    
#connect to sde (development)
if sde_instance == 'DEV':
    sde_edit = r'\\central\RESPLAN\GIS\Database_Connections\LF_GIS_EDIT\Development\oracle11g - dnr_geotest - LF_GIS_EDIT - LFGIS123.sde'

    sde_read = r'\\central\RESPLAN\GIS\Database_Connections\LF_GIS_READ\Development\oracle11g - dnr_geotest - LF_GIS_READ - Lands2015.sde'

#local copies of SDE connections for testing
if sde_instance == 'LOCAL':
    sde_edit = r'C:\Users\goddik\Working\Summary_LMC\Development-EDIT\oracle11g_dnr_geotest_LF_GIS_EDIT_LFGIS123.sde'
    
    sde_read = r'C:\Users\goddik\Working\Summary_LMC\Development-READ\oracle11g_dnr_geotest_LF_GIS_READ_Lands2015.sde'
        
    
#connect to LMC, EL, DML feature classes
LMC_edit = os.path.join(sde_edit, 'X52915.LF_MP_NR44_LANDMGT_AR_VAR') # LMC edit (this has features w/o CROSSREF_SEQ_NOs not found in READ)
ELs = os.path.join(sde_read, 'SDEDNR.EN_ECO_LANDSCAPE_REGN_AR_250K')
DML = os.path.join(sde_read, 'X52911.LF_DNR_PROPNAME_INT_24K_SV')
    
    
# make CSP EL layer
arcpy.MakeFeatureLayer_management(ELs, "CSP_EL", "ECO_LANDSCAPE_NAME = 'Central Sand Plains'")
    
# create proposed lMC layer
arcpy.MakeFeatureLayer_management(LMC_edit, "LMC_proposed", "MASTER_PLAN_SEQ_NO = 6007")
    
# create existing LMC layer
arcpy.MakeFeatureLayer_management(LMC_edit, "LMC_existing", "MASTER_PLAN_SEQ_NO <> 6007")
arcpy.SelectLayerByLocation_management("LMC_existing", "", "CSP_EL")

[JoeBorgione]

ArcGIS Pro 2.4.1 is being used.

BUG000127016...  ArcGIS Pro 2.4.2 and fixed in ArcGIS Pro 2.6.x.

Good detective work @Robert_LeClair 

[JoeBorgione]

@KatieGodding - I don't see anything that jumps out as a problem with your code as posted. (But you knew that already...)

Just as test, can you copy your data to a file geodatabase, make the appropriate adjustments to your code and try it there?  I'm not an Oracle guy so I'm just trying the eliminate or identify the database as the root issue.

I notice you are using an input() and it's not complete in you posted code, but I suspect that's just a copy and paste error.  But can you comment that out and hard-code the three different options one at a time?

Just one other thing that I see is the optional overlap type: what if you give it the actual 'INTERSECT' key word and see if that makes a difference?

Admittedly, I'm grasping at straws here, but these are the things I would do to trouble shoot.

[KatieGodding]

So I removed the connection to our editable sde data and that seemed to resolve the issue. Any insight on why that may have been causing an issue? For context, at my organization we have multiple sde database connections. There's a 'read' one where you can only read the data and there's an 'edit' one where you can edit the data.