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.
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.
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?
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.
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")
@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.