topic Re: Any way to use Oracle Bind Variables in ArcMap? in Data Management Questions

Any way to use Oracle Bind Variables in ArcMap?

RyanCoodey — Thu, 23 Jun 2011 20:42:06 GMT

We are running Oracle 10g (soon to be 11g) with ArcSDE 9.3.1 (soon to be 10). All the ArcGIS Desktop clients are at version 10.

Is there any way to use an Oracle bind variable from within an ArcMap layer's definition query?

Thanks a lot!

Re: Any way to use Oracle Bind Variables in ArcMap?

VinceAngelo — Thu, 23 Jun 2011 22:00:46 GMT

Not directly.

- V

Re: Any way to use Oracle Bind Variables in ArcMap?

szcszc — Fri, 24 Jun 2011 00:49:19 GMT

Not directly.

- V

How to query based on ArcEngine interface using Oracle Bind Variables?

At present, there is only ExecuteSQL using Oracle Bind Variables, other interfaces Search�?�Select�?�Update not using Oracle Bind Variables. If not using Oracle Bind Variables, the query efficiency is very low.

How to solve this problem?

Re: Any way to use Oracle Bind Variables in ArcMap?

anthonysanchez — Fri, 24 Jun 2011 11:19:03 GMT

Hello,
my understanding is that using bind variables via arcobjects is not possible since the inside of the cursor is not exposed. I heard that in 10 it is, but you'll need to verify.

Until then you could try setting cursor_sharing to similar or force in the database to reduce hard Parsing due to unbound literals in your SQL.

Anthony

Re: Any way to use Oracle Bind Variables in ArcMap?

VinceAngelo — Fri, 24 Jun 2011 11:31:56 GMT

Bind variables just make a certain class of very inefficient queries more efficient. ArcSDE
was altered to support bind variables when needed, but I don't think ArcObjects has a
generic interface to use them.

Most of the situations I've seen where bind variables would help would have been helped
more by different use of SQL -- if you're generating hundreds or thousands of new queries,
there's probably a more efficent way to drive the query so that you're only doing multiple
fetches.

- V