ODP.NET ORA-1000 Maximum Cursors Error

[cgyrob]

I have an app where I keep getting this error.

This is a c# .net 3.5 app running on a windows 2003 server with IIS 6.0 connecting to an Oracle 10g db.

This app had no problems during testing where it was connecting to an Oracle 9i DB but once I moved to the production 10g db I started getting this error.

All the information I have found regarding this issue just says to make sure that you either close or dispose all your commands and increase your allowed cursors in Oracle.

I have set the allowed cursors in oracle to 1000 and encapsulated all my connections in a try catch finally block where I dispose all the connections, commands, and datareaders but I continue to get this error.

Both servers have .net 3.5 sp1 and both are using the same odp.net provider version so I am confused on why I don't see this error in test.

If anyone has any thoughts or ideas on this issue I would like to hear them

Thanks.

[sknake]

Post your code where you are executing commands to the server with the connection, command, datareader, etc. Also make sure to post your connection string.

For testing I would shut connection pooling to the oracle server off. It sounds like connections are being hung up in the pool and causing the server to run out of resources. I don't use Oracle so I couldn't explain why this happened from 9i to 10g.

[cgyrob]

Connection String

<add name="DWSTAGE" connectionString="Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = ProdServer)&#xA;            (PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = Servicename)));Password=password;User ID=user"/>

This method is the main accessor of the DB.

void PopulateDataTable()
        {
            string query = buildSQL();
            TableGridView.Columns.Clear();
            Table = new DataTable();

            string TableName = Session["TableSelected"].ToString();
            string Entity = Session["entity"].ToString();

            OracleConnection con = null;
            OracleCommand cmd = null;
            OracleDataAdapter da = null;
                        

            try
            {
                con = new OracleConnection(ConfigurationManager.ConnectionStrings["DWSTAGE"].ConnectionString);
                con.Open();
                cmd = new OracleCommand(query, con);
                cmd.CommandType = CommandType.Text;
                da = new OracleDataAdapter(cmd);                

                da.Fill(Table);

                if (Table.Rows.Count > 0)
                {
                    DataView dv = Table.DefaultView;

                    if (this.ViewState["SortExpression"] != null)
                    {
                        dv.Sort = string.Format("{0} {1}", ViewState["SortExpression"].ToString(), this.ViewState["SortOrder"].ToString());
                    }

                    TableGridView.DataSource = Table;
                    TableGridView.DataBind();
                }

                if (Session["Search"].Equals(1))
                {
                    if (Table.Rows.Count < 1)
                    {
                        Session["Search"] = 0;
                        Literal1.Text = "<Script lang=JScript> alert('No results found with your search criteria.');</Script>";
                        txtSearch.Text = "";
                        txtSearch.Focus();
                        PopulateDataTable();
                    }
                }
            }
            catch (Exception ex)
            {
                msg_lbl.Text = ex.Message;//(ex.ToString()).Substring(40,200);
                MsgPanel.Visible = true;
                return;
            }
            finally
            {
                cmd.Dispose();
                da.Dispose();
                con.Close();
                con.Dispose();
            }

        }

[cgyrob]

I know it has been awhile but I solved this awhile ago. The problem was with the datagridview control data accessor on one of my pages. I had to change to the odp.net accessor and the cursor problem went away. The only draw back was that I had to build all the methods for the functionality from the control that I wanted manually. No built in sorting or paging. The fun of mixing Microsoft and Oracle products.