topic Re: ArcSDESQLExecute support calling Functions in Python Questions

ArcSDESQLExecute support calling Functions

CharlesTilly — Thu, 23 May 2013 08:30:48 GMT

Does the ArcSDESQLExecute class support calling Oracle functions? If yes, does it further support the return of a ref_cursor? I've tried

sql = "call BlahFunction()"
sdeReturn = conn.execute(sql)

Regards

Re: ArcSDESQLExecute support calling Functions

JamesCrandall — Thu, 23 May 2013 11:03:15 GMT

Does the ArcSDESQLExecute class support calling Oracle functions? If yes, does it further support the return of a ref_cursor? I've tried

sql = "call BlahFunction()"
sdeReturn = conn.execute(sql)

Regards

I am not sure if this is supported. We are implementing the cx_Oracle library for all of our attribute queries and other data processing (it's then utilized with arcpy in one form or another). It is a bit of a pain to get the field mapping done, but it has been an overall great way to integrate non-spatial data stored in Oracle db's.

Here is an example of calling a function (we are not doing this yet. We simply construct and pass in complete SQL statements to build cx_oracle cursor objects). http://stackoverflow.com/questions/8048124/cx-oracle-can-callfunc-return-list

Re: ArcSDESQLExecute support calling Functions

MarcoBoeringa — Thu, 23 May 2013 11:25:10 GMT

According to this 10.0 Help page, it seems to be returning either a List object in case of SQL SELECT statement, or a Boolean for fail/success when running a schema changing SQL query (DDL), or UPDATE / INSERT statement:

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//000v00000057000000.htm

Also see this:

http://developmentfaction.blogspot.nl/2010/07/python-tip-using-arcsdesqlexecute-to.html

Re: ArcSDESQLExecute support calling Functions

CharlesTilly — Thu, 23 May 2013 18:24:23 GMT

Thanks for the replies. I'm aware of the options you have suggested but I really need to invoke a function because one of the parameters needs to be a clob. I could use the cx_Oracle module but then I get back a non-ESRI cursor that I then have to convert. Ordinarily not a big deal but I have to do this a lot on a lot of data and in the interest of performance and maintenance. I see v.10.1 has expanded the arcpy.da module with functionality that may do the trick.

Re: ArcSDESQLExecute support calling Functions

JamesCrandall — Thu, 23 May 2013 18:49:59 GMT

Thanks for the replies. I'm aware of the options you have suggested but I really need to invoke a function because one of the parameters needs to be a clob. I could use the cx_Oracle module but then I get back a non-ESRI cursor that I then have to convert. Ordinarily not a big deal but I have to do this a lot on a lot of data and in the interest of performance and maintenance. I see v.10.1 has expanded the arcpy.da module with functionality that may do the trick.

Just to throw this out there, what about NumPyArrayToTable? Wondering if you just add the cx_Oracle cursor (from the result of your function call) into an array then let the method do the work -- rather than doing all the gymnastics to get the field mapping done. Not sure but I may have to look into this myself.

http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000016000000

Re: ArcSDESQLExecute support calling Functions

CharlesTilly — Thu, 23 May 2013 19:20:14 GMT

Ah yes, one of those new 10.1 arcpy.da features I was referring to. However, I need a v.10 solution. 😛

But your idea is a good one.

Regards

Re: ArcSDESQLExecute support calling Functions

JamesCrandall — Thu, 23 May 2013 19:21:58 GMT

Ah yes, one of those new 10.1 arcpy.da features I was referring to. However, I need a v.10 solution. 😛

But your idea is a good one.

Regards

doh! bummer!