0 Replies Latest reply on Mar 19, 2003 11:17 AM by 11608

    Using bind variables with sql statements

    11608
      We connect from a VB 6.0 program via OO4O to an Oracle 8.1.7 database, using bind variables in connection with select statements. Running ok, but performance again by using bind vars not as good as expected!
      When looking into the table v$sqlarea, we were able to detect the reason. We expected that our program submits the sql statement with bind vars, Oracle parses this once, and with each select statement again, we do not have a reparse. But: It seems that with each new session Oracle reparses the sql statement, that is, Oracle is not able to memorize or cache bind vars and statements. Even more worrying, this kind of behaviour was visible with each new dynaset, but the same database/session.

      Is there anybody our there with an idea of what is happening here?

      Code snippet:

      Dim OraSession As OracleInProcServer.OraSessionClass
      Dim OraDatabase As OracleInProcServer.OraDatabase
      Set OraSession = CreateObject("OracleInProcServer.XOraSession")
      Set OraDatabase = OraSession.OpenDatabase(my database", "my connect", 0&)
      OraDatabase.Parameters.Add "my_bind", 0, ORAPARM_INPUT
      OraDatabase.Parameters("my_bind").DynasetOption = ORADYN_NOCACHE
      OraDatabase.Parameters("my_bind").serverType = ORATYPE_NUMBER ' Bind Var Type
      Dim RS As OracleInProcServer.OraDynaset
      strSQLstatement= "Select * from my_table where igz= [my_bind] "
      Set RS = OraDatabase.CreateDynaset(strSQLstatement, &H4)
      OraDatabase.Parameters("my_bind").Value = myValue
      RS.Refresh

      Cheers and thanks a lot :)
      Michael Sonntag