Oracle Open Cursers

[Dan_Bracuk]

We have an issue with reaching the amount of available open cursers, and believe that a contributing factor is mixing cfqueryparams with other variables.  Something like this:

where field1 = #var1#

and field2 = <cfqueryparam value = "#var2#"

We believe that using cfqueryparam everywhere in the where clause that has a cf variable, we will reduce the number of cursers we have to open.

The question is, "What about the select clause?".  We have some pages that use some logic before the query, to produce variable abc.  Two possible values of abc are:

decode(dbtest_code,

   'CSA','CYCIB',
    'TACRO','FK5IB',
    'MPAC', 'MPAIB',
    'MYPA', 'MPAIB',
    'SIROL', 'RAPAM',
    'SIRIB', 'RAPAM',
    'BICV','HCO3','BICA','HCO3','BICC','HCO3',
    dbtest_code) as test_code,

or decode(dbtest_code,'INR','IN','INRA','IN','PTT','PT','APTT','PT',dbtest_code) as test_code,

This gets used in the query tag like this:

select
to_char(date_coll,'yyyy-mm-dd hh24:mi') as date_coll,
#PreserveSingleQuotes(abc)#

etc

We are considering changing to something like this:

<cfif listlen(combos) gt 0>
  decode(dbtest_code, <cfqueryparam cfsqltype="cf_sql_char" value="#combos#" list="yes">, dbtest_code) as test_code
<cfelse>
dbtest_code test_code
</cfif>

[Dan_Bracuk]

Oops, accidentally submitted the form before I finished.

The question is, would the change we are contemplating have any effect on the number of cursers we are using?  If so, will it be a reduction?

[Adam Cameron.]

We have an issue with reaching the amount of available open cursers, and believe that a contributing factor is mixing cfqueryparams with other variables.  Something like this:

This doesn't directly answer your question, but if you make sure your DSN's "maximum pooled statements" setting is lower than Oracle's OPEN_CURSORS (or whatever it is) setting, you should not get these errors.

I've never got to the bottom of the direct corelation of the pooled statements / open cursors error, but it seems to me if one floods the Oracle server with really a lot of poorly parameterised queries, you can consume all the available cursors before Oracle has a chance to recycle already used ones.  Or at least this is what seems to happen.

The more of your queries that are completely parameterised, the fewer cursors you'll need on Oracle to cache them, and the less likelihood you'll have of running out of available ones, because existing ones will get reused rather than needing to consume another one because of some dynamic value that's been hard-coded into an SQL string on the CF side of things.

--

Adam