Add attribute rule ability to call database functions

[AndrewRudin1]

Add a new arcade function to the library that allows the code to call a database function. The value returned by the function will be populated into the field. This could be very similar to the NextSequenceValue function, but instead of providing the sequence name, the user would provide the database function name.

For my organization, this would allow us to build attribute rules that could call other Oracle databases (like our permitting system) via database links. For example if a GIS user is entering a new subdivision record, they would enter the permit number in one column. This new feature would allow us to call a database function with that permit number, retrieve information about the permit from the other database via a database link, and then auto-populate that information on the GIS record in other columns. 

[AndrewQuee]

Nice idea, this could add a lot of utility for all sorts of GIS applications with a simple feature that would not be that expensive to implement by Esri.  This would offer a lot of options through customisation that the user, not vendor, does.

I can see issues in validating returns, potential costly transactions resulting in performance issues and so forth, but that would be on you and your DBA in those cases.   "With great power..." etc.

[Bud]

I 100% support this idea. There are lots of Oracle functions that would be extremely handy, such as Oracle Spatial functions, LRS functions, etc. 

Are there any db-level workarounds?

Have you played around with using a database trigger instead of an attribute rule? Such as a db trigger that uses a db function with the permit number to retrieve information about the permit from the other database via the database link? And then populate that information on the GIS record in other columns using the trigger. Or is there a reason you need to use attribute rules?

Or, what if you created a view on your subdivision table in your GIS db. The view would have a calculated column that utilizes the database link in the external db to get the permit info. Then, in your attribute rule, you could get the permit info from the related subdivision view, just like you would if you were getting info from a related table/FC. That might work? I believe it is possible to get info from related views using Arcade.

Unrelated, but still interesting: Idea: Attribute rule arcade expression calling webservice and processing result. 

[AndrewRudin1]

@Bud Maybe database triggers would work if the data is branch versioned since that eliminates the complication of delta tables being involved that the triggers aren't aware of.  We are still getting familiar with how the branch versioning works so maybe we'll rediscover some tricks that weren't applicable with traditional versioning and the delta tables

However, the better thing would be for Esri to allow calling a function.  That way our RDBMS savvy developers can build the functions, and then allow our GIS analysts to leverage them in attribute rules on the feature classes they maintain.

The nice thing about attribute rules is they live with the feature class definition, so no matter which Esri method you use to edit the data (Pro, Enterprise feature service, Geoprocessing tool), the same attribute logic is applied and esri handled, and the management is exposed on the ESRI GUI side for GIS analysts.  We are already having to implement attribute rules that come with newer Esri data management solutions, so it would be better to have all the custom data entry logic in one place, rather than having some in attribute rules and some in custom database triggers, and then you have to wonder which fires first.

Esri already allows calling a sequence from Arcade with the NextSequenceValue  function.  This expects that the user/dba has setup the sequence and granted the appropriate privileges for it to be used.  If they are okay with that then I don't see harm in expanding to also allow referencing a database function and assume that the DBA/Analyst will manage the function and privileges.  Esri may have to stipulate that the function only return one value, but that should cover most situations.