CODEWITHSUNDEEP
c#sqloracle-databaseado.netplsql
laksh
laksh

Reputation: 63

How can I avoid "Open cursor limit exceeded" error for this code?

foreach (string num in ld2.Keys)
{
  double state;
  int i;

  for (i = 0; i < states.Count; i++)
  {
    cmd = mdb.getCommand("select value V " +
                         "  from TAB_CHART_DATA " + 
                         " where TIMESTAMP = (select max(TIMESTAMP) " +
                         "                      from TAB_CHART_DATA " + 
                         "                     where chart_type= " + chart_type + 
                         "                       and upper(upper(state)) = '" + states[i] + "' " +
                         "                       and CSIS_ID = " + CSISPID + 
                         "                       and baselinecode like '" + num + "'" +
                         "                   ) " +
                         "   and chart_type = " + chart_type + 
                         "   and upper(state) = '" + states[i] + "' " +
                         "   and CSIS_ID = " + CSISPID + 
                         "   and baselinecode like '" + num + "'"
                        );

    state = mdb.ExecSQLAndGetFirstInt(cmd);
  }

}

This loop runs for more than 3000 times.

Upvotes: 0

Views: 578

Answers (1)

Oded
Oded

Reputation: 498914

Fetch all rows in one query and loop over the result set in your code instead of opening cursors and killing the database.

Upvotes: 3

Related Questions