## The SQL to the Orbiting Ball

12 09 2010

September 12, 2010

Several years ago I developed a somewhat simple program in 16 bit Borland C (last compiled in 1994, so it pre-dates graphical web pages) that was optimized for 386 and 486 computers which sported VGA graphics but was also able to work with EGA graphics cards.  The simple program, after creating 18 images on the fly that simulated a rotating basketball, generated X and Y coordinates using a specially crafted formula that was capable of producing X and Y cordinates in a circular pattern.  With slightly different inputs the same formula produced a slightly out of round circular pattern that eventually might have generated straight line patterns.  With a slight adjustment to the inputs the same formula produced W patterns within a bounded box.  Keyboard input allowed the user to specify adjusted inputs as the program executed.  The C program looked like this:

```#include <graphics.h>               // for graphics functions
#include <conio.h>                  // for kbhit()
#include <math.h>
#include <dos.h>
#define LEFT      100               // boundary of rectangle
#define TOP       100
#define pi        3.14159276
#define L_ARROW   75
#define R_ARROW   77
#define U_ARROW   72
#define D_ARROW   80
#define ESC       27

void main(void)
{
int driver, mode;                // for initgraph()
float x, y, dx, dy, i, sped, temp, x1, x2, y1, y2, sta1, sta2, ena1, ena2, rad;    // ball coordinates
int imgnum, tim, del;
char key;
unsigned char ballbuff[10][5000];    // buffer for ball image

driver = DETECT;                 // auto detect
// set graphics mode
initgraph(&driver, &mode, "c:\\bc4\\bgi");

x = LEFT + RADIUS;          // designate center where ball is created
for (i =0; i <180; i = i + 18) // create basketball rotation images
{
setcolor(RED);
setfillstyle(SOLID_FILL, RED);
floodfill(x, y, RED);
setcolor(BLACK);
rad = i / (2 * pi);
x1 = x + 30 * cos(rad);
x2 = x - 30 * cos(rad);
y1 = y + 30 * sin(rad);
y2 = y - 30 * sin(rad);
sta1 = (180 + i -62);
ena1 = (180 + i + 42);
sta2 = (i -62);
ena2 = (i + 42);
if ((i/36) != int(i/36))    // must be included to swap arcs
{
temp = sta1;
sta1 = sta2;
sta2 = temp;
temp = ena1;
ena1 = ena2;
ena2 = temp;
}
// pickup image
// clear screen
setcolor(WHITE);
rectangle(-1,-1, 640, 480);
setfillstyle(SOLID_FILL, BLACK);
floodfill(100,100, WHITE);
}

imgnum = 10;                         // load first position + 1
tim = 0;                             // set delay to zero
dx = .1;                             // set constant in x equation
dy = .1;
sped = 1;
del = 1;
while ( key != ESC )
{
if (kbhit())
{
key = getch();
if (key == 61) del++;            // = key pressed
if (key == 43) del++;            // + key pressed
if (key == 45) del--;            // - key pressed
if (key == 47) sped = sped +.1;  // / key pressed
if (key == 92) sped = sped -.1;  // \ key pressed
if (key == 0)                    // place image on screen
switch(getch())
{
case L_ARROW:
dx = dx -.01;
break;
case R_ARROW:
dx = dx + .01;
break;
case U_ARROW:
dy = dy + .01;
break;
case D_ARROW:
dy = dy -.01;
break;
case ESC:
key = 27;
break;
}
}
tim = tim + sped;
x = (sin(dx * tim)*100) + 300;
y = (cos(dy * tim)* 100) + 300;
imgnum--;                   // cycle through images
if (imgnum == -1) imgnum = 9;

// move ball across screen
// to make ball move rapidly increase +
// set height on screen
delay(del);                 // make delay smaller for slow computers
}
getch();

closegraph();
}```

If you still have a 16 bit Borland C compiler (my copy is in a huge box on the bottom self of my bookcase), feel free to compile the above program to see it in action.  The original compiled C program, last compiled in 1994, may be downloaded here: OrbitBall2.zip (save as OrbitBall.zip and extract the files – download might not work).  The original program is confirmed to work on 32 bit Windows 95 through Windows XP (in DOS full screen mode), but definitely will not work on 64 bit Windows, even in a virtual machine (it also failed to run on a 32 bit Windows 7 netbook).

You are probably thinking, “Neat, but what does that have to do with Oracle?”.  It might be interesting to produce a modernized version of the above program.  We could use a simple SQL statement like the following to generate 3,600 X and Y coordinates, much like the X and Y coordinates that were generated by the above C program (note that the COS function is typically used to derive X values (using mathematical cosine) and the SIN function is typically used to derive Y values – the functions were swapped so that the X and Y cordinates start at the bottom-center of the screen).  The SQL follows:

```SELECT
ROUND((SIN(DX * (SPEED * COUNTER)) * WIDTH/2) + WIDTH/2) X,
ROUND((COS(DY * (SPEED * COUNTER)) * HEIGHT/2) + HEIGHT/2) Y
FROM
(SELECT
0.1 DX,
0.1 DY,
1 SPEED,
1 DELAY,
300 WIDTH,
300 HEIGHT,
LEVEL COUNTER
FROM
DUAL
CONNECT BY
LEVEL<=3600);```

Now we have a slight problem, how do we present the X and Y coordinate points produced by the above SQL statement?  We need some sort of object to track the X and Y coordinate pairs.  Drawn basketballs might work, but instead I will use these pictures (created with Microsoft Power Point 2010):

To display the round oak pictures, we will put together a VBS script to build a web page on the fly, cycling through the above eight pictures (every two X,Y coordinate pairs cause the displayed picture to change).  Much like the original program, we will allow the user to control the input parameters as the program runs.  Each time the parameters are adjusted, 3,600 new X,Y coordinate points are retrieved from the database into an array – this allows the VBS script to continue at the next X,Y coordinate pair, rather than starting at the beginning every time the parameters are adjusted.

```Option Explicit

Dim dbDatabase
Dim snpData
Dim comData
Dim varData
Dim objIE

Dim strDatabase             'SID name from tnsnames.ora

startup

Sub startup()
Dim strSQL
Dim strHTML
Dim objOrbitBall
Dim objOrbitBallPic
Dim objCommand
Dim objSettings
Dim i
Dim intQuit

'Fire up Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Left = 0
objIE.Top = 0
objIE.Width = 930
objIE.Height = 820
objIE.StatusBar = True
objIE.Toolbar = False
objIE.Document.Title = "The SQL to the Orbiting Ball"
objIE.Visible = True

'The data entry area
strHTML = "<div style=""position: absolute;width: 180px; height: 200px;top: 10px;left: 710px;"">" & vbCrLf
strHTML = strHTML & "<input type=text id=txtCommand value="""" size=""1""><br>" & vbCrLf
strHTML = strHTML & "<font size=1><b>+&nbsp;&nbsp;&nbsp;Increase Delay (Not Used)<br>" & vbCrLf
strHTML = strHTML & "-&nbsp;&nbsp;&nbsp;Decrease Delay (Not Used)<br>" & vbCrLf
strHTML = strHTML & "/&nbsp;&nbsp;&nbsp;Increase Rotation Speed<br>" & vbCrLf
strHTML = strHTML & "\&nbsp;&nbsp;&nbsp;Decrease Rotation Speed<br>" & vbCrLf
strHTML = strHTML & "D&nbsp;&nbsp;&nbsp;Increase Rotation Speed X Axis<br>" & vbCrLf
strHTML = strHTML & "A&nbsp;&nbsp;&nbsp;Decrease Rotation Speed X Axis<br>" & vbCrLf
strHTML = strHTML & "W&nbsp;&nbsp;&nbsp;Increase Rotation Speed Y Axis<br>" & vbCrLf
strHTML = strHTML & "S&nbsp;&nbsp;&nbsp;Decrease Rotation Speed Y Axis<br>" & vbCrLf
strHTML = strHTML & "L&nbsp;&nbsp;&nbsp;Increase Width X Axis<br>" & vbCrLf
strHTML = strHTML & "J&nbsp;&nbsp;&nbsp;Decrease Width X Axis<br>" & vbCrLf
strHTML = strHTML & "I&nbsp;&nbsp;&nbsp;Increase Height Y Axis<br>" & vbCrLf
strHTML = strHTML & "K&nbsp;&nbsp;&nbsp;Decrease Height Y Axis<br>" & vbCrLf
strHTML = strHTML & "(space)&nbsp;&nbsp;&nbsp;Restart at 0<br>" & vbCrLf
strHTML = strHTML & "X&nbsp;&nbsp;&nbsp;Exit</b></font>" & vbCrLf
strHTML = strHTML & "</div>" & vbCrLf

'The current orbit information
strHTML = strHTML & "<div id=""Settings"" style=""position: absolute;width: 180px; height: 100px;top: 600px;left: 710px;""> </div>"
strHTML = strHTML & "<IMG ID=""picOrbitBall"" style=""position: absolute;"" src=""http://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball0.png"">" & vbCrLf
objIE.Document.Body.InnerHTML = strHTML

'The sleep here is only necessary if the database connections happen very quickly
'Wscript.Sleep 500

'Database configuration
strDatabase = "MyDB"

On Error Resume Next

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

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  ROUND((SIN(DX * (SPEED * COUNTER)) * WIDTH/2) + WIDTH/2) X," & vbCrLf
strSQL = strSQL & "  ROUND((COS(DY * (SPEED * COUNTER)) * HEIGHT/2) + HEIGHT/2) Y" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  (SELECT" & vbCrLf
strSQL = strSQL & "    ? DX," & vbCrLf
strSQL = strSQL & "    ? DY," & vbCrLf
strSQL = strSQL & "    ? SPEED," & vbCrLf
strSQL = strSQL & "    ? DELAY," & vbCrLf
strSQL = strSQL & "    ? WIDTH," & vbCrLf
strSQL = strSQL & "    ? HEIGHT," & vbCrLf
strSQL = strSQL & "    LEVEL COUNTER" & vbCrLf
strSQL = strSQL & "  FROM" & vbCrLf
strSQL = strSQL & "    DUAL" & vbCrLf
strSQL = strSQL & "  CONNECT BY" & vbCrLf
strSQL = strSQL & "    LEVEL<=3600)"

With comData
'Set up the command properties
.CommandText = strSQL
.CommandTimeout = 30

.ActiveConnection = dbDatabase

End With

comData("dx") = 0.1
comData("dy") = 0.1
comData("speed") = 1
comData("delay") = 1
comData("width") = 600
comData("height") = 600

Set snpData = comData.Execute

'Retrieve up to 10,000 data points from Oracle
varData = snpData.GetRows(10000)
snpData.Close
Set snpData = Nothing

Set objOrbitBall = objIE.Document.getElementById("picOrbitBall").Style
Set objOrbitBallPic = objIE.Document.getElementById("picOrbitBall")
Set objCommand = objIE.Document.All.txtCommand
Set objSettings = objIE.Document.getElementById("Settings")

'Write out the current settings for the orbit
objSettings.InnerText = "DX: " & comData("dx") & Chr(10) & "DY: " & comData("dy") & Chr(10) & _
"Speed: " & comData("speed") &Chr(10) & _
"Width: " & comData("width") & Chr(10) & "Height: " & comData("height")

intQuit = False

Do While intQuit = False
For i = 0 To UBound(varData, 2)
objOrbitBall.Left = CInt(varData(0, i))
objOrbitBall.Top = CInt(varData(1, i))
objOrbitBallPic.Src = "http://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball" & cStr(i/2 Mod 8 ) & ".png"

Wscript.Sleep 50
Select Case Left(objCommand.Value, 1)
Case "=", "+"
comData("delay") = comData("delay") + 1
Case "-"
comData("delay") = comData("delay") - 1
Case "/"
comData("speed") = comData("speed") + 0.1
Case "\"
comData("speed") = comData("speed") - 0.1
Case "W", "w"
comData("dy") = comData("dy") + 0.0005
Case "S", "s"
comData("dy") = comData("dy") - 0.0005
Case "D", "d"
comData("dx") = comData("dx") + 0.0005
Case "A", "a"
comData("dx") = comData("dx") - 0.0005
Case "I", "i"
comData("height") = comData("height") + 5
Case "K", "k"
comData("height") = comData("height") - 5
Case "L", "l"
comData("width") = comData("width") + 5
Case "J", "j"
comData("width") = comData("width") - 5
Case "X", "x"
intQuit = True
Exit For
Case " "
'Reset the loop from the beginning
objCommand.Value = ""
Exit For
End Select

If objCommand.Value <> "" Then
objCommand.Value = ""

Set snpData = comData.Execute

'Retrieve up to 10,000 data points from Oracle
varData = snpData.GetRows(10000)

snpData.Close
Set snpData = Nothing

'Write out the current settings for the orbit
objSettings.InnerText = "DX: " & comData("dx") & Chr(10) & "DY: " & comData("dy") & Chr(10) & _
"Speed: " & comData("speed") &Chr(10) & _
"Width: " & comData("width") & Chr(10) & "Height: " & comData("height")
End If
Next
Loop

objIE.quit
dbDatabase.Close
Set dbDatabase = Nothing
Set objIE = Nothing
End Sub```

A Circular Orbit:

————-

An Orbit that Changes from a Circular Orbit to a Straight Line:

————-

An Orbit where the Ball Bounces Between the Top and Bottom of the Window:

—————————

As written, the script assumes a minimum of 930 x 820 resolution (1080p resolution or greater should work without any problems).  Adjust the script as necessary for lower resolution screens.  The program written in C certainly is shorter than the moderized version of the program, and had a bit more wow factor prior to the widespread use of Windows and other graphical user interfaces.

## Graphical Work Center Utilization – Creating the Demo Data and Active Server Page

1 09 2010

September 1, 2010

Today’s blog article provides a graphical view of production work areas on a factory floor, providing feedback to indicate when the production area is in use.  The blog article includes a classic ASP web page which uses VBScript to write the web page on the fly to the browser on the client computer.  The web page written to the client browser automatically refreshes itself every 30 seconds, automatically advancing the view time by 15 minutes, displaying the production work areas that were in use during the new view time (the time displayed at the top of the page).

The ASP web page is the simple part of today’s blog article (although enabling classic ASP support may be a little challenging), while creating the demo data is actually the challenging portion of the article.  First, we need a table that will define the production work areas and the locations of those areas on the factory floor:

```CREATE TABLE RESOURCE_LOCATION_DEMO (
RESOURCE_ID VARCHAR2(15),
DESCRIPTION VARCHAR2(30),
LOCATION_LEFT NUMBER(12,4),
LOCATION_TOP NUMBER(12,4),
LOCATION_WIDTH NUMBER(12,4),
LOCATION_HEIGHT NUMBER(12,4),
PRIMARY KEY (RESOURCE_ID));```

To keep things interesting, I do not want to just place the first production work area next to the second, the second next to the third, etc.  Instead, I want to randomly locate the production work areas on the factory floor, making certain that no two work areas overlap.  We can accomplish this by creating a list of numbers and ordering the numbers in a random sequence, like this:

```SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=200
ORDER BY
DBMS_RANDOM.VALUE;

RN
----
191
165
122
12
48
27
104
...
198
168
150

200 rows selected.```

Now, to locate the production work areas, imagine that we permitted 10 work areas horizontally (along the X axis) across the factory floor.  We can use the above number sequence along with the MOD function to determine the horizontal location of the work areas, and the FLOOR function to determine the vertical location of the work areas (note that each time we run this SQL statement will we receive different results):

```SELECT
'MA'||TO_CHAR(ROWNUM) RESOURCE_ID,
'Shop Floor Machine '||TO_CHAR(ROWNUM) DESCRIPTION,
MOD(RN,10) BOX_LEFT,
FLOOR(RN/10) BOX_TOP
FROM
(SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=200
ORDER BY
DBMS_RANDOM.VALUE)
WHERE
ROWNUM<=50;

RESOURCE_ID DESCRIPTION                 BOX_LEFT    BOX_TOP
----------- ------------------------- ---------- ----------
MA1         Shop Floor Machine 1               4         14
MA2         Shop Floor Machine 2               9          6
MA3         Shop Floor Machine 3               5          2
MA4         Shop Floor Machine 4               5          9
MA5         Shop Floor Machine 5               7         18
MA6         Shop Floor Machine 6               9          4
MA7         Shop Floor Machine 7               0          8
MA8         Shop Floor Machine 8               6          6
MA9         Shop Floor Machine 9               5          5
MA10        Shop Floor Machine 10              7         15
...
MA49        Shop Floor Machine 49              2         11
MA50        Shop Floor Machine 50              8          7```

It would be too boring to assume that each of the production work areas is exactly the same width and height, so we will add a little more randomization.  Additionally, I want each production area to be up to 1.5 units wide and up to 1.0 units tall, both offset 0.25 units from the top-left (we are dealing with screen coordinates here, where positive Y is the same as mathematical -Y).  While there are up to 200 locations on the factory floor for work areas, we will only define 50 work areas (controlled by the ROWNUM<=50 predicate at the end of the SQL statement):

```SELECT
'MA'||TO_CHAR(ROWNUM) RESOURCE_ID,
'Shop Floor Machine '||TO_CHAR(ROWNUM) DESCRIPTION,
(MOD(RN,10))*1.5 + 0.25 LOCATION_LEFT,
(FLOOR(RN/10))*1.0 + 0.25 LOCATION_TOP,
ROUND(1.5*DBMS_RANDOM.VALUE,4) LOCATION_WIDTH,
ROUND(1.0*DBMS_RANDOM.VALUE,4) LOCATION_HEIGHT
FROM
(SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=200
ORDER BY
DBMS_RANDOM.VALUE)
WHERE
ROWNUM<=50;

RESOURCE_ID DESCRIPTION               LOCATION_LEFT LOCATION_TOP LOCATION_WIDTH LOCATION_HEIGHT
----------- ------------------------- ------------- ------------ -------------- ---------------
MA1         Shop Floor Machine 1               3.25        18.25         1.2386           .7948
MA2         Shop Floor Machine 2               4.75        11.25          .6078           .9578
MA3         Shop Floor Machine 3               1.75        12.25          .5318            .457
MA4         Shop Floor Machine 4               3.25        13.25         1.2908           .9813
MA5         Shop Floor Machine 5                .25        16.25          .3245           .4644
MA6         Shop Floor Machine 6              12.25        15.25           .239           .3822
MA7         Shop Floor Machine 7               1.75        18.25          .0159           .8868
MA8         Shop Floor Machine 8               1.75        16.25          .3948           .8511
MA9         Shop Floor Machine 9              12.25         6.25          .4856           .3356
MA10        Shop Floor Machine 10             13.75        11.25         1.2619           .6124
...
MA49        Shop Floor Machine 49              7.75        16.25          .6664           .6938
MA50        Shop Floor Machine 50              9.25        15.25         1.3449           .6606```

Now that we have tested the results, let’s insert a new set of similar random values into the RESOURCE_LOCATION_DEMO table, and display some of the inserted rows:

```INSERT INTO
RESOURCE_LOCATION_DEMO
SELECT
'MA'||TO_CHAR(ROWNUM) RESOURCE_ID,
'Shop Floor Machine '||TO_CHAR(ROWNUM) DESCRIPTION,
(MOD(RN,10))*1.5 + 0.25 LOCATION_LEFT,
(FLOOR(RN/10))*1.0 + 0.25 LOCATION_TOP,
ROUND(1.5*DBMS_RANDOM.VALUE,4) LOCATION_WIDTH,
ROUND(1.0*DBMS_RANDOM.VALUE,4) LOCATION_HEIGHT
FROM
(SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=200
ORDER BY
DBMS_RANDOM.VALUE)
WHERE
ROWNUM<=50;

COMMIT;

SELECT
*
FROM
RESOURCE_LOCATION_DEMO;

RESOURCE_ID DESCRIPTION               LOCATION_LEFT LOCATION_TOP LOCATION_WIDTH LOCATION_HEIGHT
----------- ------------------------- ------------- ------------ -------------- ---------------
MA1         Shop Floor Machine 1              10.75        13.25           .104           .2165
MA2         Shop Floor Machine 2               7.75        18.25         1.2291            .478
MA3         Shop Floor Machine 3               9.25        16.25          .3431           .4364
MA4         Shop Floor Machine 4               1.75        15.25          .3665           .7278
MA5         Shop Floor Machine 5               4.75        15.25          .6842           .4507
MA6         Shop Floor Machine 6               4.75        18.25          .1384           .6434
MA7         Shop Floor Machine 7               4.75         7.25          .7448           .2178
MA8         Shop Floor Machine 8               7.75          .25          .3756            .499
MA9         Shop Floor Machine 9               1.75        18.25         1.0155           .0769
MA10        Shop Floor Machine 10              7.75        13.25         1.1892           .7518
...
MA49        Shop Floor Machine 49              6.25         3.25           .278           .6513
MA50        Shop Floor Machine 50               .25        15.25          .5216           .9607```

To translate the above storage units (maybe in inch scale) into screen units we will multiply the storage units by 96 (96 dots per inch) and then multiply by the zoom percent (75% = 0.75).

```SELECT
RESOURCE_ID,
ROUND(LOCATION_LEFT*96 *0.75) LOCATION_LEFT,
ROUND(LOCATION_TOP*96 *0.75) LOCATION_TOP,
ROUND(LOCATION_WIDTH*96 *0.75) LOCATION_WIDTH,
ROUND(LOCATION_HEIGHT*96 *0.75) LOCATION_HEIGHT
FROM
RESOURCE_LOCATION_DEMO;

RESOURCE_ID LOCATION_LEFT LOCATION_TOP LOCATION_WIDTH LOCATION_HEIGHT
----------- ------------- ------------ -------------- ---------------
MA1                   774          954              7              16
MA2                   558         1314             88              34
MA3                   666         1170             25              31
MA4                   126         1098             26              52
MA5                   342         1098             49              32
MA6                   342         1314             10              46
MA7                   342          522             54              16
MA8                   558           18             27              36
MA9                   126         1314             73               6
MA10                  558          954             86              54
...```

Next, we need a table to maintain the time periods in which each of the production work areas was in use, and by whom the work areas were used:

```CREATE TABLE LABOR_TICKET_DEMO (
TRANSACTION_ID NUMBER,
RESOURCE_ID VARCHAR2(15),
EMPLOYEE_ID VARCHAR2(15),
CLOCK_IN DATE,
CLOCK_OUT DATE,
PRIMARY KEY (TRANSACTION_ID));```

Let’s see if we are able to generate some random data for the table:

```ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI';

SELECT
ROWNUM TRANSACTION_ID,
'MA'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,50))) RESOURCE_ID,
'EMP'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,300))) EMPLOYEE_ID,
TRUNC(SYSDATE)+DBMS_RANDOM.VALUE CLOCK_IN
FROM
DUAL
CONNECT BY
LEVEL<=10;

TRANSACTION_ID RESOURCE_ID EMPLOYEE_ID CLOCK_IN
-------------- ----------- ----------- -----------------
1 MA29        EMP50       01-SEP-2010 01:56
2 MA35        EMP181      01-SEP-2010 10:06
3 MA13        EMP172      01-SEP-2010 17:40
4 MA21        EMP182      01-SEP-2010 09:00
5 MA14        EMP80       01-SEP-2010 09:53
6 MA4         EMP80       01-SEP-2010 19:04
7 MA7         EMP110      01-SEP-2010 14:34
8 MA45        EMP19       01-SEP-2010 22:05
9 MA10        EMP207      01-SEP-2010 21:51
10 MA46        EMP127      01-SEP-2010 16:49```

That worked, but note that we did not generate a CLOCK_OUT time – we want to make certain that the CLOCK_OUT time is after the CLOCK_IN time, but we simply cannot do that with the above SQL statement as written.  We slide the above into an inline view and then set the CLOCK_OUT time to be up to 12 hours after the CLOCK_IN time (DBMS_RANDOM.VALUE by default returns a value between 0 and 1, so if we divide that value by 2, we can add up to 1/2 of a day to the CLOCK_IN date and time):

```INSERT INTO
LABOR_TICKET_DEMO
SELECT
TRANSACTION_ID,
RESOURCE_ID,
EMPLOYEE_ID,
CLOCK_IN,
CLOCK_IN + (DBMS_RANDOM.VALUE/2) CLOCK_OUT
FROM
(SELECT
ROWNUM TRANSACTION_ID,
'MA'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,50))) RESOURCE_ID,
'EMP'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,300))) EMPLOYEE_ID,
TRUNC(SYSDATE)+DBMS_RANDOM.VALUE CLOCK_IN
FROM
DUAL
CONNECT BY
LEVEL<=1000);

COMMIT;```

Let’s take a look at what made it into the table (your results will be different):

```SELECT
*
FROM
LABOR_TICKET_DEMO
ORDER BY
TRANSACTION_ID;

TRANSACTION_ID RESOURCE_ID     EMPLOYEE_ID     CLOCK_IN          CLOCK_OUT
-------------- --------------- --------------- ----------------- -----------------
1 MA34            EMP49           01-SEP-2010 20:32 02-SEP-2010 08:18
2 MA47            EMP230          01-SEP-2010 20:08 02-SEP-2010 03:06
3 MA20            EMP257          01-SEP-2010 02:17 01-SEP-2010 05:44
4 MA21            EMP129          01-SEP-2010 09:37 01-SEP-2010 15:41
5 MA18            EMP214          01-SEP-2010 18:57 01-SEP-2010 20:57
6 MA46            EMP173          01-SEP-2010 05:51 01-SEP-2010 15:32
7 MA34            EMP224          01-SEP-2010 20:23 02-SEP-2010 08:17
8 MA31            EMP8            01-SEP-2010 02:56 01-SEP-2010 14:02
9 MA37            EMP178          01-SEP-2010 09:28 01-SEP-2010 16:03
10 MA8             EMP31           01-SEP-2010 20:17 02-SEP-2010 05:51
...
999 MA43            EMP138          01-SEP-2010 05:07 01-SEP-2010 05:23
1000 MA2             EMP235          01-SEP-2010 05:29 01-SEP-2010 13:28```

We now have 1000 transactions scattered across the 50 work areas (RESOURCE_ID column).  What we next want to determine is which of the work areas was in use at a specific time of the day.  Because we eventually will want only one row per unique RESOURCE_ID value, we will use the ROW_NUMBER analytic function to number each of the rows within each unique RESOURCE_ID value:

```SELECT
RESOURCE_ID,
EMPLOYEE_ID,
TRUNC(SYSDATE) + (1.25)/24 CHECK_TIME,
CLOCK_IN,
CLOCK_OUT,
ROW_NUMBER() OVER (PARTITION BY RESOURCE_ID ORDER BY CLOCK_IN) RN
FROM
LABOR_TICKET_DEMO
WHERE
CLOCK_IN<=TRUNC(SYSDATE) + (1.25)/24
AND CLOCK_OUT>TRUNC(SYSDATE) + (1.25)/24
ORDER BY
RESOURCE_ID,
CLOCK_IN;

RESOURCE_ID  EMPLOYEE_ID  CHECK_TIME        CLOCK_IN          CLOCK_OUT         RN
------------ ------------ ----------------- ----------------- ----------------- --
MA10         EMP192       01-SEP-2010 01:15 01-SEP-2010 00:21 01-SEP-2010 05:44  1
MA10         EMP233       01-SEP-2010 01:15 01-SEP-2010 00:23 01-SEP-2010 02:42  2
MA16         EMP114       01-SEP-2010 01:15 01-SEP-2010 00:21 01-SEP-2010 04:48  1
MA18         EMP261       01-SEP-2010 01:15 01-SEP-2010 00:18 01-SEP-2010 07:02  1
MA18         EMP133       01-SEP-2010 01:15 01-SEP-2010 00:32 01-SEP-2010 04:35  2
MA2          EMP62        01-SEP-2010 01:15 01-SEP-2010 01:14 01-SEP-2010 12:03  1
MA21         EMP235       01-SEP-2010 01:15 01-SEP-2010 00:05 01-SEP-2010 10:42  1
MA22         EMP4         01-SEP-2010 01:15 01-SEP-2010 00:01 01-SEP-2010 06:27  1
MA22         EMP300       01-SEP-2010 01:15 01-SEP-2010 01:12 01-SEP-2010 11:50  2
MA23         EMP135       01-SEP-2010 01:15 01-SEP-2010 00:35 01-SEP-2010 05:19  1
MA25         EMP35        01-SEP-2010 01:15 01-SEP-2010 00:20 01-SEP-2010 06:58  1
MA28         EMP298       01-SEP-2010 01:15 01-SEP-2010 00:52 01-SEP-2010 06:27  1
MA30         EMP72        01-SEP-2010 01:15 01-SEP-2010 00:56 01-SEP-2010 07:28  1
MA32         EMP84        01-SEP-2010 01:15 01-SEP-2010 01:00 01-SEP-2010 05:25  1
MA34         EMP299       01-SEP-2010 01:15 01-SEP-2010 00:31 01-SEP-2010 12:04  1
MA38         EMP268       01-SEP-2010 01:15 01-SEP-2010 00:31 01-SEP-2010 04:15  1
MA38         EMP278       01-SEP-2010 01:15 01-SEP-2010 00:32 01-SEP-2010 04:50  2
MA38         EMP176       01-SEP-2010 01:15 01-SEP-2010 01:01 01-SEP-2010 04:01  3
MA4          EMP257       01-SEP-2010 01:15 01-SEP-2010 00:10 01-SEP-2010 10:45  1
MA40         EMP231       01-SEP-2010 01:15 01-SEP-2010 00:58 01-SEP-2010 11:01  1
MA43         EMP65        01-SEP-2010 01:15 01-SEP-2010 00:54 01-SEP-2010 09:29  1
MA44         EMP18        01-SEP-2010 01:15 01-SEP-2010 00:07 01-SEP-2010 03:30  1
MA46         EMP36        01-SEP-2010 01:15 01-SEP-2010 00:40 01-SEP-2010 04:57  1
MA48         EMP61        01-SEP-2010 01:15 01-SEP-2010 00:27 01-SEP-2010 10:20  1
MA48         EMP169       01-SEP-2010 01:15 01-SEP-2010 00:44 01-SEP-2010 01:27  2
MA5          EMP147       01-SEP-2010 01:15 01-SEP-2010 00:02 01-SEP-2010 04:48  1
MA6          EMP132       01-SEP-2010 01:15 01-SEP-2010 00:34 01-SEP-2010 09:42  1

27 rows selected.```

In some cases we have up to three employees working in a work area at 1:15AM (the time of day is indicated by the 1.25 value in the SQL statement).  Now, lets eliminate the duplicate rows, leaving just the rows where the calculated RN column is equal to 1.  We will join the above SQL statement in an inline view to the RESOURCE_LOCATION_DEMO table and convert the production work area coordinates to screen coordinates, in this case 96 pixels per unit (inches) at a 75% zoom percent (we made this same screen coordinate conversion in a previous SQL statement above):

```SELECT
RL.RESOURCE_ID,
RL.DESCRIPTION,
ROUND(RL.LOCATION_LEFT*96 *0.75) LOCATION_LEFT,
ROUND(RL.LOCATION_TOP*96 *0.75) LOCATION_TOP,
ROUND(RL.LOCATION_WIDTH*96 *0.75) LOCATION_WIDTH,
ROUND(RL.LOCATION_HEIGHT*96 *0.75) LOCATION_HEIGHT,
LT.EMPLOYEE_ID,
LT.CLOCK_IN,
LT.CLOCK_OUT
FROM
RESOURCE_LOCATION_DEMO RL,
(SELECT
RESOURCE_ID,
EMPLOYEE_ID,
CLOCK_IN,
CLOCK_OUT,
ROW_NUMBER() OVER (PARTITION BY RESOURCE_ID ORDER BY CLOCK_IN) RN
FROM
LABOR_TICKET_DEMO
WHERE
CLOCK_IN<=TRUNC(SYSDATE) + (1.25)/24
AND CLOCK_OUT>TRUNC(SYSDATE) + (1.25)/24) LT
WHERE
RL.RESOURCE_ID=LT.RESOURCE_ID(+)
AND NVL(LT.RN,1)=1
ORDER BY
RL.RESOURCE_ID;

RESOURCE_ID  DESCRIPTION                    LOCATION_LEFT LOCATION_TOP LOCATION_WIDTH LOCATION_HEIGHT EMPLOYEE_ID  CLOCK_IN          CLOCK_OUT
------------ ------------------------------ ------------- ------------ -------------- --------------- ------------ ----------------- -----------------
MA1          Shop Floor Machine 1                     774          954              7              16
MA10         Shop Floor Machine 10                    558          954             86              54 EMP192       01-SEP-2010 00:21 01-SEP-2010 05:44
MA11         Shop Floor Machine 11                    882         1098             29               1
MA12         Shop Floor Machine 12                    234          378             51              51
MA13         Shop Floor Machine 13                    882         1314             83              62
MA14         Shop Floor Machine 14                    558          378             38              61
MA15         Shop Floor Machine 15                    774          522             63              64
MA16         Shop Floor Machine 16                    126          666            103              55 EMP114       01-SEP-2010 00:21 01-SEP-2010 04:48
MA17         Shop Floor Machine 17                    558          234             94              30
MA18         Shop Floor Machine 18                    342          450             85              21 EMP261       01-SEP-2010 00:18 01-SEP-2010 07:02
MA19         Shop Floor Machine 19                    342          666             94              20
MA2          Shop Floor Machine 2                     558         1314             88              34 EMP62        01-SEP-2010 01:14 01-SEP-2010 12:03
MA20         Shop Floor Machine 20                    666          162             33              33
MA21         Shop Floor Machine 21                    774          306             66              22 EMP235       01-SEP-2010 00:05 01-SEP-2010 10:42
MA22         Shop Floor Machine 22                    990          378             78              71 EMP4         01-SEP-2010 00:01 01-SEP-2010 06:27
MA23         Shop Floor Machine 23                    666          666             50              37 EMP135       01-SEP-2010 00:35 01-SEP-2010 05:19
MA24         Shop Floor Machine 24                    990          810            107              45
...```

From the above output, we know the screen coordinates of each production work area (RESOURCE_ID) and the first employee to use the production work area (and the employee was still using it) at 1:15AM.

For the next portion of this blog article, the portion that requires an ASP enabled web server, we need a couple of pictures (these were created using Microsoft Power Point):

Representing a production work center that is in use:

Representing a production work center that is idle:

The factory floor – the background area:

——

Now we need the classic ASP page code – note that the code syntax is very similar to that of the previous VBScript examples.  The script uses Response.Write to write information to the client computer’s web browser, and an embedded Java script to call the post event of the embedded HTML form to update the display as of time every 30 seconds (yes, I should have used bind variables in the SQL statement, but that would have required an extra 120 seconds to code and would have left you with nothing to improve):

```<html>

<meta http-equiv="refresh" content="600">
<title>Graphical Work Center Utilization Animated</title>

<body>
<%
Dim strSQL

Dim sglOriginLeft
Dim sglOriginTop

Dim sglZoom
Dim strZoom

Dim i
Dim intWidth
Dim intHeight
Dim strOffset
Dim sglOffset
Dim varDateTime

Dim snpData
Dim dbDatabase

'Database configuration
strDatabase = "MyDB"

On Error Resume Next

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

'Retrieve the last value for the time offset and the selected zoom percent
strOffset = Request.Form("txtOffset")
strZoom = Request.Form("cboZoom")

'Convert back to a number
sglOffset = CSng(strOffset)
sglZoom = CSng(strZoom) / 100

'Advance to the next 0.25 hour
if (sglOffset = 0) or (sglOffset = 24) then
sglOffset = 0.25
else
sglOffset = sglOffset + 0.25
end if

'Set the zoom percent, if not already set
If sglZoom = 0 Then
sglZoom = 0.5 '50% zoom
End If

varDateTime = DateAdd("n", sglOffset*60, Date) 'Create a printable version of the view date and time

Response.Write "<form name=" & chr(34) & "reportform" & chr(34) & " method=" & chr(34) & "POST" & chr(34) & " action=" & chr(34) & "GraphicalWorkCenterUtilization.asp" & chr(34) & ">" & vbcrlf
Response.Write varDateTime & "  " & vbCrLf
Response.Write "&nbsp;&nbsp;&nbsp;Zoom Percent <select size=""1"" id=""cboZoom"" name=""cboZoom"" style=""width:50"">" & vbCrLf
For i = 10 to 200 Step 10
If sglZoom = i / 100 Then
Response.Write "<option selected value=""" & cStr(i) & """>" & cStr(i) & "</option>"
Else
Response.Write "<option value=""" & cStr(i) & """>" & cStr(i) & "</option>"
End If
Next
Response.Write "</select><br>"
Response.Write "  <input type=" & chr(34) & "submit" & chr(34) & " value=" & chr(34) & "Update View" & chr(34) & " name=" & chr(34) & "cmdViewReport" & chr(34) & ">" & vbcrlf
Response.Write "  <input type=" & chr(34) & "hidden" & chr(34) & " name=" & chr(34) & "txtOffset" & chr(34) & " size=" & chr(34) & "5" & chr(34) & " value=" & chr(34) & cStr(sglOffset) & chr(34) & ">" & vbcrlf
Response.Write "</form>" & vbcrlf

'The background image
intWidth = Round(16 * 96 * sglZoom)
intHeight = Round(20 * 96 * sglZoom)
Response.Write "<img src=" & chr(34) & "http://hoopercharles.files.wordpress.com/2010/09/graphicalworkcenterutilizationbackground.jpg" & chr(34) & " width=" & chr(34) & cstr(intWidth) & chr(34) & " height=" & chr(34) & cstr(intheight) & chr(34) & " style=" & chr(34) & "position:absolute;top:50px;left:0px;z-index:-1" & chr(34) & "/>" & vbcrlf

'The SQL statement developed earlier
strSQL = "SELECT" & VBCrLf
strSQL = strSQL & "  RL.RESOURCE_ID," & VBCrLf
strSQL = strSQL & "  RL.DESCRIPTION," & VBCrLf
strSQL = strSQL & "  ROUND(RL.LOCATION_LEFT*96 *" & cStr(sglZoom) & ") LOCATION_LEFT," & VBCrLf
strSQL = strSQL & "  ROUND(RL.LOCATION_TOP*96 *" & cStr(sglZoom) & ") LOCATION_TOP," & VBCrLf
strSQL = strSQL & "  ROUND(RL.LOCATION_WIDTH*96 *" & cStr(sglZoom) & ") LOCATION_WIDTH," & VBCrLf
strSQL = strSQL & "  ROUND(RL.LOCATION_HEIGHT*96 *" & cStr(sglZoom) & ") LOCATION_HEIGHT," & VBCrLf
strSQL = strSQL & "  LT.EMPLOYEE_ID," & VBCrLf
strSQL = strSQL & "  LT.CLOCK_IN," & VBCrLf
strSQL = strSQL & "  LT.CLOCK_OUT" & VBCrLf
strSQL = strSQL & "FROM" & VBCrLf
strSQL = strSQL & "  RESOURCE_LOCATION_DEMO RL," & VBCrLf
strSQL = strSQL & "  (SELECT" & VBCrLf
strSQL = strSQL & "    RESOURCE_ID," & VBCrLf
strSQL = strSQL & "    EMPLOYEE_ID," & VBCrLf
strSQL = strSQL & "    CLOCK_IN," & VBCrLf
strSQL = strSQL & "    CLOCK_OUT," & VBCrLf
strSQL = strSQL & "    ROW_NUMBER() OVER (PARTITION BY RESOURCE_ID ORDER BY CLOCK_IN) RN" & VBCrLf
strSQL = strSQL & "  FROM" & VBCrLf
strSQL = strSQL & "    LABOR_TICKET_DEMO" & VBCrLf
strSQL = strSQL & "  WHERE" & VBCrLf
strSQL = strSQL & "    CLOCK_IN<=TRUNC(SYSDATE) + (" & cStr(sglOffset) & ")/24" & VBCrLf
strSQL = strSQL & "    AND CLOCK_OUT>TRUNC(SYSDATE) + (" & cStr(sglOffset) & ")/24) LT" & VBCrLf
strSQL = strSQL & "WHERE" & VBCrLf
strSQL = strSQL & "  RL.RESOURCE_ID=LT.RESOURCE_ID(+)" & VBCrLf
strSQL = strSQL & "  AND NVL(LT.RN,1)=1" & VBCrLf
strSQL = strSQL & "ORDER BY" & VBCrLf
strSQL = strSQL & "  RL.RESOURCE_ID"

snpData.open strSQL, dbDatabase

If snpData.State = 1 then
Response.Write "<B><font color=" & chr(34) & "#0000FF" & chr(34) & "><p style=" & chr(34) & "position:absolute;top:15px;left:500px" & chr(34) & ">" & FormatDateTime(cdate(snpData("START_TIME")),2) & " " & FormatDateTime(cdate(snpData("START_TIME")),4) & " - " & FormatDateTime(cdate(snpData("END_TIME")),4) & "</p></font></b>" & vbcrlf

Do While Not snpData.EOF
If Not(IsNull(snpData("employee_id"))) Then
'A labor ticket was in process during this time period
Response.Write "<img alt=" & Chr(34) & snpData("resource_id") & "  " & snpData("description") & vbCrlf & snpData("employee_id") & "(" & snpData("clock_in") & " - " & snpData("clock_out") & ")" & Chr(34) & " src=" & chr(34) & "http://hoopercharles.files.wordpress.com/2010/09/graphicalworkcenterutilizationrunning.jpg" & chr(34) & " width=" & chr(34) & cStr(snpData("location_width")) & chr(34) & " height=" & chr(34) & cStr(snpData("location_height")) & chr(34) & " style=" & chr(34) & "position:absolute;top:" & cStr(cLng(snpData("location_top")) + 40) & "px;left:" & cStr(snpData("location_left")) & "px" & chr(34) & "/>" & vbcrlf
'Write the title down 1 pixel
Response.Write "<B><font color=" & chr(34) & "#00FFFF" & chr(34) & "><p style=" & chr(34) & "position:absolute;top:" & cStr(Round(41 + CSng(snpData("location_top")))) & "px;left:" & cStr(Round(CSng(snpData("location_left")))) & "px" & chr(34) & ">" & snpData("resource_id") & "</p></font></b>" & vbcrlf
Else
'No labor ticket was in process during this time period
Response.Write "<img alt=" & Chr(34) & snpData("resource_id") & "  " & snpData("description") & Chr(34) & " src=" & chr(34) & "http://hoopercharles.files.wordpress.com/2010/09/graphicalworkcenterutilizationstopped.jpg" & chr(34) & " width=" & chr(34) & cStr(snpData("location_width")) & chr(34) & " height=" & chr(34) & cStr(snpData("location_height")) & chr(34) & " style=" & chr(34) & "position:absolute;top:" & cStr(cLng(snpData("location_top")) + 40) & "px;left:" & cStr(snpData("location_left")) & "px" & chr(34) & "/>" & vbcrlf
'Write the title down 1 pixel
Response.Write "<B><font color=" & chr(34) & "#FF0000" & chr(34) & "><p style=" & chr(34) & "position:absolute;top:" & cStr(Round(41 + CSng(snpData("location_top")))) & "px;left:" & cStr(Round(CSng(snpData("location_left")))) & "px" & chr(34) & ">" & snpData("resource_id") & "</p></font></b>" & vbcrlf
End If
snpData.movenext
Loop
End if

snpData.Close
dbDatabase.Close

Set snpData = Nothing
Set dbDatabase = Nothing
%>

<script language="JavaScript1.2">
function NextInterval(){
reportform.submit();
}

setTimeout("NextInterval()",30000)
</script>
</body>

</html>```

GraphicalWorkCenterUtilization.asp (save as GraphicalWorkCenterUtilization.asp on a web server that supports classic ASP pages)

Below are samples of the output as the display as of time advanced – the zoom percent was set to 50.  Notice that the work centers go online and offline as the time progresses (click a picture to see a larger version of that picture):

The final example demonstrates how the display changed when the zoom percent was changed from 50% to 130%:

-

As the mouse pointer is moved over the boxes representing the work centers, a pop-up tooltip appears that describes the work center, as well as employee ID, clock in time, and clock out time for the first labor ticket at that work center in the time period.

——-

Hopefully, you have found this example to be interesting.  Your assignment is to now connect proximity switches to the devices in your office and surrounding areas, recording their location in the RESOURCE_LOCATION_DEMO table.  Then log the proximity switch status to the LABOR_TICKET_DEMO table so that you are able to determine when the water cooler, coffee machine, chairs, keyboards, and computer mice are in use.  Use the data to determine which employees are the hardest working, and which employees have determined how to think smarter rather than working harder.

## Oracle Logging Trigger Creator

25 08 2010

August 25, 2010

In a multi-user database environment it seems that sometimes changes just magically appear in ERP (or other) data.  The questions swirl, “Who changed that record?”; “I know that employee ABC arrived on time yesterday, why does the computer show he arrived late?”; “That new record is missing vital information, who created it?”, etc.  There are auditing features built into Oracle Database, and it is possible to review the redo log contents with LogMiner, but what if you just need a simple way to quietly record changes made to a table’s data?  Sure, triggers could be manually crafted to log changes, but it is a time consuming task, and consistency from one logging table/trigger to the next is difficult to maintain.  Well, this seems to be enough justification to develop a solution in search of a suitable problem, for which the solution will solve (we always invent the solution first, and then search for suitable problems that can be fixed, right?).

Today’s article will offer a VBS script that connects to an Oracle Database, and displays table definitions for all tables in the database using Internet Explorer as the script’s user interface.  For example, the following screen capture shows the tables in the TESTUSER schema with the definition of the LOCATIONS table displayed.  As shown, this table contains two VARCHAR2(15) columns in the primary key, a typical VARCHAR2(80) column, and a CHAR(1) column with a NOT NULL constraint.

—-

Switching to the PARTS table, we see that there is much more to the VBS script that automatically creates logging triggers and the table to maintain the captured data.  For each column in the table we are able to specify whether or not a change in a column’s value will cause a row to be inserted into the logging table – the “Trigger On Change” column in the VBS script’s user interface allows the user to specify the columns that cause the trigger to fire.  The “Log Values” column determines what data will be captured when the trigger fires:

• Both New and Old (capture the prior values before the change and the values after the change)
• New Values (capture only the values after the change)
• Old Values (capture only the values before the change)
• New Values and Delete (capture only the values after the change, unless the row is being deleted – then capture the values from before the change)
• Old Values and Insert (capture only the values before the change, unless the row is being inserted – then capture the values from the insert statement)
• Do Not Log (no data is captured for this column)

Clicking on the Create Data Log Script button builds the script which may be used to create the logging table and the logging trigger.  By default the logging table will be owned by the DATALOG user, although that can be changed within the VBS script.  The generated logging script displayed on the screen should look like this when the above selections are made:

(Added January 6, 2013: Sample trigger code shown above in a text file, save as TRIGGER_DATALOG_or112p_HPM_LOG_PARTS.SQL - without the .DOC extension.)

Notice that the generated script provides details about the purpose of the script, how to execute the script, what passwords need to be specified, sample commands to create a DATALOG tablespace and DATALOG user, the SQL to create the logging table, and the SQL to create the logging trigger.  By design, the default behavior of the script is easy to modify.

The script logic starts in the StartUp sub:

```Sub StartUp()
Dim strSQL
Dim intResult
Dim intFlag

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

objIE.Document.Title = "Charles Hoopers Oracle Logging Trigger Creator"
objIE.Visible = True

'Database configuration
strDatabase = "MyDB"

On Error Resume Next

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

strTableOwner = "TESTUSER"  'Set to the default owner name to be selected, possibly same as strUsername
strDataLogSchema = "DATALOG"```

As was the case for VBS scripts appearing in earlier articles that interact with the database, you must modify the database connection details, specifying username, password, and database.  Additionally, this script permits setting the default table schema (strTableOwner) and the default logging schema (strDataLogSchema) that display in the script’s user interface.  The script runs in a tight loop (sleeping for 0.5 seconds) in the StartUp sub until the script ends.  Within that loop the script reacts to changes in the Internet Explorer window, such as a selection of a different table name, or clicking the Create Data Log Script button.

When the user selects a different Table Owner the script executes the TableOwnerChange procedure which retrieves a list of the tables that reside in the selected user’s schema (note that this means the username used to run the script must have access to the various DBA_ views).  When a Table Name is selected, the TableNameChange procedure is executed which retrieves the definition for that table, and displays that definition in the Internet Explorer supplied user interface.  When the Create Data Log Script button is clicked the CreateLoggingScript procedure is executed, which writes the SQL*Plus compatible script to the root of the C:\ drive, and then displays the script on the screen.

If we run the script that was generated (after setting the correct passwords in the script), we should see something like this:

```SQL> @C:\TRIGGER_DATALOG_or112p_HPM_LOG_PARTS.SQL
Connected.
DROP TABLE DATALOG.HPM_LOG_PARTS
*
ERROR at line 1:
ORA-00942: table or view does not exist```

Note that the script attempted to drop an old logging table named DATALOG.HPM_LOG_PARTS at the start of the script, and that is what triggered the ORA-00942 error – just ignore this error message.

The PARTS table in this database has a definition that looks like this (it was a table created in one of my scripts mentioned in this article Faulty Quotes 5 – Block Sizes):

```CREATE TABLE PARTS (
PART_ID VARCHAR2(30) NOT NULL ENABLE,
DESCRIPTION VARCHAR2(40),
STOCK_UM VARCHAR2(15) NOT NULL ENABLE,
PLANNING_LEADTIME NUMBER DEFAULT 0 NOT NULL ENABLE,
ORDER_POLICY CHAR(1) DEFAULT 'M' NOT NULL ENABLE,
ORDER_POINT NUMBER(14,4),
SAFETY_STOCK_QTY NUMBER(14,4),
FIXED_ORDER_QTY NUMBER(14,4),
DAYS_OF_SUPPLY NUMBER,
MINIMUM_ORDER_QTY NUMBER(14,4),
MAXIMUM_ORDER_QTY NUMBER(14,4),
ENGINEERING_MSTR VARCHAR2(3),
PRODUCT_CODE VARCHAR2(15),
COMMODITY_CODE VARCHAR2(15),
MFG_NAME VARCHAR2(30),
MFG_PART_ID VARCHAR2(30),
FABRICATED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
PURCHASED CHAR(1) DEFAULT 'Y' NOT NULL ENABLE,
STOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
DETAIL_ONLY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
DEMAND_HISTORY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
TOOL_OR_FIXTURE CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
INSPECTION_REQD CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
WEIGHT NUMBER(14,4),
WEIGHT_UM VARCHAR2(15),
DRAWING_ID VARCHAR2(15),
DRAWING_REV_NO VARCHAR2(8),
PREF_VENDOR_ID VARCHAR2(15),
PRIMARY_WHS_ID VARCHAR2(15),
PRIMARY_LOC_ID VARCHAR2(15),
BACKFLUSH_WHS_ID VARCHAR2(15),
BACKFLUSH_LOC_ID VARCHAR2(15),
INSPECT_WHS_ID VARCHAR2(15),
INSPECT_LOC_ID VARCHAR2(15),
MRP_REQUIRED CHAR(1) DEFAULT 'N',
MRP_EXCEPTIONS CHAR(1) DEFAULT 'N',
PRIVATE_UM_CONV CHAR(1) DEFAULT 'N',
AUTO_BACKFLUSH CHAR(1) DEFAULT 'Y',
PLANNER_USER_ID VARCHAR2(20),
ABC_CODE CHAR(1),
ANNUAL_USAGE_QTY NUMBER(15,4),
INVENTORY_LOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
UNIT_MATERIAL_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
UNIT_LABOR_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
UNIT_BURDEN_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
UNIT_SERVICE_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,
BURDEN_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
PURC_BUR_PERCENT NUMBER(6,3) DEFAULT 0 NOT NULL ENABLE,
PURC_BUR_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
UNIT_PRICE NUMBER(20,6),
NEW_MATERIAL_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
NEW_LABOR_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
NEW_BURDEN_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
NEW_SERVICE_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
NEW_BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,
NEW_BURDEN_PERUNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
NEW_FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
MAT_GL_ACCT_ID VARCHAR2(30),
LAB_GL_ACCT_ID VARCHAR2(30),
BUR_GL_ACCT_ID VARCHAR2(30),
SER_GL_ACCT_ID VARCHAR2(30),
QTY_ON_HAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
QTY_AVAILABLE_ISS NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
QTY_AVAILABLE_MRP NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
QTY_ON_ORDER NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
QTY_IN_DEMAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
USER_1 VARCHAR2(80),
USER_2 VARCHAR2(80),
USER_3 VARCHAR2(80),
USER_4 VARCHAR2(80),
USER_5 VARCHAR2(80),
USER_6 VARCHAR2(80),
USER_7 VARCHAR2(80),
USER_8 VARCHAR2(80),
USER_9 VARCHAR2(80),
USER_10 VARCHAR2(80),
LT_PLUS_DAYS NUMBER,
LT_MINUS_DAYS NUMBER,
STATUS CHAR(1),
USE_SUPPLY_BEF_LT CHAR(1),
QTY_COMMITTED NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
PRT_CREATE_USER_ID VARCHAR2(30) DEFAULT USER,
PRT_CREATE_DATE DATE DEFAULT SYSDATE,
CONSTRAINT "CHK_PART1" CHECK (
AND (ORDER_POLICY = 'N'
Or ORDER_POLICY = 'M'
Or ORDER_POLICY = 'F'
Or ORDER_POLICY = 'E'
Or ORDER_POLICY = 'D'
Or ORDER_POLICY = 'P')
AND (ORDER_POINT >= 0)
AND (SAFETY_STOCK_QTY >= 0)
AND (FIXED_ORDER_QTY >= 0)
AND (DAYS_OF_SUPPLY >= 0)
AND (MINIMUM_ORDER_QTY >= 0)
AND (MAXIMUM_ORDER_QTY >= 0)
AND (FABRICATED = 'Y' Or FABRICATED = 'N')
AND (PURCHASED = 'Y' Or PURCHASED = 'N')
AND (STOCKED = 'Y' Or STOCKED = 'N')
AND (DETAIL_ONLY = 'Y' Or DETAIL_ONLY = 'N')
AND (DEMAND_HISTORY = 'Y' Or DEMAND_HISTORY = 'N')
AND (TOOL_OR_FIXTURE = 'Y' Or TOOL_OR_FIXTURE = 'N')
AND (MRP_REQUIRED = 'Y' Or MRP_REQUIRED = 'N')
AND (MRP_EXCEPTIONS = 'Y' Or MRP_EXCEPTIONS = 'N')
AND (PRIVATE_UM_CONV = 'Y' Or PRIVATE_UM_CONV = 'N')
AND (INVENTORY_LOCKED = 'Y' Or INVENTORY_LOCKED = 'N')
AND (INSPECTION_REQD = 'Y' Or INSPECTION_REQD = 'N')) ENABLE,
PRIMARY KEY (PART_ID),
CONSTRAINT "FKEY_INSP" FOREIGN KEY (INSPECT_WHS_ID, INSPECT_LOC_ID)
REFERENCES LOCATIONS (WAREHOUSE_ID, LOCATION_ID) ENABLE,
CONSTRAINT "FKEY_PREF_VENDOR" FOREIGN KEY (PREF_VENDOR_ID)
REFERENCES VENDORS (VENDOR_ID) ENABLE,
CONSTRAINT "FKEY_UM" FOREIGN KEY (WEIGHT_UM)
REFERENCES UMS (UNIT_OF_MEASURE) ENABLE,
CONSTRAINT "FKEY_STOCK_UM" FOREIGN KEY (STOCK_UM)
REFERENCES UMS (UNIT_OF_MEASURE) ENABLE);```

Yes, it is an ugly, long table definition that references a couple of additional tables.  Now, let’s make some changes to the above table:

```INSERT INTO PARTS (
PART_ID,
DESCRIPTION,
STOCK_UM,
ORDER_POLICY,
FABRICATED,
PURCHASED,
STOCKED,
DETAIL_ONLY,
DEMAND_HISTORY,
TOOL_OR_FIXTURE,
INSPECTION_REQD,
INVENTORY_LOCKED,
UNIT_MATERIAL_COST,
UNIT_LABOR_COST,
UNIT_BURDEN_COST,
UNIT_SERVICE_COST,
BURDEN_PERCENT,
BURDEN_PER_UNIT,
PURC_BUR_PERCENT,
PURC_BUR_PER_UNIT,
FIXED_COST,
UNIT_PRICE,
NEW_MATERIAL_COST,
NEW_LABOR_COST,
NEW_BURDEN_COST,
NEW_SERVICE_COST,
NEW_BURDEN_PERCENT,
NEW_BURDEN_PERUNIT,
NEW_FIXED_COST,
QTY_ON_HAND,
QTY_AVAILABLE_ISS,
QTY_AVAILABLE_MRP,
QTY_ON_ORDER,
QTY_IN_DEMAND,
QTY_COMMITTED)
SELECT
'AA'||ROWNUM,
'WATCH ME',
'EA',
0,
'N',
'Y',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
100,
0,
0,
0,
0,
0,
0,
0,
0,
109,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM
DUAL
CONNECT BY
LEVEL<=5;

5 rows created.```

The above added 5 rows to the PARTS table.  Now let’s modify those rows:

```UPDATE
PARTS
SET
DESCRIPTION='CHANGED'
WHERE
PART_ID IN ('AA1','AA2');

2 rows updated.

UPDATE
PARTS
SET
SAFETY_STOCK_QTY=10,
STOCK_UM='PC',
FIXED_ORDER_QTY=10
WHERE
PART_ID IN ('AA1','AA4','A5');

2 rows updated.

UPDATE
PARTS
SET
SAFETY_STOCK_QTY=10,
STOCK_UM='PC',
FIXED_ORDER_QTY=10
WHERE
PART_ID IN ('AA1','AA4','AA5');

3 rows updated.

DELETE FROM
PARTS
WHERE
PART_ID IN ('AA2','AA3','AA4','AA5');

4 rows deleted.

COMMIT;

SELECT
COUNT(*)
FROM
DATALOG.HPM_LOG_PARTS;

COUNT(*)
----------
14```

As indicated by the above output, our logging table now has 14 rows of data.  Let’s view that data using a spreadsheet program (in this case Excel 2010):

Obviously, it is important to be smart when deciding which columns to capture, whether the old and/or new values should be captured, and changes to which columns should cause the trigger to fire.  So, what was the inspiration for this article?  One of the features in my Toy Project for Performance Tuning:

The VBS script should work on client computers running Windows 2000 or above.  Have fun with the script, and let me know about any improvements that you make to the script (there were a couple of neat improvements to the Automated DBMS XPLAN Viewer script).

## Oracle Statistics Chart Viewer

11 07 2010

July 11, 2010

Last week I provided a blog article that created a horizonal orientation auto-scaling HTML-based bar chart using nothing more than a SQL statement.  At first glace the final SQL statement would likely appear to be very complicated, however if you follow the blog article from the start, the solution really is quite straightforward.  Today’s blog article will build on the final result of last week’s blog article, however the path to the final solution will be very different, and multiple vertical orientation bar charts will appear on a single web page.

The inspiration for today’s blog article comes from the Real Time Monitor in my Toy Project for Performance Tuning:

Since this project is a bit more advanced than last week’s project, we will use a programming language to perform the calculations and write out the HTML directly to a web browser.  We will use VBScript, which is available on most computers running Windows, so that will make it easy for you to modify the script for your needs:

• Changing the statistics to be displayed
• Changing the sampling frequency
• Changing the number of charts displayed per row and the number of rows
• Changing the spacing between the charts
• Changing the bar colors
• Changing the background color

The actual script may be a little difficult to understand.  Basically, the script queries several views, displays the historical delta values of the specified statistics in chart form, and then waits for the sampling frequency counter to tick down before collecting the next set of statistics.  The statistics collection process ends when the user clicks the Close button.  The following views are queried every time the statistics are gathered:

• V\$OSSTAT
• V\$SYS_TIME_MODEL
• V\$WAITSTAT
• V\$SYSSTAT
• V\$SYSTEM_EVENT

The statistic names from some of the views are prefixed to reduce the chances of name collisions between statistics from the different views.  For example, the statistics from V\$SYS_TIME_MODEL are prefixed with “SYS TIME MODEL ” and the statistics from V\$WAITSTAT are prefixed with “WAITSTAT TIME “.  The current value is displayed in the left-most bar, with up to the previous 29 values displayed in the remaining bars.

### Sample Charts:

Flat appearance with three horizontal and two vertical bar charts with the refresh interval set at 10 seconds:

-

Flat appearance with six horizontal and four vertical bar charts with the refresh interval set at 10 seconds:

-

3D appearance with three horizontal and two vertical bar charts with the refresh interval set at 10 seconds:

—–

So, what changes do you need to make to use the script?  First, if you are running a release of Oracle Database prior to 10.1 you will need to remove the queries of V\$SYS_TIME_MODEL and V\$OSSTAT.  Second, you need to have an Oracle user other than a SYSDBA user (SYS) that is able to query the various performance views.  Third, you need to specify the username and password of that user in the script:

```'Database configuration
strDatabase = "MyDB"```

Next, decide which statistics you would like to display in the charts and in what order (sequenced across the page and then down the page).  The statistic names are all recorded in uppercase, with the following as defaults in the script:

```'The statistics to be displayed in the charts
strStatToChart(1) = "BUSY_TIME"
strStatToChart(2) = "CPU USED BY THIS SESSION"
strStatToChart(3) = "USER CALLS"
strStatToChart(4) = "SQL*NET ROUNDTRIPS TO/FROM CLIENT"
strStatToChart(5) = "USER COMMITS"
strStatToChart(6) = "USER ROLLBACKS"
strStatToChart(7) = "TRANSACTION ROLLBACKS"
strStatToChart(8) = "DB FILE SEQUENTIAL READ"
strStatToChart(9) = "DB FILE SCATTERED READ"
strStatToChart(10) = "READ BY OTHER SESSION"
strStatToChart(11) = "SYS TIME MODEL DB TIME"
strStatToChart(12) = "SYS TIME MODEL DB CPU"
strStatToChart(13) = "SYS TIME MODEL BACKGROUND CPU TIME"
strStatToChart(14) = "PARSE TIME CPU"
strStatToChart(15) = "RECURSIVE CPU USAGE"
strStatToChart(16) = "CONSISTENT GETS"
strStatToChart(17) = "DB BLOCK GETS"
strStatToChart(19) = "DATA BLOCKS CONSISTENT READS - UNDO RECORDS APPLIED"
strStatToChart(20) = "REDO SIZE"
strStatToChart(21) = "REDO WRITE TIME"
strStatToChart(30) = "WAITSTAT TIME DATA BLOCK"```

Next, decide how the charts should appear on the page.  For example, for six charts, you might decide to use the following settings (the defaults)

```'Large Charts
intChartsHorizontal = 3
intChartsVertical = 2
lngLeftMargin = 50
lngTopMargin = 50
lngChartSpacingX = 50
lngChartSpacingY = 50
lngChartWidth = 300
lngChartHeight = 300
strBar3DEffect = "border-style:outset;border-width:thin;"```

For 24 charts, you might use the following settings (the defaults):

```'Small Charts
intChartsHorizontal = 6
intChartsVertical = 4
lngLeftMargin = 50
lngTopMargin = 50
lngChartSpacingX = 10
lngChartSpacingY = 20
lngChartWidth = 150
lngChartHeight = 150
strBar3DEffect = "border-style:none;border-width:inherit;"```

Finally, decide the color scheme of the charts, the update frequency, and whether delta values or absolute values should be displayed.  The following are the defaults:

```strBarColorStandard = "#AAAAAA"
strBarColorCurrent = "#1010FF"
strBarTextColor = "#000000"
strChartBackgroundColor = "#EEEEEE"
strChart3DEffect = "border-style:inset;"
intShowDelta = True
intRefreshSeconds = 60 'Refresh interval of the chart data```

I have found the book “CSS: The Definitive Guide” to be very helpful when adjusting the various appearance options.

Please post any improvements that you make to the script.  It might be interesting to note that this code can be incorporated into the Database Inspector Gadget that I created a while ago if the computer is running Windows Vista or Windows 7.

—————————————————————–

Partially related side note: There are still a couple of seats remaining for the Michigan OakTable Symposium, which will certainly have the most entertaining and in-depth training sessions for DBAs and developers in 2010.  There are 20+ OakTable Network members from around the world converging on Ann Arbor, MI. in mid-September 2010.  I will be in attendance, but not presenting (Randolf Geist, who co-wrote two chapters in the Expert Oracle Practices book with me is flying in from Germany to present two sessions).  More information may be found here: Michigan OakTable Symposium.

## Create an Auto-Scaling HTML Chart using Only SQL

8 07 2010

July 8, 2010 (Modified July 9, 2010)

I thought that I would try something a little different today – build an auto-scaling HTML bar chart using nothing more than a SQL statement.  I mentioned in this book review that I was impressed with the HTML chart that was included in the book, but I felt that it might be more interesting if the example used absolute positioning, rather than an HTML table.  So, I built an example using dynamic positioning that is not based on what appears in that book.

We will use the sample table from this blog article (side note: this is an interesting article that shows how a VBS script can generate a 10046 trace file, and then transform that trace file back into a VBS script), just with the table renamed to T1.

```CREATE TABLE T1 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;

COMMIT;```

Now that we have 1000 rows in the sample table, let’s see how many entries fall into each week in the table (the week starts on a Monday) for those indirect entries that are either VAC, ABS, or EXCUSE:

```SELECT
NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
COUNT(*) IND
FROM
T1
WHERE
INDIRECT_ID IN ('VAC','ABS','EXCUSE')
GROUP BY
NEXT_DAY(SHIFT_DATE,'MONDAY')-7
ORDER BY
1;```

Your results of course will be different from what follows due to the randomization of the data, but this is what was returned from my database:

```WEEK_OF   IND
--------- ---
08-OCT-07   1
15-OCT-07   2
22-OCT-07   4
29-OCT-07   3
05-NOV-07   3
03-DEC-07   2
10-DEC-07   3
24-DEC-07   2
...
05-JAN-09   1
12-JAN-09   3
19-JAN-09   7
02-FEB-09   1
...
21-JUN-10   3
28-JUN-10   2
05-JUL-10   2```

The above SQL statement should work for the base query, now we need to start manipulating the data so that we are able to calculate the size and location of the bars in the chart.  We will slide the above SQL statement into an inline view:

```SELECT
WEEK_OF,
IND,
MAX(IND) OVER () MAX_IND,
COUNT(WEEK_OF) OVER () COUNT_WEEK_OF,
ROWNUM RN
FROM
(SELECT
NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
COUNT(*) IND
FROM
T1
WHERE
INDIRECT_ID IN ('VAC','ABS','EXCUSE')
GROUP BY
NEXT_DAY(SHIFT_DATE,'MONDAY')-7
ORDER BY
1);```

In addition to returning the original data from the SQL statement, we are now also returning the maximum data value, the total number of weeks with at least one entry, and a row counter:

```WEEK_OF   IND MAX_IND COUNT_WEEK_OF    RN
--------- --- ------- ------------- -----
08-OCT-07   1       7           126     1
15-OCT-07   2       7           126     2
22-OCT-07   4       7           126     3
29-OCT-07   3       7           126     4
05-NOV-07   3       7           126     5
03-DEC-07   2       7           126     6
10-DEC-07   3       7           126     7
...
05-JAN-09   1       7           126    57
12-JAN-09   3       7           126    58
19-JAN-09   7       7           126    59
02-FEB-09   1       7           126    60
...
14-JUN-10   2       7           126   123
21-JUN-10   3       7           126   124
28-JUN-10   2       7           126   125
05-JUL-10   2       7           126   126```

Next, we need to calculate the position and size of each of the bars in the chart, so we will again slide the above into an inline view:

```SELECT
WEEK_OF,
IND,
MAX_IND,
COUNT_WEEK_OF,
RN,
TRUNC(300 * IND/MAX_IND) BAR_WIDTH,
TRUNC(800 * 1/COUNT_WEEK_OF) BAR_HEIGHT,
TRUNC(800 * 1/COUNT_WEEK_OF * (RN-1)) BAR_TOP,
100 BAR_LEFT
FROM
(SELECT
WEEK_OF,
IND,
MAX(IND) OVER () MAX_IND,
COUNT(WEEK_OF) OVER () COUNT_WEEK_OF,
ROWNUM RN
FROM
(SELECT
NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
COUNT(*) IND
FROM
T1
WHERE
INDIRECT_ID IN ('VAC','ABS','EXCUSE')
GROUP BY
NEXT_DAY(SHIFT_DATE,'MONDAY')-7
ORDER BY
1));```

You might notice in the above that I specified that the maximum width of the chart will be 300 (pixels) and the maximum height will be 800 (pixels).  Here is the output:

```WEEK_OF   IND MAX_IND COUNT_WEEK_OF    RN  BAR_WIDTH BAR_HEIGHT BAR_TOP BAR_LEFT
--------- --- ------- ------------- ----- ---------- ---------- ------- --------
08-OCT-07   1       7           126     1         42          6       0      100
15-OCT-07   2       7           126     2         85          6       6      100
22-OCT-07   4       7           126     3        171          6      12      100
29-OCT-07   3       7           126     4        128          6      19      100
05-NOV-07   3       7           126     5        128          6      25      100
03-DEC-07   2       7           126     6         85          6      31      100
10-DEC-07   3       7           126     7        128          6      38      100
24-DEC-07   2       7           126     8         85          6      44      100
...
05-JAN-09   1       7           126    57         42          6     355      100
12-JAN-09   3       7           126    58        128          6     361      100
19-JAN-09   7       7           126    59        300          6     368      100
02-FEB-09   1       7           126    60         42          6     374      100
...
14-JUN-10   2       7           126   123         85          6     774      100
21-JUN-10   3       7           126   124        128          6     780      100
28-JUN-10   2       7           126   125         85          6     787      100
05-JUL-10   2       7           126   126         85          6     793      100```

Now what?  We need to convert the above into HTML using DIV tags to position the bars as calculated.  Prior to the first row we need to write a couple of HTML tags to set the page title, and after the last row we need to write a couple more HTML tags to close the BODY and HTML section of the document.  The transformed SQL statement looks like this:

```SET TRIMSPOOL ON
SET LINESIZE 400
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
SET SQLPROMPT ''

SPOOL C:\CUSTOM_CHART.HTM

SELECT
'<div style="position:absolute;' ||
'top:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF * (RN-1))) || 'px;' ||
'left:' || TO_CHAR(5) || 'px;' ||
'width:' || TO_CHAR(100) || 'px;' ||
'height:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF)) || 'px;' ||
'"><font size="1px" color="#0000FF">' || TO_CHAR(WEEK_OF,'MM/DD/YY') ||
REPLACE('     ',' ',CHR(38) || 'nbsp;') || TO_CHAR(IND) || '</font></div>' ||
'<div style="background:#444466;position:absolute;' ||
'top:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF * (RN-1))) || 'px;' ||
'left:' || TO_CHAR(100) || 'px;' ||
'width:' || TO_CHAR(TRUNC(300 * IND/MAX_IND)) || 'px;' ||
'height:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF)) || 'px;' ||
'"><font size="1px" color="#FFFFFF"></font></div>' ||
DECODE(RN,COUNT_WEEK_OF, CHR(13) || CHR(10) || '</body></html>',' ') HTML_LINE
FROM
(SELECT
WEEK_OF,
IND,
MAX(IND) OVER () MAX_IND,
COUNT(WEEK_OF) OVER () COUNT_WEEK_OF,
ROWNUM RN
FROM
(SELECT
NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
COUNT(*) IND
FROM
T1
WHERE
INDIRECT_ID IN ('VAC','ABS','EXCUSE')
GROUP BY
NEXT_DAY(SHIFT_DATE,'MONDAY')-7
ORDER BY
1));

SPOOL OFF```

There is a slight problem with the above, the SQL statement and SPOOL OFF are printed in the resulting HTML file – if someone knows how to avoid that behavior (without placing the above into another script file), I would like to see how it is done (Oracle’s documentation did not help).

This is what the resulting HTML file looks like:

The number of result rows from the query was a bit high (126) so the bars are significantly compressed in height.  Just to see what happens, let’s add the following to the WHERE clause in the inner-most inline view:

`AND SHIFT_DATE >= TO_DATE('01-JAN-2010','DD-MON-YYYY')`

The resulting chart now looks like this:

Of course it is possible to adjust the colors of the font (#0000FF) and the bars (#444466), which are specified in hex in the format of RRGGBB (red green blue).  It is also possible to adjust the color of the bars to reflect the value represented by the bar, but that is an exercise for the reader.  For those who need to feel creative, it is also possible to display pictures in the bars, but that is also an exercise left for the reader.

—-

Edit: The sample output from the SQL statement displays correctly on Red Hat Enterprise Linux 3 using Firefox 0.8:

## Working with Oracle’s Time Model Data 3

9 02 2010

February 9, 2010

In the previous article in this series, we created a structured view of the V\$SYS_TIME_MODEL view:

Then on demand brought in session level detail for the same statistics so that it was possible to investigate why a statistic value continued to grow.  The previous article also displayed delta values from V\$OSSTAT and the wait events from V\$SYSTEM_EVENT.  It appears that a couple people liked the previous effort, and I gave serious thought to having the computer provide audio feedback when a session started consuming a lot of resources – but then I started to wonder if maybe we should instead have an audio warning when a session is consuming less than the average resource consumption so that we could then determine who is reading this article rather than working. :-)  In the end, I decided to leave out the audio feedback.  So, what else can we do with the framework that was provided in the second article of this series?

It might be nice to be able to drill into session-level wait events to determine why a wait event’s time delta keeps growing, maybe by adding a couple of extra buttons to the web page that is created on the fly by the VBS script:

It might also be nice to be able to view the DBMS_XPLAN for a query that was identified as a SQL statement that was recently executed by a session.  One of the problems with the original script is that it only retrieved the SQL_ID and SQL_CHILD_NUMBER from V\$SESSION, and it seemed that those columns contained NULL values just as frequently as they contained data.  The updated script will attempt to return the PREV_SQL_ID and PREV_CHILD_NUMBER columns if the other two columns contain NULL values.  In addition to retrieving the DBMS_XPLAN for the CHILD_NUMBER that is executed by the session, it might also be helpful to retrieve the DBMS_XPLAN for all child cursors for a given SQL_ID so that we are able to determine if the execution plan changed, and why it changed.  While we are at it, let’s make it possible to enable a 10046 extended SQL trace for a session just by clicking a button on the web page.  With the changes to the script, the generated web page might look something like this (note that this is a touched up image – the computer name and username were erased):

Scrolling down to the bottom of the page, note that the Session Waits button for the db file scattered read wait event was clicked:

So, what happens if one of the XPlan buttons is clicked?  We could just write the execution plan to a text file, but where is the fun in doing that?  How about popping up another web browser page that lists all execution plans for a query that are currently in the library cache, the reasons for those generated execution plans from V\$SQL_SHARED_CURSOR, and the bind variable definitions from V\$SQL_BIND_METADATA.  The generated web page might look something like this (with duplicate sections from the different child cursors removed to save space):

```**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
**********
Bind Variable Definitions
Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 1
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM
WORK_ORDER WO,    REQUIREMENT R,    PART P  WHERE
WO.TYPE='W'    AND WO.SUB_ID='0'    AND WO.STATUS IN ('F','R','U')
AND WO.DESIRED_QTY>WO.RECEIVED_QTY    AND WO.PART_ID= :1    AND
R.WORKORDER_TYPE='W'    AND WO.TYPE=R.WORKORDER_TYPE    AND
WO.BASE_ID=R.WORKORDER_BASE_ID    AND WO.LOT_ID=R.WORKORDER_LOT_ID
AND WO.SPLIT_ID=R.WORKORDER_SPLIT_ID    AND R.SUBORD_WO_SUB_ID IS NULL
AND R.CALC_QTY>R.ISSUED_QTY    AND R.PART_ID=P.ID  ORDER BY
R.PART_ID

Plan hash value: 3990487722

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |       |       |    27 (100)|          |        |
|   1 |  SORT UNIQUE                    |                 |     9 |   747 |    26   (4)| 00:00:01 |        |
|   2 |   NESTED LOOPS                  |                 |       |       |            |          |        |
|   3 |    NESTED LOOPS                 |                 |     9 |   747 |    25   (0)| 00:00:01 |        |
|   4 |     NESTED LOOPS                |                 |     9 |   621 |    16   (0)| 00:00:01 |        |
|*  5 |      TABLE ACCESS BY INDEX ROWID| WORK_ORDER      |     1 |    37 |    13   (0)| 00:00:01 |   OR11 |
|*  6 |       INDEX RANGE SCAN          | X_WORK_ORDER_1  |    21 |       |     3   (0)| 00:00:01 |   OR11 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| REQUIREMENT     |    25 |   800 |     3   (0)| 00:00:01 |   OR11 |
|*  8 |       INDEX RANGE SCAN          | X_REQUIREMENT_5 |     1 |       |     2   (0)| 00:00:01 |   OR11 |
|*  9 |     INDEX UNIQUE SCAN           | SYS_C0011459    |     1 |       |     0   (0)|          |   OR11 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | PART            |     1 |    14 |     1   (0)| 00:00:01 |   OR11 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("WO"."DESIRED_QTY">"WO"."RECEIVED_QTY" AND "WO"."SUB_ID"='0' AND
INTERNAL_FUNCTION("WO"."STATUS") AND "WO"."TYPE"='W'))
6 - access("WO"."PART_ID"=:1)
7 - filter("R"."CALC_QTY">"R"."ISSUED_QTY")
8 - access("WO"."TYPE"="R"."WORKORDER_TYPE" AND "WO"."BASE_ID"="R"."WORKORDER_BASE_ID" AND
"WO"."LOT_ID"="R"."WORKORDER_LOT_ID" AND "WO"."SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND
"R"."SUBORD_WO_SUB_ID" IS NULL)
filter("R"."WORKORDER_TYPE"='W')
9 - access("R"."PART_ID"="P"."ID")

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
ROW_LEVEL_SEC_MISMATCH
**********
Bind Variable Definitions
Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 2
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM
...
Plan hash value: 3990487722

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |       |       |    27 (100)|          |        |
|   1 |  SORT UNIQUE                    |                 |     9 |   747 |    26   (4)| 00:00:01 |        |
|   2 |   NESTED LOOPS                  |                 |       |       |            |          |        |
|   3 |    NESTED LOOPS                 |                 |     9 |   747 |    25   (0)| 00:00:01 |        |
|   4 |     NESTED LOOPS                |                 |     9 |   621 |    16   (0)| 00:00:01 |        |
|*  5 |      TABLE ACCESS BY INDEX ROWID| WORK_ORDER      |     1 |    37 |    13   (0)| 00:00:01 |   OR11 |
|*  6 |       INDEX RANGE SCAN          | X_WORK_ORDER_1  |    21 |       |     3   (0)| 00:00:01 |   OR11 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| REQUIREMENT     |    25 |   800 |     3   (0)| 00:00:01 |   OR11 |
|*  8 |       INDEX RANGE SCAN          | X_REQUIREMENT_5 |     1 |       |     2   (0)| 00:00:01 |   OR11 |
|*  9 |     INDEX UNIQUE SCAN           | SYS_C0011459    |     1 |       |     0   (0)|          |   OR11 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | PART            |     1 |    14 |     1   (0)| 00:00:01 |   OR11 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("WO"."DESIRED_QTY">"WO"."RECEIVED_QTY" AND "WO"."SUB_ID"='0' AND
INTERNAL_FUNCTION("WO"."STATUS") AND "WO"."TYPE"='W'))
6 - access("WO"."PART_ID"=:1)
7 - filter("R"."CALC_QTY">"R"."ISSUED_QTY")
8 - access("WO"."TYPE"="R"."WORKORDER_TYPE" AND "WO"."BASE_ID"="R"."WORKORDER_BASE_ID" AND
"WO"."LOT_ID"="R"."WORKORDER_LOT_ID" AND "WO"."SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND
"R"."SUBORD_WO_SUB_ID" IS NULL)
filter("R"."WORKORDER_TYPE"='W')
9 - access("R"."PART_ID"="P"."ID")

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
ROW_LEVEL_SEC_MISMATCH
**********
Bind Variable Definitions
Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 3
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM
...

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
**********
Bind Variable Definitions
Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 4
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM
...

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
ROW_LEVEL_SEC_MISMATCH
**********
Bind Variable Definitions
Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 5
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM
...

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
ROW_LEVEL_SEC_MISMATCH
**********
Bind Variable Definitions
Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 7
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM
...
Plan hash value: 1336181825

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       |   577 (100)|          |        |
|   1 |  SORT UNIQUE                          |                 |   838 | 69554 |   576   (1)| 00:00:07 |        |
|*  2 |   HASH JOIN                           |                 |   838 | 69554 |   575   (1)| 00:00:07 |        |
|   3 |    NESTED LOOPS                       |                 |       |       |            |          |        |
|   4 |     NESTED LOOPS                      |                 |   840 | 57960 |   164   (0)| 00:00:02 |        |
|*  5 |      TABLE ACCESS BY INDEX ROWID      | WORK_ORDER      |    35 |  1295 |    60   (0)| 00:00:01 |   OR11 |
|   6 |       BITMAP CONVERSION TO ROWIDS     |                 |       |       |            |          |        |
|   7 |        BITMAP AND                     |                 |       |       |            |          |        |
|   8 |         BITMAP CONVERSION FROM ROWIDS |                 |       |       |            |          |        |
|*  9 |          INDEX RANGE SCAN             | X_WORK_ORDER_1  | 13721 |       |     8   (0)| 00:00:01 |   OR11 |
|  10 |         BITMAP OR                     |                 |       |       |            |          |        |
|  11 |          BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |        |
|* 12 |           INDEX RANGE SCAN            | X_WORK_ORDER_2  | 13721 |       |     6   (0)| 00:00:01 |   OR11 |
|  13 |          BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |        |
|* 14 |           INDEX RANGE SCAN            | X_WORK_ORDER_2  | 13721 |       |     1   (0)| 00:00:01 |   OR11 |
|  15 |          BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |        |
|* 16 |           INDEX RANGE SCAN            | X_WORK_ORDER_2  | 13721 |       |    19   (0)| 00:00:01 |   OR11 |
|* 17 |      INDEX RANGE SCAN                 | X_REQUIREMENT_5 |     1 |       |     2   (0)| 00:00:01 |   OR11 |
|* 18 |     TABLE ACCESS BY INDEX ROWID       | REQUIREMENT     |    24 |   768 |     3   (0)| 00:00:01 |   OR11 |
|  19 |    TABLE ACCESS FULL                  | PART            | 37032 |   506K|   410   (1)| 00:00:05 |   OR11 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R"."PART_ID"="P"."ID")
5 - filter(("WO"."DESIRED_QTY">"WO"."RECEIVED_QTY" AND "WO"."SUB_ID"='0' AND "WO"."TYPE"='W'))
9 - access("WO"."PART_ID"=:1)
12 - access("WO"."STATUS"='F')
14 - access("WO"."STATUS"='R')
16 - access("WO"."STATUS"='U')
17 - access("WO"."TYPE"="R"."WORKORDER_TYPE" AND "WO"."BASE_ID"="R"."WORKORDER_BASE_ID" AND
"WO"."LOT_ID"="R"."WORKORDER_LOT_ID" AND "WO"."SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND
"R"."SUBORD_WO_SUB_ID" IS NULL)
filter("R"."WORKORDER_TYPE"='W')
18 - filter("R"."CALC_QTY">"R"."ISSUED_QTY")

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
**********
Bind Variable Definitions
Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 8
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM
WO.TYPE='W'    AND WO.SUB_ID='0'    AND WO.STATUS IN ('F','R','U')
AND WO.DESIRED_QTY>WO.RECEIVED_QTY    AND WO.PART_ID= :1    AND
R.WORKORDER_TYPE='W'    AND WO.TYPE=R.WORKORDER_TYPE    AND
WO.BASE_ID=R.WORKORDER_BASE_ID    AND WO.LOT_ID=R.WORKORDER_LOT_ID
AND WO.SPLIT_ID=R.WORKORDER_SPLIT_ID    AND R.SUBORD_WO_SUB_ID IS NULL
AND R.CALC_QTY>R.ISSUED_QTY    AND R.PART_ID=P.ID  ORDER BY
R.PART_ID

Plan hash value: 3990487722

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |       |       |    27 (100)|          |        |
|   1 |  SORT UNIQUE                    |                 |     9 |   747 |    26   (4)| 00:00:01 |        |
|   2 |   NESTED LOOPS                  |                 |       |       |            |          |        |
|   3 |    NESTED LOOPS                 |                 |     9 |   747 |    25   (0)| 00:00:01 |        |
|   4 |     NESTED LOOPS                |                 |     9 |   621 |    16   (0)| 00:00:01 |        |
|*  5 |      TABLE ACCESS BY INDEX ROWID| WORK_ORDER      |     1 |    37 |    13   (0)| 00:00:01 |   OR11 |
|*  6 |       INDEX RANGE SCAN          | X_WORK_ORDER_1  |    21 |       |     3   (0)| 00:00:01 |   OR11 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| REQUIREMENT     |    24 |   768 |     3   (0)| 00:00:01 |   OR11 |
|*  8 |       INDEX RANGE SCAN          | X_REQUIREMENT_5 |     1 |       |     2   (0)| 00:00:01 |   OR11 |
|*  9 |     INDEX UNIQUE SCAN           | SYS_C0011459    |     1 |       |     0   (0)|          |   OR11 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | PART            |     1 |    14 |     1   (0)| 00:00:01 |   OR11 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("WO"."DESIRED_QTY">"WO"."RECEIVED_QTY" AND "WO"."SUB_ID"='0' AND
INTERNAL_FUNCTION("WO"."STATUS") AND "WO"."TYPE"='W'))
6 - access("WO"."PART_ID"=:1)
7 - filter("R"."CALC_QTY">"R"."ISSUED_QTY")
8 - access("WO"."TYPE"="R"."WORKORDER_TYPE" AND "WO"."BASE_ID"="R"."WORKORDER_BASE_ID" AND
"WO"."LOT_ID"="R"."WORKORDER_LOT_ID" AND "WO"."SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND
"R"."SUBORD_WO_SUB_ID" IS NULL)
filter("R"."WORKORDER_TYPE"='W')
9 - access("R"."PART_ID"="P"."ID")

**********************************************************************************************
Reason for Child Cursor Creation
BIND_MISMATCH
**********
Bind Variable Definitions
Position:1  Max Length:32  VARCHAR2  Name:1
**********
SQL_ID  8p3pacrbngv80, child number 9
-------------------------------------
SELECT DISTINCT    R.PART_ID,    P.FABRICATED,    P.PURCHASED  FROM
...
**********************************************************************************************```

In the above, the selected session was actually using the execution plan for child number 3.  Notice that one of the execution plans converted normal B*Tree indexes on the fly to permit comparisons much like what are possible with bitmap indexes.  A large portion of the execution plans were created, at least in part, because of a bind mismatch – see the documentation V\$SQL_SHARED_CURSOR for more information.

This example requires at a minimum Oracle Database 10.1.0.1 running on Windows, Unix, or Linux.  The supplied script must be run from a Windows client computer.  You will need to specify a suitable username, password, and database name (from tnsnames.ora) for the strUsername, strPassword, and strDatabase variables in the VBS script.  This script ties into several of the chapters in the Expert Oracle Practices book.

Download the script from here: MonitorDatabaseTimeModel.vbs (save as MonitorDatabaseTimeModel.vbs, without the .doc extension – note that this is version 2.0.1)

## 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>
<title>I am a Hidden Web Page - You will Not See Me</title>
<body>
<%
Dim strSQL
Dim snpData
Dim comData
Dim strPartID

Dim dbDatabase

On Error Resume Next

strPartID = cStr(Request("strPartID"))

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
'
'Set up the command properties
.CommandText = strSQL
.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.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

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

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
.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
.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
.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.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
.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.Toolbar = False

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 "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 "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
For j = 1 To intSessionCount
If intSessionExists(j) = True 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.

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" ?>
<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>

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>
<title>Visual Inspector</title>
<style type="text/css">
body
{
margin: 0px;
width: 140px;
height: 200px;
}
{
margin-top: 0px;
width: 140px;
vertical-align: middle;
text-align: center;
overflow: hidden;
}
.Item {
background-position: center center;
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>
<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
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

'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 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

'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 & "  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
'    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
'        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
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>
<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>

<script language="VBScript">
Option Explicit

Dim dbMyConnection
Dim comData
Dim snpData
Dim strDatabase
Dim strSQL
Dim strHTML

On Error Resume Next

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

<script language="VBScript">
Option Explicit

Dim dbMyConnection
Dim comData
Dim snpData
Dim strDatabase
Dim strSQL
Dim strHTML

On Error Resume Next

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

<script language="VBScript">
Option Explicit

Dim dbMyConnection
Dim comData
Dim snpData
Dim strDatabase
Dim strSQL
Dim strHTML

On Error Resume Next

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

<script language="VBScript">
Option Explicit

Dim dbMyConnection
Dim comData
Dim snpData
Dim strDatabase
Dim strLastPacklist
Dim strSQL
Dim strHTML

On Error Resume Next

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

<script language="VBScript">
Option Explicit

Dim dbMyConnection
Dim comData
Dim snpData
Dim strDatabase
Dim strSQL
Dim strHTML

'On Error Resume Next

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 & "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 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>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>
<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>

<script language="VBScript">
Option Explicit

Dim dbMyConnection
Dim comData
Dim snpData
Dim strDatabase
Dim strSQL
Dim strHTML

On Error Resume Next

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 >
<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;
}
{
margin-top: 20px;
width: 130px;
vertical-align: middle;
text-align: center;
overflow: hidden;
}
</style>
<script type="text/jscript" language="jscript">
function init()
{
var oBackground = document.getElementById("imgBackground");
oBackground.src = "url(images/background.png)";
}
</script>

<g:background id="imgBackground">
</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

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")

'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

'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

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)

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;"
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)
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.Toolbar = False

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 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 strLinkFind      'What to find in the HTML code which indicates that the page link will follow
Dim strSelectedBook  'The name of the selected book

On Error Resume Next
'Set objShell = CreateObject("WScript.Shell")
Set objIESource = CreateObject("InternetExplorer.Application")
Set objIE = CreateObject("InternetExplorer.Application")

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

'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)

'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
Else
End If
Else
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.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
'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.Width = 800
objIE.Height = 600
objIE.Statusbar = True
objIE.Toolbar = True