Excel – The Graphical Master of Oracle Foreign Keys

30122009

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:

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:

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.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: