KEEP Pool – What is Wrong with this Quote?

28 08 2011

August 28, 2011

The book “Oracle Database 11g Performance Tuning Recipes” is out of the alpha stage – if you bought the alpha version from Apress, you should now be able to download the finished book.  I am currently reviewing the book review notes that I started collecting when reading the alpha version, so that I may see if any problems I found in the alpha version of the book are also found in the final printed version of the book.  My final version of the book review for this book could be interesting… stay tooned (yes, an intentional typo).

One of the sections of the book that survived the alpha stage of the book is Recipe 3-2 Managing Buffer Pools, found on page 87.  The first paragraph under the Solution heading states the following (note that I removed a couple of sentences from the middle of the paragraph, so the words in [brackets] were added to paraphrase the other sentences):

“You can use multiple buffer pools instead of Oracle’s single default buffer pool, to ensure that frequently used segments stay cached in the buffer pool without being recycled out of the buffer pool…  For example, if you want a segment to be cached (or pinned) in the buffer pool, you must specify the KEEP buffer pool [when creating the segment].”

*** Note that this section of the book is NOT suggesting the use of multiple block sizes in a single database, but is trying to suggest why KEEP and RECYCLE buffer pools should be used in addition to the DEFAULT buffer pool.

What, if anything, is wrong with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

Book Review: Beginning Oracle SQL

23 08 2011

August 23, 2011

The Ideal Guide for Beginning to Intermediate Oracle SQL: Providing the Logical Reasoning Behind the Approach to Understanding and Solving Data Access Challenges

The “Beginning Oracle SQL” book is an updated version of the book “Mastering Oracle SQL and SQL*Plus” which covered Oracle release versions through  The original version, while extremely well written, suffered from a handful of typesetting flaws that resulted in most pages appearing without page numbers, initial words of some paragraphs discarded, left-most columns of some tables mangled, and a small number of typos.  The “Beginning Oracle SQL” book corrects the typesetting flaws, eliminates sections of the original book that are obsolete due to Oracle product changes, eliminates SQL*Plus related sections that can be easily found in the Oracle documentation and those sections that are deemed too advanced, and adds a couple of new sections.  I purchased two copies of the “Beginning Oracle SQL” book, a Google Books version through the Android market on a Motorola Xoom (unfortunately, the Google Books app crashes when displaying book pages 33 and 34 side-by-side,  pages 56 and 57 as displayed in Google Books), and a PDF copy from Apress.

There are several Oracle SQL books on the market, and I have had the opportunity to read a couple of those books.  This book, much like the original, is exceptional in comparison with other SQL language books, and seems to be suitable for use in classroom environments due to the inclusion of the exploratory excises at the end of each chapter (I did not spend much time reviewing these exercises or the Appendix contents).  The book offers a carefully constructed progression of topics that build on prior material presented in the book.

Concepts Covered by the Book, Book Specific Features, and Quick Observations:

  • Why do we implement the various types of database constraints.
  • Forms an early distinction between the database and the software database management system, while other books muddy the term “database”.
  • Demonstrates how to get started with SQL*Plus, edit lines of the SQL statement that is currently in the buffer, and save the SQL statement that is currently in the buffer.
  • Builds a foundation of why things are as they are, and how to find where things are located before diving too deeply into SQL syntax.
  • Downloaded source code examples seems to work.
  • Plenty of forward and backward references within the book, and external references to other resources.
  • Components of the SQL statement, column aliases, regular expressions (page 125).
  • Several of the chapters (4, 5, 6, 9, 12) include SQL statements without showing the typical SQL*Plus SQL> prompt and line numbers – the formatting without SQL> is helpful when copying the SQL statements from the PDF file for execution in SQL*Plus (holding the Alt key while selecting the text is a work-around for the other chapters).
  • Changing the default object schema used by a session: alter session set current_schema=scott; (page 189).
  • Recovering a dropped table: flashback table history to before drop; (page 190).
  • Correctly states the facts, while other books state otherwise: “If you drop a table, you implicitly drop certain dependent database objects, such as indexes, triggers, and table privileges granted to other database users. You also invalidate certain other database objects, such as views and packages,” (page 190).
  • Cartesian products (page 198), equijoins (page 198), non-equijoins (page 199), self-joins (page 201), natural joins (page 203), outer joins (page 205 – other Oracle specific and ANSI))
  • Offers advice to pick a SQL formatting convention and adhere to that convention (page 199).
  • Book repeatedly reminds the reader of potential problems caused by NULL values, and includes several examples which show where NULL values may cause unexpected problems for developers.
  • Nugget of advice: Test performance of a query not only with the typically small datasets found in development environments, but also with larger datasets that are more typical of production systems (page 237).
  • Correlated subqueries (page 237).
  • Scalar subqueries – subqueries in the SELECT clause (page 243).
  • Inline views – subqueries in the FROM clause (page 244).
  • Demonstrates throughout the book proper techniques for table alias names, rather than just using generic aliases such as A, B, C, etc.
  • USER_UPDATABLE _COLUMNS view shows which view and table columns are possible to update through inserts, updates, and deletes (page 277).
  • Brief mention of Instead of Triggers to permit the data normally returned by a view to be updated (page 278).
  • Materialized views and automatic query rewrite (page 283).
  • Substitution variables in SQL*Plus (page 288).
  • User-defined variables in SQL*Plus (page 290).
  • Implicit user-defined variables in SQL*Plus (page 291).
  • PROMPT, PAUSE, and ACCEPT  SQL*Plus commands (page 292).
  • Common SQL*Plus system variables (page 294).
  • Bind variables (page 298).
  • login.sql and glogin.sql scripts for customizing the SQL*Plus environment (page 305).
  • SQL*Plus MARKUP setting to produce HTML formatted output for SQL statements (page 318).
  • Scripting with SQL*Plus using input parameters for scripts (page 322).
  • Passing values between scripted SQL statements with the NEW_VALUE clause in SQL*Plus (page 323).
  • Passing values between scripted SQL statements with the help of bind variables (page 324).
  • SQL*Plus WHENEVER command to react to certain errors during script execution (page 325).
  • VARARRAYs in custom data types (page 332).
  • Inserting VARARRAY values into table columns (page 333).
  • Nested tables (page 336).
  • Multiset operators with nested tables (page 341).
  • Using CAST and COLLECT to convert an array to a nested table (page 346).

Much like the “Expert Oracle Database Architecture” books, this book uses Oracle error messages to teach SQL troubleshooting techniques.  Unfortunately, most of these Oracle error messages are not highlighted in the index at the back of the book, so it is a difficult to locate a specific error message and its solution.  Oracle Error Messages as Teachable Moments:

  • Chapter 2: ORA-00904: “column”: invalid identifier (page 44, 48); ORA-00933: SQL command not properly ended (page 63)
  • Chapter 3: ORA-04043: object “owner”.”name” does not exist (page 77)
  • Chapter 4: ORA-00920: invalid relational operator (page 97); ORA-00913: too many values (page 106); ORA-01427: single-row subquery returns more than one row (page 108)
  • Chapter 5: ORA-01722: invalid number (page 118)
  • Chapter 6: ORA-00001: unique constraint ( violated (page 148); ORA-02292: integrity constraint ( violated – child record found (page 156); ORA-01555: Snapshot too old (page 161)
  • Chapter 7: ORA-02292: integrity constraint ( violated – child record found (page 172); ORA-02449: unique/primary keys in table referenced by foreign keys (page 191); ORA-08002: sequence sequencename.CURRVAL is not yet defined in this session (page 193)
  • Chapter 8: ORA-00918: column ambiguously defined (page 197); ORA-00937: not a single-group group function (page 216, 221); ORA-00934: group function is not allowed here (page 218, 219); ORA-00979: not a GROUP BY expression (page 221)
  • Chapter 9: ORA-01436: CONNECT BY loop in user data (page 249); ORA-00907: missing right parenthesis (page 254); ORA-30483: window functions are not allowed here (page 258); ORA-01466: unable to read data – table definition has changed (page 261)
  • Chapter 10: ORA-00957: duplicate column name (page 269); ORA-01400: cannot insert NULL into (???) (page 275); ORA-01732: data manipulation operation not legal on this view (page 276); ORA-01752: cannot delete from view without exactly one key-preserved table (page 276); ORA-01779: cannot modify a column which maps to a non key-preserved table (page 276); ORA-01402: view WITH CHECK OPTION where-clause violation (page 280)
  • Chapter 11: ORA-00936: missing expression (page 302); ORA-01403: no rows found (page 325)
  • Chapter 12: ORA-00904: “owner”.”table”: invalid identifier (page 338)
  • Appendix B: ORA-01839: date not valid for month specified (page 373); ORA-00001: unique constraint ( violated (page 376); ORA-02297: cannot disable constraint (owner.constraint) – dependencies exist (page 400)

The Oracle specific SQL syntax is quite broad, and while this book makes a great effort to cover much of the syntax, it does not starve the “Pro Oracle SQL” book (the intermediate to advanced SQL book) of too few advanced topics.  The “Beginning Oracle SQL” book covers the following SQL keywords:


  • OR (page 94)
  • AND (page 95)
  • NOT (page 96)
  • BETWEEN (page 98)
  • IN (page 99)
  • LIKE (page 100)
  • CASE (page 101)
  • GROUP BY (page 208)
  • HAVING (page 217)
  • GROUP BY ROLLUP (page 222)
  • GROUP BY CUBE (page 223)
  • PARTITION BY in the FROM clause (page 227)
  • ANY and ALL operators with subqueries (page 234)
  • EXISTS operator (page 238)
  • WITH clause – subquery factoring (page 245)
  • SIBLINGS keyword in the ORDER BY clause (page 251)
  • AS OF – for use with Flashback Query (page 260)
  • VERSIONS BETWEEN operator – for use with Flashback Query (page 262)
  • WITH READ ONLY (page 267)
  • WITH CHECK OPTION (page 278)


  • REGEXP_LIKE (page 128)
  • REGEXP_INSTR (page 129)
  • REGEXP_SUBSTR (page 130)
  • REGEXP_REPLACE (page 130)
  • NEW_TIME (page 132)
  • EXTRACT (page 132)
  • ROUND (page 133)
  • TRUNC (page 133)
  • MONTHS_BETWEEN (page 133)
  • ADD_MONTHS (page 133)
  • NEXT_DAY (page 134)
  • LAST_DAY (page 134)
  • NULLIF, COALESCE, NVL2 (page 135)
  • GREATEST (page 135)
  • LEAST (page 135)
  • NVL (page 136)
  • DECODE (page 136)
  • TO_NUMBER (page 138)
  • TO_CHAR (page 138)
  • TO_DATE (page 138)
  • CAST (page 141)
  • GROUPING function (page 224)
  • GROUPING_ID function (page 225)
  • RANK analytic function (page 253)
  • LAG analytic function (page 256)
  • DENSE_RANK analytic function (page 258)

SQL Statement Types:

  • INSERT (page 146)
  • UPDATE (page 151)
  • DELETE (page 154)
  • MERGE (page 157)

Data Definition:

  • CREATE TABLE (pages 72, 163)
  • DROP TABLE (page 154, 189)
  • ALTER TABLE (page 168)
  • RENAME (just a brief mention on page 169)
  • Constraints (page 170)
  • CREATE SCHEMA (page 176)
  • CREATE INDEX (page 179)
  • ALTER INDEX (page 181)
  • CREATE SEQUENCE (page 185)
  • CREATE SYNONYM (page 186)
  • TRUNCATE TABLE (page 191)
  • COMMENT (page 191)
  • CREATE VIEW (page 266)
  • CREATE TYPE (page 336, 339)

If you own the “Mastering Oracle SQL” book, you would see the following changes when reading this book (I might have missed a few changes):

  • New section “Other Query Languages, Really” – includes a brief overview of some of the query languages used by various database management systems and the different SQL dialects used by Oracle SQL, Microsoft SQL Server, and IBM DB2 (page 9).
  • New Figure 1-1 that shows the relationship between rows and columns for the DEPARTMENTS table (page 11).
  • New Figure 1-2 that shows NULL values in the EMPLOYEES table (page 13).
  • New section that introduces SQL Developer – provides just enough information to help someone who might find SQL*Plus to be too difficult to use (page 58).
  • New tip that indicates that access to the DUAL table starting in 10g is less resource intensive than using a custom table to perform the same function (page 89).
  • An explanation was added to explain why a NOT IN clause with NULL as one of the items failed to return any rows (page 114).
  • The multitable INSERT command syntax was removed from this edition of the book (it is now described in the “Pro Oracle SQL” book) and is replaced with an extended description that should help those who need to perform an INSERT using a SELECT statement for the row data source.
  • Provides enhanced descriptions for examples, such as the missing explanation of the UPDATE examples, which now appears on pages 153, 154-156.
  • Slightly smaller font size than the original book – more information per page.
  • Removed the discussion of the SQL*Plus AUTOCOMMIT option, frequency of COMMITs, and SAVEPOINTs just prior to section 6.6.
  • Removed the SET TRANSACTION discussion from section 6.6.
  • Section 7.6 shows using SQL Developer to display autotrace statistics rather than using SQL*Plus as in the previous edition (all mention of iSQL*Plus have been removed in the book).
  • New description added to section 9.1 that is dedicated to demonstrating how NULLs can be problematic in EXISTS, IN, NOT EXISTS, and NOT IN clauses (pages 240-243).
  • Removed the ROWNUM and Top-N SQL section (pages 245).
  • Extended example that shows why subquery factoring might be helpful.  Also included is an example that shows the steps one might take to build a factored subquery, and then using that factored subquery in a complex SQL statement (page 246-247).
  • The subtopic of windowing (ex: ROWS BETWEEN) was removed from the discussion of analytic functions.
  • Added RELEASE and TAB to the table of common SQL*Plus system variables (page 294)
  • Removed the “HTML in iSQL*Plus” section and added the section, “11.6 Building SQL*Plus Scripts for Automation” (page 321).

It seems that even fantastic books invite problems that creep into book pages.  The editing required to bring this book up to date seems to have introduced a couple of problems, while correcting a couple of problems that made the original book difficult to use for reference purposes.  Suggestions for Improvement, Problems, and/or Errors:

  • It appears that the wrong figure was copied (figure 1-3 is a duplicate of figure 1-5) – shows an entity relationship model diagram rather than the three set operators (UNION, INTERSECT, MINUS) – the correct diagram is shown in the original book (page 15).
  • The URL pointing to the script to recreate the example schema used by the book appeared to be incorrect – this issue might be caused by a recent redesign to the Apress website.  The actual location is that of the Apress website followed by /9781430271970 (page 19, 72, 116).
  • Book separates SELECT SQL statements into a “Retrieval” grouping, while the Oracle SQL documentation considers SELECT SQL statements as part of the “DML” grouping.  This difference might cause a little confusion.
  • Mistakenly left screen captures of SQL Plus for Windows (SQLPLUSW) in the book (page 50, 52).
  • Need to change ‘S02’ to ‘SQL’ so that the SQL statement produces the expected output (page 103).
  • The description of the EXP function has an NLS problem – the comma should be changed to a decimal point in the number (page 119).
  • The Summary paragraph for chapter 6 still mentions the SAVEPOINT and SET TRANSACTION commands which were removed from the chapter.
  • Book names the resultset rows of a query as a “result table”, which might be a little confusing.  For example, page 265 states, “The result of a query is always a table, or more precisely, a derived table.” (This might be 100% correct, it just seemed a bit odd to me.)
  • The descriptions of some of the SQL keywords, such as the description for CONNECT_BY_ROOT, could have benefitted from an example SQL statement.  In a couple of rare cases in the book the output of a SQL statement could have been explained a little more completely, rather than relying on the reader to completely understand what was displayed.
  • The word “unacceptable” should be replaced with “acceptable” (page 252).
  • The book states that chapter 6 demonstrated changing a session’s isolation level to READ ONLY so that query results were always as of a specific transaction start time.  It appears this section was removed from chapter 6 (page 259).
  • The book probably should clarify that an Enterprise Edition license is required in order to use Flashback Query – although the book does state that the DBA will be required to perform some configuration in advance (so maybe this is implied).
  • The permissions described on page 182 as required to use DBMS_XPLAN might be a little excessive, “You need the SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges.” (reference reference2)
  • The book states, “see Appendix A of this book or SQL*Plus User’s Guide and Reference for details” – Appendix A is no longer the “Quick Reference to SQL and SQL*Plus”, but instead is “The Seven Case Tables” (page 297).  A similar problem is found on pages 39, 56, 68, 81, and 89.
  • The book states: “SQL*Plus treats &1 and $2 just…” – the $2 should appear as &2 (page 303).
  • The book states: “SQL Developer has more features than SQL*Plus in this area, because it runs in a browser environment itself” – this is an unfortunate copy and replace error when removing all references to iSQL*Plus from the book (page 318).

The issues identified above are minor when compared to the value offered by the book’s contents.  I would recommend this book without reservation, whether the person’s first experience with SQL was less than ten minutes ago, or for someone who has worked with Oracle SQL for a decade or longer.  Clear, concise, and helpful, with minimal confusing details – an accurate description of not only the SQL language, but also of this book.

Oracle Database Time Model Viewer in Excel 6

18 08 2011

August 18, 2011

(Back to the Previous Post in the Series)

It has been roughly five months since the last installment in this blog article series.  Hopefully, several people have found this series helpful and have adapted the solution to better fit their specific needs.  By the end of the last article the Excel based application not only displayed time model data at the system-wide and session-level, but also operating system statistics (from V$OSSTAT), system-wide and session level wait events, various other statistics (from V$SYSSTAT), execution plans, and also allowed enabling/disabling 10046 extended SQL traces.  A lot of features, but what else may be added to the project?

As of part four of the series, the Excel project should appear similar to the following screen capture (10046 tracing and DBMS XPLAN were added in part five):

You can find the current project, through part five of this series, linked at the bottom of part five’s blog article.  Open that project (or your customized version of the project), right-click the Sheet2 worksheet name and select Rename.  Change the name to Statistics.  Rename the Sheet3 worksheet to Charts by using the same process.  Finally, right-click the Wait Events worksheet name and select View Code.  Expand the Forms group, right-click the frmTimeModel name, and select View Source.  Just before selecting View Source (or View Code), your editor window should appear similar to the following screen capture:

Locate the UserForm_Initialize subroutine (this is the subroutine that connects to the database and prepares to start retrieving statistics from the database).  Locate the following code in that subroutine:

'More code will be copied here

Just above that section of code, add the following commands which will add column titles to the Statistics worksheet when the UserForm is displayed:

    Sheets("Wait Events").Range("B2").Select
    ActiveWindow.FreezePanes = True

    Sheets("Statistics").Cells(1, 1).Value = "Time"
    Sheets("Statistics").Cells(1, 2).Value = "Administrative"
    Sheets("Statistics").Cells(1, 3).Value = "Application"
    Sheets("Statistics").Cells(1, 4).Value = "Commit"
    Sheets("Statistics").Cells(1, 5).Value = "Concurrency"
    Sheets("Statistics").Cells(1, 6).Value = "Configuration"
    Sheets("Statistics").Cells(1, 7).Value = "Network"
    Sheets("Statistics").Cells(1, 8).Value = "Other"
    Sheets("Statistics").Cells(1, 9).Value = "System I/O"
    Sheets("Statistics").Cells(1, 10).Value = "User I/O"


    Sheets("Statistics").Rows("2:10000").Delete Shift:=xlUp

Next, we will programmatically create four charts on the Charts worksheet.  Directly below were the above code was copied (above the ‘More code will be copied here line), add the following code:

    'Remove existing charts, Add the charts

    'Note that these chart styles are likely only compatible with Excel 2007 and later
    With Sheets("Charts").ChartObjects.Add(10, 10, 400, 200)
        .Chart.SetSourceData Source:=Sheets("Statistics").Range("$B$1:$J$21")
        .Chart.SeriesCollection(1).XValues = "='Statistics'!$A$2:$A$21"
        .Chart.ChartType = xlColumnStacked 'xlAreaStacked
        .Chart.HasTitle = True
        .Chart.ChartStyle = 42
        .Chart.ChartTitle.Text = "Wait Event Classes"

        'Rotate the X axis titles
        .Chart.Axes(xlCategory).TickLabels.Orientation = xlUpward

        'Add the vertical Y axis title
        .Chart.Axes(xlValue, xlPrimary).HasTitle = True
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Seconds"
        'Add a gradient to the background of the chart
        .Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
        .Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
        .Chart.PlotArea.Fill.Visible = True
    End With
    With Sheets("Charts").ChartObjects.Add(410, 10, 400, 200)
        .Chart.SetSourceData Source:=Sheets("Statistics").Range("$W$1:$AC$21")
        .Chart.SeriesCollection(1).XValues = "='Statistics'!$A$2:$A$21"
        .Chart.ChartType = xlColumn 'xlArea
        .Chart.HasTitle = True
        .Chart.ChartStyle = 42
        .Chart.ChartTitle.Text = "DB Time and CPU"

        'Rotate the X axis titles
        .Chart.Axes(xlCategory).TickLabels.Orientation = xlUpward

        'Add the vertical Y axis title
        .Chart.Axes(xlValue, xlPrimary).HasTitle = True
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Seconds"

        'Add a gradient to the background of the chart
        .Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
        .Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
        .Chart.PlotArea.Fill.Visible = True
    End With

    With Sheets("Charts").ChartObjects.Add(10, 215, 400, 200)
        .Chart.SetSourceData Source:=Sheets("Statistics").Range("$S$1:$T$21")
        .Chart.SeriesCollection(1).XValues = "='Statistics'!$A$2:$A$21"
        .Chart.ChartType = xlColumnStacked 'xlAreaStacked
        .Chart.HasTitle = True
        .Chart.ChartStyle = 42
        .Chart.ChartTitle.Text = "Server-Wide CPU Usage"

        'Rotate the X axis titles
        .Chart.Axes(xlCategory).TickLabels.Orientation = xlUpward

        'Add the vertical Y axis title
        .Chart.Axes(xlValue, xlPrimary).HasTitle = True
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Seconds"

        'Add a gradient to the background of the chart
        .Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
        .Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
        .Chart.PlotArea.Fill.Visible = True
    End With
    With Sheets("Charts").ChartObjects.Add(410, 215, 400, 200)
        .Chart.SetSourceData Source:=Sheets("Statistics").Range("$O$1:$P$21")
        .Chart.SeriesCollection(1).XValues = "='Statistics'!$A$2:$A$21"
        .Chart.ChartType = xlAreaStacked100 'xlColumnStacked100 'xlAreaStacked100
        .Chart.HasTitle = True
        .Chart.ChartStyle = 42
        .Chart.ChartTitle.Text = "Server-Wide CPU Utilization"

        'Rotate the X axis titles
        .Chart.Axes(xlCategory).TickLabels.Orientation = xlUpward

        'Add the vertical Y axis title
        .Chart.Axes(xlValue, xlPrimary).HasTitle = True
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percent"

        'Add a gradient to the background of the chart
        .Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
        .Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
        .Chart.PlotArea.Fill.Visible = True
    End With

Let’s also change a couple of default settings found on the UserForm and correct the (intentional) spelling error found in the titlebar of the UserForm by using a couple of lines of code directly below the above code:

    chkPauseRefresh.Value = True
    chkDisplaySessionDetail.Value = True
    chkExcludeIdleWaits.Value = True
    Me.Caption = "Charles Hooper's Time Model Viewer in Microsoft Excel"

Now that the code to generate the blank charts has been added to the UserForm, we need code to add the chart data to the Statistics worksheet.  If I so chose, rather than adding the data to the Statistics worksheet, I could simply build an array of numbers and use that array as the charts’ source data, however it might at times be helpful to see the raw data that is presented in the chart.  Locate the UpdateDisplay subroutine in the UserForm’s code (Public Sub UpdateDisplay).  In that subroutine, locate the following line:


Just above that line, add the following code:

'Added in Article 6
    If dblDBTimeLast > 0 Then
        'Note that the first two methods cause the data source range for the charts to shift down 1 row, so copty-paste is used
        'Sheets("Statistics").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        'Range("Statistics!A2:AC1000").Cut Destination:=Range("Statistics!A3:AC1001")
        Sheets("Statistics").Range("A3:AC1001").PasteSpecial xlPasteValuesAndNumberFormats
        Sheets("Statistics").Cells(2, 1) = Format(Now, "hh:nn am/pm")
        Sheets("Statistics").Cells(2, 2) = 0
        Sheets("Statistics").Cells(2, 3) = 0
        Sheets("Statistics").Cells(2, 4) = 0
        Sheets("Statistics").Cells(2, 5) = 0
        Sheets("Statistics").Cells(2, 6) = 0
        Sheets("Statistics").Cells(2, 7) = 0
        Sheets("Statistics").Cells(2, 8 ) = 0
        Sheets("Statistics").Cells(2, 9) = 0
        Sheets("Statistics").Cells(2, 10) = 0

        Sheets("Statistics").Cells(2, 15) = Val(lblBusyTime)
        Sheets("Statistics").Cells(2, 16) = Val(lblIdleTime)
        Sheets("Statistics").Cells(2, 17) = Val(lblBusyPercent)

        Sheets("Statistics").Cells(2, 19) = Val(lblUserMode)
        Sheets("Statistics").Cells(2, 20) = Val(lblKernelMode)
        Sheets("Statistics").Cells(2, 21) = Val(lblUserModePercent)

        Sheets("Statistics").Cells(2, 23) = Format((dblDBTime - dblDBTimeLast) / 1000000, "0.00")
        Sheets("Statistics").Cells(2, 24) = Format((dblDBCPU - dblDBCPULast) / 1000000, "0.00")
        Sheets("Statistics").Cells(2, 25) = Val(lblCPUUsedBySession)
        Sheets("Statistics").Cells(2, 26) = Val(lblParseTimeCPU)
        Sheets("Statistics").Cells(2, 27) = Val(lblRecursiveCPUUsage)
        Sheets("Statistics").Cells(2, 28) = Val(lblOtherCPU)
        Sheets("Statistics").Cells(2, 29) = Format((dblBackgroundCPU - dblBackgroundCPULast) / 1000000, "0.00")

        Sheets("Statistics").Range("B2:AC2").NumberFormat = "0.00"
    End If

Scroll down toward the end of the UpdateDisplay subroutine and locate the following line:

Sheets("Wait Events").Cells(intLastWaitClassRow, 2).Value = Format(sglWaitClassTime / 100, "0.00")

Immediately after the above line, add the following lines of code:

                    'Added in Article 6
                    'Add wait events to statistics worksheet
                    If dblDBTimeLast > 0 Then
                        Select Case UCase(strLastWaitClass)
                            Case "ADMINISTRATIVE"
                                Sheets("Statistics").Cells(2, 2) = Format(sglWaitClassTime / 100, "0.00")
                            Case "APPLICATION"
                                Sheets("Statistics").Cells(2, 3) = Format(sglWaitClassTime / 100, "0.00")
                            Case "COMMIT"
                                Sheets("Statistics").Cells(2, 4) = Format(sglWaitClassTime / 100, "0.00")
                            Case "CONCURRENCY"
                                Sheets("Statistics").Cells(2, 5) = Format(sglWaitClassTime / 100, "0.00")
                            Case "CONFIGURATION"
                                Sheets("Statistics").Cells(2, 6) = Format(sglWaitClassTime / 100, "0.00")
                            Case "NETWORK"
                                Sheets("Statistics").Cells(2, 7) = Format(sglWaitClassTime / 100, "0.00")
                            Case "OTHER"
                                Sheets("Statistics").Cells(2, 8 ) = Format(sglWaitClassTime / 100, "0.00")
                            Case "SYSTEM I/O"
                                Sheets("Statistics").Cells(2, 9) = Format(sglWaitClassTime / 100, "0.00")
                            Case "USER I/O"
                                Sheets("Statistics").Cells(2, 10) = Format(sglWaitClassTime / 100, "0.00")
                        End Select
                    End If

Run the frmTimeModel UserForm by pressing the F5 key on the keyboard.  If the code is working correctly, the Wait Events worksheet should update just as it had in the past:

You should also find that the Statistics worksheet now shows running delta values of the various statistics, with the most recent delta values on the second row of the worksheet:

One of the advantages of using Excel for the charts is that the charts automatically update as new data is added to the Statistics worksheet.  Unfortunately, the data series range for the chart is also auto-modified every time a new row is inserted into the Statistics worksheet, such that the charts never actually show any information.  To avoid this situation, the above code does not perform a row insert, rather it copies the existing data and pastes that data one row down in the worksheet.

The generated Charts worksheet should contain four charts, as shown below:

The chart formatting shown above is quite fancy – so fancy that it requires Microsoft Excel 2007 or later.  The chart creation code may be altered to create the typical flat single color chart elements found in Excel 2003 and earlier.

Are we done yet?  Your Excel worksheet contents are probably flickering quite a bit as additional data is added to the various worksheets.  To correct that problem, switch back to the window that allows seeing the source code for the UserForm and again locate the UpdateDisplay subroutine.  Locate the following line of code:

On Error Resume Next

Just above that line of code, add the following, which will tell Excel not to try updating the worksheet contents as displayed on screen until ScreenUpdating is re-enabled:

Application.ScreenUpdating = False

Scroll down to the last line of the UpdateDisplay subroutine.  Immediately after the last line (intActivated = True), add the following line:

Application.ScreenUpdating = True


Are we done yet?  Part 7 of this blog article series is still a very rough sketch.  Any ideas for improvement?

Added August 19, 2015:

The Excel project code to this point, save with a .XLS extension (currently has a .DOC extension, please change):


The New Order Oracle Coding Challenge 4 – Tic Tac Toe

12 08 2011

(Back to the Previous Post in the Series)

Tic Tac Toe, the game of X’s and O’s, was an oddly popular game in elementary school.  When playing the game you quickly learn a couple of rules:

  • Because X always places his mark first (alternating between X and O), there is an unfair advantage for the player placing the X marks.
  • The player placing his mark in the center square typically has an advantage in the game.
  • Once a player scores three marks in a row horizontally, vertically, or diagonally, there is no point in continuing the game.

Consider the above game matches.  In the first two matches both X and O score three in a row, but which player won the match – it depends on the order in which the marks were placed.  In the third match, X won with a diagnal three in a row.  The final match resulted in a tie (neither player won).  You can use a SQL statement similar to the following to output all of the roughly 362,000 combinations (note that not all combinations are unique – rotating the board 90 or 180 degrees to generate a unique combination probably is not fair, and the order in which the marks are placed could matter):

    'XOX'||'OXO'||'XOX' C
  SUBSTR(C.C,N1.N,1) ||
  SUBSTR(C.C,N2.N,1) ||
  SUBSTR(C.C,N3.N,1) || CHR(10) ||
  SUBSTR(C.C,N4.N,1) ||
  SUBSTR(C.C,N5.N,1) ||
  SUBSTR(C.C,N6.N,1) || CHR(10) ||
  SUBSTR(C.C,N7.N,1) ||
  SUBSTR(C.C,N8.N,1) ||
  N N1,
  N N2,
  N N3,
  N N4,
  N N5,
  N N6,
  N N7,
  N N8,
  N N9,
  AND N1.N<>N3.N
  AND N1.N<>N4.N
  AND N1.N<>N5.N
  AND N1.N<>N6.N
  AND N1.N<>N7.N
  AND N1.N<>N8.N
  AND N1.N<>N9.N
  AND N2.N<>N3.N
  AND N2.N<>N4.N
  AND N2.N<>N5.N
  AND N2.N<>N6.N
  AND N2.N<>N7.N
  AND N2.N<>N8.N
  AND N2.N<>N9.N
  AND N3.N<>N4.N
  AND N3.N<>N5.N
  AND N3.N<>N6.N
  AND N3.N<>N7.N
  AND N3.N<>N8.N
  AND N3.N<>N9.N
  AND N4.N<>N5.N
  AND N4.N<>N6.N
  AND N4.N<>N7.N
  AND N4.N<>N8.N
  AND N4.N<>N9.N
  AND N5.N<>N6.N
  AND N5.N<>N7.N
  AND N5.N<>N8.N
  AND N5.N<>N9.N
  AND N6.N<>N7.N
  AND N6.N<>N8.N
  AND N6.N<>N9.N
  AND N7.N<>N8.N
  AND N7.N<>N9.N
  AND N8.N<>N9.N; 

The output of the above SQL statement should appear similar to the following:











The big problem with the above SQL statement is that it is not clear which player won in all cases.  Ideally, the N1.N value would be used to output an X mark in the specified position, the N2.N value would be used to output an O mark in a specified position, the N3.N value would be used to output an X in the specified position, etc. until one of the players places three marks in a row.  For example, if N1.N is 5, an X would be placed in the center square, if N2.N is 9, an O would be placed in the bottom right square.

Now that we know that the order in which the marks are placed is important, how would be know when a player wins?  You could experiment with the following:

Vertical win, with three positions having the same resulting values:

  MOD(position - 1, 3) + 1 V

Horizontal win, with three positions having the same resulting values:

  TRUNC((position - 1) / 3) + 1 V

Diagnal win \ when V=0 in three positions:

  (MOD(position - 1, 3) + 1) - (TRUNC((position - 1) / 3) + 1) V

Diagnal win / when V=0 (watch out, could end up in a V pattern) or V=2:

  ABS((MOD(position - 1, 3) + 1) - (TRUNC((position - 1) / 3) + 1)) V


OK, now that I have explained the game, given you a couple of SQL statements to possibly help you with the solution… on to the challenge.  With the help of Oracle Database build a tic tac toe solver that will help a player win at tic tac toe.  Provided a current description of the board, for example ‘_X_’||’O_X’||’OX_’, display all board solutions that allows player O (or player X if his turn is next) to win.  (Side note: I have not yet completed the solution to this challenge – it might be possible to accomplish this challenge with just a SQL statement.)

The New Order Oracle Coding Challenge 3 – Mind Boggle

5 08 2011

August 5, 2011 (Modified August 7, 2011)

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In part 1 of this series the challenge was to simply reverse the order of digits in the numbers from 1 to 1,000,000 to find that cases where the numbers formed by the reverse ordered digits evenly divided into the original number.  In part 2 of this series the challenge required examining all of the numbers between 1000 and 9999, where arranging the digits of the original number into any of 23 valid combinations resulted in a new number that cleanly divided into the original four digit number.  There were several different solutions provided to the two challenges, so now it is time to move on to part three of the series.

In part 1 of this blog article series I mentioned playing a game years ago that used letters on the face of dice – the dice were rolled, and then the challenge was to find all words that could be completely spelled using the letters on the top of the dice.  I was not very good at the game, so I enlisted the help of a computer.  One such dice game is called Boggle, and that game’s name is probably fitting for today’s challenge.  Imagine that you played this game and the following letters appeared on the top of the dice:

One of the rules of the game requires that words must be at least 3 letters in length, for example: you say melee eye (I) see elfs file some mail (OK, the word I is too short, but we can have some fun with the words that are found).  As you might be able to guess, there are a lot of possible combinations of the 16 letters found on the dice, some of which are valid words.  If we just consider the 5 letter, 4 letter, and 3 letter combinations of the dice, there are more than a half million possible combinations (in the following table, multiply the numbers across and add the results for each row) – no wonder I needed the computer’s help with these puzzles.

16 15 14 13 12   = 16! / 11!
16 15 14 13     = 16! / 12!
16 15 14       = 16! / 13!
            = 571,200

To make the full challenge of finding words a little easier, let’s break the challenge into a couple of parts:

 Part 1: Consider the 2 x 2 letter arrangement at the left.  With the help of Oracle Database, list all of the three letter combinations of those four letters.  There will be 4 * 3 * 2 = 24 possible combinations of the letters.



 Part 2: Consider the 4 x 4 letter arrangement at the left.  With the help of Oracle Database, list all of the four letter combinations of those 16 letters.  There will be 16 * 15 * 14 * 13 = 43,680 possible combinations of the letters.

Part 3: Consider the 4 x 4 letter arrangement above.  With the help of Oracle Database, list all of the three, four, five, and six letter combinations of those 16 letters.  If you see any seven letter words in the above set of letters, you might as well retrieve those letter combinations also.  How many letter combinations do you have in total for part 3?

Part 4: Extra Credit: How many of the letter combinations generated in part 3 above are valid U.S. or U.K. English words?  List the words.

Part 5: Extra, Extra Credit: List any words found in the letters at the left that have any connection to Oracle Corporation.  Remember that a letter can only be used as many times in a single word as it appears at the left (if you can form a word with three letter A’s that have a connection to Oracle Corp., go for it.).

Added August 7, 2011:

When I put together this challenge I did not think that it was possible to complete Part 4 Extra Credit using just SQL.  I was fairly certain that there were some interesting techniques to retrieve HTML content with the help of PL/SQL, but I had not worked out a solution that utilized that technique.  As I write this, Radoslav Golian in the comments section appears to have both a PL/SQL and a SQL solution that uses the website to validate the words (only 6 words to avoid a denial of service type attack on the website).  One of the approaches that I considered, but did not develop, is something similar to how Radoslav verified the words, but I would use a VBS script to submit the request and check the result as is demonstrated in these two articles: Submit Input to an ASP Web Page and Retrieve the Result using VBS and Use VBS to Search for Oracle Books using Google’s Book Library.

The solution that I put together for Part 4 Extra Credit started with an Excel macro that I posted in another blog article, which was then converted to PL/SQL.  I then transformed the PL/SQL for use in this article, and generated a new Excel macro from the PL/SQL code.  The Excel macro (along with the calling code looks like this:

Sub StartBoggle()
    Call Boggle("ESOIMEFOALEUSAYE", 6, 3)
End Sub

Sub Boggle(strCharacters As String, intMaxWordLength As Integer, intMinWordLength As Integer)
    Dim i As Integer
    Dim strCharacter(20) As String
    Dim intCharacterIndex(20) As Integer
    Dim intCharacters As Integer
    Dim intCharactersMax As Integer
    Dim intCharactersMin As Integer
    Dim intNumberOfSuppliedCharacters As Integer
    Dim intAdjustmentPosition As Integer
    Dim intFlag As Integer
    Dim strOutput As String
    Dim strWords(10000) As String
    Dim intWordCount As Integer
    Dim intFilenum As Integer

    intFilenum = FreeFile
    Open "C:\Words " & strCharacters & ".txt" For Output As #intFilenum

    If intMaxWordLength = 0 Then
        intCharactersMax = Len(strCharacters)
        If intMaxWordLength <= Len(strCharacters) Then
            intCharactersMax = intMaxWordLength
            intCharactersMax = Len(strCharacters)
        End If
    End If

    If intMinWordLength = 0 Then
        intCharactersMin = 3
        If intMaxWordLength < intMinWordLength Then
            intCharactersMin = intCharactersMax
            intCharactersMin = intMinWordLength
        End If
    End If

    intNumberOfSuppliedCharacters = Len(strCharacters)

    For i = 1 To intNumberOfSuppliedCharacters
        strCharacter(i) = Mid(strCharacters, i, 1)
    Next i

    intCharacters = intCharactersMin - 1
    intWordCount = 0

    Do While intCharacters < intCharactersMax
        intCharacters = intCharacters + 1
        intAdjustmentPosition = 1
        For i = 1 To intCharacters
            intCharacterIndex(i) = i
        Next i

        Do While intAdjustmentPosition > 0
            intFlag = 0
            For i = 1 To intAdjustmentPosition - 1
                If intCharacterIndex(i) = intCharacterIndex(intAdjustmentPosition) Then
                    ' Found a duplicate index position in the other values to the left
                    intFlag = 1
                    Exit For
                End If
            Next i

            If intFlag = 1 Then
                ' Try the next index position in this element
                intCharacterIndex(intAdjustmentPosition) = intCharacterIndex(intAdjustmentPosition) + 1
                If intAdjustmentPosition = intCharacters Then
                    ' Output
                    strOutput = ""
                    For i = 1 To intCharacters
                        strOutput = strOutput & strCharacter(intCharacterIndex(i))
                    Next i

                    intFlag = 0
                    For i = intWordCount To 1 Step -1
                        If strOutput = strWords(i) Then
                            intFlag = 1
                            Exit For
                        End If
                    Next i
                    If intFlag = 0 Then
                        If Application.CheckSpelling(Word:=UCase(strOutput)) <> 0 Then
                            intWordCount = intWordCount + 1
                            strWords(intWordCount) = strOutput

                            Print #intFilenum, strOutput
                            Debug.Print strOutput
                        End If
                    End If

                    If intCharacterIndex(intAdjustmentPosition) = intNumberOfSuppliedCharacters Then
                        ' No more available values in the last position
                        intCharacterIndex(intAdjustmentPosition) = 1
                        intAdjustmentPosition = intAdjustmentPosition - 1
                        If intAdjustmentPosition > 0 Then
                            intCharacterIndex(intAdjustmentPosition) = intCharacterIndex(intAdjustmentPosition) + 1
                        End If
                        intCharacterIndex(intAdjustmentPosition) = intCharacterIndex(intAdjustmentPosition) + 1
                    End If
                    ' No duplicate so prepare to check the next position
                    intAdjustmentPosition = intAdjustmentPosition + 1
                End If
            End If

            Do While (intAdjustmentPosition > 0) And (intCharacterIndex(intAdjustmentPosition) > intNumberOfSuppliedCharacters)
                ' Roll back one index position as many times as necessary
                intCharacterIndex(intAdjustmentPosition) = 1
                intAdjustmentPosition = intAdjustmentPosition - 1
                If intAdjustmentPosition > 0 Then
                    intCharacterIndex(intAdjustmentPosition) = intCharacterIndex(intAdjustmentPosition) + 1
                End If
            Loop ' (intAdjustmentPosition > 0) And
        Loop 'intAdjustmentPosition > 0
    Loop 'intCharacters < intCharactersMax

    Close #intFilenum
End Sub 

The Excel macro builds letter combinations that are between the minimum and maximum length, and then tests those letter combinations using the built-in dictionary that is in Excel.  I had a little bit of difficulty coming up with a way to generate the letter combinations of variable length, so I settled on a custom developed technique – I would simply keep track of the original character positions, manipulate those original character positions, and then output the corresponding characters.  The challenge is then how does one verify that the same character position is not used more than once in a single word? 

 The method that I came up with is as follows, which assumes that we are trying to build four letter words from the supplied 16 letters.  We can start with the seed combination 1,2,3,4.  The idea is to work from left to right, and then back to the left.  Every time to make it to the right, we output a word, when we make it all the way back to the left (just before the number 1 in the above), we are done.  The rules are simple:

  • Increment the number in a position, and if that number does not appear in a position to the left, move one position to the right.
  • When the maximum character number (16 in this example) is exceeded in a position, reset the number to 1, move one position to the left, and increment the value in the new position by 1.
  • In the last position the character number should be incremented as many times as necessary to reach the maximum character number – each time a potential new combination will be generated.

 But there is a problem with this approach – it does not use Oracle Database!

Let’s go back to the PL/SQL function from which I created the Excel function (I have not worked much with pipelined functions – so there may be one or two errors):

  strCharacter CHARACTER_ARRAY;
  intCharacterIndex NUMBER_ARRAY;
  intCharacters NUMBER;
  intCharactersMax NUMBER;
  intCharactersMin NUMBER;
  intNumberOfSuppliedCharacters NUMBER;
  intAdjustmentPosition NUMBER;
  intFlag NUMBER;
  intI NUMBER;
  strOutput VARCHAR2(100);
  IF intMaxWordLength IS NULL THEN
    intCharactersMax := LENGTH(strCHARACTERS);
    IF intMaxWordLength <= LENGTH(strCHARACTERS) THEN
      intCharactersMax := intMaxWordLength;
      intCharactersMax := LENGTH(strCHARACTERS);
    END IF;

  IF intMinWordLength IS NULL THEN
    intCharactersMin := 3;
    IF intMaxWordLength < intMinWordLength THEN
      intCharactersMin := intCharactersMax;
      intCharactersMin := intMinWordLength;
    END IF;

  intNumberOfSuppliedCharacters := LENGTH(strCHARACTERS);

  FOR I IN 1.. intNumberOfSuppliedCharacters LOOP
    strCharacter(I) := SUBSTR(strCHARACTERS, I, 1);

  intCharacters := intCharactersMin - 1;
  WHILE intCharacters < intCharactersMax LOOP
    intCharacters := intCharacters + 1;
    intAdjustmentPosition := 1;
    FOR I IN 1 .. intCharacters LOOP
      intCharacterIndex(I) := I;

    WHILE intAdjustmentPosition > 0 LOOP
      intFlag := 0;
      FOR I IN 1 .. intAdjustmentPosition - 1 LOOP
        IF intCharacterIndex(I) = intCharacterIndex(intAdjustmentPosition) Then
          -- Found a duplicate index position in the other values to the left
          intFlag := 1;
        END IF;
      END LOOP;
      IF intFlag = 1 Then
        -- Try the next index position in this element
        intCharacterIndex(intAdjustmentPosition) := intCharacterIndex(intAdjustmentPosition) + 1;
        IF intAdjustmentPosition = intCharacters Then
          -- Output
          strOutput := '';
          FOR i IN 1 .. intCharacters LOOP
            strOutput := strOutput || strCharacter(intCharacterIndex(i));
          END LOOP;

          PIPE ROW (strOutput);

          IF intCharacterIndex(intAdjustmentPosition) = intNumberOfSuppliedCharacters THEN
            -- No more available values in the last position
            intCharacterIndex(intAdjustmentPosition) := 1;
            intAdjustmentPosition := intAdjustmentPosition - 1;
            IF intAdjustmentPosition > 0 THEN
              intCharacterIndex(intAdjustmentPosition) := intCharacterIndex(intAdjustmentPosition) + 1;
            END IF;
            intCharacterIndex(intAdjustmentPosition) := intCharacterIndex(intAdjustmentPosition) + 1;
          END IF;
          -- No duplicate so prepare to check the next position
          intAdjustmentPosition := intAdjustmentPosition + 1;
        END IF;
      END IF;

      WHILE (intAdjustmentPosition > 0) And (intCharacterIndex(intAdjustmentPosition) > intNumberOfSuppliedCharacters) LOOP
        -- Roll back one index position as many times as necessary
        intCharacterIndex(intAdjustmentPosition) := 1;
        intAdjustmentPosition := intAdjustmentPosition - 1;
        IF intAdjustmentPosition > 0 THEN
          intCharacterIndex(intAdjustmentPosition) := intCharacterIndex(intAdjustmentPosition) + 1;
        END IF;
      END LOOP;

 We are able to call the function from a SQL statement like this:


Remember that there are more than a half million character combinations for just the 3, 4, and 5 letter combinations – the above will as for 6,336,960 letter combinations to be generated.   But there is a problem with this approach – it does not verify that the letter combinations are actual words!

For fun, let’s see how many possible combinations will result if we allow 3, 4, 5, 6, 7, and 8 letter combinations:

Len                 Combinations  
8 16 15 14 13 12 11 10 9 518,918,400 = 16! / 8!
7 16 15 14 13 12 11 10   57,657,600 = 16! / 9!
6 16 15 14 13 12 11     5,765,760 = 16! / 10!
5 16 15 14 13 12       524,160 = 16! / 11!
4 16 15 14 13         43,680 = 16! / 12!
3 16 15 14           3,360 = 16! / 13!
                  582,912,960 582,912,960

That is more than a half billion combinations!  Warning, significant database server CPU consumption will result when generating all combinations.

Let’s take a look at the final solution that I created for Part 4 Extra, Extra Credit.  The solution is an Excel macro that calls the PL/SQL function through a SQL statement:

Sub StartBoggleOracle()
    Call BoggleOracle("ESOIMEFOALEUSAYE", 8, 3)
End Sub

Sub BoggleOracle(strCharacters As String, intMaxWordLength As Integer, intMinWordLength As Integer)
    Dim strSQL As String
    Dim strUsername As String
    Dim strPassword As String
    Dim strDatabase As String
    Dim intFilenum As Integer

    Dim intCharacters As Integer
    Dim intCharactersMax As Integer
    Dim intCharactersMin As Integer
    Dim strOutput As String

    Dim dbDatabase As ADODB.Connection
    Dim snpData As ADODB.Recordset

    Set dbDatabase = New ADODB.Connection
    Set snpData = New ADODB.Recordset

    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDatabase"

    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";FetchSize=5000;"

    intFilenum = FreeFile
    Open "C:\WordsOracle " & strCharacters & ".txt" For Output As #intFilenum

    If intMaxWordLength = 0 Then
        intCharactersMax = Len(strCharacters)
        If intMaxWordLength <= Len(strCharacters) Then
            intCharactersMax = intMaxWordLength
            intCharactersMax = Len(strCharacters)
        End If
    End If

    If intMinWordLength = 0 Then
        intCharactersMin = 3
        If intMaxWordLength < intMinWordLength Then
            intCharactersMin = intCharactersMax
            intCharactersMin = intMinWordLength
        End If
    End If

    strSQL = "SELECT DISTINCT" & vbCrLf
    strSQL = strSQL & "  *" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  (SELECT" & vbCrLf
    strSQL = strSQL & "    *" & vbCrLf
    strSQL = strSQL & "  FROM" & vbCrLf
    strSQL = strSQL & "    TABLE(BOGGLE_VAR_LENGTH('" & strCharacters & "', " & Format(intCharactersMax) & ", " & Format(intCharactersMin) & ")))" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  1"
    snpData.Open strSQL, dbDatabase

    If snpData.State = 1 Then
        Do While Not snpData.EOF
            strOutput = snpData(0)
            If Application.CheckSpelling(Word:=UCase(strOutput)) <> 0 Then
                Print #intFilenum, strOutput
                Debug.Print strOutput
            End If


    End If

    Close #intFilenum
    Set snpData = Nothing
    Set dbDatabase = Nothing
End Sub

The words found appear to depend on the version of Excel – Excel 2010 seems to find more words than Excel 2007.

  • The 799 word list from Excel 2007 for word lengths between 3 and 8 characters, including the timing information to show when the SQL statement was submitted, when the first 5,000 combinations were retrieved from the database, and when the Excel spell check finished.  Words Oracle_ESOIMEFOALEUSAYE.txt
  • The 2,179 word list from Excel 2007 for word lengths between 3 and 8 characters, including the timing information to show when the SQL statement was submitted, when the first 5,000 combinations were retrieved from the database, and when the Excel spell check finished. Words Oracle_OSERIEFAARLNCAYL.txt

Excel found Ellison in the second word list.  For Part 5 Extra, Extra Credit, what other words connected to Oracle Corporation were found?

The New Order Oracle Coding Challenge 2

2 08 2011

August 2, 2011

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the previous article in this series we tried to develop different methods for identifying special numbers between 1 and 1,000,000, such that the number formed by reversing the order of the digits will evenly divide into the original number.  I initially predicted that there would be at least three unique solutions provided before the undocumented REVERSE function was mentioned.  A fellow Oak Table member quickly provided a solution using the REVERSE function, which happens to be the fastest of the various approaches.  Another reader noticed a pattern that helps find those special numbers – if that pattern identifies all of the special numbers, it is easily the most efficient approach to finding these special numbers.

Today’s blog article also is on the topic of finding special numbers, determined as special after changing the order of the number’s digits.  I used my fancy, on-point introduction for this article in the earlier article (letters on the dice, trying to form words from those letters), so I thought that I would jump the little pony over the edge of the cliff in search of another introduction (there are probably five readers with an odd image stuck in their head at this moment).   😉

Have you ever noticed the disparity between the Environment Protection Agency’s (EPA or the equivalent in other countries) estimates for fuel efficiency and the actual fuel efficiency calculated with the hand held calculator?  Take for instance a car that I own – that car has never achieved the EPA’s estimated fuel efficiency rating.  A 412 horsepower (at the flywheel as claimed by the manufacturer, other tests have concluded that it might be higher) V8 achieving 25 miles per U.S. gallon (MPG) of fuel?

Time for the Oracle Database test case.  On a frigid 32 degree day (in Celsius, that is a hot 90 degrees Fahrenheit), I lowered the windows, topped off the fuel tank with premium grade fuel, reset the car’s fuel efficiency gauge (the MPG gauge… oddly, this gauge always reads 6% to 8% lower than the actual fuel efficiency), and then set out on a roughly 117 mile (188 KM) drive down the highway with the radar detector on the dash board.  While I have heard that the car’s speed limit is set at the factory to roughly 160 miles per hour (258 KM/H), the posted speed limit was typically 55 MPH (miles per hour) except in the roughly half-dozen or so cities along the way.  The car never did hit the EPA’s estimated fuel efficiency rating.  Somebody was playing with the numbers a bit, I think. 🙂

Along the route selected for the 117 mile test loop, I captured cell phone pictures showing the car’s fuel efficiency gauge (MPG gauge).  Below are the cell phone pictures captured at 15 miles into the drive, roughly half way through the drive, and near the end of the 117 mile drive. 

So, how does Oracle Database play a part in the introduction to this blog article?  Let’s take a guess at the car’s actual fuel efficiency using a SQL statement:

  29.5 CAR,
  ROUND(29.5 * 1.06, 2) LOW_ACTUAL,
  ROUND(29.5 * 1.08, 2) HIGH_ACTUAL

---------- ---------- -----------
      29.5      31.27       31.86

So much for EPA estimates – between 25.08% and 27.44% lower than the actual fuel efficiency.  Does this test case then suggest that if we do not use all of the features that are available in a tool, that we are then more efficient than expected?  I think that I need another test case to decide.  😉

Side note: For the tank of fuel when the car rolled over the 1,000 mile mark, the car achieved 28.75 miles per gallon while touring some of the sites around Michigan, specifically the hills and curves along the north-west shoreline of the lower peninsula (several of those hills were steep – for one hill in particular the car downshifted three gears to maintain speed going down the hill).  The car’s overall fuel efficiency since it left the factory is 25.58 MPG (excluding the 117 mile test case), so I guess that one could argue that if you TRUNC the fuel efficiency number, at the root the EPA is right.  Make like a tree and leave (that was a movie quote from “Back to the Future”).

After returning from the test case I set out on my other 412 HP vehicle… cough, that would be a 4 cylinder 12 horsepower 1946 Farmall A tractor.  Time to mow the lawn in second gear.  Cutting a 6 foot (1.83 meter) wide path through grass that is up to 12 inches (30.48 cm) high, and capable of mowing down 2 inch diameter trees as necessary.  Oh, the power of the horse…

OK, now that we have drifted far from the topic of this article and have had a chance to play with numbers in the long introduction, on to the challenge.  Consider the four digit integer numbers between 1,000 and 9,999.  There are 4! (1 * 2 * 3 * 4 = 24) possible combinations of the four digits of each of these numbers.  For example, consider the number 8712 – the 24 possible combinations are:

8712 8721

The objective is to find all combinations of the four digit numbers between 1,000 and 9,999 that evenly divide into the original number.  For the above example with the original number 8712, the combination 2178 evenly divides into 8712.  Thus, the output would include:

-------- -----------
    8712        2178

The restrictions:

  • The four digit original numbers between 1,000 and 9,999 should not be included in the output if the last digit of those numbers is 0 – the combination number may end with 0 if the original number included a 0 digit.  Thus, do not include the original numbers 1000, 1010, 1020, 1030, 1040, etc.
  • The original value cannot be equal to the combination value (in the above example, it is not valid to output the combination value 8712).
  • Each original number and combination pair should be output at most one time.
  • You may not reuse a digit position twice; all digit positions must be used once.  For example, the number 7141 includes the digit 1 twice, therefore, all generated combinations of that number must include two number 1 digits.

This challenge is a bit more difficult than was the challenge in part 1 of this series.  The greatest difficulty likely involves creating the 23 valid digit combinations (there are 24, but one of those combinations will always be invalid) of the four digit numbers. 

The problem is solvable – will your solution be the same as mine?  I will give the readers a couple of days to ponder the thoughts of why a horse entered a race between a car and a tractor before sharing my approach to solving the problem.