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 RADIUS    20                // radius of ball
#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
   y = TOP  + RADIUS;
   for (i =0; i <180; i = i + 18) // create basketball rotation images
       {
 setcolor(RED);
 setfillstyle(SOLID_FILL, RED);
 circle(x, y, RADIUS);
 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;
    }
 ellipse (x1, y1, sta1, ena1, RADIUS , RADIUS);
 ellipse (x2, y2, sta2, ena2, RADIUS , RADIUS);
 line (x - cos(rad + pi/2) * RADIUS, y - sin(rad + pi/2) * RADIUS, x + cos(rad + pi/2) * RADIUS, y + sin(rad + pi/2) * RADIUS);
        // pickup image
 getimage(x-RADIUS-20, y-RADIUS-20, x+RADIUS+20, y+RADIUS+20, ballbuff[i/18]);
        // 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:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dx = dx -.01;
     break;
  case R_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dx = dx + .01;
     break;
  case U_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dy = dy + .01;
     break;
  case D_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     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;
      putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], COPY_PUT);

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

Const adCmdText = 1
Const adCmdStoredProc = 4
Const adParamInput = 1
Const adVarNumeric = 139
Const adBigInt = 20
Const adDecimal = 14
Const adDouble = 5
Const adInteger = 3
Const adLongVarBinary = 205
Const adNumeric = 131
Const adSingle = 4
Const adSmallInt = 2
Const adTinyInt = 16
Const adUnsignedBigInt = 21
Const adUnsignedInt = 19
Const adUnsignedSmallInt = 18
Const adUnsignedTinyInt = 17
Const adDate = 7
Const adDBDate = 133
Const adDBTimeStamp = 135
Const adDBTime = 134
Const adVarChar = 200
Const adUseClient = 3

Dim dbDatabase
Dim snpData
Dim comData
Dim varData
Dim objIE

Dim strUsername             'Username
Dim strPassword             'Password
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.MenuBar = False
    objIE.Toolbar = False
    objIE.Navigate "about:blank"
    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

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

    'Database configuration
    strUsername = "MyUsername"
    strPassword = "MyPassword"
    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
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase

        'Add the bind variables
        .Parameters.Append .CreateParameter("dx", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("dy", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("speed", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("delay", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("width", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("height", adDouble, adParamInput, 30)
    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

    'Allow faster access to these objects when executing in the loop
    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

You may download the above script here: SQLOrbitingBall.vbs (save as SQLOrbitingBall.vbs).

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>

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

<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

    Set dbDatabase = Server.CreateObject("ADODB.Connection")

    'Database configuration
    strUsername = "MyUsername"
    strPassword = "MyPassword"
    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

    Set snpData = Server.CreateObject("ADODB.Recordset")

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

You can download the VBS script here: LoggingTrigger.vbs (save as LoggingTrigger.vbs). 

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

    objIE.Navigate "about:blank"
    objIE.Document.Title = "Charles Hoopers Oracle Logging Trigger Creator"
    objIE.Visible = True

    Set dbDatabase = CreateObject("ADODB.Connection")
    Set snpData = CreateObject("ADODB.Recordset")
    Set comRetrieveTableNames = CreateObject("ADODB.Command")
    Set comRetrieveTableColumns = CreateObject("ADODB.Command")

    'Database configuration
    strUsername = "MyUsername"
    strPassword = "MyPassword"
    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),
  BUYER_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 (
    (PLANNING_LEADTIME >= 0)
    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,
  PLANNING_LEADTIME,
  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).





Determine the Oracle Client Release Version – Remotely

6 08 2010

August 6, 2010

An ERP mailing list email asked if there was a way to determine which Oracle clients had not been upgraded to the Oracle 11.2.0.1 client – I also recently encountered a request to determine similar information about the Oracle client versions of potential sessions, so I thought it might be fun to develop a solution.  I might be overlooking something, but it does not appear that the Oracle Database, by default, maintains the connected client’s release version.  Is there a solution?  Assume that the following is true:

  • The client computers are running Windows 2000 (client or server) or above
  • The client computers are members of ActiveDirectory
  • WMI (Windows Management Instrumentation) has not been disabled on the client computers
  • VBS (wscript or cscript) may be run from a computer that is logged in with domain administrator permissions

First of all, I would prefer not to visit each computer to determine which Oracle client is installed.  I also do not want to type all of the computer names – so we need a way to retrieve a list of the computers that are members of the ActiveDirectory domain.  A partial code fragment might look something like this (replace oracle and com as necessary for your domain):

Dim dbActiveDirectory
Dim comData
Dim snpData
Dim strDomain

Set dbActiveDirectory = CreateObject("ADODB.Connection")
Set comData = CreateObject("ADODB.Command")
Set snpData = CreateObject("ADODB.Recordset")

dbActiveDirectory.Provider = "ADsDSOObject"
dbActiveDirectory.Open "Active Directory Provider"
comData.ActiveConnection = dbActiveDirectory
strDomain = "DC=oracle,DC=com"            'Equivalent to oracle.com, change as needed
With comData
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  NAME" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  'LDAP://" & strDomain & "'" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  OBJECTCLASS='computer'" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  NAME"

    .CommandText = strSQL          
    .Properties("Page Size") = 1000
    .Properties("Searchscope") = ADS_SCOPE_SUBTREE
End With

Set snpData = comData.Execute
If snpData.State = 1 Then
    Do While Not (snpData.EOF)
        'Do something
        snpData.MoveNext
    Loop
End If

Now that we have a method for retrieving the computer names in the domain (note that the above code might only work for the domain administrators), we need an easy method to determine which Oracle client is installed – or at least which client is listed first in the system path.  Something like this might work:

C:\> tnsping /?

TNS Ping Utility for 32-bit Windows: Version 11.1.0.7.0 - Production on 05-AUG-2010 20:54:43

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

There is the Oracle client release version on the second line of the output, along with the client’s operating system (just ignore the error message).  Now we just need a way to record the output of that command – let’s redirect the output to a text file:

C:\> tnsping /? >C:\OracleClientversion.txt

Next, we need to find a way to tell the client computers on the network to execute the above command.  WMI will help, as long as our network user account is a member of the  Administrators group on the remote computer (or our network user account is a member of the Domain Adminitrators group).  If we are somehow able to create a batch file named C:\OracleClientVersion.bat on the remote client computer, we could execute a command like this in our script:

lngResult = GetObject("winmgmts:\\" & snpData.Fields("Name").Value & "\root\cimv2:Win32_Process").Create("C:\OracleClientVersion.bat", Null, Null, intProcessID)

The next task is to read the resulting text files that are created on each of the client computers, looking for the line that contains “TNS Ping Utility”. Obviously, we need to write the results to another text file.  We end up with something like this:

Set objFile = objFSO.OpenTextFile("\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF, ForReading)

Do While Not (objFile.AtEndOfStream)
    strLine = objFile.ReadLine
    If InStr(UCase(strLine), "TNS PING UTILITY") > 0 Then
        objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & strLine & vbCrLf
        Exit Do
    Else
        'Do nothing
    End If
Loop

objFile.Close

One problem remains.  If we attempt to access the WMI objects on a computer that is offline our script will lock up for a period of time.  To work around that problem we should probably try to ping the remote computer first.  The ping routine looks like this:

Function PingTest(strComputer)
    Dim intPosition
    Dim objShell
    Dim objExec
    Dim strLine
    Dim strCommand

    On Error Resume Next

    PingTest = False
    Set objShell = CreateObject("wscript.shell")
    'command to execute
    strCommand = "PING -i 10 -w 10 -n 1 " & strComputer
    'Create Exec object
    Set objExec = objShell.Exec(strCommand)
    'skip lines that contain information about our DNS
    'server
    Do While objExec.StdOut.AtEndOfStream <> True
        strLine = objExec.StdOut.ReadLine
        intPosition = InStr(UCase(strLine), "RECEIVED =")
        If intPosition > 0 Then
            If InStr(strLines, "TTL expired in transit") = 0 Then
                If Trim(Mid(strLine, intPosition + 10, 2)) = "1" Then
                    PingTest = True
                Else
                    PingTest = False
                End If
            Else
                PingTest = False
            End If
            Exit Do
        End If
    Loop
    Set objShell = Nothing
    Set objExec = Nothing
End Function

A simple, straight-forward solution, right?  Because the columns in the combined log file are tab delimited, we are able to easily open the file using Microsoft Excel.  Partial output might look something like this:

Obviously, the client computers need to be turned on for the script to work correctly.  :-)  COMP19 certainly is running an old version of the Oracle client.

Notes:
* For every computer that responds to a ping, the script pauses for two seconds to allow enough time for the tnsping command on the remote computers to execute. 
* The script execution speed may be improved by modifying the script to remember each computer name that responded to a ping, and remotely launch the tnsping command on those computers.  Once the tnsping command has been started on each computer, make a loop through the list of computers that previously reponded to a ping and collect the OracleClientversion.txt files from those computers.

The full script may be downloaded here: DetOrclClient.vbs (save as DetermineOracleClientReleaseVersion.vbs).  The full script is displayed below (the script download from WordPress seems to fail):

'Revision 1.0 Created by Charles Hooper August 6, 2010

ProbeAllComputers

Sub ProbeAllComputers()
    'For modifying the INI file
    Const ForReading = 1
    Const ForWriting = 2
    Dim objFSO
    Dim objFile
    Dim objFileLog
    Dim objFileBatch
    Dim strLine
    Dim strOracleClientFile
    Dim strOracleClientFileF
    Dim lngResult

    'For querying active directory
    Const ADS_SCOPE_SUBTREE = 2
    Dim dbActiveDirectory
    Dim comData
    Dim snpData
    Dim strSQL
    Dim strDomain
    Dim strCommand

    On Error Resume Next

    strDomain = "DC=oracle,DC=com"            'Equivalent to oracle.com, change as needed
    strOracleClientFile = "C:\OracleClientVersion.txt"  'The location of the output file on the computers
    strCommand = "tnsping /? >C:\OracleClientVersion.txt"

    'Make the file location compatible with a UNC name
    strOracleClientFileF = Replace(strOracleClientFile, ":", "$")
    strOracleClientFileF = "\" & strOracleClientFileF

    Set dbActiveDirectory = CreateObject("ADODB.Connection")
    Set comData = CreateObject("ADODB.Command")
    Set snpData = CreateObject("ADODB.Recordset")

    dbActiveDirectory.Provider = "ADsDSOObject"
    dbActiveDirectory.Open "Active Directory Provider"

    comData.ActiveConnection = dbActiveDirectory

    If Err <> 0 Then
        lngResult = MsgBox("An error happened while connecting to Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!")
        Exit Sub
    End If

    With comData
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  NAME" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  'LDAP://" & strDomain & "'" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  OBJECTCLASS='computer'" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  NAME"

        .CommandText = strSQL

        .Properties("Page Size") = 1000
        .Properties("Searchscope") = ADS_SCOPE_SUBTREE
    End With

    Set snpData = comData.Execute

    If Err <> 0 Then
        lngResult = MsgBox("An error happened while reading the computer list from Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!")
        Exit Sub
    End If

    If snpData.State = 1 Then
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFileLog = objFSO.CreateTextFile("C:\Oracle Remote Client Log.txt", True)

        Do While Not (snpData.EOF)
            If PingTest(CStr(snpData.Fields("Name").Value)) = True Then
                'Write the command file to the remote computer
                Set objFileBatch = objFSO.CreateTextFile("\\" & CStr(snpData.Fields("Name").Value) & "\C$\OracleClientVersion.bat", True)
                objFileBatch.Write "tnsping /? >C:\OracleClientversion.txt" & vbCrLf
                objFileBatch.Close
                Set objFileBatch = Nothing

                lngResult = GetObject("winmgmts:\\" & snpData.Fields("Name").Value & "\root\cimv2:Win32_Process").Create("C:\OracleClientVersion.bat", Null, Null, intProcessID)

                If lngResult = 0 Then
                    'Wait 2 seconds for the command to execute
                    WScript.Sleep 2 * 1000
                End If

                'Erase the batch file from the remote computer
                objFSO.DeleteFile "\\" & CStr(snpData.Fields("Name").Value) & "\C$\OracleClientVersion.bat"

                Err = 0  'Reset the error indicator
                Set objFile = objFSO.OpenTextFile("\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF, ForReading)

                If Err = 0 Then
                    Do While Not (objFile.AtEndOfStream)
                        strLine = objFile.ReadLine
                        If InStr(UCase(strLine), "TNS PING UTILITY") > 0 Then
                            objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & strLine & vbCrLf
                            Exit Do
                        Else
                            'Do nothing
                        End If
                    Loop

                    objFile.Close

                    If Err = 0 Then
                        'lngResult = MsgBox("No Errors accessing the file on " & CStr(snpData.Fields("Name").Value), 64, "Good")
                    Else
                        'lngResult = MsgBox("An error happened while accessing the output file on the computer " & CStr(snpData.Fields("Name").Value) & vbCrLf & Err.Description & vbCrLf & "Continue?", 16 + 4, "Oh NO!")
                        Err = 0
                        objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "Error " & Err.Description & vbCrLf
                    End If

                    'Erase the log file from the remote computer
                    objFSO.DeleteFile "\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF
                    Err = 0
                Else
                    If Err <> 0 Then
                        'lngResult = MsgBox("An error happened while accessing the INI file on the computer " & CStr(snpData.Fields("Name").Value) & vbCrLf & Err.Description & vbCrLf & "Continue?", 16 + 4, "Oh NO!")
                        objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "Error " & Err.Description & vbCrLf

                        If lngResult = 7 Then
                            dbActiveDirectory.Close
                            Set snpData = Nothing
                            Set dbActiveDirectory = Nothing
                            Exit Sub
                        End If
                        Err = 0
                    End If
                End If

            Else
                'lngResult = MsgBox("The computer " & CStr(snpData.Fields("Name").Value) & " could not be pinged" & vbCrLf & "Continue?", 16 + 4, "Oh NO!")
                objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "No ping response" & vbCrLf
                If lngResult = 7 Then
                    dbActiveDirectory.Close
                    Set snpData = Nothing
                    Set dbActiveDirectory = Nothing
                    Exit Sub
                End If
            End If

            snpData.MoveNext
            Set objFile = Nothing
        Loop

        snpData.Close
        objFileLog.Close
    Else
        If Err <> 0 Then
            lngResult = MsgBox("An error happened while connecting to Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!")
        End If
    End If

    dbActiveDirectory.Close
    Set snpData = Nothing
    Set dbActiveDirectory = Nothing
End Sub

Function PingTest(strComputer)
    Dim intPosition
    Dim objShell
    Dim objExec
    Dim strLine
    Dim strCommand

    On Error Resume Next

    PingTest = False
    Set objShell = CreateObject("wscript.shell")
    'command to execute
    strCommand = "PING -i 10 -w 10 -n 1 " & strComputer
    'Create Exec object
    Set objExec = objShell.Exec(strCommand)
    'skip lines that contain information about our DNS
    'server
    Do While objExec.StdOut.AtEndOfStream <> True
        strLine = objExec.StdOut.ReadLine
        intPosition = InStr(UCase(strLine), "RECEIVED =")
        If intPosition > 0 Then
            If InStr(strLines, "TTL expired in transit") = 0 Then
                If Trim(Mid(strLine, intPosition + 10, 2)) = "1" Then
                    PingTest = True
                Else
                    PingTest = False
                End If
            Else
                PingTest = False
            End If
            Exit Do
        End If
    Loop
    Set objShell = Nothing
    Set objExec = Nothing
End Function

.





SQL – Programmatic Row By Row to MERGE INTO

27 07 2010

July 27, 2010

A question in an email from an ERP mailing list combined with Cary Millsap’s latest blog article inspired this blog article.  The question from the ERP mailing list asked the following question:

Does anyone have Oracle syntax for the ‘upsert‘ command?  I have found a few examples, but little success yet.

Using VB.net, I want to have one command which will see if data exists, and if yes, update, if no, then insert.

There are several ways to approach this particular problem, some of which may be more efficient than others.  For example, assume that we have a table defined like this:

CREATE TABLE T2(
  ID NUMBER,
  COL2 NUMBER,
  COL3 NUMBER,
  COL4 NUMBER,
  PRIMARY KEY (ID));

Then we insert 5 rows using the following SQL statement (if you receive a primary key violation, just try executing the INSERT statement again) and then create a table that will allow us to quickly restore the original values for various repeated tests:

INSERT INTO
  T2
SELECT
  TRUNC(DBMS_RANDOM.VALUE(1,30)),
  TRUNC(DBMS_RANDOM.VALUE(1,1000)),
  TRUNC(DBMS_RANDOM.VALUE(1,1000)),
  TRUNC(DBMS_RANDOM.VALUE(1,1000))
FROM
  DUAL
CONNECT BY
  LEVEL<=5;

CREATE TABLE
  T2_BACKUP
AS
SELECT
  *
FROM
  T2;

The five rows created by the above will have random numeric values in the COL2, COL3, and COL4 columns.  The rows might look something like this:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1        993        718        103
10        583        924        458
13         27        650        861
16        141        348        813
28        716        517        204

Now we want to fill in the missing rows, so that ID values 1 through 30 appear in the table, but if the row already exists, we will modify the column values as follows:

  • COL2 will be set to a value of 0
  • COL3 will be set to a value of COL2 + COL3
  • COL4 will be set to a random value

How might we make these changes?  Well, we might do something silly, as demonstrated by the following VB Script code (this code may be executed with wscript or cscript on the Windows platform – it is also compatible with Visual Basic 6 and the Excel macro language, but the late binding should be changed to early binding, and variable types should be declared):

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Dim dbDatabase
Dim dynData
Dim intS_ID
Dim intS_C2
Dim intS_C3
Dim intS_C4
Dim i
Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Set dbDatabase = CreateObject("ADODB.Connection")
Set dynData = CreateObject("ADODB.Recordset")

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

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

dbDatabase.BeginTrans

For i = 1 To 30
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  ID," & vbCrLf
    strSQL = strSQL & "  COL2," & vbCrLf
    strSQL = strSQL & "  COL3," & vbCrLf
    strSQL = strSQL & "  COL4" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  T2" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  ID=" & CStr(i)

    dynData.Open strSQL, dbDatabase, adOpenKeyset, adLockOptimistic

    intS_ID = i
    intS_C2 = Int(Rnd * 1000) + 1
    intS_C3 = Int(Rnd * 1000) + 1
    intS_C4 = Int(Rnd * 1000) + 1

    If Not (dynData.BOF And dynData.EOF) Then
        dynData("col2") = 0
        dynData("col3") = dynData("col2") + dynData("col3")
        dynData("col4") = intS_C4
    Else
        'No row found, need to add
        dynData.AddNew

        dynData("id") = i
        dynData("col2") = intS_C2
        dynData("col3") = intS_C3
        dynData("col4") = intS_C4
    End If
    dynData.Update

    dynData.Close
Next

dbDatabase.CommitTrans

dbDatabase.Close

Set dynData = Nothing
Set dbDatabase = Nothing

There are a couple of problems with the above, beyond the lack of bind variable usage.  At least 30 SQL statements are sent to the database.  If a row is tested to exist (the recordset’s BOF and EOF properties are not both true) then the row’s values are updated, otherwise a row is inserted.  This is the row by row (slow by slow) method of accomplishing the task.  When the script is executed, the table contents might look like this:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0        718        580
 2        290        302        775
 3         15        761        815
 4        710         46        415
 5        863        791        374
 6        962        872         57
 7        950        365        525
 8        768         54        593
 9        469        299        623
10          0        924        280
11        830        825        590
12        987        911        227
13          0        650        244
14        534        107       1000
15        677         16        576
16          0        348        799
17        285         46        296
18        383        301        949
19        980        402        279
20        161        163        647
21        411        413        713
22        327        634        208
23        187        584         81
24        458        906        262
25        786        379        290
26        920        632        628
27        429         98        562
28          0        517        835
29         23        544        917
30        431        678        503

Let’s return to the original starting point for table T2 so that we may try another test:

DELETE FROM T2;

INSERT INTO
  T2
SELECT
  *
FROM
  T2_BACKUP;

COMMIT;

Let’s eliminate the majority of the 30+ SQL statements that are sent to the database by modifying the VBS script:

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Dim dbDatabase
Dim dynData
Dim intS_ID
Dim intS_C2
Dim intS_C3
Dim intS_C4
Dim i
Dim intMissing(30)
Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Set dbDatabase = CreateObject("ADODB.Connection")
Set dynData = CreateObject("ADODB.Recordset")

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

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

dbDatabase.BeginTrans

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  ID," & vbCrLf
strSQL = strSQL & "  COL2," & vbCrLf
strSQL = strSQL & "  COL3," & vbCrLf
strSQL = strSQL & "  COL4" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  ID"

dynData.Open strSQL, dbDatabase, adOpenKeyset, adLockOptimistic

For i = 1 To 30
    intS_C2 = Int(Rnd * 1000) + 1
    intS_C3 = Int(Rnd * 1000) + 1
    intS_C4 = Int(Rnd * 1000) + 1

    If Not (dynData.EOF) Then
        If i = CInt(dynData("id")) Then
            intMissing(i) = False
            dynData("col2") = 0
            dynData("col3") = dynData("col2") + dynData("col3")
            dynData("col4") = intS_C4
            dynData.Update

            dynData.MoveNext
        Else
            intMissing(i) = True
        End If
    Else
        intMissing(i) = True
    End If
Next

'Add the missing rows
For i = 1 To 30
    intS_C2 = Int(Rnd * 1000) + 1
    intS_C3 = Int(Rnd * 1000) + 1
    intS_C4 = Int(Rnd * 1000) + 1

    If intMissing(i) = True Then
        dynData.AddNew
        dynData("id") = i
        dynData("col2") = intS_C2
        dynData("col3") = intS_C3
        dynData("col4") = intS_C4
        dynData.Update
    End If
Next

dynData.Close
dbDatabase.CommitTrans

dbDatabase.Close
Set dynData = Nothing
Set dbDatabase = Nothing

That certainly is better.  Here is the output showing the table’s contents:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0        718        580
 2        405        270         56
 3        244        980         61
 4        391        365        490
 5        156        475        258
 6        629        543        157
 7        939        655        507
 8        391        108        784
 9        460        754        597
10          0        924        280
11         74        106        332
12        129          1        537
13          0        650        244
14         82        192        679
15        455        358        150
16          0        348        799
17         90        758        402
18        462        493        208
19        330         96        590
20        170        928         98
21        444        273        873
22        751        273        674
23        257         90         31
24        323        791        298
25        236        481        255
26        341         45        483
27        207        865        589
28          0        517        835
29        543         81        635
30        411        961        115

Better, but not good enough.  There are too many round-trips between the client and server.  Let’s reset the T2 test table and try again:

DELETE FROM T2;

INSERT INTO
  T2
SELECT
  *
FROM
  T2_BACKUP;

COMMIT;

A third attempt collapses a lot of client-side code into two SQL statement:

Dim dbDatabase

Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Set dbDatabase = CreateObject("ADODB.Connection")

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

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

dbDatabase.BeginTrans

strSQL = "UPDATE" & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "SET" & vbCrLf
strSQL = strSQL & "  COL2=0," & vbCrLf
strSQL = strSQL & "  COL3=COL2+COL3," & vbCrLf
strSQL = strSQL & "  COL4=TRUNC(DBMS_RANDOM.VALUE(1,1000))"
dbDatabase.Execute strSQL

strSQL = "INSERT INTO" & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "SELECT" & vbCrLf
strSQL = strSQL & "  S.S_ID," & vbCrLf
strSQL = strSQL & "  S.S_C2," & vbCrLf
strSQL = strSQL & "  S.S_C3," & vbCrLf
strSQL = strSQL & "  S.S_C4" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  (SELECT" & vbCrLf
strSQL = strSQL & "    ROWNUM S_ID," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C2," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C3," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C4" & vbCrLf
strSQL = strSQL & "  FROM" & vbCrLf
strSQL = strSQL & "    DUAL" & vbCrLf
strSQL = strSQL & "  CONNECT BY" & vbCrLf
strSQL = strSQL & "    LEVEL<=30) S," & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  S.S_ID=T2.ID(+)" & vbCrLf
strSQL = strSQL & "  AND T2.ID IS NULL"
dbDatabase.Execute strSQL

dbDatabase.CommitTrans

Set dbDatabase = Nothing

Here is the output:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0       1711        202
 2        944        284        604
 3        612        909        576
 4        828        606        970
 5        433        868        446
 6        304        770        397
 7        502        257        474
 8        541        906        761
 9        283        614        819
10          0       1507        841
11        772         52        635
12        325         45        792
13          0        677        320
14        691        433        234
15        733        673        416
16          0        489        483
17        257         50         99
18        429        861        108
19        244          4        858
20        323        697        493
21        565        384        960
22        211        153        651
23        762        231        488
24         85        994        204
25        630        235        930
26        890        778        374
27         64        540        663
28          0       1233        955
29         70         16         56
30        493        647        742

Look closely at the above output.  Are you able to spot the “logic bug” in the first two code examples?

I like the above code sample, but we are able to improve it a bit by using a single SQL statement.  First, let’s reset the test table again:

DELETE FROM T2;

INSERT INTO
  T2
SELECT
  *
FROM
  T2_BACKUP;

COMMIT;

Now the code sample that uses a single SQL statement:

Dim dbDatabase

Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Set dbDatabase = CreateObject("ADODB.Connection")

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

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

dbDatabase.BeginTrans

strSQL = "MERGE INTO" & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "USING" & vbCrLf
strSQL = strSQL & "  (SELECT" & vbCrLf
strSQL = strSQL & "    ROWNUM S_ID," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C2," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C3," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C4" & vbCrLf
strSQL = strSQL & "  FROM" & vbCrLf
strSQL = strSQL & "    DUAL" & vbCrLf
strSQL = strSQL & "  CONNECT BY" & vbCrLf
strSQL = strSQL & "    LEVEL<=30) S" & vbCrLf
strSQL = strSQL & "ON" & vbCrLf
strSQL = strSQL & "  (T2.ID=S.S_ID)" & vbCrLf
strSQL = strSQL & "WHEN MATCHED THEN" & vbCrLf
strSQL = strSQL & "  UPDATE SET" & vbCrLf
strSQL = strSQL & "    T2.COL2=0," & vbCrLf
strSQL = strSQL & "    T2.COL3=T2.COL2+T2.COL3," & vbCrLf
strSQL = strSQL & "    T2.COL4=S.S_C4" & vbCrLf
strSQL = strSQL & "WHEN NOT MATCHED THEN" & vbCrLf
strSQL = strSQL & "  INSERT (ID, COL2, COL3, COL4) VALUES" & vbCrLf
strSQL = strSQL & "    (S.S_ID," & vbCrLf
strSQL = strSQL & "    S.S_C2," & vbCrLf
strSQL = strSQL & "    S.S_C3," & vbCrLf
strSQL = strSQL & "    S.S_C4)"
dbDatabase.Execute strSQL

dbDatabase.CommitTrans

Set dbDatabase = Nothing

The output of the above looks like this:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0       1711        286
 2        419         68        698
 3        849        296        986
 4         92         87        433
 5        425        786        802
 6        758        862        868
 7        450        327        978
 8        102        618        382
 9        276        563        620
10          0       1507        629
11        292        591        300
12        521        599        941
13          0        677        438
14        182        905        135
15        716        121        964
16          0        489        165
17        552        661         95
18        332        572        255
19        126        624        463
20        906        422        368
21        328        141        886
22        286        612        685
23        375        868        904
24        240        940        768
25          4        166        447
26        942        754        124
27        547        828        225
28          0       1233        872
29        883        417        215
30        762        427         21

At this point you are probably wondering why I even bothered to use VBScript for such a simple SQL statement.  Let’s reset the test table again:

DELETE FROM T2;

INSERT INTO
  T2
SELECT
  *
FROM
  T2_BACKUP;

COMMIT;

If I was trying to be as efficient as possible, I probably should have just executed the following in SQL*Plus:

MERGE INTO
  T2
USING
  (SELECT
    ROWNUM S_ID,
    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C2,
    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C3,
    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C4
  FROM
    DUAL
  CONNECT BY
    LEVEL<=30) S
ON
  (T2.ID=S.S_ID)
WHEN MATCHED THEN
  UPDATE SET
    T2.COL2=0,
    T2.COL3=T2.COL2+T2.COL3,
    T2.COL4=S.S_C4
WHEN NOT MATCHED THEN
  INSERT (ID, COL2, COL3, COL4) VALUES
    (S.S_ID,
    S.S_C2,
    S.S_C3,
    S.S_C4);

The following shows the modifications made by the above:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0       1711        849
 2        502        487        567
 3        273        966        847
 4        236        544        198
 5        191        970        986
 6        820        316        468
 7        833        651         82
 8         46        583        368
 9         63        685        148
10          0       1507        249
11        111        409         88
12        219        795        409
13          0        677        571
14        771         26        313
15        373        962        186
16          0        489        514
17        230        970        824
18         92        715        131
19        355        220        206
20        996         87        841
21        815        384        375
22        935        455        339
23        606        190        720
24        558        591        341
25        780        207        614
26        267        430        371
27        881        292        655
28          0       1233         70
29        379        466        628
30        293        216        881

We are certainly able to arrive at the correct answer many different ways (and the incorrect answer at least once), but what is the right way to achieve the task placed in front of us is not always easy to see.  The MERGE INTO syntax is one that I have not used often enough, and probably deserves a greater investment of experimentation.

Have you found the logic bug with the first two code samples yet?

dynData("col2") = 0
dynData("col3") = dynData("col2") + dynData("col3")
dynData("col4") = intS_C4

The above works correctly when the columns are updated in that order in a SQL statement, but VBScript requires a minor adjustment to produce the correct, expected results:

dynData("col3") = dynData("col2") + dynData("col3")
dynData("col2") = 0
dynData("col4") = intS_C4

Picky, picky, picky…  :-)





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
  • Adding grid lines

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
strUsername = "MyUsername"
strPassword = "MyPassword"
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(18) = "PHYSICAL READS CACHE"
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.

Script download: OracleStatisticChartViewer.vbs (save as OracleStatisticChartViewer.vbs)

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

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.





Improving Performance by Using a Cartesian Join

18 03 2010

March 18, 2010

(Forward to the Next Post in the Series)

This example is based on a demonstration that I gave during a presentation last year.  I did not go into great detail how the code worked, but I demonstrated that a carefully constructed Cartesian join is helpful and efficient for solutions to certain types of problems.  Assume that you have a table named APPLICATION_LIST that lists all of the modules belonging to an application, another table named USER_LIST that lists each Oracle username that has access to the application, and a third table named USER_PROGRAM_PERMISSION that lists each username that is denied access to one of the application modules.  The table construction may seem a little odd, but this is based on an actual example found in a commercial product.  The goal is to produce a cross-tab style report that shows all users’ permissions to all of the application modules, and have that cross-tab report appear in Excel.  The table definitions for our test tables look like this:

CREATE TABLE APPLICATION_LIST(
  PROGRAM_ID VARCHAR2(30),
  MENU_STRING VARCHAR2(30),
  PRIMARY KEY (PROGRAM_ID));

CREATE TABLE USER_LIST(
  NAME VARCHAR2(30),
  TYPE NUMBER,
  PRIMARY KEY(NAME));

CREATE TABLE USER_PROGRAM_PERMISSION(
  USER_ID VARCHAR2(30),
  PROGRAM_ID VARCHAR2(30),
  PERMISSION CHAR(1),
  PROGRAM_COMPONENT VARCHAR(20),
  PRIMARY KEY(USER_ID,PROGRAM_ID));

We will populate the test tables with the following script:

INSERT INTO
  APPLICATION_LIST
SELECT
  DBMS_RANDOM.STRING('Z',10) PROGRAM_ID,
  DBMS_RANDOM.STRING('A',20) MENU_STRING
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

INSERT INTO
  USER_LIST
SELECT
  'USER'||TO_CHAR(ROWNUM) USER_ID,
  1 TYPE
FROM
  DUAL
CONNECT BY
  LEVEL<=300;

INSERT INTO
  USER_PROGRAM_PERMISSION
SELECT
  USER_ID,
  PROGRAM_COMPONENT,
  PERMISSION,
  'PROGRAM'
FROM
  (SELECT
    UL.NAME USER_ID,
    AL.PROGRAM_ID PROGRAM_COMPONENT,
    'N' PERMISSION
  FROM
    USER_LIST UL,
    APPLICATION_LIST AL
  ORDER BY
    DBMS_RANDOM.VALUE)
WHERE
  ROWNUM<=27000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'APPLICATION_LIST',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'USER_LIST',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'USER_PROGRAM_PERMISSION',CASCADE=>TRUE)

The first VBS script will not use a Cartesian Merge join – instead it will retrieve a list of all users and all application modules, and then probe the USER_PROGRAM_PERMISSION table once for each USER_ID. (IntentionalCartesian1-NoCartesian.VBS – save as IntentionalCartesian1-NoCartesian.VBS)

Const adVarChar = 200
Const adCmdText = 1
Const adCmdStoredProc = 4
Const adParamInput = 1

Dim i
Dim j
Dim strSQL
Dim strLastColumn
Dim strProgramName
Dim strUsername
Dim strPassword
Dim strDatabase
Dim strPermission
Dim snpData
Dim comData
Dim dbDatabase
Dim objExcel

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

On Error Resume Next

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

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

Set snpData = CreateObject("adodb.recordset")

'Create an Excel connection
Set objExcel = CreateObject("Excel.Application")

With objExcel
    .Workbooks.Add
    .ActiveWorkbook.Sheets.Add
    .ActiveSheet.Name = "Application Permissions"

    'Remove the three default worksheets
    For i = 1 To .ActiveWorkbook.Sheets.Count
        If .ActiveWorkbook.Sheets(i).Name = "Sheet1" Then
            .Sheets("Sheet1").Select
            .ActiveWindow.SelectedSheets.Delete
        End If
        If .ActiveWorkbook.Sheets(i).Name = "Sheet2" Then
            .Sheets("Sheet2").Select
            .ActiveWindow.SelectedSheets.Delete
        End If
        If .ActiveWorkbook.Sheets(i).Name = "Sheet3" Then
            .Sheets("Sheet3").Select
            .ActiveWindow.SelectedSheets.Delete
        End If
    Next

    .Visible = True
End With

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  PROGRAM_ID," & vbCrLf
strSQL = strSQL & "  MENU_STRING" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  APPLICATION_LIST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  PROGRAM_ID NOT IN ('.SEPARATOR')" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  MENU_STRING"

snpData.Open strSQL, dbDatabase

If snpData.State = 1 Then
    strProgramName = snpData.GetRows(30000)
    snpData.Close
End If

'Set the number of elements in the strPermission array to match the number of application module names
ReDim strPermission(UBound(strProgramName, 2))

'Copy the module names into Excel
For j = 0 To UBound(strPermission)
    strPermission(j) = strProgramName(1, j) ' & " - " & strProgramName(0, j)
Next
With objExcel
    .Application.ScreenUpdating = False
    .ActiveSheet.Range(.ActiveSheet.Cells(1, 2), .ActiveSheet.Cells(1, 1 + UBound(strProgramName, 2))) = strPermission
End With

'Retrieve the list of users
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  NAME" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  USER_LIST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  TYPE=1" & vbCrLf
strSQL = strSQL & "  AND NAME NOT LIKE '%#'" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  NAME"

snpData.Open strSQL, dbDatabase

If snpData.State = 1 Then
    strUsername = snpData.GetRows(30000)
    snpData.Close
End If

'Set the SQL statement to use to retrieve permissions, ? is a bind variable placeholder
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  PROGRAM_ID," & vbCrLf
strSQL = strSQL & "  PERMISSION" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  USER_PROGRAM_PERMISSION" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  PROGRAM_COMPONENT='PROGRAM'" & vbCrLf
strSQL = strSQL & "  AND USER_ID= ?" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  PROGRAM_ID"

With comData
    'Set up the command properties
    .CommandText = strSQL
    .CommandType = adCmdText
    .CommandTimeout = 30
    .ActiveConnection = dbDatabase

    .Parameters.Append .CreateParameter("user_id", adVarChar, adParamInput, 30, "")
End With

'Loop through each user
For i = 0 To UBound(strUsername, 2)
    'Reset the permissions for the next user
    For j = 0 To UBound(strPermission)
        strPermission(j) = "Y"
    Next

    comData("user_id") = strUsername(0, i)
    Set snpData = comData.Execute

    If snpData.State = 1 Then
        Do While Not (snpData.EOF)
            For j = 0 To UBound(strProgramName, 2)
                If strProgramName(0, j) = snpData("program_id") Then
                    strPermission(j) = snpData("permission")
                    Exit For
                End If
            Next
            snpData.MoveNext
        Loop
        snpData.Close
    End If

    With objExcel
        .ActiveSheet.Cells(i + 2, 1) = strUsername(0, i)
        .ActiveSheet.Range(.ActiveSheet.Cells(i + 2, 2), .ActiveSheet.Cells(i + 2, 1 + UBound(strProgramName, 2))) = strPermission
    End With
Next

'Convert the number of columns into letter notation
strLastColumn = Chr(64 + Int((UBound(strProgramName, 2)) / 26)) & Chr(64 + ((UBound(strProgramName, 2)) Mod 26 + 1))

'Final cleanup
With objExcel
    .ActiveSheet.Range(.ActiveSheet.Cells(1, 2), .ActiveSheet.Cells(1, 1 + UBound(strProgramName, 2))).Orientation = 90
    .ActiveSheet.Columns("A:" & strLastColumn).AutoFit
    .Application.ScreenUpdating = True
    .ActiveSheet.Range("B2").Select
    .ActiveWindow.FreezePanes = True
End With

dbDatabase.Close

Set snpData = Nothing
Set comData = Nothing
Set objExcel = Nothing

If you ignore the fact that the above script redefines the meaning of the strUsername variable, the script works.  The problem with the script is that it repeatedly sends queries to the database, and probably should be optimized to remove the repeated queries (the number of repeated communication to the database server could have been much worse).  Let’s take a look at version 2 of the script (IntentionalCartesian2-NoCartesian.VBS – save as IntentionalCartesian2-NoCartesian.VBS)

Dim i
Dim j
Dim strSQL
Dim strLastColumn
Dim strProgramName
Dim strEmployeename
Dim strUsername
Dim strPassword
Dim strDatabase
Dim strPermission
Dim snpData
Dim dbDatabase
Dim objExcel

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

On Error Resume Next

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

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

Set snpData = CreateObject("adodb.recordset")

'Create an Excel connection
Set objExcel = CreateObject("Excel.Application")

With objExcel
    .Workbooks.Add
    .ActiveWorkbook.Sheets.Add
    .ActiveSheet.Name = "Visual Permissions"

    'Remove the three default worksheets
    For i = 1 To .ActiveWorkbook.Sheets.Count
        If .ActiveWorkbook.Sheets(i).Name = "Sheet1" Then
            .Sheets("Sheet1").Select
            .ActiveWindow.SelectedSheets.Delete
        End If
        If .ActiveWorkbook.Sheets(i).Name = "Sheet2" Then
            .Sheets("Sheet2").Select
            .ActiveWindow.SelectedSheets.Delete
        End If
        If .ActiveWorkbook.Sheets(i).Name = "Sheet3" Then
            .Sheets("Sheet3").Select
            .ActiveWindow.SelectedSheets.Delete
        End If
    Next

    .Visible = True
End With

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  PROGRAM_ID," & vbCrLf
strSQL = strSQL & "  MENU_STRING" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  APPLICATION_LIST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  PROGRAM_ID NOT IN ('.SEPARATOR')" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  MENU_STRING"

snpData.Open strSQL, dbDatabase

If snpData.State = 1 Then
    strProgramName = snpData.GetRows(30000)
    snpData.Close
End If

'Set the number of elements in the strPermission array to match the number of application module names
ReDim strPermission(UBound(strProgramName, 2))

'Copy the module names into Excel
For j = 0 To UBound(strPermission)
    strPermission(j) = strProgramName(1, j) ' & " - " & strProgramName(0, j)
Next
With objExcel
    .Application.ScreenUpdating = False
    .ActiveSheet.Range(.ActiveSheet.Cells(1, 2), .ActiveSheet.Cells(1, 1 + UBound(strProgramName, 2))) = strPermission
End With

'Retrieve the list of users
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  NAME" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  USER_LIST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  TYPE=1" & vbCrLf
strSQL = strSQL & "  AND NAME NOT LIKE '%#'" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  NAME"

snpData.Open strSQL, dbDatabase

If snpData.State = 1 Then
    strEmployeename = snpData.GetRows(30000)
    snpData.Close
End If

'Set the SQL statement to use to retrieve permissions, ? is a bind variable placeholder
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  USER_ID," & vbCrLf
strSQL = strSQL & "  PROGRAM_ID," & vbCrLf
strSQL = strSQL & "  PERMISSION" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  USER_PROGRAM_PERMISSION" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  PROGRAM_COMPONENT='PROGRAM'" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  USER_ID," & vbCrLf
strSQL = strSQL & "  PROGRAM_ID"

snpData.Open strSQL, dbDatabase

If snpData.State = 1 Then
    strAllPermission = snpData.GetRows(600000)
    snpData.Close
End If

strLastUser = ""
intLastPermission = 0

'Loop through all users
For i = 0 To UBound(strEmployeename, 2)
    'Reset the permissions for the next user
    For j = 0 To UBound(strPermission)
        strPermission(j) = "Y"
    Next

    For j = intLastPermission To UBound(strAllPermission, 2)
        If strAllPermission(0, j) = strEmployeename(0, i) Then
            'Examine the permissions for this user
            For k = 0 To UBound(strProgramName, 2)
                If strProgramName(0, k) = strAllPermission(1, j) Then
                    strPermission(k) = strAllPermission(2, j)
                    Exit For
                End If
            Next

        End If

        'Record the loop position so that we do not start at 0 for the next user
        intLastPermission = j

        If strAllPermission(0, j) > strEmployeename(0, i) Then
            'We have passed the last permission for this user, exit the For loop
            Exit For
        End If
    Next

    With objExcel
        .ActiveSheet.Cells(i + 2, 1) = strEmployeename(0, i)
        .ActiveSheet.Range(.ActiveSheet.Cells(i + 2, 2), .ActiveSheet.Cells(i + 2, 1 + UBound(strProgramName, 2))) = strPermission
    End With
Next

'Convert the number of columns into letter notation
strLastColumn = Chr(64 + Int((UBound(strProgramName, 2)) / 26)) & Chr(64 + ((UBound(strProgramName, 2)) Mod 26 + 1))

'Final cleanup
With objExcel
    .ActiveSheet.Range(.ActiveSheet.Cells(1, 2), .ActiveSheet.Cells(1, 1 + UBound(strProgramName, 2))).Orientation = 90
    .ActiveSheet.Columns("A:" & strLastColumn).AutoFit
    .Application.ScreenUpdating = True
    .ActiveSheet.Range("B2").Select
    .ActiveWindow.FreezePanes = True
End With

dbDatabase.Close

Set snpData = Nothing
Set dbDatabase = Nothing
Set objExcel = Nothing

While the second version of the script is better than the first, we are still sending three SQL statements to the server.  We can improve that with a Cartesian join.  Let’s take a look at version 3 of the script (IntentionalCartesian3-Cartesian.VBS – save as IntentionalCartesian3-Cartesian.VBS)

Dim i
Dim intUserCount
Dim intPermissionCount
Dim strLastColumn
Dim strLastUser
Dim strSQL
Dim strPermission(500)
Dim strModuleName(500)

Dim strUsername
Dim strPassword
Dim strDatabase
Dim snpData
Dim dbDatabase
Dim objExcel

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

On Error Resume Next

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

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

Set snpData = CreateObject("adodb.recordset")

'Create an Excel connection
Set objExcel = CreateObject("Excel.Application")

With objExcel
    .Workbooks.Add
    .ActiveWorkbook.Sheets.Add
    .ActiveSheet.Name = "Application Permissions"

    'Remove the three default worksheets
    For i = 1 To .ActiveWorkbook.Sheets.Count
        If .ActiveWorkbook.Sheets(i).Name = "Sheet1" Then
            .Sheets("Sheet1").Select
            .ActiveWindow.SelectedSheets.Delete
        End If
        If .ActiveWorkbook.Sheets(i).Name = "Sheet2" Then
            .Sheets("Sheet2").Select
            .ActiveWindow.SelectedSheets.Delete
        End If
        If .ActiveWorkbook.Sheets(i).Name = "Sheet3" Then
            .Sheets("Sheet3").Select
            .ActiveWindow.SelectedSheets.Delete
        End If
    Next

    .Visible = True
End With

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  AUP.NAME USERNAME," & vbCrLf
strSQL = strSQL & "  AUP.MENU_STRING MODULE," & vbCrLf
strSQL = strSQL & "  NVL(UGA.PERMISSION,AUP.DEFAULT_PERMISSION) PERMISSION" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  (SELECT" & vbCrLf
strSQL = strSQL & "    UL.NAME NAME," & vbCrLf
strSQL = strSQL & "    A.PROGRAM_ID," & vbCrLf
strSQL = strSQL & "    A.MENU_STRING," & vbCrLf
strSQL = strSQL & "    'Y' DEFAULT_PERMISSION" & vbCrLf
strSQL = strSQL & "  FROM" & vbCrLf
strSQL = strSQL & "    APPLICATION_LIST A," & vbCrLf
strSQL = strSQL & "    USER_LIST UL" & vbCrLf
strSQL = strSQL & "  WHERE" & vbCrLf
strSQL = strSQL & "    A.PROGRAM_ID NOT IN ('.SEPARATOR')" & vbCrLf
strSQL = strSQL & "    AND UL.NAME NOT LIKE '%#') AUP," & vbCrLf
strSQL = strSQL & "  (SELECT" & vbCrLf
strSQL = strSQL & "    USER_ID," & vbCrLf
strSQL = strSQL & "    PROGRAM_ID," & vbCrLf
strSQL = strSQL & "    PERMISSION" & vbCrLf
strSQL = strSQL & "  FROM" & vbCrLf
strSQL = strSQL & "    USER_PROGRAM_PERMISSION" & vbCrLf
strSQL = strSQL & "  WHERE" & vbCrLf
strSQL = strSQL & "    PROGRAM_COMPONENT='PROGRAM') UGA" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  AUP.NAME=UGA.USER_ID(+)" & vbCrLf
strSQL = strSQL & "  AND AUP.PROGRAM_ID=UGA.PROGRAM_ID(+)" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  AUP.NAME," & vbCrLf
strSQL = strSQL & "  AUP.MENU_STRING"

snpData.Open strSQL, dbDatabase

If snpData.State = 1 Then
  With objExcel
    .Application.ScreenUpdating = False
    strLastUser = ""
    intUserCount = 0
    Do While Not snpData.EOF
        If strLastUser <> snpData("username") Then
            If strLastUser <> "" Then
                'Write out the permissions for the previous user
                .ActiveSheet.Range(.ActiveSheet.Cells(intUserCount + 1, 1), .ActiveSheet.Cells(intUserCount + 1, 1 + intPermissionCount)) = strPermission
            End If
            If intUserCount = 1 Then
                'Write out the module names
                .ActiveSheet.Range(.ActiveSheet.Cells(1, 1), .ActiveSheet.Cells(1, 1 + intPermissionCount)) = strModuleName
            End If

            strPermission(0) = snpData("username")
            intPermissionCount = 0
            intUserCount = intUserCount + 1
        End If
        intPermissionCount = intPermissionCount + 1
        strPermission(intPermissionCount) = snpData("permission")
        strLastUser = snpData("username")

        If intUserCount = 1 Then
            'Record the module names
            strModuleName(intPermissionCount) = snpData("module")
        End If

        snpData.MoveNext
    Loop
    If strLastUser <> "" Then
        'Write out the permissions for the last user
        .ActiveSheet.Range(.ActiveSheet.Cells(intUserCount + 1, 1), .ActiveSheet.Cells(intUserCount + 1, 1 + intPermissionCount)) = strPermission
    End If

    strLastColumn = Chr(64 + Int((intPermissionCount) / 26)) & Chr(64 + ((intPermissionCount) Mod 26 + 1))
    .ActiveSheet.Range(.ActiveSheet.Cells(1, 2), .ActiveSheet.Cells(1, 1 + intPermissionCount)).Orientation = 90
    .ActiveSheet.Columns("A:" & strLastColumn).AutoFit
    .Application.ScreenUpdating = True
    .ActiveWindow.FreezePanes = False
    .ActiveSheet.Range("B2").Select
    .ActiveWindow.FreezePanes = True

    .Application.ScreenUpdating = True
  End With
End If

snpData.Close
dbDatabase.Close
Set snpData = Nothing
Set dbDatabase = Nothing
Set objExcel = Nothing

Notice in the above that the client-side code is much smaller, and we have collapsed the three SQL statements into a single SQL statement with the help of a Cartesian join between the APPLICATION_LIST and USER_LIST tables.  The end result looks like this:








Follow

Get every new post delivered to your Inbox.

Join 141 other followers