Submit Input to an ASP Web Page and Retrieve the Result using VBS

17 01 2010

January 17, 2010

While it is helpful that SQL statements may be submitted directly from VBS scripts, in most cases the username and password for the database user will be exposed in the VBS script.  So, we need another way.  How about having the VBS script pass a value of interest to an ASP web page (note that this is not ASP.Net, this is old style programming without a Net).  First, we need to create the ASP web page (after, of course, enabling ASP in Microsoft’s IIS web server configuration):

<html>
<head>
<title>I am a Hidden Web Page - You will Not See Me</title>
</head>
<body>
   <%
    Dim adVarChar
    Dim adParamInput
    Dim adCmdText
    Dim strSQL
    Dim snpData
    Dim comData
    Dim strPartID

    Dim dbDatabase

    adVarChar = 200
    adParamInput = 1
    adCmdText = 1
    Set dbDatabase = Server.CreateObject("ADODB.Connection")
    Set comData = Server.CreateObject("ADODB.Command")
    Set snpData = Server.CreateObject("ADODB.Recordset")

    On Error Resume Next

    strPartID = cStr(Request("strPartID"))

    dbDatabase.Open "Provider=MSDAORA.1;User ID=MyUser;Password=MyPassword;Data Source=MyDB;Persist Security Info=True"

    With ComData
        strSQL = "SELECT /*+ LEADING(IT) INDEX(IT X_INV_TRANS_1) */" & vbCrLf
        strSQL = strSQL & "  IT.PART_ID," & vbCrLf
        strSQL = strSQL & "  TRUNC(SUM(DECODE(IT.CLASS,'I',DECODE(IT.TYPE,'O',IT.QTY,0),0))-SUM(DECODE(IT.CLASS,'I',DECODE(IT.TYPE,'I',IT.QTY,0),0))+.9999) AS NEW_ANNUAL_USAGE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  INVENTORY_TRANS IT," & vbCrLf
        strSQL = strSQL & "  PART P" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  IT.TRANSACTION_DATE>TRUNC(SYSDATE-365)" & vbCrLf
        strSQL = strSQL & "  AND P.ID=IT.PART_ID" & vbCrLf
        strSQL = strSQL & "  AND P.ID= ?" & vbCrLf
        strSQL = strSQL & "GROUP BY" & vbCrLf
        strSQL = strSQL & "  IT.PART_ID" & vbCrLf
        '
        .Parameters.Append .CreateParameter("part_id", adVarChar, adParamInput, 30, strPartID)
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase
    End With
    Set snpData = ComData.Execute

    Response.Write "<input type=""text"" name=""txtPartID"" size=30 value=""" & strPartID & """ disabled=true>"
    If Not (snpData Is Nothing) Then
        If Not(snpData.EOF) Then
            Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""" & cstr(snpData("new_annual_usage")) & """ disabled=true>"
            Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""RETRIEVED"" disabled=true>"
        Else
            Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""0"" disabled=true>"
            Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""NO TRANSACTIONS"" disabled=true>"
        End If
    Else
        Response.Write "<input type=""text"" name=""txtAnnualUsage"" size=30 value=""0"" disabled=true>"
        Response.Write "<input type=""text"" name=""txtOK"" size=255 value=""ERROR"" disabled=true>"
    End If

    snpData.Close
    dbDatabase.Close

    Set snpData = Nothing
    Set comData = Nothing
    Set dbDatabase = Nothing
    %>
</body>
</html>

OK, reading the ASP web page code, we create an ADO database connection object, an ADO recordset object, and an ADO command object.  Next, we set the strPartID variable to the value of the passed in strPartID variable from the web session, build a SQL statement with a bind variable set to the value of the strPartID variable, and then execute the SQL statement.  If the SQL statement successfully executed, we build two HTML text box, the first with the value of NEW_ANNUAL_USAGE, and the second with a status of either RETRIEVED or NO TRANSACTIONS.  If the SQL statement failed to execute, the two HTML text boxes will contain 0 and ERROR.

Now for the VBS script that will call the ASP web page:

Dim intResult
Dim objIE
Dim strHTML
Dim strID
Dim ANNUAL_USAGE_QTY

On Error Resume Next

Set objIE = CreateObject("InternetExplorer.Application")

strID = "ABCDEF123456"
ANNUAL_USAGE_QTY = 100

objIE.Navigate "http://localhost/Update_Annual_Usage_Qty.asp?strPartID=" & strID

objIE.Width=100
objIE.Height=100
objIE.Statusbar=False
objIE.Menubar=False
objIE.Toolbar=False
objIE.Visible = False

Do While objIE.Busy <> False
    WScript.Sleep 200
Loop

'loop until the button is clicked
Do While intFlag = 0
    If Err <> 0 Then
        IntFlag = -1
    End If   
    If objIE is Nothing Then
        'User closed ID
        intFlag = -1
    Else
        If objIE.Document.All.txtOK.Value <> " " Then
            intFlag = 1
        End If
    End If
    WScript.Sleep 200
Loop

If intFlag = 1 Then
    If objIE.Document.Body.All.txtOK.Value = "ERROR" Then
        MsgBox "Error sending the query to the database"
    Else
        If objIE.Document.Body.All.txtOK.Value = "NO TRANSACTIONS" Then
            intResult = MsgBox ("No transactions for this part in the last year, OK to set the annual usage qty to 0?  The old value is " & cStr(ANNUAL_USAGE_QTY), vbQuestion + vbYesNo, "Annual Usage")
            If intResult = vbYes Then
                ANNUAL_USAGE_QTY = 0
            End If
        Else
            'Copy in the values from the web page
            intResult = MsgBox ("The old annual usage quantity value is " & cStr(ANNUAL_USAGE_QTY) & " - the database indicates that the updated quantity should be " & cstr(objIE.Document.Body.All.txtAnnualUsage.Value) & ".  Would you like to update the annual usage quantity?", vbQuestion + vbYesNo,"Annual Usage")
            If intResult = vbYes Then
                 ANNUAL_USAGE_QTY = objIE.Document.Body.All.txtAnnualUsage.Value
            End If
        End If
    End If
    objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing

The VBS script launches the ASP page in a hidden Internet Explorer window, passing in the value of strID on the address line (this is picked up in the ASP script as the strPartID session variable).  The VBS script then waits until the ASP page finishes loading.  Once the ASP page finishes, the VBS script reads the values of the two HTML text boxes and acts appropriately based on the values of those text boxes.

The neat thing about straight ASP programming code is that it looks a lot like the VBS programming code, and that looks a lot like the Excel macro programming code, and that looks a lot like the classic Visual Basic programming code, and that kind of looks like the classic BASIC programming code that I started working with in 1981/1982.  I have been sitting in on the technology training advisory committee for one of the local colleges.  The committee helps determine what computer classes will be taught to earn a degree at the college.  The question was asked what languages to teach – I heard C++ and Java being suggested… I wonder if I should have suggested Visual Basic?  VBS like languages are also used as macro languages in some ERP products and other packages (I believe that AutoCAD uses a similar macro syntax, as does PC-DMIS).





Working with Oracle’s Time Model Data 2

14 01 2010

January 14, 2010

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

So, how is it possible to transform this:

Into something like this:

A fairly long VBS script connects to the Oracle database, performs a lot of calculations on the data returned from the database, and then outputs the formatted result to a web page in Internet Explorer.  The VBS script continues to control the web page once the page is built, automatically refreshing the web page after a specified number of seconds, and responding to button clicks on the web page.  Easy, right?  Because this is done using a VBS script, the client computer must be running on Windows, while the server may run Unix, Linux, or Windows (I suggest not running this script directly on the server, instead run it from another computer).  The Show Detail button acts like a toggle to either show or hide the session details (on the yellow lines) that contributed to the system-wide statistic values (by default, the sessions must contribute to at least 10% of the total to be included in the session-level output).  Clicking the Re-Query button causes the script to update the page with the latest statistic delta values prior to the automatic refresh timer expiring.

So, where is the code?  Note that there may be bugs in the code – don’t step on them.  Also, the code is mostly written in a very verbose syntax so that it is easy to follow along with the script logic.

Const adCmdText = 1

Dim i
Dim j
Dim k
Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase
Dim intCheckIterations              'Number of times to check the instances
Dim intDelayIterations              'Number of seconds to delay between iterations
Dim sglSessionMinimumPercent        'Minimum percent of the total required for the session to be included in the report detail
Dim dteLastLoopStart                'Time of the last loop start
Dim intDataChanged                  'Indicates whether or not the data to be displayed on the web page has changed
Dim intDisplaySessionDetail         'Indicates whether or not to display the session level detail
Dim snpDataWait                     'ADO recordset used to query V$SYSTEM_EVENT
Dim comDataWait                     'ADO command object used to retrieve data from V$SYSTEM_EVENT
Dim snpDataOSStat                   'ADO recordset used to query V$OSSTAT
Dim comDataOSStat                   'ADO command object used to retrieve data from V$OSSTAT
Dim snpDataSysTime                  'ADO recordset used to query V$SYS_TIME_MODEL
Dim comDataSysTime                  'ADO command object used to retrieve from V$SYS_TIME_MODEL
Dim snpDataSessTime                 'ADO recordset used to query V$SESS_TIME_MODEL
Dim comDataSessTime                 'ADO command object used to retrieve from V$SESS_TIME_MODEL
Dim dbDatabase                      'ADO database connection object

Dim strHTML                         'The raw HTML for the web page
Dim objIE                           'The Internet Explorer object
Dim strInd                          'Indent characters for the table
Dim intFlag                         'Loop control variable, allow to jump out of the loop early

Dim intNumCPUs                      'Number of CPUs
Dim dblIdleTime                     'Current value of idle time from V$OSSTAT
Dim dblBusyTime                     'Current value of busy time from V$OSSTAT
Dim dblUserTime                     'Current value of user time from V$OSSTAT
Dim dblSysTime                      'Current value of system/kernel mode time from V$OSSTAT
Dim dblIdleTimeLast                 'Previous value of idle time from V$OSSTAT
Dim dblBusyTimeLast                 'Previous value of busy time from V$OSSTAT
Dim dblUserTimeLast                 'Previous value of user time from V$OSSTAT
Dim dblSysTimeLast                  'Previous value of system/kernel mode time from V$OSSTAT

Dim dblDBCPU                        'Current value of DB CPU from V$SYS_TIME_MODEL
Dim dblDBTime                       'Current value of DB time from V$SYS_TIME_MODEL
Dim dblJavaTime                     'Current value of Java execution elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLCompile                 'Current value of PL/SQL compilation elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLExecution               'Current value of PL/SQL execution elapsed time from V$SYS_TIME_MODEL
Dim dblRMANCPU                      'Current value of RMAN cpu time (backup/restore) from V$SYS_TIME_MODEL
Dim dblBackgroundCPU                'Current value of background cpu time from V$SYS_TIME_MODEL
Dim dblBackgroundElapsed            'Current value of background elapsed time from V$SYS_TIME_MODEL
Dim dblConnectMgmt                  'Current value of connection management call elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseMemory            'Current value of failed parse (out of shared memory) elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseElapsed           'Current value of failed parse elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseBind                'Current value of hard parse (bind mismatch) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseSharing             'Current value of hard parse (sharing criteria) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseElapsed             'Current value of hard parse elapsed time from V$SYS_TIME_MODEL
Dim dblInboundPLSQL                 'Current value of inbound PL/SQL rpc elapsed time from V$SYS_TIME_MODEL
Dim dblParseTimeElapsed             'Current value of parse time elapsed from V$SYS_TIME_MODEL
Dim dblRepeatedBind                 'Current value of repeated bind elapsed time from V$SYS_TIME_MODEL
Dim dblSequenceLoad                 'Current value of sequence load elapsed time from V$SYS_TIME_MODEL
Dim dblSQLExecuteTime               'Current value of sql execute elapsed time from V$SYS_TIME_MODEL

Dim dblDBCPULast                    'Last value of DB CPU from V$SYS_TIME_MODEL
Dim dblDBTimeLast                   'Last value of DB time from V$SYS_TIME_MODEL
Dim dblJavaTimeLast                 'Last value of Java execution elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLCompileLast             'Last value of PL/SQL compilation elapsed time from V$SYS_TIME_MODEL
Dim dblPLSQLExecutionLast           'Last value of PL/SQL execution elapsed time from V$SYS_TIME_MODEL
Dim dblRMANCPULast                  'Last value of RMAN cpu time (backup/restore) from V$SYS_TIME_MODEL
Dim dblBackgroundCPULast            'Last value of background cpu time from V$SYS_TIME_MODEL
Dim dblBackgroundElapsedLast        'Last value of background elapsed time from V$SYS_TIME_MODEL
Dim dblConnectMgmtLast              'Last value of connection management call elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseMemoryLast        'Last value of failed parse (out of shared memory) elapsed time from V$SYS_TIME_MODEL
Dim dblFailedParseElapsedLast       'Last value of failed parse elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseBindLast            'Last value of hard parse (bind mismatch) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseSharingLast         'Last value of hard parse (sharing criteria) elapsed time from V$SYS_TIME_MODEL
Dim dblHardParseElapsedLast         'Last value of hard parse elapsed time from V$SYS_TIME_MODEL
Dim dblInboundPLSQLLast             'Last value of inbound PL/SQL rpc elapsed time from V$SYS_TIME_MODEL
Dim dblParseTimeElapsedLast         'Last value of parse time elapsed from V$SYS_TIME_MODEL
Dim dblRepeatedBindLast             'Last value of repeated bind elapsed time from V$SYS_TIME_MODEL
Dim dblSequenceLoadLast             'Last value of sequence load elapsed time from V$SYS_TIME_MODEL
Dim dblSQLExecuteTimeLast           'Last value of sql execute elapsed time from V$SYS_TIME_MODEL

Dim intSessionCount                 'Number of sessions logged
Dim intSessionCurrent               'Index of the current session
Dim lngSIDLast                      'SID for the previous row from the database
Dim lngSerialLast                   'SERIAL# for the previous row
Dim intSessionExists(999)           'Used to determine if the session is still found in the system
Dim lngSID(999)                     'SID for session
Dim lngSerial(999)                  'SERIAL# for the session
Dim strSessionOther(999)            'USERNAME, MACHINE, PROGRAM
Dim dblDBCPUS(999)                  'Current value of DB CPU from V$SESS_TIME_MODEL
Dim dblDBTimeS(999)                 'Current value of DB time from V$SESS_TIME_MODEL
Dim dblJavaTimeS(999)               'Current value of Java execution elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLCompileS(999)           'Current value of PL/SQL compilation elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLExecutionS(999)         'Current value of PL/SQL execution elapsed time from V$SESS_TIME_MODEL
Dim dblRMANCPUS(999)                'Current value of RMAN cpu time (backup/restore) from V$SESS_TIME_MODEL
Dim dblBackgroundCPUS(999)          'Current value of background cpu time from V$SESS_TIME_MODEL
Dim dblBackgroundElapsedS(999)      'Current value of background elapsed time from V$SESS_TIME_MODEL
Dim dblConnectMgmtS(999)            'Current value of connection management call elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseMemoryS(999)      'Current value of failed parse (out of shared memory) elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseElapsedS(999)     'Current value of failed parse elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseBindS(999)          'Current value of hard parse (bind mismatch) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseSharingS(999)       'Current value of hard parse (sharing criteria) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseElapsedS(999)       'Current value of hard parse elapsed time from V$SESS_TIME_MODEL
Dim dblInboundPLSQLS(999)           'Current value of inbound PL/SQL rpc elapsed time from V$SESS_TIME_MODEL
Dim dblParseTimeElapsedS(999)       'Current value of parse time elapsed from V$SESS_TIME_MODEL
Dim dblRepeatedBindS(999)           'Current value of repeated bind elapsed time from V$SESS_TIME_MODEL
Dim dblSequenceLoadS(999)           'Current value of sequence load elapsed time from V$SESS_TIME_MODEL
Dim dblSQLExecuteTimeS(999)         'Current value of sql execute elapsed time from V$SESS_TIME_MODEL

Dim dblDBCPUSLast(999)              'Last value of DB CPU from V$SESS_TIME_MODEL
Dim dblDBTimeSLast(999)             'Last value of DB time from V$SESS_TIME_MODEL
Dim dblJavaTimeSLast(999)           'Last value of Java execution elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLCompileSLast(999)       'Last value of PL/SQL compilation elapsed time from V$SESS_TIME_MODEL
Dim dblPLSQLExecutionSLast(999)     'Last value of PL/SQL execution elapsed time from V$SESS_TIME_MODEL
Dim dblRMANCPUSLast(999)            'Last value of RMAN cpu time (backup/restore) from V$SESS_TIME_MODEL
Dim dblBackgroundCPUSLast(999)      'Last value of background cpu time from V$SESS_TIME_MODEL
Dim dblBackgroundElapsedSLast(999)  'Last value of background elapsed time from V$SESS_TIME_MODEL
Dim dblConnectMgmtSLast(999)        'Last value of connection management call elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseMemorySLast(999)  'Last value of failed parse (out of shared memory) elapsed time from V$SESS_TIME_MODEL
Dim dblFailedParseElapsedSLast(999) 'Last value of failed parse elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseBindSLast(999)      'Last value of hard parse (bind mismatch) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseSharingSLast(999)   'Last value of hard parse (sharing criteria) elapsed time from V$SESS_TIME_MODEL
Dim dblHardParseElapsedSLast(999)   'Last value of hard parse elapsed time from V$SESS_TIME_MODEL
Dim dblInboundPLSQLSLast(999)       'Last value of inbound PL/SQL rpc elapsed time from V$SESS_TIME_MODEL
Dim dblParseTimeElapsedSLast(999)   'Last value of parse time elapsed from V$SESS_TIME_MODEL
Dim dblRepeatedBindSLast(999)       'Last value of repeated bind elapsed time from V$SESS_TIME_MODEL
Dim dblSequenceLoadSLast(999)       'Last value of sequence load elapsed time from V$SESS_TIME_MODEL
Dim dblSQLExecuteTimeSLast(999)     'Last value of sql execute elapsed time from V$SESS_TIME_MODEL

Dim intWaitCount                    'Number of wait events read from the database
Dim intWaitCurrent                  'Current index of the wait event
Dim strWaitEventName(1300)          'Name of the wait event
Dim dblWaitValue(1300)              'Current wait event total time
Dim dblWaitValueLast(1300)          'Previous wait event total time
Dim dblWaitWaitsValue(1300)         'Current wait event number of waits
Dim dblWaitWaitsValueLast(1300)     'Previous wait event number of waits
Dim dblWaitTOValue(1300)            'Current wait event number of timeouts
Dim dblWaitTOValueLast(1300)        'Previous wait event number of timeouts

Set snpDataWait = CreateObject("ADODB.Recordset")
Set comDataWait = CreateObject("ADODB.Command")
Set snpDataOSStat = CreateObject("ADODB.Recordset")
Set comDataOSStat = CreateObject("ADODB.Command")
Set snpDataSysTime = CreateObject("ADODB.Recordset")
Set comDataSysTime = CreateObject("ADODB.Command")
Set snpDataSessTime = CreateObject("ADODB.Recordset")
Set comDataSessTime = CreateObject("ADODB.Command")

Set dbDatabase = CreateObject("ADODB.Connection")

strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

intCheckIterations = 20
intDelayIterations = 60
sglSessionMinimumPercent = 0.1  '10% of the total for the time period needed to be inccluded in the detail
strInd = "&nbsp;&nbsp;"

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

'Should verify that the connection attempt was successful, but I will leave that for someone else to code
On Error Resume Next  'Allow continuing the script if an error happens

With comDataWait
  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  EVENT," & vbCrLf
  strSQL = strSQL & "  TOTAL_WAITS," & vbCrLf
  strSQL = strSQL & "  TOTAL_TIMEOUTS," & vbCrLf
  strSQL = strSQL & "  TIME_WAITED" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$SYSTEM_EVENT" & vbCrLf
  strSQL = strSQL & "WHERE" & vbCrLf
  strSQL = strSQL & "  WAIT_CLASS<>'Idle'" & vbCrLf
  strSQL = strSQL & "ORDER BY" & vbCrLf
  strSQL = strSQL & "  EVENT"

  .CommandText = strSQL
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

With comDataOSStat
  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  STAT_NAME," & vbCrLf
  strSQL = strSQL & "  VALUE" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$OSSTAT" & vbCrLf
  strSQL = strSQL & "WHERE" & vbCrLf
  strSQL = strSQL & "  STAT_NAME IN ('NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME')"

  .CommandText = strSQL
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

With comDataSysTime
  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  VALUE," & vbCrLf
  strSQL = strSQL & "  STAT_NAME" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$SYS_TIME_MODEL"

  .CommandText = strSQL
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

With comDataSessTime
  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  S.SID," & vbCrLf
  strSQL = strSQL & "  S.SERIAL#," & vbCrLf
  strSQL = strSQL & "  NVL(S.USERNAME,' ') USERNAME," & vbCrLf
  strSQL = strSQL & "  NVL(S.MACHINE,' ') MACHINE," & vbCrLf
  strSQL = strSQL & "  NVL(S.PROGRAM,' ') PROGRAM," & vbCrLf
  strSQL = strSQL & "  NVL(S.SQL_ID,' ') SQL_ID," & vbCrLf
  strSQL = strSQL & "  NVL(S.SQL_CHILD_NUMBER,0) SQL_CHILD_NUMBER," & vbCrLf
  strSQL = strSQL & "  STM.VALUE," & vbCrLf
  strSQL = strSQL & "  STM.STAT_NAME" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$SESS_TIME_MODEL STM," & vbCrLf
  strSQL = strSQL & "  V$SESSION S" & vbCrLf
  strSQL = strSQL & "WHERE" & vbCrLf
  strSQL = strSQL & "  S.SID=STM.SID" & vbCrLf
  strSQL = strSQL & "ORDER BY" & vbCrLf
  strSQL = strSQL & "  S.USERNAME," & vbCrLf
  strSQL = strSQL & "  S.PROGRAM," & vbCrLf
  strSQL = strSQL & "  S.SID"

  .CommandText = strSQL
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

'Fire up Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Left = 0
objIE.Top = 0
objIE.Width = 950
objIE.Height = 800
objIE.StatusBar = False
objIE.MenuBar = False
objIE.Toolbar = False

objIE.Navigate "about:blank"
objIE.Document.Title = "Charles Hooper's Time Model Data Viewer"
objIE.Visible = True

For i = 1 To intCheckIterations
  Set snpDataOSStat = comDataOSStat.Execute
  If Not (snpDataOSStat Is Nothing) Then
    Do While Not (snpDataOSStat.EOF)
      Select Case CStr(snpDataOSStat("stat_name"))
        Case "NUM_CPUS"
          intNumCPUs = CInt(snpDataOSStat("value"))
        Case "IDLE_TIME"
          dblIdleTimeLast = dblIdleTime
          dblIdleTime = CDbl(snpDataOSStat("value"))
        Case "BUSY_TIME"
          dblBusyTimeLast = dblBusyTime
          dblBusyTime = CDbl(snpDataOSStat("value"))
        Case "USER_TIME"
          dblUserTimeLast = dblUserTime
          dblUserTime = CDbl(snpDataOSStat("value"))
        Case "SYS_TIME"
          dblSysTimeLast = dblSysTime
          dblSysTime = CDbl(snpDataOSStat("value"))
      End Select

      snpDataOSStat.movenext
    Loop
  End If

  Set snpDataWait = comDataWait.Execute
  If Not (snpDataWait Is Nothing) Then
    Do While Not (snpDataWait.EOF)
      intWaitCurrent = intWaitCount + 1
      'Find the previous entry for this wait event
      For j = 1 To intWaitCount
        If strWaitEventName(j) = CStr(snpDataWait("event")) Then
          intWaitCurrent = j
          Exit For
        End If
      Next
      If intWaitCurrent = intWaitCount + 1 Then
        'New entry
        intWaitCount = intWaitCount + 1
        strWaitEventName(intWaitCurrent) = CStr(snpDataWait("event"))
      End If
      dblWaitValueLast(intWaitCurrent) = dblWaitValue(intWaitCurrent)
      dblWaitValue(intWaitCurrent) = CDbl(snpDataWait("time_waited"))
      dblWaitWaitsValueLast(intWaitCurrent) = dblWaitWaitsValue(intWaitCurrent)
      dblWaitWaitsValue(intWaitCurrent) = CDbl(snpDataWait("total_waits"))
      dblWaitTOValueLast(intWaitCurrent) = dblWaitTOValue(intWaitCurrent)
      dblWaitTOValue(intWaitCurrent) = CDbl(snpDataWait("total_timeouts"))

      snpDataWait.movenext
    Loop
  End If

  Set snpDataSysTime = comDataSysTime.Execute
  If Not (snpDataSysTime Is Nothing) Then
    Do While Not (snpDataSysTime.EOF)
      Select Case CStr(snpDataSysTime("stat_name"))
        Case "DB CPU"
          dblDBCPULast = dblDBCPU
          dblDBCPU = CDbl(snpDataSysTime("value"))
        Case "DB time"
          dblDBTimeLast = dblDBTime
          dblDBTime = CDbl(snpDataSysTime("value"))
        Case "Java execution elapsed time"
          dblJavaTimeLast = dblJavaTime
          dblJavaTime = CDbl(snpDataSysTime("value"))
        Case "PL/SQL compilation elapsed time"
          dblPLSQLCompileLast = dblPLSQLCompile
          dblPLSQLCompile = CDbl(snpDataSysTime("value"))
        Case "PL/SQL execution elapsed time"
          dblPLSQLExecutionLast = dblPLSQLExecution
          dblPLSQLExecution = CDbl(snpDataSysTime("value"))
        Case "RMAN cpu time (backup/restore)"
          dblRMANCPULast = dblRMANCPU
          dblRMANCPU = CDbl(snpDataSysTime("value"))
        Case "background cpu time"
          dblBackgroundCPULast = dblBackgroundCPU
          dblBackgroundCPU = CDbl(snpDataSysTime("value"))
        Case "background elapsed time"
          dblBackgroundElapsedLast = dblBackgroundElapsed
          dblBackgroundElapsed = CDbl(snpDataSysTime("value"))
        Case "connection management call elapsed time"
          dblConnectMgmtLast = dblConnectMgmt
          dblConnectMgmt = CDbl(snpDataSysTime("value"))
        Case "failed parse (out of shared memory) elapsed time"
          dblFailedParseMemoryLast = dblFailedParseMemory
          dblFailedParseMemory = CDbl(snpDataSysTime("value"))
        Case "failed parse elapsed time"
          dblFailedParseElapsedLast = dblFailedParseElapsed
          dblFailedParseElapsed = CDbl(snpDataSysTime("value"))
        Case "hard parse (bind mismatch) elapsed time"
          dblHardParseBindLast = dblHardParseBind
          dblHardParseBind = CDbl(snpDataSysTime("value"))
        Case "hard parse (sharing criteria) elapsed time"
          dblHardParseSharingLast = dblHardParseSharing
          dblHardParseSharing = CDbl(snpDataSysTime("value"))
        Case "hard parse elapsed time"
          dblHardParseElapsedLast = dblHardParseElapsed
          dblHardParseElapsed = CDbl(snpDataSysTime("value"))
        Case "inbound PL/SQL rpc elapsed time"
          dblInboundPLSQLLast = dblInboundPLSQL
          dblInboundPLSQL = CDbl(snpDataSysTime("value"))
        Case "parse time elapsed"
          dblParseTimeElapsedLast = dblParseTimeElapsed
          dblParseTimeElapsed = CDbl(snpDataSysTime("value"))
        Case "repeated bind elapsed time"
          dblRepeatedBindLast = dblRepeatedBind
          dblRepeatedBind = CDbl(snpDataSysTime("value"))
        Case "sequence load elapsed time"
          dblSequenceLoadLast = dblSequenceLoad
          dblSequenceLoad = CDbl(snpDataSysTime("value"))
        Case "sql execute elapsed time"
          dblSQLExecuteTimeLast = dblSQLExecuteTime
          dblSQLExecuteTime = CDbl(snpDataSysTime("value"))
      End Select

      snpDataSysTime.MoveNext
    Loop
  End If

  For j = 1 To intSessionCount
    intSessionExists(j) = False
  Next
  Set snpDataSessTime = comDataSessTime.Execute
  If Not (snpDataSessTime Is Nothing) Then
    Do While Not (snpDataSessTime.EOF)
      'Find the matching session's previous statistics
      If (lngSIDLast <> CLng(snpDataSessTime("sid"))) Or (lngSerialLast <> CLng(snpDataSessTime("serial#"))) Then
        'This is a different session, see if the session was previously captured
        lngSIDLast = CLng(snpDataSessTime("sid"))
        lngSerialLast = CLng(snpDataSessTime("serial#"))

        intSessionCurrent = intSessionCount + 1
        For j = 1 To intSessionCount
          If (lngSID(j) = CLng(snpDataSessTime("sid"))) And (lngSerial(j) = CLng(snpDataSessTime("serial#"))) Then
            intSessionCurrent = j
            Exit For
          End If
        Next
        If intSessionCurrent = intSessionCount + 1 Then
          intSessionCount = intSessionCount + 1
          lngSID(intSessionCurrent) = CLng(snpDataSessTime("sid"))
          lngSerial(intSessionCurrent) = CLng(snpDataSessTime("serial#"))
          strSessionOther(intSessionCurrent) = CStr(snpDataSessTime("machine")) & " ~ " & _
             CStr(snpDataSessTime("username")) & " ~ " & _
             CStr(snpDataSessTime("program")) & " ~ "
          If snpDataSessTime("sql_id") <> " " Then
            strSessionOther(intSessionCurrent) = strSessionOther(intSessionCurrent) & "SQL_ID/Child: " & _
              CStr(snpDataSessTime("sql_id")) & "/" & CStr(snpDataSessTime("sql_child_number"))
          End If
        End If
      End If

      intSessionExists(intSessionCurrent) = True
      Select Case CStr(snpDataSessTime("stat_name"))
        Case "DB CPU"
          dblDBCPUSLast(intSessionCurrent) = dblDBCPUS(intSessionCurrent)
          dblDBCPUS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "DB time"
          dblDBTimeSLast(intSessionCurrent) = dblDBTimeS(intSessionCurrent)
          dblDBTimeS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "Java execution elapsed time"
          dblJavaTimeSLast(intSessionCurrent) = dblJavaTimeS(intSessionCurrent)
          dblJavaTimeS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "PL/SQL compilation elapsed time"
          dblPLSQLCompileSLast(intSessionCurrent) = dblPLSQLCompileS(intSessionCurrent)
          dblPLSQLCompileS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "PL/SQL execution elapsed time"
          dblPLSQLExecutionSLast(intSessionCurrent) = dblPLSQLExecutionS(intSessionCurrent)
          dblPLSQLExecutionS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "RMAN cpu time (backup/restore)"
          dblRMANCPUSLast(intSessionCurrent) = dblRMANCPUS(intSessionCurrent)
          dblRMANCPUS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "background cpu time"
          dblBackgroundCPUSLast(intSessionCurrent) = dblBackgroundCPUS(intSessionCurrent)
          dblBackgroundCPUS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "background elapsed time"
          dblBackgroundElapsedSLast(intSessionCurrent) = dblBackgroundElapsedS(intSessionCurrent)
          dblBackgroundElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "connection management call elapsed time"
          dblConnectMgmtSLast(intSessionCurrent) = dblConnectMgmtS(intSessionCurrent)
          dblConnectMgmtS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "failed parse (out of shared memory) elapsed time"
          dblFailedParseMemorySLast(intSessionCurrent) = dblFailedParseMemoryS(intSessionCurrent)
          dblFailedParseMemoryS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "failed parse elapsed time"
          dblFailedParseElapsedSLast(intSessionCurrent) = dblFailedParseElapsedS(intSessionCurrent)
          dblFailedParseElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "hard parse (bind mismatch) elapsed time"
          dblHardParseBindSLast(intSessionCurrent) = dblHardParseBindS(intSessionCurrent)
          dblHardParseBindS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "hard parse (sharing criteria) elapsed time"
          dblHardParseSharingSLast(intSessionCurrent) = dblHardParseSharingS(intSessionCurrent)
          dblHardParseSharingS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "hard parse elapsed time"
          dblHardParseElapsedSLast(intSessionCurrent) = dblHardParseElapsedS(intSessionCurrent)
          dblHardParseElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "inbound PL/SQL rpc elapsed time"
          dblInboundPLSQLSLast(intSessionCurrent) = dblInboundPLSQLS(intSessionCurrent)
          dblInboundPLSQLS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "parse time elapsed"
          dblParseTimeElapsedSLast(intSessionCurrent) = dblParseTimeElapsedS(intSessionCurrent)
          dblParseTimeElapsedS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "repeated bind elapsed time"
          dblRepeatedBindSLast(intSessionCurrent) = dblRepeatedBindS(intSessionCurrent)
          dblRepeatedBindS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "sequence load elapsed time"
          dblSequenceLoadSLast(intSessionCurrent) = dblSequenceLoadS(intSessionCurrent)
          dblSequenceLoadS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
        Case "sql execute elapsed time"
          dblSQLExecuteTimeSLast(intSessionCurrent) = dblSQLExecuteTimeS(intSessionCurrent)
          dblSQLExecuteTimeS(intSessionCurrent) = CDbl(snpDataSessTime("value"))
      End Select

      snpDataSessTime.MoveNext
    Loop
  End If

  dteLastLoopStart = Now
  intDataChanged = True

  'Uncomment the following line if you would like for the session detail to be collapsed on each refresh
  'intDisplaySessionDetail = False

  Do While DateDiff("s", dteLastLoopStart, Now) < intDelayIterations
    'Remain in this loop until intDelayIterations seconds have elapsed
    intFlag = 0
    If intDataChanged = True Then
      'Update the web page
      strHTML = ""

      strHTML = strHTML & "<form name=""OracleTimeModel"">" & vbCrLf
      'strHTML = strHTML & "<input type=text id=divStatus name=divStatus value="" "" size=50 disabled=true><br />" & vbCrLf
      strHTML = strHTML & "<input type=hidden id=txtOK value="" "">" & vbCrLf
      strHTML = strHTML & "<input type=button value=""Re-Query"" id=cmdQuery onclick=""document.getElementById('txtOK').value='QUERY';"">" & vbCrLf
      strHTML = strHTML & "<input type=button value=""Show Detail"" id=cmdShowDetail onclick=""document.getElementById('txtOK').value='DETAIL';"">" & vbCrLf
      strHTML = strHTML & "<input type=button value=""Close"" id=cmdClose onclick=""document.getElementById('txtOK').value='CLOSE';"">" & vbCrLf
      strHTML = strHTML & "</form>" & vbCrLf

      strHTML = strHTML & "<table border=""1"" width=""500"" style=""font-family: Courier New; font-size: 8pt""" & vbCrLf
      strHTML = strHTML & "<tr><td bgcolor=""#11AAFF"">CPUs</td><td  bgcolor=""#11AAFF"">Busy Time</td><td bgcolor=""#11AAFF"">Idle Time</td>" & _
        "<td bgcolor=""#11AAFF"">User Mode</td><td bgcolor=""#11AAFF"">Kernel Mode</td></tr>" & vbCrLf
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber(intNumCPUs, 0) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblBusyTime - dblBusyTimeLast) / 100, 2) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblIdleTime - dblIdleTimeLast) / 100, 2) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblUserTime - dblUserTimeLast) / 100, 2) & "</td>"
      strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblSysTime - dblSysTimeLast) / 100, 2) & "</td></tr>"
      strHTML = strHTML & "</table><p>" & vbCrLf

      strHTML = strHTML & "<table border=""1"" width=""900"" style=""font-family: Courier New; font-size: 8pt""" & vbCrLf
      strHTML = strHTML & "<tr><td bgcolor=""#11AAFF"">Value</td><td bgcolor=""#11AAFF"" colspan=""5"">Statistic Name</td></tr>" & vbCrLf
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblBackgroundElapsed - dblBackgroundElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">Background Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblBackgroundElapsed - dblBackgroundElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblBackgroundElapsedS(j) - dblBackgroundElapsedSLast(j)) / (dblBackgroundElapsed - dblBackgroundElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblBackgroundElapsedS(j) - dblBackgroundElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblBackgroundCPU - dblBackgroundCPULast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Background CPU Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblBackgroundCPU - dblBackgroundCPULast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblBackgroundCPUS(j) - dblBackgroundCPUSLast(j)) / (dblBackgroundCPU - dblBackgroundCPULast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblBackgroundCPUS(j) - dblBackgroundCPUSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblRMANCPU - dblRMANCPULast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & "RMAN CPU Time (Backup Restore)</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblRMANCPU - dblRMANCPULast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblRMANCPUS(j) - dblRMANCPUSLast(j)) / (dblRMANCPU - dblRMANCPULast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><<td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblRMANCPUS(j) - dblRMANCPUSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblDBTime - dblDBTimeLast) / 1000000, 2) & "</td><td colspan=""5"">DB Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblDBTime - dblDBTimeLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblDBTimeS(j) - dblDBTimeSLast(j)) / (dblDBTime - dblDBTimeLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblDBTimeS(j) - dblDBTimeSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblDBCPU - dblDBCPULast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "DB CPU</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblDBCPU - dblDBCPULast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblDBCPUS(j) - dblDBCPUSLast(j)) / (dblDBCPU - dblDBCPULast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblDBCPUS(j) - dblDBCPUSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblConnectMgmt - dblConnectMgmtLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Connection Management Call Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblConnectMgmt - dblConnectMgmtLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblConnectMgmtS(j) - dblConnectMgmtSLast(j)) / (dblConnectMgmt - dblConnectMgmtLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblConnectMgmtS(j) - dblConnectMgmtSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblSequenceLoad - dblSequenceLoadLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Sequence Load Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblSequenceLoad - dblSequenceLoadLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblSequenceLoadS(j) - dblSequenceLoadSLast(j)) / (dblSequenceLoad - dblSequenceLoadLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblSequenceLoadS(j) - dblSequenceLoadSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblSQLExecuteTime - dblSQLExecuteTimeLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "SQL Execute Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblSQLExecuteTime - dblSQLExecuteTimeLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblSQLExecuteTimeS(j) - dblSQLExecuteTimeSLast(j)) / (dblSQLExecuteTime - dblSQLExecuteTimeLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblSQLExecuteTimeS(j) - dblSQLExecuteTimeSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblParseTimeElapsed - dblParseTimeElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Parse Time Elapsed</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblParseTimeElapsed - dblParseTimeElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblParseTimeElapsedS(j) - dblParseTimeElapsedSLast(j)) / (dblParseTimeElapsed - dblParseTimeElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblParseTimeElapsedS(j) - dblParseTimeElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblHardParseElapsed - dblHardParseElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & "Hard Parse Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblHardParseElapsed - dblHardParseElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblHardParseElapsedS(j) - dblHardParseElapsedSLast(j)) / (dblHardParseElapsed - dblHardParseElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblHardParseElapsedS(j) - dblHardParseElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblHardParseSharing - dblHardParseSharingLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & strInd & "Hard Parse (Sharing Criteria) Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblHardParseSharing - dblHardParseSharingLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblHardParseSharingS(j) - dblHardParseSharingSLast(j)) / (dblHardParseSharing - dblHardParseSharingLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblHardParseSharingS(j) - dblHardParseSharingSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblHardParseBind - dblHardParseBindLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & strInd & strInd & "Hard Parse (Bind Mismatch) Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblHardParseBind - dblHardParseBindLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblHardParseBindS(j) - dblHardParseBindSLast(j)) / (dblHardParseBind - dblHardParseBindLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblHardParseBindS(j) - dblHardParseBindSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblFailedParseElapsed - dblFailedParseElapsedLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & "Failed Parse Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblFailedParseElapsed - dblFailedParseElapsedLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblFailedParseElapsedS(j) - dblFailedParseElapsedSLast(j)) / (dblFailedParseElapsed - dblFailedParseElapsedLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblFailedParseElapsedS(j) - dblFailedParseElapsedSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblFailedParseMemory - dblFailedParseMemoryLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & strInd & strInd & "Failed Parse (Out of Shared Memory) Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblFailedParseMemory - dblFailedParseMemoryLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblFailedParseMemoryS(j) - dblFailedParseMemorySLast(j)) / (dblFailedParseMemory - dblFailedParseMemoryLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblFailedParseMemoryS(j) - dblFailedParseMemorySLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblPLSQLExecution - dblPLSQLExecutionLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "PL/SQL Execution Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblPLSQLExecution - dblPLSQLExecutionLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblPLSQLExecutionS(j) - dblPLSQLExecutionSLast(j)) / (dblPLSQLExecution - dblPLSQLExecutionLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblPLSQLExecutionS(j) - dblPLSQLExecutionSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblInboundPLSQL - dblInboundPLSQLLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Inbound PL/SQL RPC Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblInboundPLSQL - dblInboundPLSQLLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblInboundPLSQLS(j) - dblInboundPLSQLSLast(j)) / (dblInboundPLSQL - dblInboundPLSQLLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblInboundPLSQLS(j) - dblInboundPLSQLSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblPLSQLCompile - dblPLSQLCompileLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "PL/SQL Compilation Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblPLSQLCompile - dblPLSQLCompileLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblPLSQLCompileS(j) - dblPLSQLCompileSLast(j)) / (dblPLSQLCompile - dblPLSQLCompileLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblPLSQLCompileS(j) - dblPLSQLCompileSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblJavaTime - dblJavaTimeLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Java Execution Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblJavaTime - dblJavaTimeLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblJavaTimeS(j) - dblJavaTimeSLast(j)) / (dblJavaTime - dblJavaTimeLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblJavaTimeS(j) - dblJavaTimeSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "<tr><td><p align=""right"">" & FormatNumber((dblRepeatedBind - dblRepeatedBindLast) / 1000000, 2) & "</td><td colspan=""5"">" & strInd & "Repeated Bind Elapsed Time</td></tr>" & vbCrLf
      If (intDisplaySessionDetail = True) And ((dblRepeatedBind - dblRepeatedBindLast) <> 0) Then
        For j = 1 To intSessionCount
          If intSessionExists(j) = True Then
            If (dblRepeatedBindS(j) - dblRepeatedBindSLast(j)) / (dblRepeatedBind - dblRepeatedBindLast) >= sglSessionMinimumPercent Then
              strHTML = strHTML & "<tr bgcolor=""#FFFF88""><td>&nbsp;</td><td colspan=""1"">&nbsp;</td><td colspan=""1""><p align=""right"">" & FormatNumber((dblRepeatedBindS(j) - dblRepeatedBindSLast(j)) / 1000000, 2) & "</td><td>SID: " & CStr(lngSID(j)) & "</td><td>Serial #: " & CStr(lngSerial(j)) & "</td><td>" & strSessionOther(j) & "</td></tr>" & vbCrLf
            End If
          End If
        Next
      End If
      strHTML = strHTML & "</table><p>" & vbCrLf
      strHTML = strHTML & "<table border=""1"" width=""500"" style=""font-family: Courier New; font-size: 8pt""" & vbCrLf
      strHTML = strHTML & "<tr><td bgcolor=""#11AAFF"">Wait Event Name</td><td  bgcolor=""#11AAFF"">Wait Time</td><td bgcolor=""#11AAFF"">Waits</td><td bgcolor=""#11AAFF"">Timeouts</td></tr>" & vbCrLf
      For j = 1 To intWaitCount
        If (dblWaitValue(j) - dblWaitValueLast(j)) <> 0 Then
          strHTML = strHTML & "<tr><td>" & strWaitEventName(j) & "</td>"
          strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblWaitValue(j) - dblWaitValueLast(j)) / 100, 2) & "</td>"
          strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblWaitWaitsValue(j) - dblWaitWaitsValueLast(j)), 0) & "</td>"
          strHTML = strHTML & "<td><p align=""right"">" & FormatNumber((dblWaitTOValue(j) - dblWaitTOValueLast(j)), 0) & "</td></tr>"
        End If
      Next
      strHTML = strHTML & "</table>" & vbCrLf

      objIE.Document.Body.InnerHTML = strHTML
      intDataChanged = False
    End If

    'Put the VBS script into suspend mode for 1/2 second to prevent hammering the CPUs
    Wscript.Sleep 500

    If objIE Is Nothing Then
      'User closed the Window
      intFlag = -1
    Else
      If objIE.Document.All.txtOK.Value <> " " Then
        Select Case objIE.Document.All.txtOK.Value
          Case "QUERY"
            intFlag = 1
            objIE.Document.All.txtOK.Value = " "
          Case "DETAIL"
            intFlag = 2
            If intDisplaySessionDetail = True Then
              intDisplaySessionDetail = False
            Else
              intDisplaySessionDetail = True
            End If
            intDataChanged = True
            objIE.Document.All.txtOK.Value = " "
          Case "CLOSE"
            intFlag = -1
            objIE.Document.All.txtOK.Value = " "
            objIE.Quit
        End Select
      End If
    End If

    If Abs(intFlag) = 1 Then
      Exit Do
    End If
  Loop

  If intFlag = -1 Then
    Exit For
  End If
Next

dbDatabase.Close

Set snpDataWait = Nothing
Set comDataWait = Nothing
Set snpDataOSStat = Nothing
Set comDataOSStat = Nothing
Set snpDataSysTime = Nothing
Set comDataSysTime = Nothing
Set snpDataSessTime = Nothing
Set comDataSessTime = Nothing
Set dbDatabase = Nothing
Set objIE = Nothing

Easy, right? 

There is no need to stop at this level – dig into the session-level wait events, enable a 10046 trace for sessions, set up the script to send an email to a user if the user consumes more than 10% of the server’s capacity, … and most important, have fun.





Drilling into Session Detail from the Operating System – On the Windows Platform 2

9 01 2010

January 9, 2010

In the earlier post, you saw a script output that looked something like the following:

1/8/2010 12:57:56 PM Processes: 73 Threads: 861 C. Switches: 35972 Q. Length: 6
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 9.33S Sys  Time: 25.19S Memory: 141.11MB Page File: 0.63MB
  Handle: 1444 User Time: 9.34S Sys  Time: 25.5S ElapsedTime: 70S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 103.9S Sys  Time: 0.13S Memory: 442.42MB Page File: 0.67MB
  Handle: 3520 User Time: 34.13S Sys  Time: 0.02S ElapsedTime: 69S Priority: 8 ThreadState: Running
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;

    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

  Handle: 2152 User Time: 34.04S Sys  Time: 0S ElapsedTime: 69S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

The above was echoed to the command line window and also written to a logging file.  So, how do we create this output?  Save the following on the root of the C:\ drive as CPUHighLoadProcesses.vbs:

Const adNumeric=131
Const adParamInput=1
Const adCmdText=1
Const adVarChar=200

Dim i                       'Loop counter for iterations
Dim j                       'Loop counter to find the previous stats for the thread
Dim intInstance             'Index to the instance statistics
Dim IntOldCSPerSec          'Previous value for Context Switches Per Second
Dim intOldProcesses         'Previous value for the number of processes
Dim intOldThreads           'Previous value for the number of threads
Dim strSQL                  'SQL to check WMI Win32_PerfRawData_PerfOS_System
Dim strSQL2                 'SQL to find the Oracle processes using WMI Win32_Process
Dim strSQL3                 'SQL to drill into the threads in the Oracle processes using WMI Win32_Thread
Dim strSQL4                 'SQL to find the Oracle session, SQL statement, and execution plan
Dim strOut                  'Data to be written to the text file
Dim strComputer             'Oracle database server name, or . for the current computer
Dim sglUMTime(20)           'User mode time in seconds for the Oracle instance
Dim sglKMTime(20)           'Kernel mode time in seconds for the Oracle instance
Dim sglWorkingSet(20)       'Working set memory size for the Oracle instance
Dim sglPageFileUsage(20)    'Page/swap file usage for the Oracle instance
Dim sglOUMTime(20)          'Previous user mode time in seconds for the Oracle instance
Dim sglOKMTime(20)          'Previous kernel mode time in seconds for the Oracle instance
Dim sglOldWorkingSet(20)    'Previous working set memory size for the Oracle instance
Dim sglOldPageFileUsage(20) 'Previous page/swap file usage for the Oracle instance
Dim objWMIService           'An object used to pass in the WMI calls
Dim colItems                'Collection of processes running on the server
Dim objItem                 'An individual process running on the server
Dim colThreads              'Collection of threads running in a process
Dim objThread               'An individual thread running in a process
Dim intThread               'Index to the previous values for the thread's statistics
Dim intThreadCount(20)      'Maximum previous thread index for the instance
Dim intThreadH(20,600)      'Thread handle
Dim sglTUMTime(20,600)      'User mode time in seconds for the thread
Dim sglTKMTime(20,600)      'Kernel mode time in seconds for the thread
Dim sglTETime(20,600)       'Elapsed time in seconds for the thread
Dim adsFile                 'ADO stream object used to write out the log file
Dim snpData                 'ADO recordset used to query V$SESSION/V$SQL
Dim comData                 'ADO command object used to permit passing in bind variables for the V$SESSION/V$SQL query
Dim snpDataPlan             'ADO recordset used to retrieve the execution plan for the session's SQL statement
dim comDataPlan             'ADO command object used to permit passing in bind variables for the execution plan
Dim dbDatabase              'ADO database connection object
Dim intCheckIterations      'Number of times to check the instances
Dim intDelayIterations      'Number of seconds to delay between iterations
Dim sglThreadBusyPercent    'Percentage of the seconds in the iteration delay does a session need to consume to be examined

Set snpData = CreateObject("ADODB.Recordset")
Set comData = CreateObject("ADODB.Command")
Set snpDataPlan = CreateObject("ADODB.Recordset")
Set comDataPlan = CreateObject("ADODB.Command")
Set dbDatabase = CreateObject("ADODB.Connection")

strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

intCheckIterations = 10
intDelayIterations = 60
sglThreadBusyPercent = 0.50  '50%

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

'Should verify that the connection attempt was successful, but I will leave that for someone else to code
On Error Resume Next  'Allow continuing the script if an error happens

'Prepare the ADO Stream to write the data to the text file
Set adsFile = CreateObject("ADODB.Stream")
adsFile.Type = 2
adsFile.Charset = "iso-8859-1"
adsFile.Open

'Prepare the SQL statements
strSQL = "SELECT * FROM Win32_PerfRawData_PerfOS_System"
strSQL2 = "SELECT * FROM Win32_Process Where Name like 'Oracle%'"
strSQL3 = "SELECT * FROM Win32_Thread Where ProcessHandle="

With comData
  strSQL4 = "SELECT" & VBCrLf
  strSQL4 = strSQL4 & "  P.PID," & VBCrLf
  strSQL4 = strSQL4 & "  P.SPID," & VBCrLf
  strSQL4 = strSQL4 & "  S.SID," & VBCrLf
  strSQL4 = strSQL4 & "  S.SERIAL#," & VBCrLf
  strSQL4 = strSQL4 & "  NVL(S.USERNAME,' ') USERNAME," & VBCrLf
  strSQL4 = strSQL4 & "  NVL(S.MACHINE,' ') MACHINE," & VBCrLf
  strSQL4 = strSQL4 & "  NVL(S.PROGRAM,' ') PROGRAM," & VBCrLf
  strSQL4 = strSQL4 & "  S.SQL_ID," & VBCrLf
  strSQL4 = strSQL4 & "  S.SQL_CHILD_NUMBER," & VBCrLf
  strSQL4 = strSQL4 & "  NVL(SQL.SQL_TEXT,' ') SQL_TEXT" & VBCrLf
  strSQL4 = strSQL4 & "FROM" & VBCrLf
  strSQL4 = strSQL4 & "  V$PROCESS P," & VBCrLf
  strSQL4 = strSQL4 & "  V$SESSION S," & VBCrLf
  strSQL4 = strSQL4 & "  V$SQL SQL" & VBCrLf
  strSQL4 = strSQL4 & "WHERE" & VBCrLf
  strSQL4 = strSQL4 & "  P.SPID=?" & VBCrLf
  strSQL4 = strSQL4 & "  AND P.ADDR=S.PADDR" & VBCrLf
  strSQL4 = strSQL4 & "  AND S.SQL_ID = SQL.SQL_ID(+)" & VBCrLf
  strSQL4 = strSQL4 & "  AND S.SQL_CHILD_NUMBER = SQL.CHILD_NUMBER(+)" & VBCrLf
  strSQL4 = strSQL4 & "ORDER BY" & VBCrLf
  strSQL4 = strSQL4 & "  S.USERNAME," & VBCrLf
  strSQL4 = strSQL4 & "  P.PROGRAM"

  .Parameters.Append .CreateParameter("spid", adNumeric, adParamInput, 12, 0)
  .CommandText = strSQL4
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

With comDataPlan
  strSQL4 = "SELECT" & VBCrLf
  strSQL4 = strSQL4 & "  *" & VBCrLf
  strSQL4 = strSQL4 & "FROM" & VBCrLf
  strSQL4 = strSQL4 & "  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(?, ?, 'TYPICAL'))" & VBCrLf

  .Parameters.Append .CreateParameter("sql_id", adVarchar, adParamInput, 20, "")
  .Parameters.Append .CreateParameter("child_number", adNumeric, adParamInput, 12, 0)
  .CommandText = strSQL4
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

strComputer = "."  ' the . indicates the local computer
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\CIMV2")

IntOldCSPerSec = 0

For intInstance = 1 to 20
    sglOUMTime(intInstance) = 0
    sglOKMTime(intInstance) = 0
    sglOldWorkingSet(intInstance) = 0
    sglOldPageFileUsage(intInstance) = 0
    intThreadCount(intInstance) = 0
    intThreadH(intInstance,1) = 0
    sglTUMTime(intInstance,1) = 0
    sglTKMTime(intInstance,1) = 0
    sglTETime(intInstance,1) = 0
Next

For i = 1 to intCheckIterations 'Number of iterations
  Set colItems = objWMIService.ExecQuery(strSQL,"WQL",48)

  For Each objItem in colItems
    strOut = Now() & " Processes: " & objItem.Processes
    strOut = strOut & " Threads: " & objItem.Threads
    strOut = strOut & " C. Switches: " & objItem.ContextSwitchesPersec - IntOldCSPerSec
    strOut = strOut & " Q. Length: " & objItem.ProcessorQueueLength

    'Write to screen
    Wscript.Echo strOut

    'Write to log file
    adsFile.WriteText(strOut & vbCrLf)

    IntOldCSPerSec = objItem.ContextSwitchesPersec
    intOldProcesses = objItem.Processes
    intOldThreads = objItem.Threads
  Next

  Set colItems = Nothing
  Set colItems = objWMIService.ExecQuery(strSQL2,"WQL",48)

  intInstance = 0
  For Each objItem in colItems
    intInstance = intInstance + 1
    sglUMTime(intInstance) = Round(objItem.UserModeTime/10000000, 2)
    sglKMTime(intInstance) = Round(objItem.KernelModeTime/10000000, 2)
    sglWorkingSet(intInstance) = Round(objItem.WorkingSetSize/1048576, 2)
    sglPageFileUsage(intInstance) = Round(objItem.PageFileUsage/1048576, 2)

    strOut = "Instance: " & objItem.CommandLine & vbCrLf
    strOut = strOut & " User Time: " & Round(sglUMTime(intInstance) - sglOUMTime(intInstance),2) & "S"
    strOut = strOut & " Sys  Time: " & Round(sglKMTime(intInstance) - sglOKMTime(intInstance),2) & "S"
    strOut = strOut & " Memory: " & sglWorkingSet(intInstance) & "MB"
    strOut = strOut & " Page File: " & sglPageFileUsage(intInstance) & "MB" & vbCrLf

    Set colThreads = Nothing
    Set colThreads = objWMIService.ExecQuery(strSQL3 & cStr(objItem.ProcessID),"WQL",48)

    For Each objThread in colThreads
      'Find the statistics for thread from the previous iteration's statistics
      intThread = intThreadCount(intInstance) + 1
      For j = 1 to intThreadCount(intInstance)
        If intThreadH(intInstance,j) = objThread.Handle Then
          intThread = j
          Exit For
        End If
      Next

      If intThreadH(intInstance,intThread) = 0 Then
        'This is a new thread that was not captured before
        intThreadH(intInstance,intThread) = objThread.Handle
        If intThreadCount(intInstance) < intThread Then
          intThreadCount(intInstance) = intThread
        End If
      End If

      If (cSng(Round(objThread.UserModeTime/1000,2)-sglTUMTime(intInstance,intThread)+ _
          Round(objThread.KernelModeTime/1000,2)-sglTKMTime(intInstance,intThread)) / _
          intDelayIterations) >= sglThreadBusyPercent Then
        strOut = strOut & "  Handle: " & cStr(objThread.Handle)
        strOut = strOut & " User Time: " & Round((Round(objThread.UserModeTime/1000,2)-sglTUMTime(intInstance,intThread)),2) & "S"
        strOut = strOut & " Sys  Time: " & Round((Round(objThread.KernelModeTime/1000,2)-sglTKMTime(intInstance,intThread)),2) & "S"
        strOut = strOut & " ElapsedTime: " & Round((Round(objThread.ElapsedTime/1000,2)-sglTETime(intInstance,intThread)),2) & "S"
        strOut = strOut & " Priority: " & objThread.Priority
        strOut = strOut & " ThreadState:"
        Select Case objThread.ThreadState
          Case 0
            strOut = strOut & " Initialized"
          Case 1
            strOut = strOut & " In Run Queue"
          Case 2
            strOut = strOut & " Running"
          Case 3
            strOut = strOut & " Preparing to Run"
          Case 4
            strOut = strOut & " Terminated"
          Case 5
            strOut = strOut & " Idle"
          Case 6
            strOut = strOut & " Non-CPU Wait Event:"
            'See http://msdn.microsoft.com/en-us/library/aa394494(VS.85).aspx
            Select Case objThread.ThreadWaitReason
              Case 0
                strOut = strOut & "Executive"
              Case 1
                strOut = strOut & "FreePage"
              Case 2
                strOut = strOut & "PageIn"
              Case 3
                strOut = strOut & "PoolAllocation"
              Case 4
                strOut = strOut & "ExecutionDelay"
              Case 5
                strOut = strOut & "FreePage"
              Case 6
                strOut = strOut & "PageIn"
              Case 7
                strOut = strOut & "Executive"
              Case 8
                strOut = strOut & "FreePage"
              Case 9
                strOut = strOut & "PageIn"
              Case 10
                strOut = strOut & "PoolAllocation"
              Case 11
                strOut = strOut & "ExecutionDelay"
              Case 12
                strOut = strOut & "FreePage"
              Case 13
                strOut = strOut & "PageIn"
              Case 14
                strOut = strOut & "EventPairHigh"
              Case 15
                strOut = strOut & "EventPairLow"
              Case 16
                strOut = strOut & "LPCReceive"
              Case 17
                strOut = strOut & "LPCReply"
              Case 18
                strOut = strOut & "VirtualMemory"
              Case 19
                strOut = strOut & "PageOut"
              Case 20
                strOut = strOut & "Unknown"
              Case Else
                strOut = strOut & objThread.ThreadWaitReason
            End Select
          Case 7
            strOut = strOut & " Unknown"
          Case Else
            strOut = strOut & objThread.ThreadState
        End Select       

        strOut = strOut & vbCrLf
        comData("spid") = objThread.Handle
        Set snpData = comData.Execute

        If Not(snpData Is Nothing) Then
          If Not(snpData.EOF) Then
            strOut = strOut & "   PID:" & snpData("pid")
            strOut = strOut & " SPID:" & snpData("spid")
            strOut = strOut & " SID:" & snpData("sid")
            strOut = strOut & " SERIAL#:" & snpData("serial#")
            strOut = strOut & " USERNAME:" & snpData("username")
            strOut = strOut & " MACHINE:" & snpData("machine")
            strOut = strOut & " PROGRAM:" & snpData("program") & vbCrLf
            strOut = strOut & "   " & snpData("sql_text") & vbCrLf

            If Not(IsNull(snpData("sql_id"))) Then
              comDataPlan("sql_id") = snpData("sql_id")
              comDataPlan("child_number") = snpData("sql_child_number")
              Set snpDataPlan = comDataPlan.Execute

              If Not(snpDataPlan Is Nothing) Then
                strOut = strOut & vbCrLf
                Do While Not(snpDataPlan.EOF)
                  strOut = strOut & "    " & snpDataPlan(0) & vbCrLf
                  snpDataPlan.MoveNext
                Loop
                snpDataPlan.Close
              End If
              strOut = strOut & vbCrLf
            End If
          End If
          snpData.Close 
        End If
      End If

      sglTUMTime(intInstance,intThread) = Round(objThread.UserModeTime/1000,2)
      sglTKMTime(intInstance,intThread) = Round(objThread.KernelModeTime/1000,2)
      sglTETime(intInstance,intThread) = Round(objThread.ElapsedTime/1000,2)
    Next
    strOut = strOut & vbCrLf

    'Write to screen
    Wscript.Echo strOut

    'Write to log file
    adsFile.WriteText(strOut & vbCrLf)

    sglOUMTime(intInstance) = Round(objItem.UserModeTime/10000000, 2)
    sglOKMTime(intInstance) = Round(objItem.KernelModeTime/10000000, 2)
    sglOldWorkingSet(intInstance) = Round(objItem.WorkingSetSize/1048576, 2)
    sglOldPageFileUsage(intInstance) = Round(objItem.PageFileUsage/1048576, 2)
  Next

  'Wait intDelayIterations seconds before sampling again
  Wscript.Sleep intDelayIterations * 1000
Next

adsFile.SaveToFile "C:\CPUHighLoadProcesses.txt", 2 
adsFile.Close

dbDatabase.Close

Set snpData = Nothing
Set comData = Nothing
Set snpDataPlan = Nothing
Set comDataPlan = Nothing
Set dbDatabase = Nothing
Set adsFile = Nothing

If you decide to use the above script, test it very carefully.  There may be one or two typos or logic errors.  If you have administrator rights on the server, the script can be executed remotely (it does not need to be run directly on the server).  WMI queries are given a very low priority, so if the CPU run queue is long, the script may appear to hang for a long time.  To run the script, open a command line window and type:

cscript C:\CPUHighLoadProcesses.vbs

WMI queries are very powerful, as demonstrated by the above script.

Update: 1.5 hours before this blog article is scheduled to appear:

{RANT}

After I wrote the above script and this and the previous blog articles I stumbled upon the following very recent news article that states the following:
dba-oracle.com/t_display_background_processes_windows.htm

“In Windows, the ‘thread’ model is used, and Oracle dispatches his own background tasks within the domain of the single process, oracle.exe.  Hence, you cannot see any background processes from the Windows OS (but you can see listener process and parallel query slaves).”

OK, forget about my script, because the quote directly above states that it is not possible.  Someone please call Oracle Corp. and tell them that their Oracle Administration Assistant for Windows needs to be removed from the documentation because what it shows is simply not possible. Did I mention that I dislike being confused?

{/RANT}





Drilling into Session Detail from the Operating System – On the Windows Platform

9 01 2010

January 9, 2010

(Forward to the Follow-Up Article)

As you might be aware, Oracle running on Unix/Linux uses a process model, while Oracle running on Windows uses a thread model.  With the process model it is fairly easy to use operating system tools, such as ps or top, to monitor the performance of individual database sessions, and then the DBA can use that information to manually drill back into what the high utilization sessions are doing.  Unfortunately, with the thread model, that procedure is impossible.  Unless, of course, you know a trick.

In one of the two chapters of the Expert Oracle Practices book, Randolf and I stated the following:

“In the case of performance problems caused by Oracle-related processes, consider drilling into the Oracle process activity by using the operating system process identifier (PID) to search V$PROCESS for a matching PID, and then join to V$SESSION on V$PROCESS.ADDR=V$SESSION.PADDR.”

I think that we also stated somewhere in the chapters that WMI queries could be used to drill into the session level activity on the Windows platform.  We did not provide a script to demonstrate the process – the chapters were running long by a couple pages (OK more than a couple if you have seen the chapters), so we did not bother to construct a demonstration.  I played with the idea a little, and then decided that the 10 to 20 pages it would take to describe the process could be better used for something else.

So, let’s play a game of bury the poor Windows box (this happens to be a computer with a dual core CPU).  It would be neat, if we have 20 database instances running on this poor Windows box, to play a game of Whose Got My CPU (R).  So, it would be neat if we could send something like this out to a command line window:

1/8/2010 12:47:18 PM Processes: 73 Threads: 880 C. Switches: 10573606 Q. Length: 7
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 668.4S Sys  Time: 295.9S Memory: 275.59MB Page File: 0.63MB
  Handle: 1444 User Time: 663.32S Sys  Time: 296.99S ElapsedTime: 3358S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 2959.84S Sys  Time: 11.73S Memory: 539.5MB Page File: 0.67MB
  Handle: 3520 User Time: 1081.91S Sys  Time: 0.92S ElapsedTime: 3238S Priority: 8 ThreadState: Running

The above states that at 12:47 there were 73 processes running on this poor Windows box with 880 process threads.  There were 10,573,606 context switches over some period of time, and the run queue length is currently 7 (could this be a sign of a problem on a computer with a single dual core CPU – no, can’t be – someone on the Internet said that pushing the CPUs to 100% utilization is desirable, and because it is on the Internet, it must be true).

Next, the above output shows that an Oracle 10.2.0.x database instance was found (OR10).  This instance had consumed 668.4 seconds of CPU time in user mode, and 295.9 seconds of CPU time in system (kernel) mode – that system mode time seems to be high compared to the user mode time.  The instance was using about 276MB of memory. A single thread representing a dedicated connection was created 33,585 seconds earlier (that might be a calculation error – looks like I should have divided that number by 10).  That thread has used 663.32 seconds of user mode CPU time, and 296.99 seconds of system (kernel) mode CPU time.  The thread had a scheduling priority of 8 assigned to it and at the time was sitting in the run queue waiting for the CPU to become available.

Next, the above output shows that an Oracle 11.1.0.x database instance was found (OR11).  This instance consumed 2959.84 seconds of CPU time in user mode, and 11.73 second of CPU time in system (kernel) mode.  The instance was using about 540MB of memory.   A single thread representing a dedicated session was created 32,385 seconds earlier.  That thread had used 1081.91 seconds of user mode CPU time (about 1/3 of the total for the instance) and 0.92 seconds of system (kernel) mode CPU time.  The thread had a scheduling priority of 8 assigned to it and at the time was running on the CPU (something else must have been running on the other CPU core – possibly the script that collected this data).

OK, the above is kind of neat, but what are the sessions doing that caused the CPU usage?  I don’t have the SID and SERIAL# for the sessions, so I can’t enable a 10046 trace.  It will take too long to fire up a GUI of some sort to see what is happening.  I wonder if I can do anything with the handle number that was output?  Well, I read the chapters that Randolf and I wrote, and that Handle just so happens to be treated like the PID on Oracle platforms that use a process model.  So, I could do all kinds of interesting things once I resolve the displayed Handle to a SID.  For instance, I could do something like this:

1/8/2010 12:47:18 PM Processes: 73 Threads: 880 C. Switches: 10573606 Q. Length: 7
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 668.4S Sys  Time: 295.9S Memory: 275.59MB Page File: 0.63MB
  Handle: 1444 User Time: 663.32S Sys  Time: 296.99S ElapsedTime: 3358S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 2959.84S Sys  Time: 11.73S Memory: 539.5MB Page File: 0.67MB
  Handle: 3520 User Time: 1081.91S Sys  Time: 0.92S ElapsedTime: 3238S Priority: 8 ThreadState: Running
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,   REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID, REQ.R2_PART_ID,...

    SQL_ID  3p1v051atxt1z, child number 0
    -------------------------------------
    SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,  
    REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID,
    REQ.R2_PART_ID, REQ.R3_PART_ID,   REQ.R4_PART_ID, REQ.R5_PART_ID,
    REQ.R6_PART_ID,   REQ.R1_CALC_QTY, REQ.R2_CALC_QTY, REQ.R3_CALC_QTY,  
    REQ.R4_CALC_QTY, REQ.R5_CALC_QTY, REQ.R6_CALC_QTY FROM   (SELECT    
    TOP_LEVEL_PART_ID,     DECODE(R6_PART_ID,NULL,      
    DECODE(R5_PART_ID,NULL,         DECODE(R4_PART_ID,NULL,          
    DECODE(R3_PART_ID,NULL,             DECODE(R2_PART_ID,NULL,            
           R1_PART_ID,R2_PART_ID),                  R3_PART_ID),           
        R4_PART_ID),              R5_PART_ID),            R6_PART_ID)
    PURC_PART_ID,     NVL(R1_CALC_QTY,0)*NVL(R2_CALC_QTY,1)*NVL(R3_CALC_QTY,
    1)*NVL(R4_CALC_QTY,1)*NVL(R5_CALC_QTY,1)*NVL(R6_CALC_QTY,1) TOTAL_QTY, 
       R1_PART_ID, R1_CALC_QTY, R2_PART_ID, R2_CALC_QTY, R3_PART_ID,
    R3_CALC_QTY,     R4_PART_ID, R4_CALC_QTY, R5_PART_ID, R5_CALC_QTY,
    R6_PART_ID, R6_CALC_QTY   FROM     (SELECT       PL.PART_ID TOP_LEVE

    Plan hash value: 3313542492

    ---------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                |                      |       |       |   101 (100)|          |        |
    |   1 |  VIEW                                           | VM_NWVW_1            |     1 |   225 |   101   (2)| 00:00:02 |   OR11 |
    |   2 |   HASH UNIQUE                                   |                      |     1 |   498 |   101   (2)| 00:00:02 |        |
    |   3 |    NESTED LOOPS OUTER                           |                      |     1 |   498 |   100   (1)| 00:00:02 |        |
    |*  4 |     FILTER                                      |                      |       |       |            |          |        |
    |   5 |      NESTED LOOPS OUTER                         |                      |     1 |   474 |    99   (2)| 00:00:02 |        |
    |   6 |       NESTED LOOPS OUTER                        |                      |     1 |   435 |    96   (2)| 00:00:02 |        |
    |   7 |        NESTED LOOPS OUTER                       |                      |     1 |   411 |    95   (2)| 00:00:02 |        |
    |*  8 |         FILTER                                  |                      |       |       |            |          |        |
    |   9 |          NESTED LOOPS OUTER                     |                      |     1 |   387 |    94   (2)| 00:00:02 |        |
    |* 10 |           FILTER                                |                      |       |       |            |          |        |
    |  11 |            NESTED LOOPS OUTER                   |                      |     1 |   348 |    91   (2)| 00:00:02 |        |
    |  12 |             NESTED LOOPS OUTER                  |                      |     1 |   309 |    88   (2)| 00:00:02 |        |
    |  13 |              NESTED LOOPS OUTER                 |                      |     1 |   285 |    87   (2)| 00:00:02 |        |
    |  14 |               NESTED LOOPS OUTER                |                      |     1 |   261 |    86   (2)| 00:00:02 |        |
    |* 15 |                FILTER                           |                      |       |       |            |          |        |
    |  16 |                 NESTED LOOPS OUTER              |                      |     1 |   237 |    85   (2)| 00:00:02 |        |
    |* 17 |                  FILTER                         |                      |       |       |            |          |        |
    |  18 |                   NESTED LOOPS OUTER            |                      |     1 |   198 |    82   (2)| 00:00:01 |        |
    |  19 |                    NESTED LOOPS                 |                      |     1 |   159 |    79   (2)| 00:00:01 |        |
    |  20 |                     NESTED LOOPS                |                      |     1 |   118 |    76   (2)| 00:00:01 |        |
    |  21 |                      MERGE JOIN CARTESIAN       |                      |     1 |    94 |    71   (2)| 00:00:01 |        |
    |* 22 |                       TABLE ACCESS FULL         | TEMP_PART_PRICE_DATE |     1 |    85 |     2   (0)| 00:00:01 |   OR11 |
    |  23 |                       BUFFER SORT               |                      |  7016 | 63144 |    69   (2)| 00:00:01 |        |
    |* 24 |                        TABLE ACCESS FULL        | CUSTOMER_ORDER       |  7016 | 63144 |    69   (2)| 00:00:01 |   OR11 |
    |* 25 |                      TABLE ACCESS BY INDEX ROWID| CUST_ORDER_LINE      |     1 |    24 |     5   (0)| 00:00:01 |   OR11 |
    |* 26 |                       INDEX RANGE SCAN          | SYS_C0028623         |     9 |       |     1   (0)| 00:00:01 |   OR11 |
    |  27 |                     TABLE ACCESS BY INDEX ROWID | REQUIREMENT          |     1 |    41 |     3   (0)| 00:00:01 |   OR11 |
    |* 28 |                      INDEX RANGE SCAN           | X_REQUIREMENT_5      |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  29 |                    TABLE ACCESS BY INDEX ROWID  | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 30 |                     INDEX RANGE SCAN            | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  31 |                  TABLE ACCESS BY INDEX ROWID    | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 32 |                   INDEX RANGE SCAN              | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 33 |                TABLE ACCESS BY INDEX ROWID      | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 34 |                 INDEX UNIQUE SCAN               | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 35 |               TABLE ACCESS BY INDEX ROWID       | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 36 |                INDEX UNIQUE SCAN                | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 37 |              TABLE ACCESS BY INDEX ROWID        | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 38 |               INDEX UNIQUE SCAN                 | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  39 |             TABLE ACCESS BY INDEX ROWID         | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 40 |              INDEX RANGE SCAN                   | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  41 |           TABLE ACCESS BY INDEX ROWID           | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 42 |            INDEX RANGE SCAN                     | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 43 |         TABLE ACCESS BY INDEX ROWID             | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 44 |          INDEX UNIQUE SCAN                      | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 45 |        TABLE ACCESS BY INDEX ROWID              | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 46 |         INDEX UNIQUE SCAN                       | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  47 |       TABLE ACCESS BY INDEX ROWID               | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 48 |        INDEX RANGE SCAN                         | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 49 |     TABLE ACCESS BY INDEX ROWID                 | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 50 |      INDEX UNIQUE SCAN                          | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    ---------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       4 - filter(("TPPD"."PART_ID"=DECODE(DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID"
                  ,NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'N
                  ONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE
                  (NVL("P5"."FABRICATED",'NONE'),'Y',"R6"."PART_ID",NULL)),NULL,DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NO
                  NE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(N
                  VL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",
                  NULL)),NULL,DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."
                  FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NU
                  LL,DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NON
                  E'),'Y',"R3"."PART_ID",NULL)),NULL,DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,"R"."PART_ID"
                  ,DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL)),DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_
                  ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL))),DECODE(DECODE(DECODE(NVL("P"."FABRICA
                  TED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,
                  DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL))),DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),
                  'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P
                  3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)
                  )),DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FA
                  BRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL
                  ,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P5"."FABRICATED",'NONE'),'Y',"
                  R6"."PART_ID",NULL))) AND "R6"."SUBORD_WO_SUB_ID" IS NULL))
       8 - filter("R5"."SUBORD_WO_SUB_ID" IS NULL)
      10 - filter("R4"."SUBORD_WO_SUB_ID" IS NULL)
      15 - filter("R3"."SUBORD_WO_SUB_ID" IS NULL)
      17 - filter("R2"."SUBORD_WO_SUB_ID" IS NULL)
      22 - filter("TPPD"."INCREASE"<>0)
      24 - filter(("CO"."STATUS"='C' OR "CO"."STATUS"='F' OR "CO"."STATUS"='P' OR "CO"."STATUS"='R' OR "CO"."STATUS"='U'))
      25 - filter("COL"."DESIRED_SHIP_DATE">SYSDATE@!-365)
      26 - access("CO"."ID"="COL"."CUST_ORDER_ID")
      28 - access("R"."WORKORDER_TYPE"='M' AND "COL"."PART_ID"="R"."WORKORDER_BASE_ID" AND "R"."WORKORDER_LOT_ID"='0' AND
                  "R"."WORKORDER_SPLIT_ID"='0' AND "R"."SUBORD_WO_SUB_ID" IS NULL)
      30 - access("R2"."WORKORDER_TYPE"='M' AND "R"."PART_ID"="R2"."WORKORDER_BASE_ID" AND "R2"."WORKORDER_LOT_ID"='0')
      32 - access("R3"."WORKORDER_TYPE"='M' AND "R2"."PART_ID"="R3"."WORKORDER_BASE_ID" AND "R3"."WORKORDER_LOT_ID"='0')
      33 - filter("P3"."FABRICATED"='Y')
      34 - access("R3"."PART_ID"="P3"."ID")
      35 - filter("P"."FABRICATED"='Y')
      36 - access("R"."PART_ID"="P"."ID")
      37 - filter("P2"."FABRICATED"='Y')
      38 - access("R2"."PART_ID"="P2"."ID")
      40 - access("R4"."WORKORDER_TYPE"='M' AND "R3"."PART_ID"="R4"."WORKORDER_BASE_ID" AND "R4"."WORKORDER_LOT_ID"='0')
      42 - access("R5"."WORKORDER_TYPE"='M' AND "R4"."PART_ID"="R5"."WORKORDER_BASE_ID" AND "R5"."WORKORDER_LOT_ID"='0')
      43 - filter("P4"."FABRICATED"='Y')
      44 - access("R4"."PART_ID"="P4"."ID")
      45 - filter("P5"."FABRICATED"='Y')
      46 - access("R5"."PART_ID"="P5"."ID")
      48 - access("R6"."WORKORDER_TYPE"='M' AND "R5"."PART_ID"="R6"."WORKORDER_BASE_ID" AND "R6"."WORKORDER_LOT_ID"='0')
      49 - filter("P6"."FABRICATED"='Y')
      50 - access("R6"."PART_ID"="P6"."ID")

  Handle: 2152 User Time: 983.26S Sys  Time: 2.74S ElapsedTime: 3209S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

  Handle: 5960 User Time: 532.46S Sys  Time: 0.98S ElapsedTime: 1676S Priority: 8 ThreadState: Idle
   PID:20 SPID:5960 SID:132 SERIAL#:55 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe

Neat, for one of the database instances I am able to see the PID and SPID (in case I want to use SQLPLUS’ ORADEBUG), as well as the SID and SERIAL# (in case I want to enable a 10046 trace).  While I only retrieved the SQL statement being executed and its plan, I could have done a lot of other things also to investigate what is happening in the sessions.  I just need to find a way to log into the database to accomplish the above – note that in the above, I only logged into one database in the script. Having the CPU consumed since the thread started might be helpful, but it would be better to know how much was consumed in a particular time period, for instance the last couple minutes.  With a loop in the script, we can accomplish this task.  The script output continues (something is happening in the 10.2.0.x session, compare the user mode time with the system (kernel) mode time for this time period):

1/8/2010 12:48:31 PM Processes: 73 Threads: 866 C. Switches: 53465 Q. Length: 4
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 10.75S Sys  Time: 25.27S Memory: 275.59MB Page File: 0.63MB
  Handle: 1444 User Time: 11.37S Sys  Time: 26.88S ElapsedTime: 78S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 107.79S Sys  Time: 0.16S Memory: 543.09MB Page File: 0.67MB
  Handle: 3520 User Time: 74.24S Sys  Time: 0.02S ElapsedTime: 76S Priority: 8 ThreadState: In Run Queue
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,   REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID, REQ.R2_PART_ID, REQ.R3_PART_ID,...

    SQL_ID  3p1v051atxt1z, child number 0
    -------------------------------------
    SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,  
    REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID,
    REQ.R2_PART_ID, REQ.R3_PART_ID,   REQ.R4_PART_ID, REQ.R5_PART_ID,
    REQ.R6_PART_ID,   REQ.R1_CALC_QTY, REQ.R2_CALC_QTY, REQ.R3_CALC_QTY,  
    REQ.R4_CALC_QTY, REQ.R5_CALC_QTY, REQ.R6_CALC_QTY FROM   (SELECT    
    TOP_LEVEL_PART_ID,     DECODE(R6_PART_ID,NULL,      
    DECODE(R5_PART_ID,NULL,         DECODE(R4_PART_ID,NULL,          
    DECODE(R3_PART_ID,NULL,             DECODE(R2_PART_ID,NULL,            
           R1_PART_ID,R2_PART_ID),                  R3_PART_ID),           
        R4_PART_ID),              R5_PART_ID),            R6_PART_ID)
    PURC_PART_ID,     NVL(R1_CALC_QTY,0)*NVL(R2_CALC_QTY,1)*NVL(R3_CALC_QTY,
    1)*NVL(R4_CALC_QTY,1)*NVL(R5_CALC_QTY,1)*NVL(R6_CALC_QTY,1) TOTAL_QTY, 
       R1_PART_ID, R1_CALC_QTY, R2_PART_ID, R2_CALC_QTY, R3_PART_ID,
    R3_CALC_QTY,     R4_PART_ID, R4_CALC_QTY, R5_PART_ID, R5_CALC_QTY,
    R6_PART_ID, R6_CALC_QTY   FROM     (SELECT       PL.PART_ID TOP_LEVE

    Plan hash value: 3313542492

    ---------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                |                      |       |       |   101 (100)|          |        |
    |   1 |  VIEW                                           | VM_NWVW_1            |     1 |   225 |   101   (2)| 00:00:02 |   OR11 |
    |   2 |   HASH UNIQUE                                   |                      |     1 |   498 |   101   (2)| 00:00:02 |        |
    |   3 |    NESTED LOOPS OUTER                           |                      |     1 |   498 |   100   (1)| 00:00:02 |        |
    |*  4 |     FILTER                                      |                      |       |       |            |          |        |
    |   5 |      NESTED LOOPS OUTER                         |                      |     1 |   474 |    99   (2)| 00:00:02 |        |
    |   6 |       NESTED LOOPS OUTER                        |                      |     1 |   435 |    96   (2)| 00:00:02 |        |
    |   7 |        NESTED LOOPS OUTER                       |                      |     1 |   411 |    95   (2)| 00:00:02 |        |
    |*  8 |         FILTER                                  |                      |       |       |            |          |        |
    |   9 |          NESTED LOOPS OUTER                     |                      |     1 |   387 |    94   (2)| 00:00:02 |        |
    |* 10 |           FILTER                                |                      |       |       |            |          |        |
    |  11 |            NESTED LOOPS OUTER                   |                      |     1 |   348 |    91   (2)| 00:00:02 |        |
    |  12 |             NESTED LOOPS OUTER                  |                      |     1 |   309 |    88   (2)| 00:00:02 |        |
    |  13 |              NESTED LOOPS OUTER                 |                      |     1 |   285 |    87   (2)| 00:00:02 |        |
    |  14 |               NESTED LOOPS OUTER                |                      |     1 |   261 |    86   (2)| 00:00:02 |        |
    |* 15 |                FILTER                           |                      |       |       |            |          |        |
    |  16 |                 NESTED LOOPS OUTER              |                      |     1 |   237 |    85   (2)| 00:00:02 |        |
    |* 17 |                  FILTER                         |                      |       |       |            |          |        |
    |  18 |                   NESTED LOOPS OUTER            |                      |     1 |   198 |    82   (2)| 00:00:01 |        |
    |  19 |                    NESTED LOOPS                 |                      |     1 |   159 |    79   (2)| 00:00:01 |        |
    |  20 |                     NESTED LOOPS                |                      |     1 |   118 |    76   (2)| 00:00:01 |        |
    |  21 |                      MERGE JOIN CARTESIAN       |                      |     1 |    94 |    71   (2)| 00:00:01 |        |
    |* 22 |                       TABLE ACCESS FULL         | TEMP_PART_PRICE_DATE |     1 |    85 |     2   (0)| 00:00:01 |   OR11 |
    |  23 |                       BUFFER SORT               |                      |  7016 | 63144 |    69   (2)| 00:00:01 |        |
    |* 24 |                        TABLE ACCESS FULL        | CUSTOMER_ORDER       |  7016 | 63144 |    69   (2)| 00:00:01 |   OR11 |
    |* 25 |                      TABLE ACCESS BY INDEX ROWID| CUST_ORDER_LINE      |     1 |    24 |     5   (0)| 00:00:01 |   OR11 |
    |* 26 |                       INDEX RANGE SCAN          | SYS_C0028623         |     9 |       |     1   (0)| 00:00:01 |   OR11 |
    |  27 |                     TABLE ACCESS BY INDEX ROWID | REQUIREMENT          |     1 |    41 |     3   (0)| 00:00:01 |   OR11 |
    |* 28 |                      INDEX RANGE SCAN           | X_REQUIREMENT_5      |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  29 |                    TABLE ACCESS BY INDEX ROWID  | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 30 |                     INDEX RANGE SCAN            | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  31 |                  TABLE ACCESS BY INDEX ROWID    | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 32 |                   INDEX RANGE SCAN              | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 33 |                TABLE ACCESS BY INDEX ROWID      | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 34 |                 INDEX UNIQUE SCAN               | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 35 |               TABLE ACCESS BY INDEX ROWID       | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 36 |                INDEX UNIQUE SCAN                | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 37 |              TABLE ACCESS BY INDEX ROWID        | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 38 |               INDEX UNIQUE SCAN                 | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  39 |             TABLE ACCESS BY INDEX ROWID         | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 40 |              INDEX RANGE SCAN                   | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  41 |           TABLE ACCESS BY INDEX ROWID           | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 42 |            INDEX RANGE SCAN                     | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 43 |         TABLE ACCESS BY INDEX ROWID             | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 44 |          INDEX UNIQUE SCAN                      | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 45 |        TABLE ACCESS BY INDEX ROWID              | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 46 |         INDEX UNIQUE SCAN                       | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  47 |       TABLE ACCESS BY INDEX ROWID               | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 48 |        INDEX RANGE SCAN                         | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 49 |     TABLE ACCESS BY INDEX ROWID                 | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 50 |      INDEX UNIQUE SCAN                          | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    ---------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       4 - filter(("TPPD"."PART_ID"=DECODE(DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID"
                  ,NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'N
                  ONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE
                  (NVL("P5"."FABRICATED",'NONE'),'Y',"R6"."PART_ID",NULL)),NULL,DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NO
                  NE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(N
                  VL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",
                  NULL)),NULL,DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."
                  FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NU
                  LL,DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NON
                  E'),'Y',"R3"."PART_ID",NULL)),NULL,DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,"R"."PART_ID"
                  ,DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL)),DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_
                  ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL))),DECODE(DECODE(DECODE(NVL("P"."FABRICA
                  TED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,
                  DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL))),DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),
                  'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P
                  3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)
                  )),DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FA
                  BRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL
                  ,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P5"."FABRICATED",'NONE'),'Y',"
                  R6"."PART_ID",NULL))) AND "R6"."SUBORD_WO_SUB_ID" IS NULL))
       8 - filter("R5"."SUBORD_WO_SUB_ID" IS NULL)
      10 - filter("R4"."SUBORD_WO_SUB_ID" IS NULL)
      15 - filter("R3"."SUBORD_WO_SUB_ID" IS NULL)
      17 - filter("R2"."SUBORD_WO_SUB_ID" IS NULL)
      22 - filter("TPPD"."INCREASE"<>0)
      24 - filter(("CO"."STATUS"='C' OR "CO"."STATUS"='F' OR "CO"."STATUS"='P' OR "CO"."STATUS"='R' OR "CO"."STATUS"='U'))
      25 - filter("COL"."DESIRED_SHIP_DATE">SYSDATE@!-365)
      26 - access("CO"."ID"="COL"."CUST_ORDER_ID")
      28 - access("R"."WORKORDER_TYPE"='M' AND "COL"."PART_ID"="R"."WORKORDER_BASE_ID" AND "R"."WORKORDER_LOT_ID"='0' AND
                  "R"."WORKORDER_SPLIT_ID"='0' AND "R"."SUBORD_WO_SUB_ID" IS NULL)
      30 - access("R2"."WORKORDER_TYPE"='M' AND "R"."PART_ID"="R2"."WORKORDER_BASE_ID" AND "R2"."WORKORDER_LOT_ID"='0')
      32 - access("R3"."WORKORDER_TYPE"='M' AND "R2"."PART_ID"="R3"."WORKORDER_BASE_ID" AND "R3"."WORKORDER_LOT_ID"='0')
      33 - filter("P3"."FABRICATED"='Y')
      34 - access("R3"."PART_ID"="P3"."ID")
      35 - filter("P"."FABRICATED"='Y')
      36 - access("R"."PART_ID"="P"."ID")
      37 - filter("P2"."FABRICATED"='Y')
      38 - access("R2"."PART_ID"="P2"."ID")
      40 - access("R4"."WORKORDER_TYPE"='M' AND "R3"."PART_ID"="R4"."WORKORDER_BASE_ID" AND "R4"."WORKORDER_LOT_ID"='0')
      42 - access("R5"."WORKORDER_TYPE"='M' AND "R4"."PART_ID"="R5"."WORKORDER_BASE_ID" AND "R5"."WORKORDER_LOT_ID"='0')
      43 - filter("P4"."FABRICATED"='Y')
      44 - access("R4"."PART_ID"="P4"."ID")
      45 - filter("P5"."FABRICATED"='Y')
      46 - access("R5"."PART_ID"="P5"."ID")
      48 - access("R6"."WORKORDER_TYPE"='M' AND "R5"."PART_ID"="R6"."WORKORDER_BASE_ID" AND "R6"."WORKORDER_LOT_ID"='0')
      49 - filter("P6"."FABRICATED"='Y')
      50 - access("R6"."PART_ID"="P6"."ID")

  Handle: 2152 User Time: 37.25S Sys  Time: 0S ElapsedTime: 75S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:49:51 PM Processes: 73 Threads: 869 C. Switches: 43307 Q. Length: 6
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 11.9S Sys  Time: 27.29S Memory: 275.59MB Page File: 0.63MB
  Handle: 1444 User Time: 11.54S Sys  Time: 26.1S ElapsedTime: 76S Priority: 8 ThreadState: Running

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 117.66S Sys  Time: 0.11S Memory: 545.34MB Page File: 0.67MB
  Handle: 3520 User Time: 75.34S Sys  Time: 0.01S ElapsedTime: 76S Priority: 8 ThreadState: In Run Queue
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,   REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID, REQ.R2_PART_ID, REQ.R3_PART_ID,...

    SQL_ID  3p1v051atxt1z, child number 0
    -------------------------------------
    SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,  
    REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID,
    REQ.R2_PART_ID, REQ.R3_PART_ID,   REQ.R4_PART_ID, REQ.R5_PART_ID,
    REQ.R6_PART_ID,   REQ.R1_CALC_QTY, REQ.R2_CALC_QTY, REQ.R3_CALC_QTY,  
    REQ.R4_CALC_QTY, REQ.R5_CALC_QTY, REQ.R6_CALC_QTY FROM   (SELECT    
    TOP_LEVEL_PART_ID,     DECODE(R6_PART_ID,NULL,      
    DECODE(R5_PART_ID,NULL,         DECODE(R4_PART_ID,NULL,          
    DECODE(R3_PART_ID,NULL,             DECODE(R2_PART_ID,NULL,            
           R1_PART_ID,R2_PART_ID),                  R3_PART_ID),           
        R4_PART_ID),              R5_PART_ID),            R6_PART_ID)
    PURC_PART_ID,     NVL(R1_CALC_QTY,0)*NVL(R2_CALC_QTY,1)*NVL(R3_CALC_QTY,
    1)*NVL(R4_CALC_QTY,1)*NVL(R5_CALC_QTY,1)*NVL(R6_CALC_QTY,1) TOTAL_QTY, 
       R1_PART_ID, R1_CALC_QTY, R2_PART_ID, R2_CALC_QTY, R3_PART_ID,
    R3_CALC_QTY,     R4_PART_ID, R4_CALC_QTY, R5_PART_ID, R5_CALC_QTY,
    R6_PART_ID, R6_CALC_QTY   FROM     (SELECT       PL.PART_ID TOP_LEVE

    Plan hash value: 3313542492

    ---------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                |                      |       |       |   101 (100)|          |        |
    |   1 |  VIEW                                           | VM_NWVW_1            |     1 |   225 |   101   (2)| 00:00:02 |   OR11 |
    |   2 |   HASH UNIQUE                                   |                      |     1 |   498 |   101   (2)| 00:00:02 |        |
    |   3 |    NESTED LOOPS OUTER                           |                      |     1 |   498 |   100   (1)| 00:00:02 |        |
    |*  4 |     FILTER                                      |                      |       |       |            |          |        |
    |   5 |      NESTED LOOPS OUTER                         |                      |     1 |   474 |    99   (2)| 00:00:02 |        |
    |   6 |       NESTED LOOPS OUTER                        |                      |     1 |   435 |    96   (2)| 00:00:02 |        |
    |   7 |        NESTED LOOPS OUTER                       |                      |     1 |   411 |    95   (2)| 00:00:02 |        |
    |*  8 |         FILTER                                  |                      |       |       |            |          |        |
    |   9 |          NESTED LOOPS OUTER                     |                      |     1 |   387 |    94   (2)| 00:00:02 |        |
    |* 10 |           FILTER                                |                      |       |       |            |          |        |
    |  11 |            NESTED LOOPS OUTER                   |                      |     1 |   348 |    91   (2)| 00:00:02 |        |
    |  12 |             NESTED LOOPS OUTER                  |                      |     1 |   309 |    88   (2)| 00:00:02 |        |
    |  13 |              NESTED LOOPS OUTER                 |                      |     1 |   285 |    87   (2)| 00:00:02 |        |
    |  14 |               NESTED LOOPS OUTER                |                      |     1 |   261 |    86   (2)| 00:00:02 |        |
    |* 15 |                FILTER                           |                      |       |       |            |          |        |
    |  16 |                 NESTED LOOPS OUTER              |                      |     1 |   237 |    85   (2)| 00:00:02 |        |
    |* 17 |                  FILTER                         |                      |       |       |            |          |        |
    |  18 |                   NESTED LOOPS OUTER            |                      |     1 |   198 |    82   (2)| 00:00:01 |        |
    |  19 |                    NESTED LOOPS                 |                      |     1 |   159 |    79   (2)| 00:00:01 |        |
    |  20 |                     NESTED LOOPS                |                      |     1 |   118 |    76   (2)| 00:00:01 |        |
    |  21 |                      MERGE JOIN CARTESIAN       |                      |     1 |    94 |    71   (2)| 00:00:01 |        |
    |* 22 |                       TABLE ACCESS FULL         | TEMP_PART_PRICE_DATE |     1 |    85 |     2   (0)| 00:00:01 |   OR11 |
    |  23 |                       BUFFER SORT               |                      |  7016 | 63144 |    69   (2)| 00:00:01 |        |
    |* 24 |                        TABLE ACCESS FULL        | CUSTOMER_ORDER       |  7016 | 63144 |    69   (2)| 00:00:01 |   OR11 |
    |* 25 |                      TABLE ACCESS BY INDEX ROWID| CUST_ORDER_LINE      |     1 |    24 |     5   (0)| 00:00:01 |   OR11 |
    |* 26 |                       INDEX RANGE SCAN          | SYS_C0028623         |     9 |       |     1   (0)| 00:00:01 |   OR11 |
    |  27 |                     TABLE ACCESS BY INDEX ROWID | REQUIREMENT          |     1 |    41 |     3   (0)| 00:00:01 |   OR11 |
    |* 28 |                      INDEX RANGE SCAN           | X_REQUIREMENT_5      |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  29 |                    TABLE ACCESS BY INDEX ROWID  | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 30 |                     INDEX RANGE SCAN            | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  31 |                  TABLE ACCESS BY INDEX ROWID    | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 32 |                   INDEX RANGE SCAN              | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 33 |                TABLE ACCESS BY INDEX ROWID      | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 34 |                 INDEX UNIQUE SCAN               | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 35 |               TABLE ACCESS BY INDEX ROWID       | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 36 |                INDEX UNIQUE SCAN                | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 37 |              TABLE ACCESS BY INDEX ROWID        | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 38 |               INDEX UNIQUE SCAN                 | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  39 |             TABLE ACCESS BY INDEX ROWID         | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 40 |              INDEX RANGE SCAN                   | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  41 |           TABLE ACCESS BY INDEX ROWID           | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 42 |            INDEX RANGE SCAN                     | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 43 |         TABLE ACCESS BY INDEX ROWID             | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 44 |          INDEX UNIQUE SCAN                      | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 45 |        TABLE ACCESS BY INDEX ROWID              | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 46 |         INDEX UNIQUE SCAN                       | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  47 |       TABLE ACCESS BY INDEX ROWID               | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 48 |        INDEX RANGE SCAN                         | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 49 |     TABLE ACCESS BY INDEX ROWID                 | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 50 |      INDEX UNIQUE SCAN                          | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    ---------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       4 - filter(("TPPD"."PART_ID"=DECODE(DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID"
                  ,NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'N
                  ONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE
                  (NVL("P5"."FABRICATED",'NONE'),'Y',"R6"."PART_ID",NULL)),NULL,DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NO
                  NE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(N
                  VL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",
                  NULL)),NULL,DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."
                  FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NU
                  LL,DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NON
                  E'),'Y',"R3"."PART_ID",NULL)),NULL,DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,"R"."PART_ID"
                  ,DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL)),DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_
                  ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL))),DECODE(DECODE(DECODE(NVL("P"."FABRICA
                  TED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,
                  DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL))),DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),
                  'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P
                  3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)
                  )),DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FA
                  BRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL
                  ,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P5"."FABRICATED",'NONE'),'Y',"
                  R6"."PART_ID",NULL))) AND "R6"."SUBORD_WO_SUB_ID" IS NULL))
       8 - filter("R5"."SUBORD_WO_SUB_ID" IS NULL)
      10 - filter("R4"."SUBORD_WO_SUB_ID" IS NULL)
      15 - filter("R3"."SUBORD_WO_SUB_ID" IS NULL)
      17 - filter("R2"."SUBORD_WO_SUB_ID" IS NULL)
      22 - filter("TPPD"."INCREASE"<>0)
      24 - filter(("CO"."STATUS"='C' OR "CO"."STATUS"='F' OR "CO"."STATUS"='P' OR "CO"."STATUS"='R' OR "CO"."STATUS"='U'))
      25 - filter("COL"."DESIRED_SHIP_DATE">SYSDATE@!-365)
      26 - access("CO"."ID"="COL"."CUST_ORDER_ID")
      28 - access("R"."WORKORDER_TYPE"='M' AND "COL"."PART_ID"="R"."WORKORDER_BASE_ID" AND "R"."WORKORDER_LOT_ID"='0' AND
                  "R"."WORKORDER_SPLIT_ID"='0' AND "R"."SUBORD_WO_SUB_ID" IS NULL)
      30 - access("R2"."WORKORDER_TYPE"='M' AND "R"."PART_ID"="R2"."WORKORDER_BASE_ID" AND "R2"."WORKORDER_LOT_ID"='0')
      32 - access("R3"."WORKORDER_TYPE"='M' AND "R2"."PART_ID"="R3"."WORKORDER_BASE_ID" AND "R3"."WORKORDER_LOT_ID"='0')
      33 - filter("P3"."FABRICATED"='Y')
      34 - access("R3"."PART_ID"="P3"."ID")
      35 - filter("P"."FABRICATED"='Y')
      36 - access("R"."PART_ID"="P"."ID")
      37 - filter("P2"."FABRICATED"='Y')
      38 - access("R2"."PART_ID"="P2"."ID")
      40 - access("R4"."WORKORDER_TYPE"='M' AND "R3"."PART_ID"="R4"."WORKORDER_BASE_ID" AND "R4"."WORKORDER_LOT_ID"='0')
      42 - access("R5"."WORKORDER_TYPE"='M' AND "R4"."PART_ID"="R5"."WORKORDER_BASE_ID" AND "R5"."WORKORDER_LOT_ID"='0')
      43 - filter("P4"."FABRICATED"='Y')
      44 - access("R4"."PART_ID"="P4"."ID")
      45 - filter("P5"."FABRICATED"='Y')
      46 - access("R5"."PART_ID"="P5"."ID")
      48 - access("R6"."WORKORDER_TYPE"='M' AND "R5"."PART_ID"="R6"."WORKORDER_BASE_ID" AND "R6"."WORKORDER_LOT_ID"='0')
      49 - filter("P6"."FABRICATED"='Y')
      50 - access("R6"."PART_ID"="P6"."ID")

  Handle: 2152 User Time: 37.44S Sys  Time: 0S ElapsedTime: 76S Priority: 8 ThreadState: Running
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:51:07 PM Processes: 73 Threads: 865 C. Switches: 41992 Q. Length: 7
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 10.81S Sys  Time: 26.83S Memory: 276.09MB Page File: 0.63MB
  Handle: 1444 User Time: 10.02S Sys  Time: 25.51S ElapsedTime: 71S Priority: 8 ThreadState: Running

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 112.71S Sys  Time: 0.09S Memory: 545.63MB Page File: 0.67MB
  Handle: 3520 User Time: 72.57S Sys  Time: 0S ElapsedTime: 74S Priority: 10 ThreadState: Idle
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,   REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID, REQ.R2_PART_ID, REQ.R3_PART_ID,...

    SQL_ID  3p1v051atxt1z, child number 0
    -------------------------------------
    SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,  
    REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID,
    REQ.R2_PART_ID, REQ.R3_PART_ID,   REQ.R4_PART_ID, REQ.R5_PART_ID,
    REQ.R6_PART_ID,   REQ.R1_CALC_QTY, REQ.R2_CALC_QTY, REQ.R3_CALC_QTY,  
    REQ.R4_CALC_QTY, REQ.R5_CALC_QTY, REQ.R6_CALC_QTY FROM   (SELECT    
    TOP_LEVEL_PART_ID,     DECODE(R6_PART_ID,NULL,      
    DECODE(R5_PART_ID,NULL,         DECODE(R4_PART_ID,NULL,          
    DECODE(R3_PART_ID,NULL,             DECODE(R2_PART_ID,NULL,            
           R1_PART_ID,R2_PART_ID),                  R3_PART_ID),           
        R4_PART_ID),              R5_PART_ID),            R6_PART_ID)
    PURC_PART_ID,     NVL(R1_CALC_QTY,0)*NVL(R2_CALC_QTY,1)*NVL(R3_CALC_QTY,
    1)*NVL(R4_CALC_QTY,1)*NVL(R5_CALC_QTY,1)*NVL(R6_CALC_QTY,1) TOTAL_QTY, 
       R1_PART_ID, R1_CALC_QTY, R2_PART_ID, R2_CALC_QTY, R3_PART_ID,
    R3_CALC_QTY,     R4_PART_ID, R4_CALC_QTY, R5_PART_ID, R5_CALC_QTY,
    R6_PART_ID, R6_CALC_QTY   FROM     (SELECT       PL.PART_ID TOP_LEVE

    Plan hash value: 3313542492

    ---------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                |                      |       |       |   101 (100)|          |        |
    |   1 |  VIEW                                           | VM_NWVW_1            |     1 |   225 |   101   (2)| 00:00:02 |   OR11 |
    |   2 |   HASH UNIQUE                                   |                      |     1 |   498 |   101   (2)| 00:00:02 |        |
    |   3 |    NESTED LOOPS OUTER                           |                      |     1 |   498 |   100   (1)| 00:00:02 |        |
    |*  4 |     FILTER                                      |                      |       |       |            |          |        |
    |   5 |      NESTED LOOPS OUTER                         |                      |     1 |   474 |    99   (2)| 00:00:02 |        |
    |   6 |       NESTED LOOPS OUTER                        |                      |     1 |   435 |    96   (2)| 00:00:02 |        |
    |   7 |        NESTED LOOPS OUTER                       |                      |     1 |   411 |    95   (2)| 00:00:02 |        |
    |*  8 |         FILTER                                  |                      |       |       |            |          |        |
    |   9 |          NESTED LOOPS OUTER                     |                      |     1 |   387 |    94   (2)| 00:00:02 |        |
    |* 10 |           FILTER                                |                      |       |       |            |          |        |
    |  11 |            NESTED LOOPS OUTER                   |                      |     1 |   348 |    91   (2)| 00:00:02 |        |
    |  12 |             NESTED LOOPS OUTER                  |                      |     1 |   309 |    88   (2)| 00:00:02 |        |
    |  13 |              NESTED LOOPS OUTER                 |                      |     1 |   285 |    87   (2)| 00:00:02 |        |
    |  14 |               NESTED LOOPS OUTER                |                      |     1 |   261 |    86   (2)| 00:00:02 |        |
    |* 15 |                FILTER                           |                      |       |       |            |          |        |
    |  16 |                 NESTED LOOPS OUTER              |                      |     1 |   237 |    85   (2)| 00:00:02 |        |
    |* 17 |                  FILTER                         |                      |       |       |            |          |        |
    |  18 |                   NESTED LOOPS OUTER            |                      |     1 |   198 |    82   (2)| 00:00:01 |        |
    |  19 |                    NESTED LOOPS                 |                      |     1 |   159 |    79   (2)| 00:00:01 |        |
    |  20 |                     NESTED LOOPS                |                      |     1 |   118 |    76   (2)| 00:00:01 |        |
    |  21 |                      MERGE JOIN CARTESIAN       |                      |     1 |    94 |    71   (2)| 00:00:01 |        |
    |* 22 |                       TABLE ACCESS FULL         | TEMP_PART_PRICE_DATE |     1 |    85 |     2   (0)| 00:00:01 |   OR11 |
    |  23 |                       BUFFER SORT               |                      |  7016 | 63144 |    69   (2)| 00:00:01 |        |
    |* 24 |                        TABLE ACCESS FULL        | CUSTOMER_ORDER       |  7016 | 63144 |    69   (2)| 00:00:01 |   OR11 |
    |* 25 |                      TABLE ACCESS BY INDEX ROWID| CUST_ORDER_LINE      |     1 |    24 |     5   (0)| 00:00:01 |   OR11 |
    |* 26 |                       INDEX RANGE SCAN          | SYS_C0028623         |     9 |       |     1   (0)| 00:00:01 |   OR11 |
    |  27 |                     TABLE ACCESS BY INDEX ROWID | REQUIREMENT          |     1 |    41 |     3   (0)| 00:00:01 |   OR11 |
    |* 28 |                      INDEX RANGE SCAN           | X_REQUIREMENT_5      |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  29 |                    TABLE ACCESS BY INDEX ROWID  | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 30 |                     INDEX RANGE SCAN            | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  31 |                  TABLE ACCESS BY INDEX ROWID    | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 32 |                   INDEX RANGE SCAN              | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 33 |                TABLE ACCESS BY INDEX ROWID      | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 34 |                 INDEX UNIQUE SCAN               | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 35 |               TABLE ACCESS BY INDEX ROWID       | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 36 |                INDEX UNIQUE SCAN                | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 37 |              TABLE ACCESS BY INDEX ROWID        | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 38 |               INDEX UNIQUE SCAN                 | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  39 |             TABLE ACCESS BY INDEX ROWID         | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 40 |              INDEX RANGE SCAN                   | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  41 |           TABLE ACCESS BY INDEX ROWID           | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 42 |            INDEX RANGE SCAN                     | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 43 |         TABLE ACCESS BY INDEX ROWID             | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 44 |          INDEX UNIQUE SCAN                      | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 45 |        TABLE ACCESS BY INDEX ROWID              | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 46 |         INDEX UNIQUE SCAN                       | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  47 |       TABLE ACCESS BY INDEX ROWID               | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 48 |        INDEX RANGE SCAN                         | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 49 |     TABLE ACCESS BY INDEX ROWID                 | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 50 |      INDEX UNIQUE SCAN                          | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    ---------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       4 - filter(("TPPD"."PART_ID"=DECODE(DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID"
                  ,NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'N
                  ONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE
                  (NVL("P5"."FABRICATED",'NONE'),'Y',"R6"."PART_ID",NULL)),NULL,DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NO
                  NE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(N
                  VL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",
                  NULL)),NULL,DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."
                  FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NU
                  LL,DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NON
                  E'),'Y',"R3"."PART_ID",NULL)),NULL,DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,"R"."PART_ID"
                  ,DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL)),DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_
                  ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL))),DECODE(DECODE(DECODE(NVL("P"."FABRICA
                  TED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,
                  DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL))),DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),
                  'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P
                  3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)
                  )),DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FA
                  BRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL
                  ,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P5"."FABRICATED",'NONE'),'Y',"
                  R6"."PART_ID",NULL))) AND "R6"."SUBORD_WO_SUB_ID" IS NULL))
       8 - filter("R5"."SUBORD_WO_SUB_ID" IS NULL)
      10 - filter("R4"."SUBORD_WO_SUB_ID" IS NULL)
      15 - filter("R3"."SUBORD_WO_SUB_ID" IS NULL)
      17 - filter("R2"."SUBORD_WO_SUB_ID" IS NULL)
      22 - filter("TPPD"."INCREASE"<>0)
      24 - filter(("CO"."STATUS"='C' OR "CO"."STATUS"='F' OR "CO"."STATUS"='P' OR "CO"."STATUS"='R' OR "CO"."STATUS"='U'))
      25 - filter("COL"."DESIRED_SHIP_DATE">SYSDATE@!-365)
      26 - access("CO"."ID"="COL"."CUST_ORDER_ID")
      28 - access("R"."WORKORDER_TYPE"='M' AND "COL"."PART_ID"="R"."WORKORDER_BASE_ID" AND "R"."WORKORDER_LOT_ID"='0' AND
                  "R"."WORKORDER_SPLIT_ID"='0' AND "R"."SUBORD_WO_SUB_ID" IS NULL)
      30 - access("R2"."WORKORDER_TYPE"='M' AND "R"."PART_ID"="R2"."WORKORDER_BASE_ID" AND "R2"."WORKORDER_LOT_ID"='0')
      32 - access("R3"."WORKORDER_TYPE"='M' AND "R2"."PART_ID"="R3"."WORKORDER_BASE_ID" AND "R3"."WORKORDER_LOT_ID"='0')
      33 - filter("P3"."FABRICATED"='Y')
      34 - access("R3"."PART_ID"="P3"."ID")
      35 - filter("P"."FABRICATED"='Y')
      36 - access("R"."PART_ID"="P"."ID")
      37 - filter("P2"."FABRICATED"='Y')
      38 - access("R2"."PART_ID"="P2"."ID")
      40 - access("R4"."WORKORDER_TYPE"='M' AND "R3"."PART_ID"="R4"."WORKORDER_BASE_ID" AND "R4"."WORKORDER_LOT_ID"='0')
      42 - access("R5"."WORKORDER_TYPE"='M' AND "R4"."PART_ID"="R5"."WORKORDER_BASE_ID" AND "R5"."WORKORDER_LOT_ID"='0')
      43 - filter("P4"."FABRICATED"='Y')
      44 - access("R4"."PART_ID"="P4"."ID")
      45 - filter("P5"."FABRICATED"='Y')
      46 - access("R5"."PART_ID"="P5"."ID")
      48 - access("R6"."WORKORDER_TYPE"='M' AND "R5"."PART_ID"="R6"."WORKORDER_BASE_ID" AND "R6"."WORKORDER_LOT_ID"='0')
      49 - filter("P6"."FABRICATED"='Y')
      50 - access("R6"."PART_ID"="P6"."ID")

  Handle: 2152 User Time: 37.07S Sys  Time: 0.05S ElapsedTime: 75S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:52:17 PM Processes: 73 Threads: 861 C. Switches: 1548363 Q. Length: 5
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 11.42S Sys  Time: 27.27S Memory: 232MB Page File: 0.63MB
  Handle: 1444 User Time: 11.85S Sys  Time: 27.97S ElapsedTime: 69S Priority: 8 ThreadState: Running

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 55.19S Sys  Time: 0.78S Memory: 519.81MB Page File: 0.67MB
  Handle: 2152 User Time: 48.79S Sys  Time: 0.66S ElapsedTime: 67S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:53:24 PM Processes: 73 Threads: 859 C. Switches: 66962 Q. Length: 2
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 21.67S Sys  Time: 43.91S Memory: 175.28MB Page File: 0.63MB
  Handle: 1444 User Time: 22.11S Sys  Time: 45.18S ElapsedTime: 70S Priority: 8 ThreadState: Running

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 65.88S Sys  Time: 0.08S Memory: 441.9MB Page File: 0.67MB
  Handle: 2152 User Time: 66.77S Sys  Time: 0.06S ElapsedTime: 68S Priority: 8 ThreadState: Running
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:54:35 PM Processes: 73 Threads: 860 C. Switches: 47967 Q. Length: 5
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 15.63S Sys  Time: 48.93S Memory: 141.9MB Page File: 0.63MB
  Handle: 1444 User Time: 14.4S Sys  Time: 46.38S ElapsedTime: 68S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 75.85S Sys  Time: 0.09S Memory: 438.77MB Page File: 0.67MB
  Handle: 2152 User Time: 64.79S Sys  Time: 0S ElapsedTime: 71S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:55:42 PM Processes: 73 Threads: 864 C. Switches: 35978 Q. Length: 6
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 8.83S Sys  Time: 23.93S Memory: 141.25MB Page File: 0.63MB
  Handle: 1444 User Time: 9.08S Sys  Time: 24.16S ElapsedTime: 68S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 98.7S Sys  Time: 0.22S Memory: 445.1MB Page File: 0.67MB
  Handle: 2152 User Time: 32.07S Sys  Time: 0S ElapsedTime: 65S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

  Handle: 5960 User Time: 65.07S Sys  Time: 0S ElapsedTime: 65S Priority: 8 ThreadState: Running
   PID:20 SPID:5960 SID:132 SERIAL#:55 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;

    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

1/8/2010 12:56:47 PM Processes: 73 Threads: 856 C. Switches: 59846 Q. Length: 5
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 9.3S Sys  Time: 22.7S Memory: 141.1MB Page File: 0.63MB
  Handle: 1444 User Time: 9.25S Sys  Time: 22.85S ElapsedTime: 65S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 96.47S Sys  Time: 0.03S Memory: 440.21MB Page File: 0.67MB
  Handle: 2152 User Time: 32.72S Sys  Time: 0S ElapsedTime: 67S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

  Handle: 5960 User Time: 54.01S Sys  Time: 0S ElapsedTime: 67S Priority: 8 ThreadState: In Run Queue
   PID:20 SPID:5960 SID:132 SERIAL#:55 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;

    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

1/8/2010 12:57:56 PM Processes: 73 Threads: 861 C. Switches: 35972 Q. Length: 6
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 9.33S Sys  Time: 25.19S Memory: 141.11MB Page File: 0.63MB
  Handle: 1444 User Time: 9.34S Sys  Time: 25.5S ElapsedTime: 70S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 103.9S Sys  Time: 0.13S Memory: 442.42MB Page File: 0.67MB
  Handle: 3520 User Time: 34.13S Sys  Time: 0.02S ElapsedTime: 69S Priority: 8 ThreadState: Running
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;

    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

  Handle: 2152 User Time: 34.04S Sys  Time: 0S ElapsedTime: 69S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

  Handle: 5960 User Time: 34.18S Sys  Time: 0S ElapsedTime: 68S Priority: 8 ThreadState: In Run Queue
   PID:20 SPID:5960 SID:132 SERIAL#:55 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;
    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Notice in the above that toward the end two sessions were doing nothing but burning CPU in a PL/SQL loop - the script caught the person trying to soak the computer's dual core CPU.  The CPU queue length jumped significantly as a result of the activity of the two sessions.

Well, the above is neat, but what about the script to create the output?  Sorry, I ran out of space in this blog post - check back in a little while... (Forward to the Follow-Up Article)





Database Inpector Gadget

19 12 2009

December 19, 2009

This example is adapted from an example I created for a presentation a  couple months ago.  While the example as displayed is specific to displaying ERP data, the example may be easily adapted for use in monitoring Oracle database performance.

Vista and Windows 7 offer a set of built in gadgets that perform a variety of tasks, such as displaying calendars, clocks, resource meters, stock quotes, and so on.  It might be nice to show a quick overview of various statistics in an ERP system (or in the database itself) so that a determination may be made whether or not a potential problem exists.  Such a gadget would need to be able to automatically update its statistics.  This example is complicated as it combines a large number of technologies each with their own unique syntax which looks vaguely like English.  This example uses VBScript, Oracle database access, cascading style sheets, XML, HTML, DHTML, and coding that is specific to Vista/Windows 7 gadgets.

When the gadget is opened, it determines the statistics that should be displayed for the user, and the order in which the statistics should be displayed.  The selected statistics are then retrieved from the database and displayed on the gadget surface in a DIV tag.  Every ten minutes the gadget reconnects to the database and displays updated statistics.  As the mouse pointer is moved over the gadget, the gadget’s background lights up slightly, and as the mouse pointer passes over the statistics, those also light up (more specifically, the background image for the statistic is changed).  When one of the statistics is clicked, a fly-out child window appears on the screen that shows the detailed statistics behind the one line numeric statistic.

The gadget code files should be placed into the C:\Program Files\Windows Sidebar\Gadgets\KMInspector.gadget folder on the computer (note that KMInspector may be specified with a different name).  The gadget.xml file in that folder describes the gadget to Vista/Windows 7 and tells Windows where to find the main gadget HTML file, the name of the gadget, and the name of the various icons – this file MUST be saved in a UTF-8 characterset (this is an option when saving with Notepad).  The KMInspector.html file is the main gadget page, and the various pages with names beginning with FlyOut are the pages containing the detail information.  The security requirement that all Windows gadgets must be signed needs to be disabled.

Below are a couple of screen shots of what we are trying to achieve (a standard Windows sidebar calendar gadget appears above, with the custom developed gadget below):
  

 

The top left screen shot shows the custom gadget directly below a built-in Windows calendar gadget.  The top right screen shot shows what happens when the mouse pointer passes over the gadget (note that the background becomes lighter in color, and the button under the mouse pointer changes color).  The bottom picture shows what happens when one of the buttons in the gedget is clicked.

gadget.xml file (save as in the UTF-8 character set):

<?xml version="1.0" encoding="utf-8" ?>
<gadget>
  <name>KM Inspector</name>
  <version>1.0.0.0</version>
  <icons>
    <icon height="48" width="36" src="InspectorIcon.png" />
  </icons>
  <hosts>
    <host name="sidebar">
      <base type="HTML" apiVersion="1.0.0" src="KMInspector.html" />
      <permissions>Full</permissions>
      <platform minPlatformVersion="1.0" />
      <defaultImage src="Inspector.png" />
    </host>
  </hosts>
</gadget>

Inspector.png is the picture that is displayed when the gadget is dragged from the list of available Windows gadgets to the sidebar.  InspectorIcon.png is the picture of the gadget that is displayed in the list of available Windows gadgets.  KMInspector.html is the web page that contains the code for the gadget.

Inspector.png:

InspectorIcon.png:

KMInspector.html file:

<html>
<head>
    <title>Visual Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 140px;
            height: 200px;
        }
        #gadgetContent
        {
            margin-top: 0px;
            width: 140px;
            vertical-align: middle;
            text-align: center;
            overflow: hidden;
        }
        .Item {
            background-position: center center;
            padding: 0px;
            font-family: Arial;
            font-size: 8pt;
            color: #FFFFFF;
            border-style: none;
            clip: width: 130px;
            height: 14px;
            position: absolute;
            left: 10px;
            width: 128px;
            background-image: url('InspectorButtonUp.png');
            background-repeat: no-repeat;
         }
        </style>
</head>
<script language="VBScript">
Option Explicit
Dim intMinuteCount       'Number of minutes that have elapsed since the last refresh
Dim intRefreshMinutes    'Frequency of the data refresh, in minutes
Dim strInpector(10)      'Keeps track of which data is displayed in each Inspector position
Dim userEntry
Sub Window_Onload
    Dim lngTimerID
    'Specify the Inspector items to be output, in the order that they should appear
    'Currently Available options
    '  "Negative On Hand"
    '  "Past Due PO Line Count"
    '  "In Use Shop Resources"
    '  "Employees Clocked In"
    '  "Employees Clocked Into Indirect"
    '  "Non Invoiced Shipper"

    strInpector(1) = "Negative On Hand"
    strInpector(2) = "Past Due PO Line Count"
    strInpector(3) = "In Use Shop Resources"
    strInpector(4) = "Employees Clocked In"
    strInpector(5) = "Employees Clocked Into Indirect"
    strInpector(6) = "Non Invoiced Shipper"
    strInpector(7) = ""
    strInpector(8) = ""
    strInpector(9) = ""
    strInpector(10) = ""

    'Hide those Inspector items which are not specified (set = "")
    If strInpector(1) = "" Then
        divInspector1.style.visibility = "hidden"
    End If
    If strInpector(2) = "" Then
        divInspector2.style.visibility = "hidden"
    End If
    If strInpector(3) = "" Then
        divInspector3.style.visibility = "hidden"
    End If
    If strInpector(4) = "" Then
        divInspector4.style.visibility = "hidden"
    End If
    If strInpector(5) = "" Then
        divInspector5.style.visibility = "hidden"
    End If
    If strInpector(6) = "" Then
        divInspector6.style.visibility = "hidden"
    End If
    If strInpector(7) = "" Then
        divInspector7.style.visibility = "hidden"
    End If
    If strInpector(8) = "" Then
        divInspector8.style.visibility = "hidden"
    End If
    If strInpector(9) = "" Then
        divInspector9.style.visibility = "hidden"
    End If
    If strInpector(10) = "" Then
        divInspector10.style.visibility = "hidden"
    End If

    'System.Gadget.onSettingsClosing = "SaveSettings"
    'System.Gadget.onSettingsClosed = "SaveSettingsClosed"
    System.Gadget.settingsUI = "settings.html"

    'Set the refresh frequency in minutes
    intRefreshMinutes = 10

    'Force a refresh on the first execution
    intMinuteCount = intRefreshMinutes

    'Refresh the Inspector items
    RefreshInspector

    'Set a times that fires every 60 seconds that determines if it is time to refresh the data
    lngTimerID = window.SetInterval("RefreshInspector", 60000)
End Sub

Sub RefreshInspector
    Dim i
    Dim lngCount
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strOut

    On Error Resume Next

    'See if the specified number of minutes have elapsed since the last refresh, if not, exit the subroutine
    intMinuteCount = intMinuteCount + 1
    If intMinuteCount < intRefreshMinutes Then
        Exit Sub
    End If

    intMinuteCount = 0

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    'Note that Switch is a function that implments a simple light-weight reversible encryption so that the password does not appear in clear text
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        For i = 1 to 10
            strOut = ""

            Select Case strInpector(i)
                Case "Negative On Hand"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(*) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  PART" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  QTY_ON_HAND<0"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "Negative On Hand: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "Past Due PO Line Count"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(*) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  PURCHASE_ORDER PO," & VBCrLf
                    strSQL = strSQL & "  PURC_ORDER_LINE POL," & VBCrLf
                    strSQL = strSQL & "  PURC_LINE_DEL PLD" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  PO.STATUS IN ('F','R','U')" & VBCrLf
                    strSQL = strSQL & "  AND PO.ID=POL.PURC_ORDER_ID" & VBCrLf
                    strSQL = strSQL & "  AND POL.LINE_STATUS='A'" & VBCrLf
                    strSQL = strSQL & "  AND POL.SERVICE_ID IS NULL" & VBCrLf
                    strSQL = strSQL & "  AND POL.ORDER_QTY>POL.TOTAL_RECEIVED_QTY" & VBCrLf
                    strSQL = strSQL & "  AND POL.PURC_ORDER_ID=PLD.PURC_ORDER_ID(+)" & VBCrLf
                    strSQL = strSQL & "  AND POL.LINE_NO=PLD.PURC_ORDER_LINE_NO(+)" & VBCrLf
                    strSQL = strSQL & "  AND DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,0),NVL(PLD.ORDER_QTY,0)) > " & VBCrLf
                    strSQL = strSQL & "     DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,0),NVL(PLD.RECEIVED_QTY,0))" & VBCrLf
                    strSQL = strSQL & "  AND COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE) < SYSDATE"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "PO Past Due: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "In Use Shop Resources"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(DISTINCT RESOURCE_ID) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  LABOR_TICKET" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
                    strSQL = strSQL & "  AND HOURS_WORKED IS NULL" & VBCrLf
                    strSQL = strSQL & "  AND RESOURCE_ID IS NOT NULL" & VBCrLf
                    strSQL = strSQL & "ORDER BY" & VBCrLf
                    strSQL = strSQL & "  RESOURCE_ID"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "Resources in Use: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "Employees Clocked In"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(DISTINCT EMPLOYEE_ID) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  LABOR_TICKET" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
                    strSQL = strSQL & "  AND HOURS_WORKED IS NULL"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "Emp Clocked In: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "Employees Clocked Into Indirect"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(DISTINCT EMPLOYEE_ID) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  LABOR_TICKET" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
                    strSQL = strSQL & "  AND HOURS_WORKED IS NULL" & VBCrLf
                    strSQL = strSQL & "  AND RESOURCE_ID IS NULL"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "Emp on Indirect: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "Non Invoiced Shipper"
                    STRSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  S.PACKLIST_ID" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  SHIPPER S," & VBCrLf
                    strSQL = strSQL & "  SHIPPER_LINE SL" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  S.INVOICE_ID IS NULL" & VBCrLf
                    strSQL = strSQL & "  AND S.STATUS='A'" & VBCrLf
                    strSQL = strSQL & "  AND S.PACKLIST_ID=SL.PACKLIST_ID" & VBCrLf
                    strSQL = strSQL & "  AND SL.SHIPPED_QTY>0" & VBCrLf
                    strSQL = strSQL & "GROUP BY" & VBCrLf
                    strSQL = strSQL & "  S.PACKLIST_ID"
                    snpData.Open strSQL, dbMyConnection

                    lngCount = 0
                    If snpData.State = 1 Then
                        Do While Not(snpData.EOF)
                            lngCount = lngCount +1

                            snpData.MoveNext
                        Loop
                        strOut = "PLs to Invoice: " & cStr(lngCount)
                        snpData.Close
                    End If
            End Select

            'Output the selections in the correct divInspector box
            Select Case i
                Case 1
                    divInspector1.InnerText = strOut
                Case 2
                    divInspector2.InnerText = strOut
                Case 3
                    divInspector3.InnerText = strOut
                Case 4
                    divInspector4.InnerText = strOut
                Case 5
                    divInspector5.InnerText = strOut
                Case 6
                    divInspector6.InnerText = strOut
                case 7
                    divInspector7.InnerText = strOut
                Case 8
                    divInspector8.InnerText = strOut
                Case 9
                    divInspector9.InnerText = strOut
                case 10
                    divInspector10.InnerText = strOut
            End Select
        Next

        divLastUpdate.InnerText = "Last Update: " & Time

        dbMyConnection.Close
    Else
        divLastUpdate.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

'Function SaveSettings(Event)
'    If Event.closeAction = event.Action.commit Then
'        System.Gadget.Settings.write("settingsSelectionIndex", selUserEntry.selectedIndex)
'    End If
'    'Allow the Settings dialog to close.
'    event.cancel = false
'End Function

'Function SaveSettingsClosed(event)
'    'User hits OK on the settings page.
'    If event.closeAction = event.Action.commit Then
'        userEntry = System.Gadget.Settings.readString("settingsUserEntry")
'        SetContentText userEntry
'    Else
'        If event.closeAction = event.Action.cancel Then
'            'User hits Cancel on the settings page.
'            SetContentText "Cancelled"
'        End If
'    End If
'End Function
Sub ShowBackgroundHighlight
    imgBackground.brightness = 0.0
End Sub

Sub ShowFlyOut(intInspector)
    Dim strPage

    On Error Resume Next

    'Determine which of the flyout web pages to display based on the Inspector loaded into the position
    Select Case strInpector(intInspector)
        Case "Negative On Hand"
            strPage = "FlyOutNOH.html"
        Case "Past Due PO Line Count"
            strPage = "FlyOutPOPD.html"
        Case "In Use Shop Resources"
            strPage = "FlyOutRIU.html"
        Case "Employees Clocked In"
            strPage = "FlyOutECI.html"
        Case "Employees Clocked Into Indirect"
            strPage = "FlyOutEOI.html"
        Case "Non Invoiced Shipper"
            strPage = "FlyOutPLI.html"
    End Select
    If strPage <> "" Then
        System.Gadget.Flyout.file = cStr(strPage)
        System.Gadget.Flyout.Show = true
    End If
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background" onmouseover="imgBackground.brightness = 0.5" onmouseout="imgBackground.brightness = 0.0">
    <g:background id="imgBackground" src="url(Inspectorbackground.png)" brightness="0.0" style="padding: 0px"</g:background>
    <div id="divInspector1" style="top: 10px;"
          onmouseover="divInspector1.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector1.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(1)">Inspector 1</div>
    <div id="divInspector2" style="top: 25px;"
          onmouseover="divInspector2.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector2.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(2)">Inspector 2</div>
    <div id="divInspector3" style="top: 40px;"
          onmouseover="divInspector3.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector3.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(3)">Inspector 3</div>
    <div id="divInspector4" style="top: 55px;"
          onmouseover="divInspector4.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector4.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(4)">Inspector 4</div>
    <div id="divInspector5" style="top: 70px;"
          onmouseover="divInspector5.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector5.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(5)">Inspector 5</div>
    <div id="divInspector6" style="top: 85px;"
          onmouseover="divInspector6.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector6.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(6)">Inspector 6</div>
    <div id="divInspector7" style="top: 100px;"
          onmouseover="divInspector7.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector7.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(7)">Inspector 7</div>
    <div id="divInspector8" style="top: 115px;"
          onmouseover="divInspector8.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector8.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(8)">Inspector 8</div>
    <div id="divInspector9" style="top: 130px;"
          onmouseover="divInspector9.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector9.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(9)">Inspector 9</div>
    <div id="divInspector10" style="top: 145px;"
          onmouseover="divInspector10.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector10.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(10)">Inspector 10</div>

    <div id="divLastUpdate" style="background-position: center center; padding: 0px; font-family: Perpetua; font-size: 10px; font-weight: bold; position: absolute; top: 169px; left: 12px; color: #000000;">Time</div>  
    <div id="divTitle"
        style="background-position: left center; border-style: none; border-width: 0px; padding: 0px; margin: 0px; position: absolute; top: 178px; left: 3px; height: 25px; width: 135px; background-image: url(Inspector.png); background-repeat: no-repeat;">
        </div>
    <div id="div1"
        style="background-position: left center; border-style: none; border-width: 0px; padding: 0px; margin: 0px; position: absolute; top: 200px; left: 3px; height: 25px; width: 135px; background-image: url(Inspector.png); background-repeat: no-repeat; visibility: hidden;">
        </div>
</body>
</html>

 FlyOutECI.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 655px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  LT.EMPLOYEE_ID," & VBCrLf
        strSQL = strSQL & "  E.LAST_NAME || ', ' || E.FIRST_NAME AS EMPLOYEE_NAME," & VBCrLf
        strSQL = strSQL & "  LT.RESOURCE_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_BASE_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_LOT_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_SPLIT_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_SUB_ID," & VBCrLf
        strSQL = strSQL & "  LT.OPERATION_SEQ_NO," & VBCrLf
        strSQL = strSQL & "  WO.PART_ID," & VBCrLf
        strSQL = strSQL & "  LT.INDIRECT_ID," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN) AS CLOCK_IN" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  LABOR_TICKET LT," & VBCrLf
        strSQL = strSQL & "  EMPLOYEE E," & VBCrLf
        strSQL = strSQL & "  WORK_ORDER WO" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  LT.TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
        strSQL = strSQL & "  AND LT.HOURS_WORKED IS NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.EMPLOYEE_ID=E.ID" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_TYPE=WO.TYPE(+)" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_BASE_ID=WO.BASE_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_LOT_ID=WO.LOT_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_SPLIT_ID=WO.SPLIT_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND WO.SUB_ID(+)='0'" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  E.LAST_NAME || ', ' || E.FIRST_NAME," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN)"

        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Employees Clocked In</b>" & vbCrLf
            strHTML = strHTML & "<table width=640 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Emp ID</b></td>"
            strHTML = strHTML & "<td><b>Name</b></td>"
            strHTML = strHTML & "<td><b>Resource ID</b></td>"
            strHTML = strHTML & "<td><b>Work Order Op</b></td>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Clock In</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                strHTML = strHTML & "<td>" & cStr(snpData("employee_id")) & "</td>"
                If Not IsNull(snpData("employee_name")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("employee_name")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("resource_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("resource_id")) & "</td>"
                    If cStr(snpData("workorder_sub_id")) = "0" Then
                        strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>"
                    Else
                        strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "-" & cStr(snpData("workorder_sub_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>"
                    End If
                    If Not IsNull(snpData("part_id")) Then
                        strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>"
                    Else
                        strHTML = strHTML & "<td>&nbsp;</td>"
                    End If
                Else
                    strHTML = strHTML & "<td>Indirect: " & cStr(snpData("indirect_id")) & "</td>"
                    strHTML = strHTML & "<td>&nbsp;</td>"
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("clock_in")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("clock_in")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 645px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

 —

FlyOutEOI.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 505px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  LT.EMPLOYEE_ID," & VBCrLf
        strSQL = strSQL & "  E.LAST_NAME || ', ' || E.FIRST_NAME AS EMPLOYEE_NAME," & VBCrLf
        strSQL = strSQL & "  LT.INDIRECT_ID," & VBCrLf
        strSQL = strSQL & "  I.DESCRIPTION," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN) AS CLOCK_IN" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  LABOR_TICKET LT," & VBCrLf
        strSQL = strSQL & "  EMPLOYEE E," & VBCrLf
        strSQL = strSQL & "  INDIRECT I" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  LT.TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
        strSQL = strSQL & "  AND LT.HOURS_WORKED IS NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.EMPLOYEE_ID=E.ID" & VBCrLf
        strSQL = strSQL & "  AND LT.INDIRECT_ID IS NOT NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.INDIRECT_ID=I.ID" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  E.LAST_NAME || ', ' || E.FIRST_NAME," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN)"

        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Employees Clocked Into Indirect</b>" & vbCrLf
            strHTML = strHTML & "<table width=490 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Emp ID</b></td>"
            strHTML = strHTML & "<td><b>Name</b></td>"
            strHTML = strHTML & "<td><b>Indirect</b></td>"
            strHTML = strHTML & "<td><b>Description</b></td>"
            strHTML = strHTML & "<td><b>Clock In</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                strHTML = strHTML & "<td>" & cStr(snpData("employee_id")) & "</td>"
                If Not IsNull(snpData("employee_name")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("employee_name")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML &  "<td>" & cStr(snpData("indirect_id")) & "</td>"
                If Not IsNull(snpData("description")) Then
                    strHTML = strHTML &  "<td>" & cStr(snpData("description")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("clock_in")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("clock_in")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function
</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 495px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

FlyOutNOH.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 505px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  ID," & VBCrLf
        strSQL = strSQL & "  DESCRIPTION," & VBCrLf
        strSQL = strSQL & "  PRODUCT_CODE," & VBCrLf
        strSQL = strSQL & "  COMMODITY_CODE," & VBCrLf
        strSQL = strSQL & "  QTY_ON_HAND," & VBCrLf
        strSQL = strSQL & "  DECODE(PURCHASED,'Y','Purchased','Fabricated') PART_TYPE" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  PART" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  QTY_ON_HAND<0" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  ID"
        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Parts with a Negative QTY On Hand</b>" & vbCrLf
            strHTML = strHTML & "<table width=490 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Description</b></td>"
            strHTML = strHTML & "<td><b>P.C.</b></td>"
            strHTML = strHTML & "<td><b>C.C.</b></td>"
            strHTML = strHTML & "<td><b>Qty</b></td>"
            strHTML = strHTML & "<td><b>Type</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                strHTML = strHTML & "<td>" & cStr(snpData("id")) & "</td>"
                If Not IsNull(snpData("description")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("product_code")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("product_code")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("commodity_code")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("commodity_code")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("qty_on_hand")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & FormatNumber(snpData("qty_on_hand"),4) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("part_type")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_type")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 495px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

FlyOutPLI.html:

 <html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 655px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strLastPacklist
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  S.PACKLIST_ID," & VBCrLf
        strSQL = strSQL & "  SL.LINE_NO," & VBCrLf
        strSQL = strSQL & "  S.BOL_ID," & VBCrLf
        strSQL = strSQL & "  S.SHIPPED_DATE," & VBCrLf
        strSQL = strSQL & "  CO.CUSTOMER_ID," & VBCrLf
        strSQL = strSQL & "  SL.CUST_ORDER_ID," & VBCrLf
        strSQL = strSQL & "  SL.CUST_ORDER_LINE_NO," & VBCrLf
        strSQL = strSQL & "  COL.PART_ID," & VBCrLf
        strSQL = strSQL & "  SL.SHIPPED_QTY" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  SHIPPER S," & VBCrLf
        strSQL = strSQL & "  SHIPPER_LINE SL," & VBCrLf
        strSQL = strSQL & "  CUST_ORDER_LINE COL," & VBCrLf
        strSQL = strSQL & "  CUSTOMER_ORDER CO" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  S.INVOICE_ID IS NULL" & VBCrLf
        strSQL = strSQL & "  AND S.STATUS='A'" & VBCrLf
        strSQL = strSQL & "  AND S.PACKLIST_ID=SL.PACKLIST_ID" & VBCrLf
        strSQL = strSQL & "  AND SL.CUST_ORDER_ID=COL.CUST_ORDER_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND SL.CUST_ORDER_LINE_NO=COL.LINE_NO(+)" & VBCrLf
        strSQL = strSQL & "  AND COL.CUST_ORDER_ID=CO.ID(+)" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  S.PACKLIST_ID," & VBCrLf
        strSQL = strSQL & "  SL.LINE_NO"

        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Pack Lists Not Yet Invoiced</b>" & vbCrLf
            strHTML = strHTML & "<table width=640 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>PL</b></td>"
            strHTML = strHTML & "<td><b>Line</b></td>"
            strHTML = strHTML & "<td><b>BOL</b></td>"
            strHTML = strHTML & "<td><b>Ship Date</b></td>"
            strHTML = strHTML & "<td><b>Customer</b></td>"
            strHTML = strHTML & "<td><b>Cust Order</b></td>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Qty</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                If strLastPacklist <> snpData("packlist_id") Then
                    'Need to output the pack list ID this time, since it is not the same as the last row returned by the database
                    strHTML = strHTML & "<td>" & cStr(snpData("packlist_id")) & "</td>"   
                    strLastPacklist = snpData("packlist_id")
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "<td>" & cStr(snpData("line_no")) & "</td>"
                If Not IsNull(snpData("bol_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("bol_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("shipped_date")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("shipped_date")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("customer_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("customer_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("cust_order_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("cust_order_id")) & "/" & cStr(snpData("cust_order_line_no")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("part_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("shipped_qty")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("shipped_qty")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 645px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

FlyOutPOPD.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 610px;
            height: 500px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    'On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  POL.PART_ID," & VBCrLf
        strSQL = strSQL & "  POL.VENDOR_PART_ID," & VBCrLf
        strSQL = strSQL & "  P.DESCRIPTION," & VBCrLf
        strSQL = strSQL & "  POL.PURC_ORDER_ID," & VBCrLf
        strSQL = strSQL & "  POL.LINE_NO AS PURC_ORDER_LINE_NO," & VBCrLf
        strSQL = strSQL & "  PLD.DEL_SCHED_LINE_NO," & VBCrLf
        strSQL = strSQL & "  COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE) AS DESIRED_RECV_DATE," & VBCrLf
        strSQL = strSQL & "  DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,0),NVL(PLD.ORDER_QTY,0)) AS ORDER_QTY," & VBCrLf
        strSQL = strSQL & "  DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,0),NVL(PLD.RECEIVED_QTY,0)) AS RECEIVED_QTY" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  PURCHASE_ORDER PO," & VBCrLf
        strSQL = strSQL & "  PURC_ORDER_LINE POL," & VBCrLf
        strSQL = strSQL & "  PART P," & VBCrLf
        strSQL = strSQL & "  PURC_LINE_DEL PLD" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  PO.STATUS IN ('F','R','U')" & VBCrLf
        strSQL = strSQL & "  AND PO.ID=POL.PURC_ORDER_ID" & VBCrLf
        strSQL = strSQL & "  AND POL.LINE_STATUS='A'" & VBCrLf
        strSQL = strSQL & "  AND POL.SERVICE_ID IS NULL" & VBCrLf
        strSQL = strSQL & "  AND POL.ORDER_QTY>POL.TOTAL_RECEIVED_QTY" & VBCrLf
        strSQL = strSQL & "  AND POL.PART_ID=P.ID(+)" & VBCrLf
        strSQL = strSQL & "  AND POL.PURC_ORDER_ID=PLD.PURC_ORDER_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND POL.LINE_NO=PLD.PURC_ORDER_LINE_NO(+)" & VBCrLf
        strSQL = strSQL & "  AND DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,0),NVL(PLD.ORDER_QTY,0)) > DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,0),NVL(PLD.RECEIVED_QTY,0))" & VBCrLf
        strSQL = strSQL & "  AND COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE) < SYSDATE" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  POL.PART_ID," & vbCrLf
        strSQL = strSQL & "  POL.PURC_ORDER_ID," & VBCrLf
        strSQL = strSQL & "  POL.LINE_NO," & VBCrLf
        strSQL = strSQL & "  PLD.DEL_SCHED_LINE_NO" & VBCrLf
        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Past Due Purchase Orders</b>" & vbCrLf
            strHTML = strHTML & "<table width=590 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Description</b></td>"
            strHTML = strHTML & "<td><b>PO</b></td>"
            strHTML = strHTML & "<td><b>Received</b></td>"
            strHTML = strHTML & "<td><b>Wanted</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                If Not IsNull(snpData("part_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>"
                Else
                    If Not IsNull(snpData("vendor_part_id")) Then
                        strHTML = strHTML & "<td>" & cStr(snpData("vendor_part_id")) & "</td>"
                    Else
                        strHTML = strHTML & "<td>&nbsp;</td>"
                    End If
                End If
                If Not IsNull(snpData("description")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("del_sched_line_no")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("purc_order_id")) & "/" & cStr(snpData("purc_order_line_no")) & "/DL " & cStr(snpData("del_sched_line_no")) & "</td>"
                Else
                    strHTML = strHTML & "<td>" & cStr(snpData("purc_order_id")) & "/" & cStr(snpData("purc_order_line_no")) & "</td>"
                End If
                strHTML = strHTML & "<td>" & cStr(snpData("received_qty")) & " of " & cStr(snpData("order_qty")) & "</td>"
                If Not IsNull(snpData("desired_recv_date")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("desired_recv_date")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 590px; height: 480px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

FlyOutRIU.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 655px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  LT.RESOURCE_ID," & VBCrLf
        strSQL = strSQL & "  SR.DESCRIPTION," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_BASE_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_LOT_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_SPLIT_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_SUB_ID," & VBCrLf
        strSQL = strSQL & "  LT.OPERATION_SEQ_NO," & VBCrLf
        strSQL = strSQL & "  WO.PART_ID," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN) AS CLOCK_IN" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  LABOR_TICKET LT," & VBCrLf
        strSQL = strSQL & "  SHOP_RESOURCE SR," & VBCrLf
        strSQL = strSQL & "  WORK_ORDER WO" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  LT.TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
        strSQL = strSQL & "  AND LT.HOURS_WORKED IS NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.RESOURCE_ID IS NOT NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.RESOURCE_ID=SR.ID" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_TYPE='W'" & VBCrLf
        strSQL = strSQL & "  AND WO.TYPE='W'" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_TYPE=WO.TYPE" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_BASE_ID=WO.BASE_ID" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_LOT_ID=WO.LOT_ID" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_SPLIT_ID=WO.SPLIT_ID" & VBCrLf
        strSQL = strSQL & "  AND WO.SUB_ID='0'" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  LT.RESOURCE_ID," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN)" & VBCrLf

        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Shop Resources Currently in Use</b>" & vbCrLf
            strHTML = strHTML & "<table width=640 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Resource ID</b></td>"
            strHTML = strHTML & "<td><b>Description</b></td>"
            strHTML = strHTML & "<td><b>Work Order Op</b></td>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Clock In</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                strHTML = strHTML & "<td>" & cStr(snpData("resource_id")) & "</td>"
                If Not IsNull(snpData("description")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If cStr(snpData("workorder_sub_id")) = "0" Then
                    strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>"
                Else
                    strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "-" & cStr(snpData("workorder_sub_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>"
                End If
                If Not IsNull(snpData("part_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("clock_in")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("clock_in")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 645px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

Settings.html (this is just a placeholder for configuration settings):

<html >
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=Unicode" />
        <title>Hello World</title>
        <style type="text/css">
        body
        {
            margin: 0;
            width: 130px;
            height: 75px;
            font-family: verdana;
            font-weight: bold;
            font-size: small;
        }
        #gadgetContent
        {
            margin-top: 20px;
            width: 130px;
            vertical-align: middle;
            text-align: center;
            overflow: hidden;
        }
        </style>
        <script type="text/jscript" language="jscript">
            // Initialize the gadget.
            function init()
            {
                var oBackground = document.getElementById("imgBackground");
                oBackground.src = "url(images/background.png)";
            }
        </script>
    </head>

    <body onload="init()">
        <g:background id="imgBackground">
            <span id="gadgetContent">Hello World!</span>
        </g:background>
    </body>
</html>

InspectorButtonUp.png:

InspectorButtonMouseOver.png:

InspectorBackground.png (Background Image for Main Web Page):

Other Backgrounds that May be Used (Note that Power Point is a great tool for creating backgrounds):
 

Resources for Developing Windows Sidebar Applications:
http://msdn.microsoft.com/en-us/library/bb456468(VS.85).aspx
http://www.microsoft.com/uk/msdn/screencasts/screencast/262/building-a-vista-sidebar-gadget-part-1-getting-started.aspx

Resources for VBScript Programming:
http://msdn.microsoft.com/en-us/library/d1wf56tt.aspx
http://www.w3schools.com/vbscript/vbscript_ref_functions.asp

Suggested Book:
CSS  The Definitive Guide” by Eric A. Meyer:
http://www.amazon.com/CSS-Definitive-Guide-Eric-Meyer/dp/0596527330





Output Employee Attendance Calendar to Web with VBS

16 12 2009

December 16, 2009

This post is adapted from a small part of a presentation I gave a couple months ago.  The original code sample integrated into an ERP system to display an employee’s running attendance record for the last 6 or 12 months in graphical form, output to an Internet Explorer window.

First, we need sample “attendance” data in a table:

CREATE TABLE EMPLOYEE_RECORD_TEST AS
SELECT
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,5)),
          0,'MIKE',
          1,'ROB',
          2,'SAM',
          3,'JOE',
          4,'ERIC') EMPLOYEE_ID,
  TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE,
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)),
          0,'VAC',
          1,'HOL',
          2,'BEREAVE',
          3,'JURY',
          4,'ABS',
          5,'EXCUSE',
          6,'MIL',
          'OTHER') INDIRECT_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

The above created a table with 1,000 rows that picked one of five employees at random for each row, specifying a random date between today and 999 days ago, with one of eight random identifiers for the date.  The data in the table will look something like this:

SELECT
  *
FROM
  EMPLOYEE_RECORD_TEST
WHERE
  ROWNUM<=10;

EMPLOYEE_ID SHIFT_DAT INDIRECT_ID
----------- --------- -----------
MIKE        03-SEP-08 OTHER
MIKE        26-JUL-09 HOL
MIKE        27-MAY-09 EXCUSE
ERIC        27-JUL-08 OTHER
ERIC        02-NOV-07 VAC
ROB         02-OCT-07 OTHER
JOE         26-MAY-08 HOL
ERIC        29-JUL-08 ABS
ERIC        23-JUL-09 MIL
ERIC        14-JUN-07 HOL

Now that we have sample data, let’s see what we are trying to achieve (reduced in size):

As the color-coded output shows, Eric took a vacation day on January 4 and March 8.  Eric also was on bereavement leave on February 23 and 24, as well as March 22 and 25.  So, how was this output created?  A VBS script connected to the Oracle database (using a custom DLL to hide the username and password, and to simplify the process of submitting the SQL statement with bind variables), submitted a query, and then built the web page on the fly.

Dim varDateStart
Dim varDateEnd
Dim varDateMonthStart
Dim varDateMonthEnd
Dim intWeekdayStart
Dim intShiftDay
Dim i
Dim intRow
Dim intCol

'The color constants
Dim lngVacationBackColor
Dim lngHolidayBackColor
Dim lngBereavementBackColor
Dim lngJuryDutyBackColor
Dim lngAbsentBackColor
Dim lngExcusedBackColor
Dim lngMilitaryBackColor
Dim lngOtherMonthBackColor

Dim lngDateBackColor(31)
Dim lngDateForeColor(31)
Dim strSQL
Dim snpData
Dim OracleSQL
Dim objIE
Dim objShell

Dim intLastMonth
Dim intOutputMonth

Dim strHTML
Dim strEmployeeID
Dim dteTransactionDate

strEmployeeID = "MIKE"
'strEmployeeID = "ROB"
'strEmployeeID = "SAM"
'strEmployeeID = "JOE"
'strEmployeeID = "ERIC"

'dteTransactionDate = CDate("January 1, 2009") 'Can specify a specific date
dteTransactionDate = Date 'Can specify the current date

'Define the colors to be used to indicate the indirect in the date
'Note that the RGB components must be specified as BGR to be compatible with HTML
lngVacationBackColor = RGB(255, 0, 0)
lngHolidayBackColor = RGB(0, 255, 0)
lngBereavementBackColor = RGB(255, 175, 0)
lngJuryDutyBackColor = RGB(33, 153, 255)
lngAbsentBackColor = RGB(0, 0, 255)
lngExcusedBackColor = RGB(0, 255, 255)
lngMilitaryBackColor = RGB(255, 0, 150)
lngOtherMonthBackColor = RGB(75, 75, 100)

Set OracleSQL = CreateObject("VMDBOracle.SQLProcessor")
Set snpData = CreateObject("ADODB.Recordset")

'Specify the start of the month based on the current transaction date - set it back to the first day of the month
varDateStart = DateAdd("m", -11, DateSerial(DatePart("yyyy", dteTransactionDate), DatePart("m", dteTransactionDate), 1))

'Finding the end of the month is a little more difficult - we add 1 month to the transaction date, find the start of that month, and subtract one day
varDateEnd = DateAdd("d", -1, DateSerial(DatePart("yyyy", DateAdd("m", 1, dteTransactionDate)), DatePart("m", DateAdd("m", 1, dteTransactionDate)), 1))

'Set the starting colors
For i = 1 To 31
    lngDateBackColor(i) = RGB(230, 230, 230) 'Off White
    lngDateForeColor(i) = RGB(0, 0, 0) 'Black
Next

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
strSQL = strSQL & "  INDIRECT_ID" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE BETWEEN ? AND ?" & vbCrLf
strSQL = strSQL & "  AND INDIRECT_ID IS NOT NULL" & vbCrLf

'Specify the start of the month based on the current transaction date - set it back to the first day of the month
OracleSQL.SetParameter varDateStart, "DATE"

'Finding the end of the month is a little more difficult - we add 1 month to the transaction date, find the start of that month, and subtract one day
OracleSQL.SetParameter varDateEnd, "DATE"

If strEmployeeID <> "" Then
    strSQL = strSQL & "  AND EMPLOYEE_ID= ?" & vbCrLf
    OracleSQL.SetParameter strEmployeeID, "VARCHAR"
End If

strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
strSQL = strSQL & "  INDIRECT_ID DESC"

OracleSQL.Sql = strSQL
Set snpData = OracleSQL.Execute

intOutputMonth = False
strHTML = ""
intRow = 0

'Shadow
strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 7px;left: 7;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #000000;"
strHTML = strHTML & "background-color: #FFFFFF;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 5px;left: 5;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #FFFF00;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 6px;left: 6;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #0000FF;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

If Not (snpData Is Nothing) Then
    Do While Not (snpData.EOF)
        intShiftDay = DatePart("d", CDate(snpData("shift_date")))
        Select Case CStr(snpData("indirect_id"))
            Case "VAC", "VACB", "VACC", "VACF", "VACM"
                lngDateBackColor(intShiftDay) = lngVacationBackColor
            Case "HOL", "HOLC", "HOLF", "HOLB", "HOLM"
                lngDateBackColor(intShiftDay) = lngHolidayBackColor
            Case "BEREAVE", "BEREAVEC", "BEREAVEF", "BEREAVEB", "BEREAVEM"
                lngDateBackColor(intShiftDay) = lngBereavementBackColor
            Case "JURY", "JURYC", "JURYF", "JURYB", "JURYM"
                lngDateBackColor(intShiftDay) = lngJuryDutyBackColor
            Case "ABS", "ABSC", "ABSF", "ABSB", "ABSM"
                lngDateBackColor(intShiftDay) = lngAbsentBackColor
            Case "EXCUSE", "EXCUSEC", "EXCUSEF", "EXCUSEB", "EXCUSEM"
                lngDateBackColor(intShiftDay) = lngExcusedBackColor
            Case "MIL", "MILC", "MILF", "MILB", "MILM"
                lngDateBackColor(intShiftDay) = lngMilitaryBackColor
        End Select

        'See if the month will change
        intLastMonth = DatePart("m", CDate(snpData("shift_date")))
        varDateMonthStart = DateSerial(DatePart("yyyy", CDate(snpData("shift_date"))), DatePart("m", CDate(snpData("shift_date"))), 1)
        varDateMonthEnd = DateAdd("d", -1, DateAdd("m", 1, DateSerial(DatePart("yyyy", CDate(snpData("shift_date"))), DatePart("m", CDate(snpData("shift_date"))), 1)))

        snpData.MoveNext

        intOutputMonth = False

        If snpData.EOF Then
            intOutputMonth = True
        Else
            If DatePart("m", CDate(snpData("shift_date"))) <> intLastMonth Then
                intOutputMonth = True
            End If
        End If

        If intOutputMonth = True Then
            intWeekdayStart = Weekday(varDateMonthStart)

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 14pt; color: #110011;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "<b>" & MonthName(DatePart("m", varDateMonthStart)) & " " & CStr(DatePart("yyyy", varDateMonthStart)) & "</b></div>" & vbCrLf

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Sun</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(2 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Mon</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(3 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Tue</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(4 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Wed</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(5 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Thu</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(6 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Fri</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(7 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Sat</div>" & vbCrLf

            intRow = intRow + 1
            'Fill in the days from the previous month
            For i = 1 To intWeekdayStart - 1
                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(i * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #FFFFFF;"
                'Pad with leading 0s
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngOtherMonthBackColor)), "0") & Hex(lngOtherMonthBackColor) & ";"">"
                strHTML = strHTML & DatePart("d", DateAdd("d", -(intWeekdayStart - i), varDateMonthStart)) & "</div>" & vbCrLf
            Next

            For i = 1 To DatePart("d", varDateMonthEnd)
                'See if we need to jump to the next row
                If i > 1 Then
                    'See if the week day is less than the previous week day - if so, jump to the next row in the calendar since the week changed
                    If Weekday(DateAdd("d", i - 1, varDateMonthStart)) < Weekday(DateAdd("d", i - 2, varDateMonthStart)) Then
                        intRow = intRow + 1
                    End If
                End If
                intCol = Weekday(DateAdd("d", i - 1, varDateMonthStart))

                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(Weekday(DateAdd("d", i - 1, varDateMonthStart)) * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #" & String(6 - Len(Hex(lngDateForeColor(i))), "0") & Hex(lngDateForeColor(i)) & ";"
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngDateBackColor(i))), "0") & Hex(lngDateBackColor(i)) & ";"">"
                strHTML = strHTML & CStr(i) & "</div>" & vbCrLf
            Next

            'Finish out the final week
            For i = Weekday(varDateMonthEnd) + 1 To 7
                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(i * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #FFFFFF;"
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngOtherMonthBackColor)), "0") & Hex(lngOtherMonthBackColor) & ";"">"
                strHTML = strHTML & CStr(i - Weekday(varDateMonthEnd)) & "</div>" & vbCrLf
            Next

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngVacationBackColor)), "0") & Hex(lngVacationBackColor) & ";"">"
            strHTML = strHTML & "VAC</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(2 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngHolidayBackColor)), "0") & Hex(lngHolidayBackColor) & ";"">"
            strHTML = strHTML & "HOL</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(3 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngBereavementBackColor)), "0") & Hex(lngBereavementBackColor) & ";"">"
            strHTML = strHTML & "BER</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(4 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngJuryDutyBackColor)), "0") & Hex(lngJuryDutyBackColor) & ";"">"
            strHTML = strHTML & "JUR</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(5 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngAbsentBackColor)), "0") & Hex(lngAbsentBackColor) & ";"">"
            strHTML = strHTML & "ABS</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(6 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngExcusedBackColor)), "0") & Hex(lngExcusedBackColor) & ";"">"
            strHTML = strHTML & "EXC</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(7 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngMilitaryBackColor)), "0") & Hex(lngMilitaryBackColor) & ";"">"
            strHTML = strHTML & "MIL</div>" & vbCrLf

            'Reset the starting colors
            For i = 1 To 31
                lngDateBackColor(i) = RGB(230, 230, 230) 'Off White
                lngDateForeColor(i) = RGB(0, 0, 0) 'Black
            Next
        End If
    Loop

    snpData.Close
End If

'Fire up Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Left = 0
objIE.Top = 0
objIE.Width = 260
objIE.Height = 700
objIE.StatusBar = False
objIE.MenuBar = False
objIE.Toolbar = False

objIE.Navigate "about:blank"
objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title = "Attendance " & strEmployeeID
objIE.Visible = True

'with the help of custom program, set a 1 second delay, then force the Attendance web page to the top
Set objShell = CreateObject("WScript.Shell")
objShell.Run("C:\BringToTop.exe " & Chr(34) & "Attendance " & strEmployeeID & Chr(34) & " 1")

Set objShell = Nothing
Set snpData = Nothing
Set objIE = Nothing
Set OracleSQL = Nothing

While the above uses a custom DLL for the database connection, a standard ADO connection will work just as well.  The script also uses a custom program that I created called BringToTop that simply addresses the “pop under” behavior on Vista and Windows 7.

So, which employee has the best attendance record?





Use VBS to Search for Oracle Books using Google’s Book Library

13 12 2009

December 12, 2009

Below is a somewhat complicated VBS script that interacts with Internet Explorer to submit a query to books.google.com.  Once Google prepares the web page, the VBS script parses the raw HTML code in the web page to generate a new web page containing the list of matching books supplied by Google.  Selecting a book and then clicking the View button opens that book on the Google books site.

The search keyword is specified on the objIESource.Navigate line at the end of the website address.  A plus sign should appear between each search keyword.  To exclude a particular word from the search, prefix the word with a minus sign, for example to search for the keywords Oracle and SQL, but exclude the word dummy:

objIESource.Navigate "http://books.google.com/books?um=1&q=oracle+sql+-dummy"

Note that there are various ways to extend this example.

Dim objIE            'For the data entry form where we present the harvested book list
Dim objShell         'To add a delay, may throw an error when executed in a Visual macro
Dim strHTML          'Holds what we are displaying on our data entry form
Dim intFlag          'Indicates if the user clicked OK or closed the browser window
Dim i                'For our counter
Dim objIESource      'Holds the data source web page
Dim strHTMLSource    'Holds the HTML contents of the source web page
Dim intStart         'The starting position of title="  in the source web page
Dim intEnd           'The position of the next " after title="  in the source web page
Dim intApproxStart   'The book titles appear after the first entry of coverthumb, so we will start there
Dim strBook(1000)    'Holds the book titles found on this page
Dim intBookCount     'Counter for the number of books found
Dim strFind          'What to find in the HTML code which indicates that the data of interest will follow
Dim intFindLen       'The length of the what to find string
Dim intLinkStart     'The starting position of title="  in the source web page
Dim intLinkEnd       'The position of the next " after title="  in the source web page
Dim strBookLink(1000)'Holds the link to the book found on this page
Dim strLinkFind      'What to find in the HTML code which indicates that the page link will follow
Dim intLinkFindLen   'The length of the what to find link string
Dim strSelectedBook  'The name of the selected book
Dim strBookAddress   'The web address of the book
Dim adsFile          'Used if we want to write the downloaded web page to the hard drive

On Error Resume Next
'Set objShell = CreateObject("WScript.Shell")
Set objIESource = CreateObject("InternetExplorer.Application")
objIESource.Navigate "http://books.google.com/books?um=1&q=oracle+tuning"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "about:blank"

For i = 1 to 10000
    'Give it some time to prepare the objIESource
Next
Do While objIESource.Busy <> False
    'objShell.Sleep 500 'Edit: This line was supposed to be replaced with the following line Dec 13, 2009
    Wscript.Sleep 200
Loop

intBookCount = 0
strHTMLSource = cStr(objIESource.Document.Body.InnerHTML)    'Retrieve the raw HTML code from the web page

'Uncomment to save the web page to the hard drive
'Set adsFile = CreateObject("ADODB.Stream")
'adsFile.Type = 2
'adsFile.Charset = "iso-8859-1"
'adsFile.Open
'adsFile.WriteText(strHTMLSource)
'adsFile.SaveToFile "c:\InnerHTML.txt", 2
'adsFile.close
'Set adsFile = Nothing

'In the same HTML page, the first book title starts after the first entry of: 
intApproxStart = InStr(strHTMLSource, "coverthumb") + 1
strFind = "title=" & Chr(34)
intFindLen = Len(strFind)
strLinkFind = "<A href=" & Chr(34)
intLinkFindLen = Len(strLinkFind)

'Find the start of the first book title
'Might be listed like this in the HTML code:  title="Excel 2007 VBA Programming For Dummies"
intStart = InStr(intApproxStart, strHTMLSource, strFind)

Do While intStart > 0
    'Find the end of the book title
    intEnd = InStr(intStart + intFindLen, strHTMLSource, Chr(34))
    If intEnd > 0 Then
        intBookCount = intBookCount + 1
        strBook(intBookCount) = Mid(strHTMLSource, intStart + intFindLen, intEnd - (intStart + intFindLen))

        'Find the link to the book title
        intLinkStart = InStr(intEnd, strHTMLSource, strLinkFind)
        If intLinkStart > 0 Then
            intLinkEnd = InStr(intLinkStart + intLinkFindLen, strHTMLSource, Chr(34))
            If intLinkEnd > 0 Then
                strBookLink(intBookCount) = Mid(strHTMLSource, intLinkStart + intLinkFindLen, intLinkEnd - (intLinkStart + intLinkFindLen))
            Else
                strBookLink(intBookCount) = ""
            End If
        Else
            strBookLink(intBookCount) = ""
        End If

        'Find the start of the next book title
        intStart = InStr(intEnd, strHTMLSource, strFind)
    Else
        Exit Do
    End If
Loop

'Edit: place a single quote in front of the following two lines to prevent the list of books from disappearing, Dec 13, 2009
objIESource.Quit
Set objIESource = Nothing

strHTML = strHTML & "<form name=""BookFind"">" & vbCrLf
strHTML = strHTML & "<input type=hidden id=""txtOK"" value="" "">" & vbCrLf
strHTML = strHTML & "Book:<br /> <select size=""23"" id=""lstBooks"" style=""width:450"">" & vbCrLf

For i = 1 To intBookCount
    strHTML = strHTML & "<option value=" & Chr(34) & strBook(i) & Chr(34) & ">" & strBook(i) & "</option>" & vbCrLf
Next
strHTML = strHTML & "</select>" & vbCrLf
strHTML = strHTML & "<p><center><input type=button value=""View"" id=""cmdOK"" onclick=""document.getElementById('txtOK').value='OK';""></center>" & vbCrLf
strHTML = strHTML & "</form>" & vbCrLf

objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title = "Select Book Title from Google Books"
objIE.Left = 0
objIE.Top = 0
objIE.Width = 500
objIE.Height = 520
objIE.Statusbar = False
objIE.Menubar = False
objIE.Toolbar = False
objIE.Visible = True

For i = 1 to 10000
    'Give it some time to prepare the objIE
Next
Do While objIE.Busy <> False
    Wscript.Sleep 200
Loop

intFlag = 0
'loop until the button is clicked
Do While intFlag = 0
    If Err <> 0 Then
        intFlag = -1
    End If
    If objIE Is Nothing Then
        'User closed ID
        intFlag = -1
    Else
        If objIE.Document.All.txtOK.Value <> " " Then
            intFlag = 1
        End If
    End If
    Wscript.Sleep 250
Loop

If intFlag = 1 Then
    'Copy in the values from the web page
    strSelectedBook = objIE.Document.Body.All.lstBooks.Value

    'Try to find the associated link to the book
    For i = 1 to intBookCount
        If strBook(i) = strSelectedBook Then
            'Found the book
            strBookAddress = strBookLink(i)
            'Extra credit - display the link associated with the selected book
            'Comment out the following objIE lines and uncomment the objIE.Quit
            '  if the link associated with the book should not be displayed
            objIE.Navigate "about:blank"
            objIE.Width = 800
            objIE.Height = 600
            objIE.Statusbar = True
            objIE.Menubar = True
            objIE.Toolbar = True
            objIE.Navigate strBookLink(i)
            Exit For
        End If
    Next
    'objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing

The list of results:

The selected book:





Find the DB Names on a Remote Windows Server using VBS

12 12 2009

December 12, 2009

The following question appeared in one of the OTN forums a couple months ago:
http://forums.oracle.com/forums/thread.jspa?threadID=958849 

I am new in this database field.
I have a small query that how can i find the number of database running on a particular host in windows environment by query?

 There is no query in Oracle which will show you the database instances on a Windows server. That said, here is a portion (adaptation) of a WMI script from the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table” which will do it for you as long as the database instance is started (even on a remote server if you have administrator privileges on the remote server):

Dim objWMIService
Dim strSQL
Dim strComputer
Dim colItems
Dim objItem

strSQL = "SELECT * FROM Win32_Process Where Name like 'Oracle%'"

strComputer = "."  ' the . indicates the local computer
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\CIMV2")

Set colItems = objWMIService.ExecQuery(strSQL,"WQL",48)

For Each objItem in colItems
  Wscript.Echo "Instance: " & objItem.CommandLine & vbCrLf
Next

To run the above script, save it as a text file with the extension .VBS. Open a Windows command prompt, and type cscript followed by the name of the script:

C:\> cscript c:\databases.vbs

Note that you must be an administrator on the remote computer (or a domain administrator) to remotely query the running processes on another Windows computer. 





Print Oracle Data to a Barcode Label with a Zebra Printer using VBS and Excel

12 12 2009

December 12, 2009

Here is the problem: You know how to query the database using a Windows Scripting Host VBS file, but you have no way to print a barcode label using the data to the networked Zebra label printer and there is no printer driver installed for the Zebra printer.  Fortunately, Zebra printers understand ZPL (Zebra Printer Langauge), so we are able to easily work around the issue of not having a driver for the printer installed.  The second problem is how do we send the print job to the printer?  This is where Excel comes to the rescue.  Excel is able to work with the Windows API to start and submit a print job to a printer that is not necessarily installed on the local computer.

First, the Windows Scripting Host VBS file (note that this uses a DLL that I wrote which hides the database name, username, and password, as well as simplifying the process of submitting SQL statements with bind variables to the database):

'PrintLabel.vbs
Dim intParentPartLine
Dim strRawText
Dim strSQL
Dim strWhereUsed
Dim snpData
Dim OracleSQL
Dim objExcel

On Error Resume Next

Set OracleSQL = CreateObject("VMDBOracle.SQLProcessor") 'This is my custom DLL
Set snpData = CreateObject("ADODB.Recordset")
Set objExcel = CreateObject("Excel.Application")

strOut = ""
With objExcel
    'Open the Excel file containing the macro functions
    .Workbooks.Open "C:\ExcelMacroFunction.xls"

    strRawText = "~SD25^XA" 'Set Darkness, Label start
    strRawText = strRawText & "^SZ2" 'Enable ZPL2
    strRawText = strRawText & "^JZ" 'Reprint on error
    strRawText = strRawText & "^PR8,8,8" 'Print speed 8" per second, 8" per sec slew, 8" per sec backfeed
    strRawText = strRawText & "^LH12,30" 'Label home position in pixels
    strRawText = strRawText & "^FO5,0^A0,40,40^FD" & ID & "^FS" 'Proportional font
    strRawText = strRawText & "^FO5,40^A0,20,20^FD" & DESCRIPTION & "^FS" 'Proportional font
    strRawText = strRawText & "^FO5,80^A0,15,15^FD**** WHERE USED ****^FS" ' 'Proportional font

    strSQL = "SELECT DISTINCT" & vbCrLf
    strSQL = strSQL & "  R.WORKORDER_BASE_ID AS TOP_PART_ID," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID AS SUB_PART_ID" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  REQUIREMENT R," & vbCrLf
    strSQL = strSQL & "  WORK_ORDER WO" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  WO.TYPE='M'" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_TYPE='M'" & vbCrLf
    strSQL = strSQL & "  AND R.PART_ID= ?" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_TYPE=WO.TYPE" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_BASE_ID=WO.BASE_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_LOT_ID=WO.LOT_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_SPLIT_ID=WO.SPLIT_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_SUB_ID=WO.SUB_ID" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  R.WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID"
    OracleSQL.SQL = strSQL
    OracleSQL.SetParameter ID, "VARCHAR"

    Set snpData = OracleSQL.Execute

    If Not (snpData Is Nothing) Then
        If Not (snpData.EOF) Then
            intParentPartLine = 0
            Do While Not (snpData.EOF)
                intParentPartLine = intParentPartLine + 1
                If snpData("top_part_id") <> snpData("sub_part_id") Then
                    strWhereUsed = strWhereUsed & cStr(snpData("top_part_id")) & " (Sub " & cStr(snpData("sub_part_id")) & ")"
                Else
                    strWhereUsed = strWhereUsed & cStr(snpData("top_part_id"))
                End If
                strRawText = strRawText & "^FO10," & cStr(80 + 25 * intParentPartLine) & "^A0,25,25^FD" & strWhereUsed & "^FS" 'Proportional font
                strWhereUsed  = ""

                snpData.MoveNext
            Loop
        End If

        snpData.Close
    End If

    strRawText = strRawText & "^FO20,562^AF^FDMy Company Here^FS"
    strRawText = strRawText & "^XZ"  'End of label indicator

    'Excute a macro located in mdlGlobal that prints a label using API calls
                                 'Subroutine, printer device name, raw ZPL code

    'Print to a shared Zebra printer named ZEBRA on computer named KMMACH98
    'strResult = .Application.Run("ZebraPrintLabel", "\\KMMACH98\ZEBRA", strRawText)

    'Print to a "Local" printer named ZEBRA_M1 with a redirected LTP2 port with NET USE
    strResult = .Application.Run("ZebraPrintLabel", "ZEBRA_M1", strRawText)

    .DisplayAlerts = False
    .ActiveWorkbook.Saved = True
    .Quit
End With

Set objExcel = Nothing 
Set OracleSQL = Nothing
Set snpData = Nothing
'End of PrintLabel.vbs

The above script expects to find an Excel spreadsheet named C:\ExcelMacroFunction.xls with a public subroutine named  ZebraPrintLabel in a regular Excel code module.

The code in the Excel code module looks like this:

'mdlGlobal in C:\ExcelMacroFunction.xls - a global module
Option Explicit

'Type declaration for Zebra label printing
Public Type DOC_INFO_1
    pDocName As String
    pOutputFile As String
    pDatatype As String
End Type

'Zebra Printing API functions
Public Declare Function ClosePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function EndDocPrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function EndPagePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function OpenPrinter Lib "winspool.drv" Alias "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, ByVal pDefault As Long) As Long
Public Declare Function StartDocPrinter Lib "winspool.drv" Alias "StartDocPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, pDocInfo As DOC_INFO_1) As Long
Public Declare Function StartPagePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function WritePrinter Lib "winspool.drv" (ByVal hPrinter As Long, pBuf As Any, ByVal cdBuf As Long, pcWritten As Long) As Long

Public Sub ZebraPrintLabel(strPrinter As Variant, strRawText As Variant)
    'Variables for handling printing
    Dim i As Integer
    Dim lngPrinterHandle As Long
    Dim lngResult As Long
    Dim lngWritten As Long
    Dim lngPrinterDocHandle As Long
    Dim bytRawText() As Byte
    Dim MyDocInfo As DOC_INFO_1

    On Error Resume Next

    'In VB6 to see the list of printer device names, enter the following into the Debug window
    'For i = 0 to Printers.Count - 1:Debug.Print Printers(i).DeviceName:Next

    'Sample label for testing using just Excel
    'strPrinter = "\\KMMACH98\ZEBRA"
    'strRawText = "~SD25^XA" 'Set Darkness, Label start
    'strRawText = strRawText & "^SZ2" 'Enable ZPL2
    'strRawText = strRawText & "^JZ" 'Reprint on error
    'strRawText = strRawText & "^PR8,8,8" 'Print speed 8" per second, 8" per sec slew, 8" per sec backfeed
    'strRawText = strRawText & "^LH10,26" 'Label home position in pixels
    'strRawText = strRawText & "^FO2,14^A0R,20,20^FDMy Company Here^FS" 'rotated text in font A
    'strRawText = strRawText & "^FO2,480^A0R,20,20^FDSomwhere, USA^FS"
    'strRawText = strRawText & "^FO180,135^B3R,,105,N^FD" & "ABC111" & "^FS"  'Font 3 of 9 barcode
    'strRawText = strRawText & "^FO180,0^GB0,760,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO335,0^GB0,1218,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO550,0^GB0,1218,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO260,760^GB0,452,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO0,760^GB335,0,3^FS"  'Horizontal Line 3 pixels wide
    'strRawText = strRawText & "^XZ"  'End of label indicator
    'Convert the variant data types to strings
    strPrinter = CStr(strPrinter)
    strRawText = CStr(strRawText)

    'Terminate the string with a CRLF combination (may not be needed)
    If Right(strRawText, 2) <> vbCrLf Then
        strRawText = strRawText & vbCrLf
    End If

    'Convert the strRawText string to a byte stream
    ReDim bytRawText(1 To Len(strRawText))
    For i = 1 To Len(strRawText)
        bytRawText(i) = Asc(Mid(strRawText, i, 1))
    Next i

    'Create a connection to the printer, returns a handle to the printer
    lngResult = OpenPrinter(strPrinter, lngPrinterHandle, 0)

    If lngPrinterHandle = 0 Then
        MsgBox "Could Not Open Printer"
        Exit Sub
    End If

    'Fill in the document header structure
    MyDocInfo.pDocName = "Zebra Label from Excel"
    MyDocInfo.pOutputFile = vbNullString
    MyDocInfo.pDatatype = "RAW"
    lngPrinterDocHandle = StartDocPrinter(lngPrinterHandle, 1, MyDocInfo)

    If lngPrinterDocHandle = 0 Then
        MsgBox "Could Not Start the Document"
        lngResult = ClosePrinter(lngPrinterHandle)
        Exit Sub
    End If

    'Prepare to start the first page
    Call StartPagePrinter(lngPrinterHandle)

    'Write out the contents of the first page
    lngResult = WritePrinter(lngPrinterHandle, bytRawText(1), Len(strRawText), lngWritten)
    If lngResult = 0 Then
        MsgBox "Could Not Write to the Page"
        lngResult = ClosePrinter(lngPrinterHandle)
        Exit Sub
    End If

    'End the first page
    lngResult = EndPagePrinter(lngPrinterHandle)

    'End the document
    lngResult = EndDocPrinter(lngPrinterHandle)

    'Close the connection to the printet
    lngResult = ClosePrinter(lngPrinterHandle)
End Sub
'End of mdlGlobal

Note that while the above uses a custom DLL to connect to the database and submit SQL statements, the Simple VBS Script to Retrieve Data from Oracle blog entry shows how to do the same without using the custom DLL.  Also, it probably is not necessary to convert the Unicode string that is passes into the procedure into a byte stream for the Windows API call.

The printed label appears below:





Simple VBS Script to Retrieve Data from Oracle

12 12 2009

December 12, 2009

All recent releases of the Windows operating system include the Windows Scripting Host, which allows executing program commands that look much like the macro language used in Excel, Access, and Word, as well as the original line of Microsoft’s Visual Basic (before the introduction of .Net).  Scripts intended to be excuted by the Windows Scripting Host have an extension of .VBS and are executed either with cscript that outputs to a command line window, or the default wscript that outputs to Windows popup messages.

A simple script that connects to an Oracle database (without using ODBC), queries a table, and then performs a comparison on the values retrieved from the table follows:

'Save as ConnectDB.vbs
Dim strSQL
Dim strUsername
Dim strPassword
Dim snpData
Dim dbMyDBConnection
Dim ORDER_ID         'Note that this variable was omitted originally as it was automatically supplied by an ERP package

Set snpData = CreateObject("ADODB.Recordset")
Set dbMyDBConnection = CreateObject("ADODB.Connection")
strUsername = "MyUser"
strPassword = "MySecret"

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyDB;User ID=" & strUsername & ";Password=" & strPassword & ";"
dbMyDBConnection.Open

ORDER_ID = "MYORDERID123"  'Note that this variable value was omitted originally as it was automatically supplied by an ERP package
strSQL = "SELECT C1, C2 FROM MY_TABLE WHERE PURC_ORDER_ID='" & ORDER_ID & "'"

snpData.Open strSQL, dbMyDBConnection

If Not(snpData.EOF) Then
    If cInt(snpData("c1")) < cInt(snpData("c2")) Then
        MsgBox "C1 is Less than C2"
    End If
    If cInt(snpData("c1")) > cInt(snpData("c2")) Then
        MsgBox "C1 is Greater than C2"
    End If
    If cInt(snpData("c1")) = cInt(snpData("c2")) Then
        MsgBox "C1 is Equal to C2"
    End If

    MsgBox cInt(snpData("c1")) - cInt(snpData("c2"))
Else
    MsgBox "Ut oh, No Matching Records"
End If

snpData.Close
dbMyDBConnection.Close

Set snpData = Nothing
Set dbMyDBConnection = Nothing

Once the script is saved, double-clicking the script should automatically execute it using wscript.  Alternatively, open a Windows command prompt and type the following (assuming that the script is named ConnectDB.vbs and is saved in the root of the C:\ drive):

cscript c:\ConnectDB.vbs







Follow

Get every new post delivered to your Inbox.

Join 137 other followers