How To Set Oracle Cursor Settings From VB.Net

Hello,

I am using a dataset in VB.Net to populate a listview.  I am getting an error "ORA-01000: maximum open cursors exceeded".  There are 1700+ records that I am retrieving.  Our Oracle database has a limit of 300 cursors and I am hitting 301 when retrieving the records.

From researching this I found out that I need to change HOLD_CURSOR to NO and RELEASE_CURSOR to YES in Oracle, but I cannot find anything on how to do this in VB.Net.

Does anyone know how to do this?

Thanks

RichW
LVL 4
RichWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DhaestCommented:
When do you open all those cursors ? Perhaps you should rewrite your stored procedure.

If you use OracleCommand.ExecuteReader(), don't forget to close it...
Can you show the code where you get the error (part of the program)

0
RichWAuthor Commented:
The dataset object is being disposed and set to Nothing when it is done being used.  It simply loops through the records to populate a Listview control.

All 1700+ records are found and appear in the Listview control.  The error happens on the execute of the next method, which is a simple call to a sequence.  The strange thing about it it that when I step through the code in debug, when the cursor reaches the Catch of my Try block, if I then move the cursor to go back to the line where it erred, the code runs through fine.
0
DhaestCommented:
Can you show the code where you get the error (part of the program).
That can be helpful to correct the error.
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

RichWAuthor Commented:
Dim conComp As New cConnectit ' This class opens a connection
Dim dsSelect As DataSet = New DataSet("CONTRACTS")
Dim sSQL As String
Dim i As Integer
Dim x As ListViewItem

Try
conComp.openConnection()

sSQL = "SELECT * FROM BISCONTRACT.CONTRACT WHERE CUSTOMER_ID=" & indCust & " AND LEVELS =1 AND NVL2(PARENT_CONTRACT_ID, PARENT_CONTRACT_ID, 0)= 0 AND CONTRACT_STATUS_ID <> 3 ORDER BY LEGACY_CONTRACT_ID"

Dim da As OracleDataAdapter = New OracleDataAdapter(sSQL, conComp.conn)

da.Fill(dsSelect)

Do Until i = dsSelect.Tables(0).Rows.Count
x = vLstView.Items.Add(dsSelect.Tables(0).Rows(i).Item("LEGACY_CONTRACT_ID"))
x.SubItems.Add(GetCustomerName(vCustNum))
x.SubItems.Add(CStr(dsSelect.Tables(0).Rows(i).Item("BEGIN_DATE")))
x.SubItems.Add(CStr(dsSelect.Tables(0).Rows(i).Item("END_DATE")))
x.SubItems.Add(FormatCurrency(dsSelect.Tables(0).Rows(i).Item("BID_PRICE"), 2))
x.SubItems.Add(FormatCurrency(dsSelect.Tables(0).Rows(i).Item("OTHER_BILLABLE_CHARGES"), 2))
x.SubItems.Add(GetContractType(dsSelect.Tables(0).Rows(i).Item("CONTRACT_TYPE_ID")))
Loop

Catch ex As Exception
MsgBox("Error Getting Component Data: " & ex.Message)
Finally
dsSelect.Dispose()
dsSelect = Nothing
conComp.Close(True)
conComp = Nothing
End Try

GetContractType method uses a different dataset object to get data for the Listview field. That dataset object is also being closed and disposed.

The Close method of conComp closes and disposes the object.

Thanks

RichW
0
RichWAuthor Commented:
Let me add, this code works fine.  The error occurs in the next method when I use a reader object to get a Nextval value from a sequence.    Through trial and error I have discovered that any new connection to the db would cause the error, but when I lower the number of records being looped through, the error does not occur.

Thanks

RichW
0
DhaestCommented:
Can you check if you dispose your dataadapter in GetContractType !
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RichWAuthor Commented:
Yes it is.  All methods using data objects are properly disposed.
0
RichWAuthor Commented:
Dhaest,
I'll give you the points because you tried to help.  Here's the solution I discovered with this problem.  

The only way you can close an Oracle cursor is by destroying the command object which opens the cursor.  In other words disposing your command object by simply calling cmd.dispose everywhere its getting used.

I was previously using a dataAdapter object without a command object.

As soon as I rewrote the code to use a command object and I disposed of the command object, the maximum cursors error went away.

Thanks for your help.

RichW
0
plane4069Commented:
Thanks.  I've just converted a large application that prevously used an Access database to Oracle at my client's request.  

Disposing of the OLEDB command object cured the ORA-1000 problems!

Well done!

at
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.