Excel – Session Viewer with Query Capability

January 16, 2010

This is a simple example that shows how to query an Oracle database using user input, passing in the user specified values with bind variables.  While this example just queries V$SESSION, it is possible to expand this demonstration considerably to allow Excel to act as a command center for viewing SQL statements executed by sessions (with their execution plans), enable 10046 traces, and more.

To begin, we need to create two ActiveX command buttons in cells A1 through A3.  Name the top command button cmdInitialize, and the bottom button cmdFind.

Next, name the worksheet as DatabaseInfo, then right-click the worksheet DatabaseInfo tab name and select View Code.  Once in the Visual Basic editor, add a reference to the Microsoft ActiveX Data Objects, as demonstrated here.

Now, we need to add the code to make the cmdInitialize button work:

Option Explicit 'Forces all variables to be declared

Dim dbDatabase As New ADODB.Connection
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String

Dim intColumns As Integer
Dim strLastColumn As String

Private Function ConnectDatabase() As Integer
    Dim intResult As Integer

    On Error Resume Next

    If dbDatabase.State <> 1 Then
        'Connection to the database if closed
        strDatabase = "MyDB"
        strUserName = "MyUser"
        strPassword = "MyPassword"

        'Connect to the database
        'Oracle connection string
        dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

        dbDatabase.ConnectionTimeout = 40
        dbDatabase.CursorLocation = adUseClient
        dbDatabase.Open

        If (dbDatabase.State <> 1) Or (Err <> 0) Then
            intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")

            ConnectDatabase = False
        Else
            ConnectDatabase = True
        End If
    Else
        ConnectDatabase = True
    End If
End Function

Private Sub cmdInitialize_Click()
    Dim i As Integer
    Dim intResult As Integer
    Dim strSQL As String
    Dim snpData As ADODB.Recordset

    'Don't allow Excel to display an error message on the screen if an error happens while executing this
    '  procedure, we will handle the problem in-line in the code
    On Error Resume Next

    'Jump to our ConnectDatabase function which returns a value of True if we are connected to the database
    '  or False if the connection attempt failed
    intResult = ConnectDatabase

    'If we could not connect to the database, display a message for the user that something is wrong and stop
    '  the execution of the code in this module
    If intResult = False Then
        Exit Sub
    End If

    'Create the ADO object which will be used to retrieve the data from the database
    Set snpData = New ADODB.Recordset

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  *" & vbCrLf    'Retrieve all columns from the table without listing the columns
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  V$SESSION" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
  'ROWNUM is an Oracle only function - each row returned is assigned an increasing sequential value,
  '  essentially, I am telling Oracle to not retrieve any rows, as I am just interested in the column name
  '  and the data types in the PART table.  Using 0=1  in place of ROWNUM<1 will likely work on other database
  '  platforms.
    strSQL = strSQL & "  ROWNUM<1"

    'Pass the SQL statement into our database connection to return the matching rows
    snpData.Open strSQL, dbDatabase

    'Always verify that the SQL statement was able to be executed, and that the database server did not simply
    '  return an error message.  Failing to perform the check could result in a situation where the macro
    '  becomes stuck in an infinite loop.  State = 1 indicates that the SQL statement was executed, and that
    '  the recordset is available for use, but does not necessarily mean that there are any rows in the
    '  recordset
    If snpData.State = 1 Then
        For i = 0 To snpData.Fields.Count - 1
            'Let's try to determine the type of data that may be stored in the database column and output that
            '  to the Excel spreadsheet.  Doing so will help the user, and it will help the cmdFind_Click
            '  procedure determine how the bind variables should be set up
            Select Case snpData.Fields(i).Type
                Case adVarChar
                    'A string of characters
                    ActiveSheet.Cells(1, i + 2).Value = "String"
                Case adChar
                    'A fixed length string of characters, where values are padded with spaces as needed
                    ActiveSheet.Cells(1, i + 2).Value = "Character"
                Case adDate, 135
                    'A column which may contain date and time information
                    ActiveSheet.Cells(1, i + 2).Value = "Date"
                Case adNumeric, adSingle, adInteger, adDouble, 139
                    'A column which may contain integers, floating point numbers, but not imaginary numbers
                    ActiveSheet.Cells(1, i + 2).Value = "Number"
                Case Else
                    'What should we do with these types of columns, are they BLOBs, RAWs?
                    ActiveSheet.Cells(1, i + 2).Value = snpData.Fields(i).Type
            End Select
            'Output the name of the column on the second row in the spreadsheet
            ActiveSheet.Cells(2, i + 2).Value = snpData.Fields(i).Name
            'Blank out the third row in the spreadsheet so that the user may specify how the rows returned
            '  by the SQL statement should be restricted on that row
            ActiveSheet.Cells(3, i + 2).Value = ""
        Next i
        'Record the number of columns in the PART table for future reference
        intColumns = snpData.Fields.Count

        'Just for fun, output to the Debug window (View menu - Immediate Window the Excel column names
        '  for the 26th through the 100th columns in the spreadsheet just to make certain that our interesting
        '  looking formula below is working correctly
        For i = 26 To 100
            Debug.Print i, Chr(64 + Int((i + 2) / 26)); Chr(64 + ((i + 2) Mod 26 + 1))
        Next i
        'Chr returns the character represented by the ASCII/ANSI value specified.  An uppercase letter A has
        '  an ASCII/ANSI value of 65, so the first column based on the formula would be Chr(64 + 1) = A
        'Mod is a function which returns the remainder after a number is divided by another number
        '  28 Mod 26 would equal 2  as 28/26 = 1 with a remainder of 2, are to be mathematically fancy:
        '  (28 / 26 - Int(28 / 26)) * 26
        '  Thus Mod produces a repeating sequence from 0 to one less than the number following the word Mod
        strLastColumn = Chr(64 + Int((intColumns + 2) / 26)) & Chr(64 + ((intColumns + 2) Mod 26 + 1))

        'Close the ADO recordset to free up memory on the database server since we are done using the data
        snpData.Close

        'Make certain that the full column names of the various columns in the PART table are visible in the
        '  spreadsheet
        ActiveSheet.Columns("A:" & strLastColumn).AutoFit
    End If

    'Erase any rows in the spreadsheet that may have been left by a previous execution of the cmdFind code
    Worksheets("DatabaseInfo").Range("4:50000").Delete Shift:=xlUp

    'Remove the ADO recordset object that we created earlier from memory - in theory this happens automatically
    '  but it is good practice to explicitly perform the operation
    Set snpData = Nothing
End Sub 

Now, switch back to the Excel window to verify that the cmdInitialize button works correctly.  You should see something like this:

Notice that row 1 in the worksheet shows the data type of the columns in the V$SESSION view (204 is a RAW data type) and row 2 in the worksheet shows the column names.  Row 3 will be used to allow the user to restrict the rows that will be returned.  Next, we need to add the code to the cmdFind button.  Switch back to the Visual Basic editor and add the following code:

Private Sub cmdFind_Click()
    Dim i As Integer
    Dim lngRow As Long
    Dim strSQL As String
    Dim snpData As ADODB.Recordset
    Dim comData As ADODB.Command

    'Create the in-memory ADO objects that will be used to return the data from the PART table
    Set snpData = New ADODB.Recordset
    Set comData = New ADODB.Command

    With comData
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  *" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SESSION" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  1=1" & vbCrLf

        'Walk through the columns to determine which have restrictions placed on them by the user
        For i = 1 To intColumns
            If ActiveSheet.Cells(3, i).Value <> "" Then
                'The user placed a striction on this column
                If (InStr(ActiveSheet.Cells(3, i).Value, "%") > 0) Or (InStr(ActiveSheet.Cells(3, i).Value, "_") > 0) Then
                    'Partial match, the column name is in row 2 of the spreadsheet
                    strSQL = strSQL & "  AND " & ActiveSheet.Cells(2, i).Value & " LIKE ?" & vbCrLf
                    'We need to look in row 1 for the data type of the column and set up an appropriate bind
                    '  variable data type to pass in the restriction requested by the user
                    'Each bind variable must have a unique name, so we generate one as  ValueCol#
                    Select Case ActiveSheet.Cells(1, i).Value
                        Case "String"
                            .Parameters.Append .CreateParameter("value" & Format(i), adVarChar, adParamInput, Len(ActiveSheet.Cells(3, i).Value), ActiveSheet.Cells(3, i).Value)
                        Case "Character"
                            .Parameters.Append .CreateParameter("value" & Format(i), adChar, adParamInput, Len(ActiveSheet.Cells(3, i).Value), ActiveSheet.Cells(3, i).Value)
                        Case "Number"
                            'A partial match on a number is not possible, just including to see what happens
                            .Parameters.Append .CreateParameter("value" & Format(i), adNumeric, adParamInput, 12, ActiveSheet.Cells(3, i).Value)
                        Case "Date"
                            'A partial match on a date is not possible, just including to see what happens
                            .Parameters.Append .CreateParameter("value" & Format(i), adDate, adParamInput, 8, CDate(ActiveSheet.Cells(3, i).Value))
                    End Select
                Else
                    'Full match, the column name is in row 2 of the spreadsheet
                    strSQL = strSQL & "  AND " & ActiveSheet.Cells(2, i).Value & " = ?" & vbCrLf
                    'We need to look in row 1 for the data type of the column and set up an appropriate bind
                    '  variable data type to pass in the restriction requested by the user
                    'Each bind variable must have a unique name, so we generate one as  ValueCol#
                    Select Case ActiveSheet.Cells(1, i).Value
                        Case "String"
                            .Parameters.Append .CreateParameter("value" & Format(i), adVarChar, adParamInput, Len(ActiveSheet.Cells(3, i).Value), ActiveSheet.Cells(3, i).Value)
                        Case "Character"
                            .Parameters.Append .CreateParameter("value" & Format(i), adChar, adParamInput, Len(ActiveSheet.Cells(3, i).Value), ActiveSheet.Cells(3, i).Value)
                        Case "Number"
                            .Parameters.Append .CreateParameter("value" & Format(i), adNumeric, adParamInput, 12, ActiveSheet.Cells(3, i).Value)
                        Case "Date"
                            .Parameters.Append .CreateParameter("value" & Format(i), adDate, adParamInput, 8, CDate(ActiveSheet.Cells(3, i).Value))
                    End Select
                End If
            End If
        Next i
        'We will sort the rows by the part ID
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  SID"

        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase
    End With

    Set snpData = comData.Execute

    lngRow = 3 'We will start outputting at row 4, so 3 is our "0" line - the starting point

    'The slow way to populate the cells
'    If Not (snpData Is Nothing) Then
'        Do While Not snpData.EOF
'            'Increase the row number so that we do not output all of the information on the same row of the
'            '  spreadsheet
'            lngRow = lngRow + 1
'            'Output the data returned by the SQL statement, one column at a time.  The first column is in the
'            '  0 position, and the last column is one less than the total number of columns returned
'            For i = 0 To snpData.Fields.Count - 1
'                ActiveSheet.Cells(lngRow, i + 2).Value = snpData.Fields(i)
'            Next i
'            snpData.MoveNext
'        Loop
'
'        snpData.Close
'    End If
'
'    'Do we have extra rows left over from the last run?  If so, delete all rows below the last row that we output
'    Worksheets("DatabaseInfo").Range(Format(lngRow + 1) & ":50000").Delete Shift:=xlUp

    'The fast way to place the query results into cells   
    Worksheets("DatabaseInfo").Range(Format(lngRow + 1) & ":50000").Delete Shift:=xlUp
    If Not (snpData Is Nothing) Then

        ActiveSheet.Range("B4").CopyFromRecordset snpData

        ActiveSheet.Range("B4").Select

        snpData.Close
    End If

    'Tell Excel to fix the column widths so that all of the data returned in each column is visible
    'We recorded the value of strLastColumn in the initialize procedure
    ActiveSheet.Columns("B:" & strLastColumn).AutoFit

    'Memory clean up
    Set snpData = Nothing
    Set comData = Nothing
End Sub

Switch back to the Excel worksheet and test the cmdFind button.  You should see something like this:

Next, try to enter a search keyword in row 3 – if a wildcard character ( % or _ ) is used, the query will use a LIKE keyword, rather than an = operator.  After entering the search criteria, click the Find button:

There is no need to stop at this point.  It is easy to add a UserForm to the Excel workbook, for example something like this from another demonstration: 

For example, to enable a trace for a session, you could create a function like this:

Sub SetTraceInSession(lngSID As Long, lngSerial As Long, lngTrace As Long, lngTraceLevel As Long)
    Dim cmdTrace As New ADODB.Command
    Dim strSQL As String

    On Error Resume Next

    With cmdTrace
        strSQL = "SYS.DBMS_SYSTEM.SET_EV(" & Format(lngSID) & "," & Format(lngSerial) & "," & Format(lngTrace) & "," & Format(lngTraceLevel) & ",'')"
        .CommandText = strSQL
        .CommandType = adCmdStoredProc
        .ActiveConnection = dbDatabase
    End With

    cmdTrace.Execute

    Set cmdTrace = Nothing
End Sub