3 Replies Latest reply on Jan 3, 2007 3:16 PM by 554276

    Monitoring Oracle SQL queries in VBA

    412388
      I currently use VBA with Excel to generate reports based on data within an Oracle 9 database. The amount of data is very large and the reports can take a long time to process. I have added status bar progress indicators for the majority of the report processing tasks to inform the user of what is going on.

      The problem is that I need to be able to monitor the progress of the main SQL queries since they take upwards of a few minutes to run each (mainly because I need to rank and order data). I currently use code similar to that shown below to query the database from VBA.

      Sub simpleVersion()

      Dim username As String
      Dim password As String
      Dim sid As String
      Dim objSession As Object
      Dim objdatabase As Object

      Dim OraDynaset As Object
      Dim strSQL As String

      username = "user"
      password = "pass"
      sid = "database"
      ' connect to database
      Set objSession = CreateObject("OracleInProcServer.XOraSession")
      Set objdatabase = objSession.OpenDatabase(sid, username & "/" & password, 0&)

      strSQL = "select latitude, longitude, otherdata from scatter where otherdata > 50 order by latitude, longitude"

      ' This command takes a long time to execute when doing ranks/order bys
      Set OraDynaset = objdatabase.DBCreateDynaset(strSQL, 0&)

      Do While OraDynaset.EOF = False

      'Process the data and put on a spreadsheet
      'Status bar messages can be used here without problems
      OraDynaset.MoveNext

      Loop

      Set OraDynaset = Nothing
      Set objSession = Nothing
      objdatabase.Close
      Set objdatabase = Nothing

      End Sub

      When the DBCreateDynaset command is executed VBA waits until the dynaset data is retrieved from oracle. I would like to be able to do something similar to the pseudo code below.

      Execute SQL to create dynaset
      Do while still obtaining dynaset
      Check v$session_longops - Indicate time taken / remaining on status bar
      Loop

      I have found that if you execute a large query without any tasks that require the whole dataset before they can begin (ranks, ordering etc) the dynaset is created almost instantly and excel does not freeze up.

      With non dynaset operations such as inserts it is possible to run the query in non blocking mode. I am able to monitor the progress of the query and indicate it to the user.


      Dim myStatement As OraSqlStmt
      Set myStatement = objdatabase.CreateSql(strSQL, ORASQL_NONBLK)

      Do While myStatement.NonBlockingState = ORASQL_STILL_EXECUTING
      DoEvents
      Select Case intSpinCount
      Case 1
      Application.StatusBar = "Please Wait. Calculating data /"
      Case 2
      Application.StatusBar = "Please Wait. Calculating data -"
      Case 3
      Application.StatusBar = "Please Wait. Calculating data \"
      Case 4
      Application.StatusBar = "Please Wait. Calculating data |"
      intSpinCount = 0
      End Select
      Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 1)
      intSpinCount = intSpinCount + 1
      Loop

      Non blocking mode is explained here. However dynasets are not supported.

      http://download-west.oracle.com/docs/cd/A91202_01/901_doc/win.901/a90173/o4o00022.htm

      I have tried using pl/sql to insert the data requested into a temporary table and then the normal select statement to get the data back out. However since the data stored in a table is not ordered it isn’t much help to me since I still have to do the order by command.

      Anyone got any ideas?

      All help is much appreciated.