Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORA-01000: maximum open cursors exceeded C#

Posted on 2009-03-31
13
Medium Priority
?
1,859 Views
Last Modified: 2012-05-06
I'm trying to access to another database table during an function but this exception kept appearing @ the 2nd "drOracle = cmdOracle.ExecuteReader();"

I hope someone can have me clear this error, thanks.
try
                {
                    localSQL = "SELECT KEYID,PKEYID, PRODUCT_NAME,PRODUCT_DESC FROM PRODUCT_NODE START WITH PKEYID IS NULL CONNECT BY PRIOR KEYID = PKEYID ORDER BY DT_NODE_NAME";
                    OracleCommand cmdOracle = new OracleCommand(localSQL, localConnection);
                    cmdOracle.CommandType = CommandType.Text;
                    OracleDataReader drOracle = cmdOracle.ExecuteReader();
 
                    DataTable ordt = new DataTable();
                    OracleDataAdapter orda = new OracleDataAdapter();
                    orda.SelectCommand = cmdOracle;
                    orda.Fill(ordt);
                    drOracle.Close();
                    
                    TreeNode nodeCheck = null;
                    TreeNode[] node = null;
                    string strText = string.Empty;
                    string[] strList = null;
                    string strDesc = null;
                    
                    foreach (DataRow dr in ordt.Rows)
                    {
                        nodeCheck = new TreeNode();
                        nodeCheck.Name = dr[0].ToString();                        
                        strText = dr[2].ToString();
                        strDesc = dr[3].ToString();
                        strList = strText.Split('.');
 
                        nodeCheck.Text = strList[strList.Length - 1];
 
                        if (dr[1] != DBNull.Value)
                        {
 
                            node = treeScadaItems.Nodes.Find(dr[1].ToString(), true);
                                if (strList.Length == 5)
                                {
                                    nodeCheck.Text = nodeCheck.Text + ": " + strDesc;
                                    node[0].Nodes.Add(nodeCheck);
 
                                    localSQL = "SELECT * FROM OPC_DT_PT WHERE PKEYID LIKE '" + dr[0].ToString() + "'";
                                    cmdOracle = new OracleCommand(localSQL, localConnection);
                                    cmdOracle.CommandType = CommandType.Text;
                                    //Exception Error appearing here.
                                    drOracle = cmdOracle.ExecuteReader();
 
                                    while (drOracle.Read())
                                    {
                                    }
                                }
                                else
                                { node[0].Nodes.Add(nodeCheck); }
                        }                        
                        else
                        {
                            treeScadaItems.Nodes.Add(nodeCheck);
                        } 
                        nodeCheck.Tag = dr[0].ToString();
                    } 
                }

Open in new window

0
Comment
Question by:csharp_learner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 20

Expert Comment

by:informaniac
ID: 24026923
while (drOracle.Read())
                                    {
                                    }
drOracle.Close();

Try this code

0
 

Author Comment

by:csharp_learner
ID: 24027017
After trying the code you mentioned the program compile for qite some time and pop-up:
ContextSwitchDeadlock was detected @ the line "drOracle = cmdOracle.ExecuteReader();"
0
 
LVL 20

Expert Comment

by:informaniac
ID: 24027101
Does this help?

OracleDataReader drOracle1 = cmdOracle.ExecuteReader();

while (drOracle1.Read())
                                    {
                                    }
drOracle1.Close();
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:csharp_learner
ID: 24027136
The same error "ContextSwitchDeadlock was detected" @ the line
"OracleDataReader drOracle1 = cmdOracle.ExecuteReader();"
0
 
LVL 20

Expert Comment

by:informaniac
ID: 24027159
Just try by writing this in line no 13

cmdOracle = null;

or try by creating a new oracle command
0
 

Author Comment

by:csharp_learner
ID: 24027198
In both cases the same error came out.
0
 
LVL 20

Accepted Solution

by:
informaniac earned 2000 total points
ID: 24027274
Well that's the first time i've seen/heard that error, googling gave me this

http://harriyott.com/2006/05/contextswitchdeadlock-was-detected.aspx
0
 

Author Comment

by:csharp_learner
ID: 24027329
I did as the site instructed and unchecked the "ContextSwitchDeadlock " the error did not appear.
But i don't think it's the solution I'm looking for, as the compliation time took very long.
0
 
LVL 20

Expert Comment

by:informaniac
ID: 24027968
Have u closed the connection?
0
 

Author Comment

by:csharp_learner
ID: 24035366
If you mean closing the 2nd drOracle1 ,yes I've closed it.
0
 

Author Comment

by:csharp_learner
ID: 24037313
I have narrowed down to this code...
But when i put the drOracle.Close(); before the while loop i will get an
Exception Error:"Operation is not valid due to the current state of the object"
If i put the drOracle.Close();  after the while loop it'll prompt the ContextSwitchDeadlock error.
				    drOracle = null;
                                    localSQL = "SELECT * FROM PRODUCT_PT WHERE PKEYID LIKE '" + dr[0].ToString() + "'";
                                    OracleCommand cmdOracle1 = new OracleCommand(localSQL, localConnection);
                                    cmdOracle1.CommandType = CommandType.Text;
                                    drOracle = cmdOracle1.ExecuteReader();
                                    //drOracle.Close();
                                    while (drOracle.Read())
                                    {
                                    }
                                    //drOracle.Close();

Open in new window

0
 

Author Closing Comment

by:csharp_learner
ID: 31564727
I've decided to go with unchecking Context error for now.
0
 
LVL 20

Expert Comment

by:informaniac
ID: 24056733
Well I was just searching the net and I came across this article.

Possibly this might help u.

http://forums.devx.com/showthread.php?t=16742

http://www.dotnet247.com/247reference/msgs/38/190956.aspx
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question