Cleaning Up Query Layers

[NathanHeickLACSD]

I'm using arcpy.management.MakeQueryLayer to run a SQL statement in an Oracle database and return a table.  It works successfully and I am able to acces the data I need through a SearchCursor, but I feel like I should delete the query table when I am done.  If I were in ArcGIS Pro, it would create a table view, so I feel like I should clean up after myself.  I tried using arcpy.management.Delete on the name of the query table that I passed to MakeQueryLayer, but it gives me the following error:

arcgisscripting.ExecuteError: Underlying DBMS error[ORA-00911: invalid character
ORA-00911: invalid character] Underlying DBMS error[ORA-00911: invalid character
ORA-00911: invalid character]

This seems kind of strange to me why Oracle would be involved.  I did try saving the result of MakeQueryLayer and then using del on the arcpy._mp.Table object.  It did delete the object and when I try to read the query layer again using its name, arcpy no longer recognizes it.  Still, I'm wondering if this is the correct approach.

[MikeMacRae]

If the ORA error is returned, then most likely it is hung up somewhere in your SQL and should not have anything to do with the deletion event you are attempting.

Some questions to ask:

• are you passing the SQL as a string? If so, how are you using quotes on the string?
• Are there special characters in your SQL that is causing the error?

From Oracle Documentation: https://docs.oracle.com/cd/B28359_01/server.111/b28278/e900.htm#ORA-00910

Other sources for troubleshooting: https://www.techonthenet.com/oracle/errors/ora00911.php

But, you should post your code so others can look and troubleshoot 🙂

[NathanHeickLACSD]

Hi Mike,

Yes, last night I determined that the error was loosely coupled with the Delete call.  If I skipped the Delete call on my query table and just ignored cleaning it up, a later call to Delete in the script fails with the same error.  The later call involves an in memory feature class that has nothing to do with Oracle at all.  It is like the issue is floating around somewhere waiting to come out when Delete is called.  It feels like a bug.  I did simplify the code down tremendously and I still get the issue:

import arcpy

arcpy.management.MakeQueryLayer(
    "WAM_PROD.sde",
    "PMQueryTable",
    "SELECT * FROM sa_pm_master",
    oid_fields="PM_MASTER_NO"
)

arcpy.management.Delete("PMQueryTable")

I still get the exact same error and that is the simplest SQL query I can make.  There might be something I am missing here, but this seems buggy.  At the least, it would be nice if the error did not show up on an unrelated in memory feature class.  Still, I think it has something to do with arcpy's management of these in memory objects.  Also, it might be somehow related to user permissions.  This user has minimal permissions.

I did find that if I Describe the query table after deleting it, it is gone.  I can delete it a second time without error.  I might just catch and ignore the exception.

[Scott_T_Anderson]

Nathan, did you find a solution to this?  Similarly, I export an Oracle table to a local file geodb table and when trying to delete, it throws the same error.  The table is completely resident in the file geodb and unrelated to the original Oracle table at that point. 

[NathanHeickLACSD]

Hi Scott,

Esri Technical Support eventually registered what I described as a bug.  I'm not sure that your situation is the same problem.  I would talk to Technical Support if you want to find a resolution.  I spent a long time just getting to the bug declaration.

[Scott_T_Anderson]

Thank you Nathan, have a great day! 🙂