OraNA :: Oracle News Aggregator

In 6 attributes of a good ScrumMaster Mike Cohn repeats the common line that the ScrumMaster role does not always require a full-time, eight-hour-a-day commitment. Often the 'orchestra conductor' role of ScrumMaster is an unofficial one within your organisation even though it clearly has well defined functions and responsibilities. So many people do ask the question Is a ScrumMaster a full time position? As Boris points out, it is, and he explains why it is a 100% fulltime job.

The ScrumMaster has internal and external responsibilities. Even if the team is well disciplined with following the process, and they address most of their own impediments there is still the challenge of being a gatekeeper between the management and the team. This is being recognised in many organisations now and you can even see ScrumMaster as a recruitment position. It's interesting to note that many of these positions have Project Manager / ScrumMaster as the title.

What does a Project Manager do that a ScrumMaster does not (or vice versa)? A project manager is the person who has the overall responsibility for the successful planning and execution of a project. This title is used in the construction industry, architecture, information technology and many different occupations that are based on production of a product or service. While strictly speaking, the team, rather than the ScrumMaster has responsibility for the success of the project, a ScrumMaster does assume responsibility for the team’s adoption of Scrum and practice of it. A ScrumMaster takes on this responsibility without assuming any of the power that might be useful in achieving in it.

To boil it all down to it's essence, a ScrumMaster is a Project Manager who has realised that they don't really have the power to successfully deliver a project, and has adopted a framework to take advantage of that humbling position.

PROGRAMA FORMACION PARTNERS Talleres FMW iSeminars DB Bootcamps DB&FMW Si deseas descargarte el calendario completo en PDF haz click aqui El equipo de Alianzas & Canal de Oracle a diseñado un Programa de Formación Gratuita exclusivo para Partners. Este calendario incluye diferentes seminarios y cursos que cubren tanto el área Comercial como Técnica de FMW (SOA, E2.0, IdM, BI) y DB & Options. Como Novedad, presentamos los Nuevos Bootcamps impartidos por Partners especialistas que le ayudaran a conseguir las especializaciones del nuevo programa de OPN a un precio realmente competitivo (250€ persona y día). Para más información haga click aqui

If you ever experience these symptoms, ask your self whether you have Grid Control (or OEM) running.Single Sign On failsOID Processes suddenly have stopped (which explains the above)ODS database account is locked, which seems to cause the problemUnlocking the ODS account resolves the problem, but it gets locked after a short period.The cause is the fact you changed the ODS password. Now, you may

Sometimes it's hard to find something if you don't use it that much.

I was searching for a Word and Character count in Pages (OSX). Pages is like Word, but than from Apple. Anyway, in Pages most nice "things" are under the Inspector icon.

The first icon (Document) and the third tab in there, give you what you need. Information about your document, like the number of words you used, number of characters etc.


The better you know something, the faster you can get what you want, it applies to everything ...

March 12, 2010

I thought that we would try something a bit more difficult today.  In previous articles I showed how to generate and read 10046 trace files using various methods, and I also showed a couple of different VBS scripts that could interact with an Oracle database.  With some effort we could even read through a 10046 to pull out bits of information, much like TKPROF, but it probably does not make much sense to reproduce what TKPROF already accomplishes.  I thought instead what I would do is to create a VBS script that generates a 10046 trace file at level 4, while executing a couple of SQL statements.  A second VBS script will read the raw 10046 trace file and convert that trace file back into a VBS script, complete with bind variables.  The code for the second VBS script is based on some of the code in my Toy project for performance tuning – something that I originally created just to see if it could be done.

First, we need a table to use as the data source for the first VBS script – this is the test table used in this blog article:

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

With the test table built, we execute the following simple VBS script (using either CSCRIPT or WSCRIPT on a Windows client):

Const adCmdText = 1
Const adNumeric = 131
Const adDate = 7
Const adDBDate = 133
Const adDBTimeStamp = 135
Const adDBTime = 134
Const adVarChar = 200
Const adParamInput = 1

Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Dim dbDatabase
Dim snpDataEmployees
Dim comDataEmployees
Dim snpDataAttend
Dim comDataAttend
Dim snpDataEmpRecord
Dim comDataEmpRecord

Set dbDatabase = CreateObject("ADODB.Connection")
Set snpDataEmployees = CreateObject("ADODB.Recordset")
Set comDataEmployees = CreateObject("ADODB.Command")
Set snpDataAttend = CreateObject("ADODB.Recordset")
Set comDataAttend = CreateObject("ADODB.Command")
Set snpDataEmpRecord = CreateObject("ADODB.Recordset")
Set comDataEmpRecord = CreateObject("ADODB.Command")

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.Execute "ALTER SESSION SET TRACEFILE_IDENTIFIER = 'VBS2TRACE2VBS'"
dbDatabase.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'"

strSQL = "INSERT INTO EMPLOYEE_RECORD_TEST(" & VBCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & VBCrLf
strSQL = strSQL & "  SHIFT_DATE," & VBCrLf
strSQL = strSQL & "  INDIRECT_ID)" & VBCrLf
strSQL = strSQL & "VALUES(" & VBCrLf
strSQL = strSQL & "  ?," & VBCrLf
strSQL = strSQL & "  ?," & VBCrLf
strSQL = strSQL & "  ?)"

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

    .Parameters.Append .CreateParameter("employee_id", adVarChar, adParamInput, 15, "TEST")
    .Parameters.Append .CreateParameter("shift_date", adDate, adParamInput, 8, Date)
    .Parameters.Append .CreateParameter("indirect_id", adVarchar, adParamInput, 15, "HOL")
End With

'Rollback Test
dbDatabase.BeginTrans

comDataEmpRecord.Execute

dbDatabase.RollbackTrans

strSQL = "SELECT DISTINCT" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE>= ?" & vbCrLf
strSQL = strSQL & "  AND INDIRECT_ID= ?" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID"

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

    .Parameters.Append .CreateParameter("shift_date", adDate, adParamInput, 8, DateAdd("d", -90, Date))
    .Parameters.Append .CreateParameter("indirect_id", adVarChar, adParamInput, 15, "VAC")
End With

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'2',1,0)) MON_COUNT," & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'3',1,0)) TUE_COUNT," & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'4',1,0)) WED_COUNT," & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'5',1,0)) THU_COUNT," & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'6',1,0)) FRI_COUNT" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID= ?" & vbCrLf
strSQL = strSQL & "  AND INDIRECT_ID= ?"

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

    .Parameters.Append .CreateParameter("employee_id", adVarChar, adParamInput, 15, "HOOPER")
    .Parameters.Append .CreateParameter("indirect_id", adVarChar, adParamInput, 15, "EXCUSE")
End With

Set snpDataEmployees = comDataEmployees.Execute

If Not (snpDataEmployees Is Nothing) Then
    Do While Not snpDataEmployees.EOF
        comDataAttend("employee_id") = snpDataEmployees("employee_id")
        comDataAttend("indirect_id") = "EXCUSE"
        Set snpDataAttend = comDataAttend.Execute
        If Not snpDataAttend.EOF Then
            'Do Something with the data
        End If
        snpDataAttend.Close

        comDataAttend("indirect_id") = "ABS"
        Set snpDataAttend = comDataAttend.Execute
        If Not snpDataAttend.EOF Then
            'Do Something with the data
        End If
        snpDataAttend.Close

        snpDataEmployees.MoveNext
    Loop

    snpDataEmployees.Close
End If

dbDatabase.Close
Set snpDataEmployees = Nothing
Set snpDataAttend = Nothing
Set comDataEmployees = Nothing
Set comDataAttend = Nothing
Set snpDataEmpRecord = Nothing
Set comDataEmpRecord = Nothing
Set dbDatabase = Nothing

(TestScript.vbs - save as TestScript.vbs)

In the above, replace MyDB with a valid database name from the tnsnames.ora file, MyUsername with a valid username, and MyPassword with the password for the user.  The script starts by starting a transaction (the default behavior is an implicit commit), a row is inserted into the test table, and then a ROLLBACK is performed.  The script then submits a SQL statement that retrieves a list of 5 employees from the test table.  For each of the (up to) 5 employees a second SQL statement is executed with two different bind variable sets to determine the number of each week day the employee has been out of work on an excused (EXCUSE) or unexcused (ABS) absence.  If I were writing a real program to accomplish this task I would combine the three SELECT statements into a single SELECT statement, but I want to demonstrate how the second VBS script handles multiple SQL statements that are open at the same time.

Running the above script generated a trace file when executed against Oracle Database 11.2.0.1: or112_ora_5482_VBS2TRACE2VBS.trc  (save as C:\or112_ora_5482_VBS2TRACE2VBS.trc – Windows users can view the file with Wordpad and convert the file into a plain text file that can be opened with Notepad).  The goal is to take the trace file and transform it back into a VBS script, ignoring SQL statements that appear in the trace file at a depth greater than 0.

The output of the VBS script that reads the 10046 trace file and generates a VBS file should look something like this:

'Source File:C:\or112_ora_5482_VBS2TRACE2VBS.trc

HyperactiveTrace

Sub HyperactiveTrace()
    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 adChar = 129
    Const adUseClient = 3

    Dim i
    Dim strSQL
    Dim strUsername
    Dim strPassword
    Dim strDatabase

    Dim dbDatabase
    Set dbDatabase = CreateObject("ADODB.Connection")
    Dim snpData1
    Dim comData1
    Set snpData1 = CreateObject("ADODB.Recordset")
    Set comData1 = CreateObject("ADODB.Command")
    Dim snpData2
    Dim comData2
    Set snpData2 = CreateObject("ADODB.Recordset")
    Set comData2 = CreateObject("ADODB.Command")
    Dim snpData3
    Dim comData3
    Set snpData3 = CreateObject("ADODB.Recordset")
    Set comData3 = CreateObject("ADODB.Command")
    Dim snpData4
    Dim comData4
    Set snpData4 = CreateObject("ADODB.Recordset")
    Set comData4 = CreateObject("ADODB.Command")
    Dim snpData5
    Dim comData5
    Set snpData5 = CreateObject("ADODB.Recordset")
    Set comData5 = CreateObject("ADODB.Command")
    Dim snpData6
    Dim comData6
    Set snpData6 = CreateObject("ADODB.Recordset")
    Set comData6 = CreateObject("ADODB.Command")
    Dim snpData7
    Dim comData7
    Set snpData7 = CreateObject("ADODB.Recordset")
    Set comData7 = CreateObject("ADODB.Command")
    Dim snpData8
    Dim comData8
    Set snpData8 = CreateObject("ADODB.Recordset")
    Set comData8 = CreateObject("ADODB.Command")
    Dim snpData9
    Dim comData9
    Set snpData9 = CreateObject("ADODB.Recordset")
    Set comData9 = CreateObject("ADODB.Command")
    Dim snpData10
    Dim comData10
    Set snpData10 = CreateObject("ADODB.Recordset")
    Set comData10 = CreateObject("ADODB.Command")
    Dim snpData11
    Dim comData11
    Set snpData11 = CreateObject("ADODB.Recordset")
    Set comData11 = CreateObject("ADODB.Command")
    Dim snpData12
    Dim comData12
    Set snpData12 = CreateObject("ADODB.Recordset")
    Set comData12 = CreateObject("ADODB.Command")
    Dim snpData13
    Dim comData13
    Set snpData13 = CreateObject("ADODB.Recordset")
    Set comData13 = CreateObject("ADODB.Command")
    Dim snpData14
    Dim comData14
    Set snpData14 = CreateObject("ADODB.Recordset")
    Set comData14 = CreateObject("ADODB.Command")
    Dim snpData15
    Dim comData15
    Set snpData15 = CreateObject("ADODB.Recordset")
    Set comData15 = CreateObject("ADODB.Command")
    Dim snpData16
    Dim comData16
    Set snpData16 = CreateObject("ADODB.Recordset")
    Set comData16 = CreateObject("ADODB.Command")
    Dim snpData17
    Dim comData17
    Set snpData17 = CreateObject("ADODB.Recordset")
    Set comData17 = CreateObject("ADODB.Command")
    Dim snpData18
    Dim comData18
    Set snpData18 = CreateObject("ADODB.Recordset")
    Set comData18 = CreateObject("ADODB.Command")
    Dim snpData19
    Dim comData19
    Set snpData19 = CreateObject("ADODB.Recordset")
    Set comData19 = CreateObject("ADODB.Command")
    Dim snpData20
    Dim comData20
    Set snpData20 = CreateObject("ADODB.Recordset")
    Set comData20 = 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

    'dbDatabase.BeginTrans

    'Transaction Committed and NO Records were Affected, Need to determine transaction start
    dbDatabase.CommitTrans

    'dbDatabase.BeginTrans

    Set comData3 = CreateObject("ADODB.Command")

    strSQL = "INSERT INTO EMPLOYEE_RECORD_TEST(" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
    strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
    strSQL = strSQL & "  INDIRECT_ID)" & vbCrLf
    strSQL = strSQL & "VALUES(" & vbCrLf
    strSQL = strSQL & "  ?," & vbCrLf
    strSQL = strSQL & "  ?," & vbCrLf
    strSQL = strSQL & "  ?)"

    With comData3
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
        'Bind variables will be defined below, if there are no bind variable, uncomment the next line and add the looping construct
    End With
    'comData3.Execute

    With comData3
        .Parameters.Append .CreateParameter("B1", adChar, adParamInput, 4, "TEST")
        .Parameters.Append .CreateParameter("B2", adDate, adParamInput, 7, "3/11/2010 0:0:0")
        .Parameters.Append .CreateParameter("B3", adChar, adParamInput, 3, "HOL")
    End With

    comData3("B1") = "TEST"
    comData3("B2") = cDate("3/11/2010 0:0:0")
    comData3("B3") = "HOL"

    comData3.Execute

    'Transaction Rolled Back and Records Should have been Affected, Need to determine transaction start
    dbDatabase.RollbackTrans

    'dbDatabase.BeginTrans

    'Cursor 2 Closing
    If snpData2.State = 1 Then
        snpData2.Close
    End If
    Set comData2 = Nothing

    Set comData2 = CreateObject("ADODB.Command")

    strSQL = "SELECT DISTINCT" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_ID" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  SHIFT_DATE>= ?" & vbCrLf
    strSQL = strSQL & "  AND INDIRECT_ID= ?" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_ID"

    With comData2
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
        'Bind variables will be defined below, if there are no bind variable, uncomment the next line and add the looping construct
    End With
    'Set snpData2 = comData2.Execute

    'Cursor 4 Closing
    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set comData4 = Nothing

    'Cursor 4 Closing
    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set comData4 = Nothing

    'Cursor 4 Closing
    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set comData4 = Nothing

    With comData2
        .Parameters.Append .CreateParameter("B1", adDate, adParamInput, 7, "12/11/2009 0:0:0")
        .Parameters.Append .CreateParameter("B2", adChar, adParamInput, 3, "VAC")
    End With

    comData2("B1") = cDate("12/11/2009 0:0:0")
    comData2("B2") = "VAC"

    Set snpData2 = comData2.Execute

    If Not (snpData2 Is Nothing) Then
        Do While Not snpData2.EOF

            snpData2.MoveNext
        Loop
    End If

    'Cursor 4 Closing
    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set comData4 = Nothing

    Set comData4 = CreateObject("ADODB.Command")

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'2',1,0)) MON_COUNT," & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'3',1,0)) TUE_COUNT," & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'4',1,0)) WED_COUNT," & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'5',1,0)) THU_COUNT," & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'6',1,0)) FRI_COUNT" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_ID= ?" & vbCrLf
    strSQL = strSQL & "  AND INDIRECT_ID= ?"

    With comData4
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
        'Bind variables will be defined below, if there are no bind variable, uncomment the next line and add the looping construct
    End With
    'Set snpData4 = comData4.Execute

    With comData4
        .Parameters.Append .CreateParameter("B1", adChar, adParamInput, 4, "ERIC")
        .Parameters.Append .CreateParameter("B2", adChar, adParamInput, 6, "EXCUSE")
    End With

    comData4("B1") = "ERIC"
    comData4("B2") = "EXCUSE"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    'Cursor 5 Closing
    If snpData5.State = 1 Then
        snpData5.Close
    End If
    Set comData5 = Nothing

    comData4("B1") = "ERIC"
    comData4("B2") = "ABS"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    'Cursor 5 Closing
    If snpData5.State = 1 Then
        snpData5.Close
    End If
    Set comData5 = Nothing

    comData4("B1") = "JOE"
    comData4("B2") = "EXCUSE"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "JOE"
    comData4("B2") = "ABS"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "MIKE"
    comData4("B2") = "EXCUSE"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "MIKE"
    comData4("B2") = "ABS"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "SAM"
    comData4("B2") = "EXCUSE"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "SAM"
    comData4("B2") = "ABS"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    'Transaction Committed and NO Records were Affected, Need to determine transaction start
    dbDatabase.CommitTrans

    'dbDatabase.BeginTrans

    'Cursor 5 Closing
    If snpData5.State = 1 Then
        snpData5.Close
    End If
    Set comData5 = Nothing

    '*************************************************************
    'Maximum Recordset Number Used is 5 - Adjust the Code at the Start Accordingly
    '*************************************************************

    If snpData1.State = 1 Then
        snpData1.Close
    End If
    Set snpData1 = Nothing

    If snpData2.State = 1 Then
        snpData2.Close
    End If
    Set snpData2 = Nothing

    If snpData3.State = 1 Then
        snpData3.Close
    End If
    Set snpData3 = Nothing

    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set snpData4 = Nothing

    If snpData5.State = 1 Then
        snpData5.Close
    End If
    Set snpData5 = Nothing

    Set comData1 = Nothing
    Set comData2 = Nothing
    Set comData3 = Nothing
    Set comData4 = Nothing
    Set comData5 = Nothing

    dbDatabase.Close
    Set dbDatabase = Nothing
End Sub

(TraceToVBSOutput.vbs – save as TraceToVBSOutput.vbs)

If you compare the original TestScript.vbs with the above output, we see that the two scripts are similar, but with a couple of distinct differences:

• It is not necessarily easy to determine when a transaction starts, but it is possible to determine when a transaction ends.  The script that reads the trace file inserts ‘dbDatabase.BeginTrans where it believes that a transaction should start – remove the ‘ if that is the correct starting point for the transaction.
• Looping structures with nested SQL statements (the retrieval of the employee list from the EMPLOYEE_RECORD_TEST table and the probing of matching rows for each of those employees) cannot be reproduced automatically – you will have to recognize when one SQL statement is feeding the bind variables of a second SQL statement.
• The VBS script assumes that up to 20 cursors will be open at any one time, but will automatically handle many more than 20 simultaneously open cursors.  The resulting VBS file should be cleaned up to remove the unneeded comData and snpData objects.
• There are spurious snpDatan.Close statements – see the suggestions for improvements.
• SQL statements submitted without bind variables will not have code written to execute those statements in the generated VBS file - see the suggestions for improvements.
• Bind variables that are submitted as VARCHAR (adVarchar) are written to the trace file as if the bind variables were declared as CHAR (adChar) – while this does not appear to cause a problem, it might appear to be an unexpected change.

Suggestions for improvement:

• Recognize the EXEC line in the 10046 trace to actually execute the SQL statement, rather than executing the SQL statement in response to the submission of bind variables.
• Allow submitting the source trace file name and the destination VBS file on the command line.
• Allow submitting the username, password, and database name on the command line or in a web page.
• Correct the script so that it does not attempt to close recordsets when those recordsets were never opened at dep=0 – this is caused by the script seeing a recursive SQL statement with the same cursor number as a cursor having a depth of 0.

The VBS script that converts 10046 trace files to VBS files may be downloaded here: TraceToVBS.vbs (save as TraceToVBS.vbs).  There may be bugs in the script, but it should be close enough to provide some degree of educational benefit.

I have been asked this question time and time again – “Why do you need a dedicated MDM solution now when all this time IT has been able to help businesses with product attributes as descriptive Flexfields, custom designed tables or other stitched up solutions?”

 

Well, most of us who have been working with Item Master in the past have been forced to utilize the application’s unintended features creatively to store product attributes in Categories, Cross References, DFFs etc. Finally, when it becomes unmanageable, custom tables provided refuge of the last resort. Think of it, PIM Data Hub is the natural progression step for the organizations at the higher end of the product MDM maturity spectrum.

 

I beg to differ with the opinion that MDM is just another application to store additional data.  There is no disputing that the products are both the bread and butter and the key ingredient to success for any organization. Now that most companies in the marketplace are past the struggle of implementing Enterprise Resource Management systems, they are paying attention to software that provides them deeper insight into the products, improve supply chain efficiencies and help them connect to the customers better. Internet based purchasing has added fuel to the fire where online retailers do need to provide more than just the name and description of the product and the customer across the globe are rightfully demanding more information to make better buying decision. UCCNet and other standardization concepts that have been around for a while are finally shaping up and the retailers in turn are collecting hundreds of product attributes from the manufacturers about the product that define:

-        Product Dimensions

-        Key properties

-        Composition

-        Quality Information

-        Packaging

-        Regulatory and Compliance Information

 

To easily sum up, the following matrix provides the key reasons for implementing PIM Data Hub as opposed to simple Item Master:

 

Features	Product Information Management Data Hub(PIM)	Item Master (IM)
Centralized Data	PIM centralizes all product data in hierarchical catalogs with unlimited attributes in addition to the ERP Operational attributes. PIM Product Workbench provides 360 degree view of:  ·       Product Attributes ·       Attachments (capability to display pictures and schematic diagrams etc. ·       BOM ·       Product Lifecycle and changes during phases	IM focuses mainly on operational and some transactional attributes.    Functional area categories can be utilized for reporting.
Extended Attributes	Extended attributes can serve as storage and validation of the important product related data such as marketing, sales, cost management and product research etc. Such data is  usually stored in spreadsheets on individual laptops in most organizations making is costly and error prone to extract	Limited capability with Descriptive Flexfields(DFF) and Item Catalog attributes to store additional information
Flexible Attributes	PIM provides amazing flexibility in defining various types of attributes that can be keyed in by the user or derived from other attributes using dynamic functions.	Data must be static in nature and user entered.
Display Flexibility	PIM provides flexibility to create your own page layout. This feature provides easy means to displaying attributes on item pages by business area thus facilitates role-based attribute maintenance. Excellent productivity feature!	Predefined attribute arrangement- Live with it :–(
Advanced Search Capabilities	Customizable advanced searches provide highly flexible searching capabilities that can easily help identify the duplicates parts thereby saving cost in creating new parts, procurement and wastage	Item Master focus is simply in keep the ERP running. Good luck searching if you do not have all the attributes embedded in the Product description.
Display Formats	PIM Display formats are equivalent to ‘Folder Functionality’ on Steroids	Folder functionality is limited to only operation attributes: DFF and catalog attributes can’t be displayed in folders
Excel Export and Import	Any data displayed on the search results can be exported to excel and imported back after mass updates. Self-service capabilities of the PIM application greatly reduce IT work making information available to right people at the right time!	Limited excel export capabilities and NO excel import capabilities are available from Item Master
Accelerate New product development	PIM provides the most extensible and configurable workflow to automate the ‘New Product Introduction’	Workflow capabilities are next to none and workflow customizations can easily add to the implementation cost
Synchronize data with trading partners	Workflow based events make it easy to synchronize data. Latest releases also provide capabilities to use out-of-the-box Service Oriented Architecture (SOA) integration services	Still relies on old trigger based functionality to facilitate the data export and synchronization
Data Governance	Product data governance can be handled via automating processes and rule-based validations. Additionally, PIM data security in addition can help achieve the governance objectives	Governance is achievable with process changes and form customizations but is a sticky situation
Change Control	Lifecycle phase based change control of attributes, attachments, BOM and Routings	No lifecycle controls are available in the Item Master. ECOs functionality is very inflexible in terms of workflows
Security	Highly granular role based Security available at the attribute group level	Item Master has old menu/function based security
Workflow based Productivity	Flexible and highly configurable workflows for product, attachment, BOM, Routing and other attribute changes enhance productivity without adding to the cost of implementation	Low to no workflow flexibility unless workflow is customized

 

Next: In my next post, I will be covering the ‘R12 PIM Data Hub Valuable Functionalities’.

3 weeks of traveling through Asia and Australia - nearly 500 customers and partners in 8 workshops in Tokyo, Seoul, Beijing, Shenzhen, Singapore, Melbourne, Perth and Manila. Great people in all places, many interesting discussions, several new reference prospects for Oracle Database 11g Release 2 - YOU should upgrade as well pretty soon :-)

But now it's time to go home. We are a bit exhausted but we really enjoyed it talking to and with you. And I'd suppose we'll meet again the sooner or later.

Thanks to everybody - and special thanks to the local colleagues and especially to Abe-san, Kota-san, Blair Layton and Shaheen Ismail for taking care on us, organizing our workshops and the whole setup!!!

Thanks a lot to everybody for attending today in the Oracle Office in Manila. It was (actually it still is as we are still running the workshop at the moment) a pleasure for us - and great fun, too :-)
And, as always, please download the most recent version of the slides from here:
http://apex.oracle.com/folien
Use the keyword (Schluesselwort): upgrade112

Let us and the local colleagues from Oracle know once you have upgraded successfully - and don't wait too long - 10.2 goes out of Premier Support end of July this year - that's only 4.5 months to go.

My last day already, so I hereby leave you with some impressions from Tanel’s Training Day. Maybe until next year. If you also want to learn from him, then here’s your chance in Holland. M.

How many times have you been working on implementing, unsuccessfully, a feature in your application which relies on a framework, container, or some other indirection layer? You've studied the specifications, memorised the API and you're following the developers guide which are all telling you that what you are trying to implement is taken care of, but it still does not work!

While recently working on a really simple application feature using ADF data model validation I had just this experience. The feature just did not work and there was nothing in the logs to indicate what the problem was or that there was even a problem. Admittedly, I was using an internal development build, so it would not have been a fully tested version. I checked, and double checked everything, the documentation, searched through forums and blogs, trying to find out what I was doing wrong.

By the way, while searching I did come across an interesting blog about ADF bugs and workarounds. Steve Muench's Dive into ADF blog is probably the most comprehensive on ADF features though.

It turned out I wasn't doing anything wrong but I only found this out when a colleague suggested I check the bugs database. And there it was! The exact scenario I was trying to implement had a bug against it. What a relief, because I thought I was missing or skipping over something really obvious and fundamental and that's why it wasn't working for me. Better still, the defect was marked as closed so it will be available in the next internal developers build.

This got me thinking about all the documentation sources I had been to and how useful it would be to have a cross reference between the structured and unstructured text of developer guides, API, forums, blogs and bug databases. Isn't that what search engines are for?

My last day already, so I hereby leave you with some impressions from Tanel’s Training Day. Maybe until next year.

If you also want to learn from him, then here’s your chance in Holland. Scripts and tools used by Tanel can be found here.

OK so not a customized world, but do you know what goes into creating that customized web store front for you? How do you get those additional offers from vendors when you call in for service or when you are browsing a storefront.

This is what is has been happening behind the scenes. 

When a customer calls in a contact center for service, at the end of the conversation, they are offered a new product, or service. But what just transpired was that the CRM system that was in place had routed the call to the right agent, the agent got the pop up screen with the customer information, and the call request  was handled. Then came the decision point to cross-sell and up-sell, The agent got some recommended offers that were created based on analyzed data (this data had been put into a data warehouse, modeled, profiled and rules were implemented e.g.. People with profile X like product Y).  But with this system, what happens is that analytics can be applied to a very small subset.

Now comes Real Time Decisioning (RTD), this helps companies make optimal decisions in the context of transactional systems. It enables companies to improve business processes with real time intelligence on every single transaction. RTD is like a service plug-in that you put at the back of your transactional systems and that you  ping to get a recommendation.  It listens to business process flows and data moving through the process, getting all that data, processes all that you can do with that data, and gives out out various offers. It takes a process centric view of analytics rather than just a data centric view. It continuously observes and learns from ever-changing customer behavior and applies those insights to providing real-time decisions and recommendations at any customer touch point.

At Oracle we define Real Time Decisioning as “ The solution that addresses a business issue faced by all organizations : how to make accurate decisions, using the most up to date information, in real time…consistently and in large volumes”.

Here is a video on recommendation engines that are benefiting from real time decisioning today and see how it is helping online vendors.

SOA

Clemens Utschig puts his focus on SOA for the java developer.

EBS

New this week at the Oracle E-Business Suite Technology blog:

OCFS2 for Linux Certified for E-Business Suite Release 12 Application Tiers

Performing Better: Improving Skills and Knowledge of EBS Tools and Technology

E-Business Suite Release 12.1.1 Consolidated Upgrade Patch 1 Now Available

Patching

Also in the realm of EBS this week is this excellent summary of patch types prepared by Renee Van Dusen of Oracle:

Patch Types & Reasons to Patch

Oracle consolidates and releases the following patch types. Patches include bug fixes as well as new functionality.

• Version Maintenance Pack – This would be a large consolidation of patches including all versions up to the latest for all products in the Oracle eBusiness Suite. For example, 11.5.10 would include all version changes prior to 11.5.10 such as 11.5.8, 11.5.9, etc. These patches are cumulative. Maintenance packs include all the relevant Family packs.
• Family Pack – This would be a consolidation of patches for a particular family of products such as Financials which includes General Ledger, Accounts Payable, Cash Management, etc. or CRM which includes Sales, Marketing, Service, etc. These patches are cumulative. Family packs include all the relevant Mini-Packs.
• Product Mini-Pack – This would be a consolidation of patches for a particular product such as General Ledger or Enterprise Budgeting and Planning. These patches are cumulative. For example, General Ledger Mini-Pack C includes mini-packs A, B, and C.
• Consolidated Rollups – These are rollup releases of patches as add-ons or fixes to Mini-packs, Family packs, Maintenance packs, or specific areas within a Mini-pack. Typically these don’t increase the version level of the Maintenance, Family, or Mini pack.
• Quarterly Security Patches – Oracle now releases on a quarterly basis a compilation of High Priority security patches for all tiers of the Oracle Applications: Database, Application Server, or Application. Some patches are cumulative, some aren’t.
• One-offs – One off patches are released to fix specific issues. They are generally smaller patches and usually at some point in time get rolled up into the other patch types described above.

Typically one would apply a patch type to fix a bug, keep current on the latest versions, implement new functionality, or implement a new product of the eBusiness Suite.


EBS Patching

And now the combination of the two articles above, EBS and Patching. There are alerts out this week for our HP users running Oracle EBS 11i and 12i. Please look up the following doc IDs in My Oracle Support:

ADRELINK utility for E-Business Suite Release 12.0 and 12.1.1 result in large executables which may lead to out-of-memory issues (Doc ID 1060979.1) (effects both PA-RISC and Itanium)

and

New E-Business Suite Release 12.0 and 12.1 Operating System Patch Requirements on the HP-UX Itanium platform (Doc ID 1066323.1) (effecting Itanium users).

Optimizer

There will be member of the Oracle optimizer team presenting at ODTUG Kaleidoscope in July in Washington, DC, (who chose that location, the heat and humidity committee?). You can read all about it on their blog: Inside the Oracle Optimizer - Removing the black magic

PeopleSoft and the Optimizer

Speaking of the optimizer, this time from the PeopleSoft side of the equation, there's a handy technique described over at the PeopleSoft DBA blog on: Hinting Dynamic Generated SQL in Application Engine.

In Support we field a lot of questions about the migration of Oracle Applications to different platforms.  This article describes the techniques available for migrating an Oracle Applications environment to a new platform and discusses some of the common questions that arise during migration.  This subject has been frequently discussed in previous blog articles but there still seems to be a gap regarding the type of questions we are frequently asked in Service Requests.

Some of the questions we see are quite abstract. Customers simply want to get a grip on understanding how they approach a migration. Others want to know if a particular architecture is viable. Other customers ask about mixing different platforms within a single Oracle Applications environment.   

Just to clarify, throughout this article, the term 'platform' refers specifically to operating systems and not to the underlying hardware. For a clear definition of 'platform' in the context of Oracle Applications Support then Terri's very timely article:

• Oracle E-Business Suite Platform Smörgåsbord
  

The migration process is very similar for both 11i and R12 so this article only mentions specific differences where relevant.

A few years ago, my wife and I went to New York for a holiday. We got a cab from the airport into Manhattan. It was an expensive way to see, at great length, some of the more uninteresting automobile transit routes through New York. We arrived at our hotel a great deal later than we anticipated. And with most of our paper dollars no longer in our possession.

I’ve also taken cabs through London, usually at the weekend to get back to Liverpool Street Station. The trip is generally quick, painless and not too expensive. Those black-cab drivers know their stuff.

Of course, the CABs I refer to in the title of this Friday Philosophy are not private cars for hire. In this context CAB is Change Advisory Board. A term that can make grown developers weep. If you do not know, the Change Advisory Board is a group of people who look at the changes that are planed for a computer system and decide if they are fit for release. My personal experience of them has been similar to my experience of the taxi variety, though sadly more of the New York than London variety.

You might expect me to now sink into a diatribe {ie extended rant} about how I hate CABs. Well, I don’t. CABs can be a part of a valuable and highly worthwhile process control mechanism. Just as proper QA is core to any mature software development process, so CABs are important in getting informed, talented stakeholders to review proposed changes. They check for overall system impact, clashes with other proposed changes that individual development streams may be unaware of and to verify Due Diligence has been invoked {that last one is a bit of a minefield and where, I believe, most CABs fail}.

Sadly, though this is often the aim. The end result is a bunch of uninformed and technically naive politicos trying to wield power, using the CAB meeting as an extended game of management chess.

I’ve seen CABs trade changes. “I’ll let you have X if I can have Y and Z”. I’ve seen CABs turn down changes because the form had spelling mistakes in it. I’ve seen CABs object to a change that will save the company 5 million pounds a day because it lacked a signature.

That last one just stopped me in my tracks {I’m not exaggerating either, if anything I am underplaying the cost impact of that decision. I saw the figures and I wasted a couple of days of my life checking, 5 million pounds a day was the least I felt I could prove.} We are talking about enough money every day to pay the salary of everyone on the CAB for several years. And they blocked it because the DBA team had not signed off the change.

The DBA Team had not signed off the change because the one and only DBA Team Leader who was allowed to sign off was on holiday for two weeks. They needed that holiday too, for other but I suspect linked reasons.

Now I knew the DBA Team Lead and he was a good bloke, he knew his stuff and he was not paid 5 million pounds a day. His deputy was paid even less but was no less talented but she was not allowed to sign off the change as she was not the DBA Team Lead.

That was a CAB gone very wrong. The process of the CAB had been allowed to over-rule good business sense. It was also overruling general and technical sense, but that really is secondary to what keeps the business making a profit.

I’ve seen the opposite of course, technical teams that just apply whatever changes they feel are fit and, to be honest, they seem to mess up less often than a poor CAB process as they know they are the ones who will spend the weekend fixing a mess if one occurs. But that mess will occur eventually, if control is lacking.

So, I feel CABs are good, if you have the right people on them and you have a sensible cascade of authority so one person being away does not block the system. That is quite a bit harder to put in place than a simple “Dave A, John, Andrea, Alex, Raj, Dave P, Mal, Malcolm and Sarah have final signoff” which most CABs effecively become.

But there is one last fault of CABs I want to highlight. They tend to treat all changes in the same way and all changes are not the same. Upgrading the underlying OS is not the same as adding a cardinality hint to one Business Objects report.

If your CAB or change process treat the two above examples the same, then your CAB or change process is broken. Now, in all IT “rules of thumb” there is an exception. In this case, I am truly struggling to think of one. If your change process treats an OS upgrade the same as adding a hint to a report, it is not fit for purpose. That is my main issue with CABs. They should be of significant business importance, but nearly always they are implemented with one process to deal with all situations and then get taken over by people with an “Office Politics” agenda as opposed to a “Getting the best job we can reasonably expect done” agenda.

I’m very passionate about this and I have a way I hope can throw this issue into context, an analogy.

Ask yourself one this senario.
You go to your Doctor with a niggly cough you have had for a week OR you go to your doctor because you almost passed out each day you got out of bed for the last three days.
If your doctor treated you the same for both sets of symptoms, would you be happy with that doctor?

Why are all IT changes handled by most CABs in exactly the same way?

I could say no one dies when it comes to IT, but you work in healthcare computing for a while and that argument falls apart a little.

Oh dear, got a bit “deep” there!

(BTW if you ever almost collapse when you get out of the bed in the morning, do NOT go to work, go instead to your doctor and ask them for a full medical and if he/she does not take blood pressure tests and order a full blood chemisty test, go find a new doctor.)

Right after Oracle OpenWorld 2009 we went right into planning for our 2010 World Tour. An ambitious 90+ city tour visiting cities on every continent.

 

The Oracle Applications Strategy Update Tour started January 19th and is in full swing right now. We've put some heavy hitters on the road. If you didn't get a chance to see Steve Miranda, Senior Vice President of Oracle Application Development in Tokyo, Anthony Lye, Senior Vice President of Oracle CRM Development in New Delhi or Sonny Singh, Senior Vice President of Oracle Industries Business Unit in Stockholm don't worry...we're not done yet.

The theme, Smart Strategies: Your Roadmap to the Future is a nod to the fact that everyone needs to be smart about what's going on in their business and industry right now. But just as important---how to make sure that you're on the course to where you need to be down the road. Get the big picture and key trends in "The New Normal" of today's business climate and drill down and find out about the latest and greatest innovations in Oracle Applications.

Check out http://www.oracle.com/events/applicationstour/index.html for an upcoming tour date near you. Pictures, feedback, summaries and learnings from the tour to come soon.

While applying patch 7303033 on top of a 12.1.0 R12 installation, I got the following error in one of my workers:

FNDLOAD APPS/***** 0 Y UPLOAD @FND:patch/115/import/afscursp.lct @JTF:patch/115/import/US/jtfdiagresp.ldt -

Connecting to APPS......Connected successfully.

Calling FNDLOAD function.

Returned from FNDLOAD function.

Log file: /r12u/apps/apps_st/appl/admin/UPG/log/

Over the last six months, the FASB and IASB have been working feverishly to converge US GAAP and IFRS standards to meet the 2011 deadline. In March alone, the standards-setters met eight times. Many people fear that this accelerated pace is compromising the quality of the end product and that maybe they should slow down and do their due diligence.

According to WG&L Accounting & Compliance Alert Checkpoint 3/10/2010, many people believe they should slow down their pace and pay more attention to quality, which might be compromised if they focus too much on reaching the 2011 finish line. Thus, I'm not holding my breath that the convergence will really be finalized by 2011.

I personally have mixed emotions about this. On one hand, I have been growing impatient with how slow the US has jumped on the IFRS band wagon. On the other hand, being the conservative that I am and knowing this convergence will be costly and disruptive to businesses, I would prefer to be safe than sorry and get it right the first time.

Note: To view the full article, it requires a subscription and unfortunately I am not allowed to copy any portion of it here.

I'm frequently asked where I got the nickname "Bex." Back in 2008 I finally put the matter to rest by explaining the origin of "Bex".

The quick story is this: a while back I saw a British TV show with a character named "Bexley," thought it was a cool name, and started using "Bex" as one of my (many) internet aliases. When I went to college, there were too many "Brians" in my dorm, so they decided I needed a nickname... one of my geekier dorm-mates asked me what I used for internet aliases, I mentioned them, and they liked "Bex" the best.

The rest, as they say, is history...

The TV show in question is Red Dwarf, which is a something of a sci-fi cult comedy... I was recently surprised to see that Red Dwarf is available via NetFlix-On-Demand! If you have NetFlix, and about 20 minutes to spare, you might want to watch the television episode that spawned my nickname. And maybe a few more episodes, if you care to...

The show is well written, if not a bit quirky... but, if you're a frequent reader of my blog, you probably have the sense of humor necessary to find it charming ;-)

read more