1 Reply Latest reply on Aug 12, 2005 8:23 PM by 87853

    Open Cursors will not close, when using GetChunk

    87853
      I'm having an issue while building an automated document in VB6 (SP6) using OO4O 10.1.0.5.2, where I'm pulling Clobs from the database. (As many as 350). This caused the cursor limit of 300 to exceed, in our 10g database. This was odd, as the application should only uses ONE Cursor (and yes it is closed properly, or at least how documentation suggests). The Cursors will close properly , if I remove the GetChunk statment. So some how, GetChunk seems to be plugging the plumming. What's worse, is that tha database connetion will also not close properly due to the GetChunk issue. Only by terminating the VB application completely, will the database close everything out.

      Is there something special that needs to be done to release the clobs?



      Here is my code sample function, the database is opened in the Main Sub, and it is not displayed here.




      Function GetClobString(Path) As String

      Dim sqlStatement As String
      Dim lClobSize As Long

      sqlStatement = "SELECT Data FROM Xmldata WHERE Name='" & Path & "'"

      Set OraDynaset = OraData.CreateDynaset(sqlStatement, &H0&)

      lClobSize = OraDynaset.Fields("Data").FieldSize

      GetClobString = ""
      GetClobString = OraDynaset.Fields("Data").GetChunk(0, lClobSize)

      OraDynaset.Close
      Set OraDynaset = Nothing

      End Function


      If you put a break point some place in the function, and then run it a few times, you'll see the cursors stacking up in the database.
        • 1. Re: Open Cursors will not close, when using GetChunk
          87853
          Fixed my own issue, when I filed a TAR I noticed someone had a similar issue Many Years ago. Seems like a good idea to stay from GetChunk, so there was an alternative suggested. I used the following code, and I was able to properly close the Dynaset, and database.




          Function GetClobString(Path) As String

          Dim sqlStatement As String
          Dim lRead As Long
          Dim oClob As OraClob
          Dim buffer As Variant

          Dim AmountRead As Long

          sqlStatement = "SELECT Data FROM Xmldata WHERE Name='" & Path & "'"

          Set OraDynaset = OraData.CreateDynaset(sqlStatement, &H0&)

          GetClobString = ""

          Set oClob = OraDynaset.Fields("Data").Value
          AmountRead = oClob.Read(buffer)
          GetClobString = buffer

          OraDynaset.Close
          Set OraDynaset = Nothing

          End Function