Excel – The Graphical Master of Oracle Foreign Keys

30 12 2009

December 30, 2009

Foreign keys… those practical rules that help maintain data integrity between parent and child tables, allow for table eliminations in queries beginning with Oracle release 10.2.0.1, and provide the potential for the optimizer to generate additional predicates during query optimization for better cardinality estimates.

Those benefits all sound like they might be helpful to someone, but what good are they for the average person (I mean the average developer)?  Foreign keys are great for generating abstract art (note that I am a poor judge of art), with a bunch of odd writing all over the place.

This example demonstrates how to query an Excel spreadsheet as if it were a database.  The results of the queries are used to build a graphical model of the relationships between the tables based on the foreign key relationships that are established in the database.  Excel 2003 limits text boxes to no more than 255 characters, so use Excel 2007 or later for this example, if possible.

First, we need to build the source Excel spreadsheet that will act as the database that will be queried by the second Excel spreadsheet.  The source Excel spreadsheet will have two worksheets (tabs) named “Data Dict Tables” and “Data Dict Foreign Keys”.  Use the Microsoft Query Tool, or another approach to bring in the data from the Oracle data dictionary.  The queries for each of those worksheets follows:

Data Dict Tables

SELECT
  DT.OWNER,
  DT.TABLE_NAME,
  DTC.COLUMN_NAME,
  DTC.DATA_TYPE,
  DTC.DATA_LENGTH,
  DTC.DATA_PRECISION,
  DTC.DATA_SCALE,
  DTC.NULLABLE,
  DTC.COLUMN_ID,
  DT.TABLESPACE_NAME,
  DTCC.COMMENTS TABLE_COMMENTS,
  SUBSTR(DCC.COMMENTS,1,255) COLUMN_COMMENTS
FROM
  DBA_TABLES DT,
  DBA_TAB_COLUMNS DTC,
  DBA_TAB_COMMENTS DTCC,
  DBA_COL_COMMENTS DCC
WHERE
  DT.OWNER=DTC.OWNER
  AND DT.TABLE_NAME=DTC.TABLE_NAME
  AND DT.OWNER=DTCC.OWNER(+)
  AND DT.TABLE_NAME=DTCC.TABLE_NAME(+)
  AND DTC.OWNER=DCC.OWNER(+)
  AND DTC.TABLE_NAME=DCC.TABLE_NAME(+)
  AND DTC.COLUMN_NAME=DCC.COLUMN_NAME(+)
ORDER BY
  DT.OWNER,
  DT.TABLE_NAME,
  DTC.COLUMN_ID;

— 

Data Dict Foreign Keys

SELECT
  DC2.OWNER,
  DC2.CONSTRAINT_NAME PKEY_CONTRAINT,
  DC1.CONSTRAINT_NAME FKEY_CONTRAINT,
  DCC2.TABLE_NAME PKEY_TABLE_NAME,
  DCC2.COLUMN_NAME PKEY_COLUMN_NAME,
  DCC1.TABLE_NAME FKEY_TABLE_NAME,
  DCC1.COLUMN_NAME FKEY_COLUMN_NAME,
  DCC1.POSITION
FROM
  DBA_CONSTRAINTS DC1,
  DBA_CONSTRAINTS DC2,
  DBA_CONS_COLUMNS DCC1,
  DBA_CONS_COLUMNS DCC2
WHERE
  DC1.CONSTRAINT_TYPE='R'
  AND DC1.R_OWNER=DC2.OWNER
  AND DC1.R_CONSTRAINT_NAME=DC2.CONSTRAINT_NAME
  AND DC1.OWNER=DCC1.OWNER
  AND DC1.CONSTRAINT_NAME=DCC1.CONSTRAINT_NAME
  AND DC2.OWNER=DCC2.OWNER
  AND DC2.CONSTRAINT_NAME=DCC2.CONSTRAINT_NAME
  AND DCC1.POSITION=DCC2.POSITION
  AND DC1.OWNER='SYSADM'
ORDER BY
  DC2.OWNER,
  DCC2.TABLE_NAME,
  DCC1.TABLE_NAME,
  DCC2.POSITION;

The source spreadsheet with the data dictionary details should be saved to the root of the C:\ drive with the name C:\Data Dictionary.xls

In a second spreadsheet we are trying to build the following interface:

Next, we need to add a couple ActiveX controls to the second spreadsheet:
B3: Combo Box  with a name of cboTableName with a blank Text value
C3: Check Box  with a name of chkAliasNames with a Caption of Alias Table Names
C1: Command Button  with a name of cmdInitialize with a Caption of Initialize
A1, A3, B1 – type in the text as shown above

Once we add the macro code, clicking the Initialize button will send a query to the other spreadsheet to retrieve a list of the tables:

And with all of the code in place, selecting a table builds the abstract art:

Running down the left side is the list of columns in the selected table.  Every foreign key that is defined against the selected table will trigger a recursive lookup for child tables of the child table, with the necessary join conditions listed as well as the columns that are defined in the child table.  For instance, there is a foreign key on the CO_PRODUCT table that points back to the primary key on the PART table, and the join between the tables should be P.ID = CP.PART_ID.  The CO_PRODUCT table does not have any child tables.

Skipping down to the CUST_ORDER_LINE table, it has a foreign key that references the PART table on P.ID = COL.PART_ID.  The RECEIVABLE_LINE table has a foreign key that references the CUST_ORDER_LINE table on COL.CUST_ORDER_ID = RL.CUST_ORDER_ID AND COL.LINE_NO = RL.CUST_ORDER_LINE_NO.  The RECV_LINE_BINARY table has a foreign key that references the RECEIVABLE_LINE table on RL.INVOICE_ID = RLB.INVOICE_ID AND RL.LINE_NO = RLB.RECV_LINE_NO.  With this tool we just discovered a way to join the PART table to the RECV_LINE_BINARY by analyzing the defined foreign keys.

OK, enough fun, lets enter the code.  Right-click the worksheet name (probably Sheet1) rename the sheet to ExcelQueryOfExcel and then right-click the sheet and to select View Code.  Let’s start with making the Initialize button work:

'Need to add a reference to Microsoft ActiveX Data Objects 2.8 Library before starting
'Declare a connection object in the general section to hold the connection to the database
Dim dbExcel As New ADODB.Connection

'Declare a set of variables to hold the username and password for the database
Dim strUserName As String
Dim strPassword As String
Dim strDatabase As String

Private Function ConnectDatabase() As Integer
    Dim intResult As Integer

    On Error Resume Next

    If dbExcel.State <> 1 Then
        'Connection to the database if closed
        strDatabase = Sheets("ExcelQueryOfExcel").Cells(1, 2).Value

        strUserName = ""
        strPassword = ""

        'Connect to the database
        dbExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

        dbExcel.ConnectionTimeout = 40
        dbExcel.CursorLocation = adUseClient
        dbExcel.Open

        If (dbExcel.State <> 1) Or (Err <> 0) Then
            intResult = MsgBox("Could not connect to the Excel Database." & 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 intResult As Integer
    Dim strSQL As String
    Dim strLastTable As String
    Dim snpData As ADODB.Recordset

    On Error Resume Next

    cboTableName.Clear
    cboTableName = ""
    Sheets("ExcelQueryOfExcel").Range("A6:Z10006").Delete Shift:=xlUp

    intResult = ConnectDatabase
    strLastTable = ""

    If intResult = True Then
        Set snpData = New ADODB.Recordset

        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  *" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  [Data Dict Tables$]" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  2"     '  Sort the rows by the second column in the data source
        snpData.Open strSQL, dbExcel

        If snpData.State = 1 Then
            Do While Not snpData.EOF
                If strLastTable <> snpData("table name") Then
                    strLastTable = snpData("table name")
                    cboTableName.AddItem snpData("table name")
                End If

                snpData.MoveNext
            Loop
            snpData.Close
        Else
            intResult = MsgBox("Could not send the SQL statement to the Excel Database." & vbCrLf & Error(Err), 16, "Excel Demo")
        End If

        Set snpData = Nothing
    End If
End Sub

The above code should be sufficient for the Initialize button to work – you may need to turn off Design Mode in Excel for the button to work.

Now we need to work on the recursion when a table is selected (and the rest of the code):

Private Function FindRelatedTables(strTableName As String, lngColumn As Long, lngRow As Long, intAliasTableNames As Integer) As Long
    Dim i As Integer
    Dim lngBoxLeft As Long
    Dim lngBoxTop As Long
    Dim lngBoxWidth As Long
    Dim lngBoxHeight As Long
    Dim lngResult As Long
    Dim intTableColumns As Integer
    Dim intForeignColumns As Integer
    Dim intFlag As Integer
    Dim strOut As String
    Dim strLastTable As String
    Dim strSplit() As String  'Use to alias the table names
    Dim strPrimary As String  'Stores the aliased table primary key table name
    Dim strForeign As String  'Stores the aliased table foreign key table name
    Dim strSQL As String
    Dim snpData As ADODB.Recordset
    Dim snpDataForeign As ADODB.Recordset

    On Error Resume Next

    If intAliasTableNames = True Then
        strPrimary = ""
        strSplit = Split(strTableName, "_")

        For i = 0 To UBound(strSplit)
            strPrimary = strPrimary & Left(strSplit(i), 1)
        Next i
    End If

    Set snpDataForeign = New ADODB.Recordset
    Set snpData = New ADODB.Recordset

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  *" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  [Data Dict Foreign Keys$]" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  [Pkey Table Name] = '" & strTableName & "'" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  [Fkey Table Name]," & vbCrLf
    strSQL = strSQL & "  [Position]"
    snpDataForeign.Open strSQL, dbExcel

    If snpDataForeign.State = 1 Then
        If Not snpDataForeign.EOF Then
            strOut = strTableName & " " & strPrimary & " :" & vbLf
            intTableColumns = 0
            intForeignColumns = 0
            strLastTable = snpDataForeign("Fkey Table Name")
            Do While Not snpDataForeign.EOF
                If intAliasTableNames = True Then
                    strForeign = ""
                    strSplit = Split(snpDataForeign("Fkey Table Name"), "_")

                    For i = 0 To UBound(strSplit)
                        strForeign = strForeign & Left(strSplit(i), 1)
                    Next i
                    'Verify that the two aliases are not identical
                    If strForeign = strPrimary Then
                        strForeign = strForeign & "F"
                    End If
                    strOut = strOut & strPrimary & "." & snpDataForeign("Pkey Column Name") & " = " & strForeign & "." & snpDataForeign("Fkey Column Name") & vbLf
                Else
                    strOut = strOut & snpDataForeign("Pkey Table Name") & "." & snpDataForeign("Pkey Column Name") & " = " & snpDataForeign("Fkey Table Name") & "." & snpDataForeign("Fkey Column Name") & vbLf
                End If

                intForeignColumns = intForeignColumns + 1

                snpDataForeign.MoveNext

                'See if we need to retrieve the table columns for the previous table
                intFlag = False
                If snpDataForeign.EOF Then
                    intFlag = True
                Else
                    If strLastTable <> snpDataForeign("Fkey Table Name") Then
                        intFlag = True
                    End If
                End If

                If intFlag = True Then
                    strSQL = "SELECT" & vbCrLf
                    strSQL = strSQL & "  *" & vbCrLf
                    strSQL = strSQL & "FROM" & vbCrLf
                    strSQL = strSQL & "  [Data Dict Tables$]" & vbCrLf
                    strSQL = strSQL & "WHERE" & vbCrLf
                    strSQL = strSQL & "  [Table Name] = '" & strLastTable & "'" & vbCrLf
                    strSQL = strSQL & "ORDER BY" & vbCrLf
                    strSQL = strSQL & "  [Column Id]"     '  Sort the rows by the second column in the data source
                    snpData.Open strSQL, dbExcel
                    If snpData.State = 1 Then
                        If Not snpData.EOF Then
                            strOut = strOut & vbLf & strLastTable & " " & strForeign & " :" & vbLf
                            intTableColumns = 0
                            Do While Not snpData.EOF
                                strOut = strOut & snpData("column name") & vbLf
                                intTableColumns = intTableColumns + 1

                                snpData.MoveNext
                            Loop
                        End If
                    End If
                    snpData.Close

                    'Create the text box
                    'Strip off the trailing CrLf
                    strOut = Left(strOut, Len(strOut) - 1)

                    lngBoxLeft = lngColumn * 200
                    lngBoxWidth = 200
                    lngBoxTop = lngRow * 20
                    lngBoxHeight = (intTableColumns + intForeignColumns + 3) * 5.5

                    With Sheets("ExcelQueryOfExcel").Shapes.AddTextbox(msoTextOrientationHorizontal, lngBoxLeft, lngBoxTop, lngBoxWidth, (lngBoxHeight * 2 - 1))
                        With .TextFrame
                            If Application.Version <= 11 Then
                                'There is a 255 character limit on text boxes before Office 2007
                                .Characters.Text = Left(strOut, 255)
                            Else
                                .Characters.Text = strOut
                            End If
                            .Characters.Font.Size = 8
                        End With
                        .Fill.ForeColor.RGB = RGB(255 - lngColumn * 10, 255 - lngColumn * 10, 255)
                    End With

                    'With Sheets("ExcelQueryOfExcel").Shapes.AddTextbox(msoTextOrientationHorizontal, lngBoxLeft, lngBoxTop, lngBoxWidth, (lngBoxHeight * 2 - 1))
                    '    With .TextFrame
                    '        .Characters.Text = strOut
                    '        .Characters.Font.Size = 8
                    '    End With
                    '    .Fill.ForeColor.RGB = RGB(255 - lngColumn * 10, 255 - lngColumn * 10, 255)
                    'End With

                    If lngColumn < 6 Then
                        'Only recursively call to 6 levels
                        'Recursive call into this function
                        lngResult = FindRelatedTables(strLastTable, lngColumn + 1, lngRow, intAliasTableNames)
                    End If

                    'Prepare for the next text box
                    strOut = strTableName & " " & strPrimary & " :" & vbLf
                    intTableColumns = 0
                    intForeignColumns = 0
                    lngRow = lngRow + 3
                End If
                If Not snpDataForeign.EOF Then
                    strLastTable = snpDataForeign("Fkey Table Name")
                End If

                If lngRow > 5000 Then
                    'Protection against a very long wait
                    Exit Do
                End If
            Loop
        End If
        snpDataForeign.Close
    End If

    Set snpDataForeign = Nothing
    Set snpData = Nothing
End Function

Private Sub FindTable()
    Dim intAliasTableNames As Integer
    Dim intResult As Integer
    Dim lngResult As Long
    Dim lngRow As Long
    Dim strSQL As String
    Dim strTableName As String
    Dim snpData As ADODB.Recordset

    On Error Resume Next

    intAliasTableNames = chkAliasNames.Value
    lngRow = 6

    Sheets("ExcelQueryOfExcel").Range("A" & Format(lngRow) & ":Z" & Format(lngRow + 10000)).Delete Shift:=xlUp

    If cboTableName <> "" Then
        Application.ScreenUpdating = False

        strTableName = cboTableName
        Set snpData = New ADODB.Recordset

        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  *" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  [Data Dict Tables$]" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  [Table Name] = '" & strTableName & "'" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  [Column Id]"     '  Sort the rows by the second column in the data source
        snpData.Open strSQL, dbExcel
        If snpData.State = 1 Then
            Sheets("ExcelQueryOfExcel").Cells(lngRow, 1).Value = strTableName
            Sheets("ExcelQueryOfExcel").Cells(lngRow, 1).Font.Bold = True
            lngRow = lngRow + 1

            If Not snpData.EOF Then
                Do While Not snpData.EOF
                    Sheets("ExcelQueryOfExcel").Cells(lngRow, 1).Value = snpData("column name")
                    lngRow = lngRow + 1

                    snpData.MoveNext

                    'Safety net
                    If lngRow > 1000 Then
                        Exit Do
                    End If
                Loop

                lngResult = FindRelatedTables(strTableName, 1, 5, intAliasTableNames)
            End If
            snpData.Close
        Else
            intResult = MsgBox("Could not send the SQL statement to the Excel Database." & vbCrLf & Error(Err), 16, "Excel Demo")
        End If

        Set snpData = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub cboTableName_Click()
    FindTable
End Sub

Private Sub cboTableName_GotFocus()
    cboTableName.SelStart = 0
    cboTableName.SelLength = Len(cboTableName)
End Sub

Private Sub cboTableName_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If (KeyCode = 13) Or (KeyCode = 8  ) Then
        FindTable
    End If
End Sub

Private Sub chkAliasNames_Click()
    FindTable
End Sub

I will not try here to explain how all of the above code works.  I created this demonstration for a presentation that I gave a couple months ago.  It really should be easy to understand if you step through the code starting with the cboTableName_Click Sub.








Follow

Get every new post delivered to your Inbox.

Join 139 other followers