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
Recent Comments