Send an Email From Excel, Visual Basic 6, or a Windows Command Line Using Oracle’s UTL_MAIL Package

30 11 2012

November 30, 2012

(Back to the Previous Post in the Series)

Today is this blog’s third anniversary, so to celebrate, I thought that I would share a simple code example.  As many regular readers of this blog probably know, Oracle Database 10.1 introduced the UTL_MAIL package, which allowed programs accessing Oracle Database to easily send emails without using the more complex UTL_SMTP package.  Using UTL_MAIL requires that:

  1. The SMTP_OUT_SERVER parameter is set correctly, and potentially the email server is configured to permit receiving SMTP emails from the Oracle Database server.
  2. The utlmail.sql and prvtmail.plb scripts (found in the rdbms/admin directory of the Oracle home) are executed to create the UTL_MAIL package components in the database.
  3. The Oracle user account that will access the UTL_MAIL package’s procedures has sufficient access permissions for the package.

A couple of years ago I wrote an article that showed how to schedule the periodic sending of an email using UTL_MAIL – that article might also be of interest if you find this article helpful.

Let’s take a look at sample code that is compatible with Visual Basic 6 (VB 6) and the scripting language in Excel (the same scripting language is also available in Microsoft Word, Excel, Power Point, Outlook, Access, etc.):

Dim intResult As Integer
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strSQL As String
Dim dbDatabase As ADODB.Connection
Dim comEmail As ADODB.Command

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = New ADODB.Connection

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

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = New ADODB.Command

    With comEmail
        strSQL = "UTL_MAIL.SEND(" & vbCrLf
        strSQL = strSQL & "  'VisitorRegister@mysite.com'," & vbCrLf
        strSQL = strSQL & "  'MyRecipient1@mysite.com;MyRecipient2@mysite.com'," & vbCrLf
        strSQL = strSQL & "  null," & vbCrLf  'CC
        strSQL = strSQL & "  null," & vbCrLf  'BCC
        strSQL = strSQL & "  '" & strMessageSubject & "'," & vbCrLf
        strSQL = strSQL & "  '" & strMessage & "')"
        .CommandText = strSQL
        .CommandType = adCmdStoredProc                        ' Const adCmdStoredProc = 4
        .ActiveConnection = dbDatabase
    End With

    comEmail.Execute
End If

Set comEmail = Nothing

The sample code looks quite similar to code that has appeared on this site in the past (as such, regular readers will know that MyDB, MyUserID, and MyPassword should be changed to appropriate values for your database), were an ADO Command type object is set up to execute a SQL statement with bind variables.  The difference, however, is that there are no bind variables in the SQL statement, and the CommandType is set to adCmdStoredProc, rather than adCmdText.  Before we are able to use the above code sample, we must first add a reference in the project to the Microsoft ActiveX Data Objects Library:

connect-with-vb-6-references-2

Looks to be very simple, right?  But wait, maybe it would be better that the call to UTL_MAIL use bind variables, rather than literals, to not only save some space in the library cache, but also to make it a bit more difficult for the DBA to read sent emails from Oracle Database’s various V$ views, and to make it easier to include apostrophes (single quotes), line breaks, and other email formatting commands in the email message.

We might try to use something like the following, replacing literals with bind variable placeholders, as a replacement for the above code:

Dim intResult As Integer
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strSQL As String
Dim dbDatabase As ADODB.Connection
Dim comEmail As ADODB.Command

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = New ADODB.Connection

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

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = New ADODB.Command

    With comEmail
        strSQL = "UTL_MAIL.SEND(" & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? ," & vbCrLf
        strSQL = strSQL & "  ? )"
        .CommandText = strSQL
        .CommandType = adCmdStoredProc ' Const adCmdStoredProc = 4
        .ActiveConnection = dbDatabase

        .Parameters.Append .CreateParameter("sender", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("recipients", adVarChar, adParamInput, 500)
        .Parameters.Append .CreateParameter("cc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("bcc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("subject", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("message", adVarChar, adParamInput, 500)
    End With

    comEmail("sender") = "VisitorRegister@mysite.com"
    comEmail("recipients") = "MyRecipient1@mysite.com;MyRecipient2@mysite.com"
    comEmail("subject") = strMessageSubject
    comEmail("message") = strMessage
    comEmail.Execute
End If

Set comEmail = Nothing

Well, that was easy, when executed, the code results in an error message that reads: “Unspecified Error“!  Fine, don’t tell me what is wrong… I will just search the Internet for the answer.

Hey, a site recommended using named variable in the SQL statement, rather than the usual question mark bind placeholders in the SQL statement to be executed, something similar to the following:

Dim intResult As Integer
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strSQL As String
Dim dbDatabase As ADODB.Connection
Dim comEmail As ADODB.Command

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = New ADODB.Connection

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

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = New ADODB.Command

    With comEmail
        strSQL = "UTL_MAIL.SEND(" & vbCrLf
        strSQL = strSQL & "  :sender ," & vbCrLf
        strSQL = strSQL & "  :recipients ," & vbCrLf
        strSQL = strSQL & "  :cc ," & vbCrLf
        strSQL = strSQL & "  :bcc ," & vbCrLf
        strSQL = strSQL & "  :subject ," & vbCrLf
        strSQL = strSQL & "  :message )"
        .CommandText = strSQL
        .CommandType = adCmdStoredProc ' Const adCmdStoredProc = 4
        .ActiveConnection = dbDatabase

        .Parameters.Append .CreateParameter("sender", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("recipients", adVarChar, adParamInput, 500)
        .Parameters.Append .CreateParameter("cc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("bcc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("subject", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("message", adVarChar, adParamInput, 500)
    End With

    comEmail("sender") = "VisitorRegister@mysite.com"
    comEmail("recipients") = "MyRecipient1@mysite.com;MyRecipient2@mysite.com"
    comEmail("subject") = strMessageSubject
    comEmail("message") = strMessage

    comEmail.Execute
End If

Set comEmail = Nothing

Well, that was easy, resulting in an error message that reads: “Unspecified Error! Fine, don’t tell me what is wrong, I will just guess.

Oh, a book recommended putting “Begin ” before the UTL_MAIL in the SQL statement, and “; END;” just after the “)” in the SQL statement.  “Unspecified Error“!

10046 trace at level 12 to see what Oracle Database rejected?  Sorry, no SQL statements that were attempted to be directly executed by the application appeared in the trace file.

Well, obviously it must be possible to execute stored procedures, such as those in the UTL_MAIL package, with bind variables from within Visual Basic 6 or Excel.  Maybe we are just trying too hard?  How about something like this:

Dim intResult As Integer
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strSQL As String
Dim dbDatabase As ADODB.Connection
Dim comEmail As ADODB.Command

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = New ADODB.Connection

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

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = New ADODB.Command

    With comEmail
        strSQL = "UTL_MAIL.SEND"

        .CommandText = strSQL
        .CommandType = adCmdStoredProc ' Const adCmdStoredProc = 4
        .ActiveConnection = dbDatabase

        .Parameters.Append .CreateParameter("sender", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("recipients", adVarChar, adParamInput, 500)
        .Parameters.Append .CreateParameter("cc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("bcc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("subject", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("message", adVarChar, adParamInput, 500)
    End With

    comEmail("sender") = "VisitorRegister@mysite.com"
    comEmail("recipients") = "MyRecipient1@mysite.com;MyRecipient2@mysite.com"
    comEmail("subject") = strMessageSubject
    comEmail("message") = strMessage

    comEmail.Execute
End If

Set comEmail = Nothing

Well that was easy, although it might seem a little confusing not being permitted to specify essentially the same (literal) SQL statement as was used originally, just with bind variable placeholders when calling UTL_MAIL procedures.

The title of this article seems to suggest that we are able to call Oracle’s UTL_MAIL package procedures from the Windows command line – that is almost true.  We need to create a plain text file using Notepad (or a similar tool), and simply make a couple of changes to the above code sample so that variable types are not declared (this code example has not been tested yet):

Dim intResult
Dim strDatabase
Dim strUserName
Dim strPassword
Dim strSQL
Dim dbDatabase
Dim comEmail

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

On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

Set dbDatabase = CreateObject("ADODB.Connection")

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

If (dbDatabase.State = 1) And (Err = 0) Then
    Set comEmail = CreateObject("ADODB.Command")

    With comEmail
        strSQL = "UTL_MAIL.SEND"

        .CommandText = strSQL
        .CommandType = adCmdStoredProc
        .ActiveConnection = dbDatabase

        .Parameters.Append .CreateParameter("sender", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("recipients", adVarChar, adParamInput, 500)
        .Parameters.Append .CreateParameter("cc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("bcc", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("subject", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("message", adVarChar, adParamInput, 500)
    End With

    comEmail("sender") = "VisitorRegister@mysite.com"
    comEmail("recipients") = "MyRecipient1@mysite.com;MyRecipient2@mysite.com"
    comEmail("subject") = strMessageSubject
    comEmail("message") = strMessage

    comEmail.Execute
End If

Set comEmail = Nothing

Then, all that we need to do is execute the saved plain text file using either the cscript or wscript command from the Windows command line.

A year older, any wiser?





Connecting to an Oracle Database with Visual Basic 6.0 on Windows 8 64 Bit

25 11 2012

November 25, 2012 (Modified December 7, 2012)

Compatibility problems?  Visual Basic 6.0, released in 1998, is not officially compatible with Windows 8 Pro 64 bit… or Windows 7, or Windows Vista.  But I still like the language a lot for its simplicity, rapid development, and significant pre-existing code base within my company.  Of course, Oracle Database 11.2.0.3 and the Oracle Client 11.2.0.3 are not officially supported on Windows 8, so maybe the quest is an exercise in futility.

Roughly a month ago a thread appeared on the OTN forums asking how to connect Visual Basic 2010 to Oracle Database Personal Edition on Windows 7 Pro 64 bit running on the same computer.  Several people offered very good advice to guide the OP.  The OP eventually asked how to connect Visual Basic 6 to Oracle Database Personal Edition running on the same Windows 7 Pro 64 bit computer.  As I mentioned, Visual Basic 6 is not officially compatible with Windows 7, and the fact that it is a 32 bit application running on a 64 bit Windows computer means that the 32 bit Oracle Client must also be installed on the computer.

Visual Basic 6.0 (note that you should install service pack 6 for Visual Basic 6, even if the installer locks up at the very end) will run just fine on Windows 7 Pro 64 bit (and probably on Windows 8 Pro 64 bit also), although drawing form objects is a little slow.  The automatic Windows updates may also pose an issue.  From time to time it may be necessary to re-register the 32 bit MSCOMCTL.OCX file to avoid error messages when opening Visual Basic projects.  To re-register that file, from a Windows command prompt (Run – cmd.exe) , enter the following:

cd \windows\syswow64
regsvr32 MSCOMCTL.OCX

Using Visual Basic 6.0, or any development environment for that matter, requires that certain components be selected for installation when installing the Oracle Client.  At a minimum, Oracle ODBC Driver 11.2.0.x.0 (for ODBC type connections), and Oracle Provider for OLE DB 11.2.0.x.0 (for OLE DB type connections) must be installed with the Oracle Client.

Let’s set up a simple Visual Basic project to demonstrate connecting to Oracle Database 11.2.0.3 running on the same Windows 8 computer.  First, when installing Visual Basic 6, you may want to install and register a couple of additional uncommonly used controls.  Those controls are located in the \COMMON\TOOLS\VB\CONTROLS folder on the Visual Basic install CD.  On a 64 bit computer, the controls should be copied to the C:\Windows\Syswow64 folder and then registered in that location using regsvr32 as demonstrated above with the MSCOMCTL.OCX file.  In some cases, a license file must also be imported into the Windows registry – those files have a .REG extension in the same location on the CD:

On to building the project.  First, we need to add a reference to allow the project to use ADO and the Oracle Provider for OLE DB that was installed with the Oracle Client.  From the Project menu, select References…:

Next, we need to add the most recent version of the Microsoft ActiveX Data Objects Library, version 6.1 for Windows 7 and Windows 8, version 6.0 for Vista, or version 2.8 for Windows XP.  After selecting the correct version, click the OK button:

Now let’s add a couple of additional controls to the project.  I first started using the Microsoft Grid Control in Visual Basic 2.0, so for old time’s sake let’s add that control to the project.  We may also want to add a status bar to the project’s form, so let’s also add Microsoft Windows Common Controls 6.0 (SP6).  Then click the OK button:

Now, draw the form controls on the form.  We need three command buttons named cmdLateBinding, cmdEarlyBinding, and cmdQueryDatabase, a grid control named grdOutput, and optionally a status bar named stbStatus with its Style property set to Simple:

Let’s add the code to the Late Binding (cmdLateBinding) button (this is essentially the same code that I provided in the OTN thread, and does not require the addition of the Microsoft ActiveX Data Objects Library in the references for the project – so this code is a good simple test to make certain that everything is installed correctly):

Dim intResult
Dim strDatabase
Dim strUserName
Dim strPassword
Dim strSQL
Dim dbDatabase
Dim snpData

'On Error Resume Next

strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"

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

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

If (dbDatabase.State = 1) And (Err = 0) Then
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  SYSDATE CURRENT_DATE" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  DUAL"

    snpData.Open strSQL, dbDatabase

    If snpData.State = 1 Then
        If Not (snpData.EOF) Then
            Do While Not (snpData.EOF)
                MsgBox snpData("current_date")

                snpData.MoveNext
            Loop
        End If
        snpData.Close
    End If
Else
    intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Oracle Connection Demo")
End If

dbDatabase.Close
Set snpData = Nothing
Set dbDatabase = Nothing

Let’s run the project and click the Late Binding button to see what happens.  We should expect to see some sort of connection failure message, unless there is an entry in the tnsnames.ora for MyDB:

Well, that error message certainly was unexpected (the OP in the OTN thread may have encountered the same error message)!  Let’s click on the Debug button:

ADODB cannot find the OraOLEDB.Oracle provider.  I am sure that I installed that component when installing the 32 bit Oracle Client on the computer.  The Visual Basic code that was added to the Late Binding command button is very generic.  In fact, as written the code can be placed in a plain text file with a .VBS extension and executed as a VBScript file with either the wscript or cscript command.  Copy the code from the Late Binding command button into a plain text file (start the Notepad program and paste the code), then save the file as “LateBinding.vbs” (including the quotes).

Now to test the script that was just created.  Open a Windows command prompt (Windows key and R, type cmd, press the Enter key).  Change to the folder where the LateBinding.vbs file was saved, then type:

wscript LateBinding.vbs

Note that this time the computer indicated an ORA-12154: TNS:could not resolve the connect identifier error, rather than a Provider could not be found error – we were hoping to obtain the same ORA-12154 error as we did when working in Visual Basic 6.  What changed?  Well, this is a 64 bit computer, so the 64 bit wscript program was used, which used the 64 bit Oracle client (actually the 64 bit Oracle Database home files).

Let’s try again, this time with the 32 bit version of the wscript program.  To execute the script with the 32 bit wscript, execute the following:
c:\windows\SysWOW64\wscript LateBinding.vbs

Notice that the error now appears as Provider could not be found, which is the same error message that was observed in Visual Basic 6.  We reproduced the problem!  Now how do we fix it?  I suppose that we should check with the Oracle Client installer to verify that the Oracle Provider for OLE DB 11.2.0.x.0 component was in fact installed.  Let’s re-run the Oracle installer to check the Inventory of the installed components.  Just open the Start menu, click the Oracle – OraClient11g_home1 folder, then expand Oracle Installation Products, then click Universal Installer:

Uh, sure.  Windows 8 and its tablet-like Start menu… good luck finding the Universal Installer for the Oracle Client. (Edit November 26, 2012: I was trying to be humorous here.  If you have a touch screen, you can swipe your finger up from the bottom of the screen to reveal the option to display all items that normally appear on the pre-Windows 8 Start menu, separated by folder/Oracle home.  If you do not have a touch screen, hold down the Windows key and press the W key while in this new Windows 8 start screen to display the same list of items separated by folder/Oracle home.)  I always change the base install folder to C:\Oracle when installing Oracle components, so to start up the Universal Installer for the Oracle Client, I can just run:
C:\oracle\product\11.2.0\client_1\oui\bin\setup.exe

Oracle Provider for OLE DB 11.2.0.3.0 is in the list of installed components (click the Installed Products button in the Oracle Installer to see the above list), so why doesn’t the Oracle Provider for OLE DB from the 32 bit client work as expected?  That component requires that the OraOLEDB11.DLL file is found in the expected location, in my case, here:
C:\Oracle\product\11.2.0\client_1\bin\OraOLEDB11.DLL

Yes, the file is in that location.  Is the Oracle Client 11.2.0.3 just not compatible with Windows 8 Pro?  The Oracle Client 11.2.0.3 seemed to work as expected on a computer that was upgraded from Windows 7 Pro to Windows 8 Pro, including the OLE DB functionality, so why will that functionality NOT work on a new computer with Windows 8 Pro installed?

While comparing the Windows registry entries on a Windows 7 Pro 64 bit computer with the Windows registry entries on the Windows 8 Pro computer where the OLE DB functionality did not work, I noticed a problem – missing entries in the Windows 8 Pro’s Windows registry.  From the Windows 7 Pro 64 bit computer, I exported the three missing Windows registry sections:

3F63C36E-51A3-11D2-BB7D-00C04FA30080.reg:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}]
@="OraOLEDB.Oracle"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors]
@="Extended Error Lookup Service"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}]
@="Oracle OLE DB Error Lookup"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\InprocServer32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
"ThreadingModel"="Both"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\OLE DB Provider]
@="Oracle Provider for OLE DB"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ProgID]
@="OraOLEDB.Oracle.1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\TypeLib]
@="{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\VersionIndependentProgID]
@="OraOLEDB.Oracle"

3FC8E6E4-53FF-11D2-BB7D-00C04FA30080.reg:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}]
@="Oracle OLE DB Error Lookup"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\InprocServer32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
"ThreadingModel"="Both"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\ProdID]
@="OraOLEDB.ErrorLookup.1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\VersionIndependentProgID]
@="OraOLEDB.ErrorLookup.1"

0BB9AFD1-51A1-11D2-BB7D-00C04FA30080.reg (just the win32 section was missing):

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\TypeLib\{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}\1.0\win32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"

After verifying that the Oracle Client’s home on the Windows 8 Pro computer was in fact located in C:\Oracle\product\11.2.0\client_1 (it may not be on your computer, change the above registry files as necessary for your computer – use two \\ characters for each \ character that normally appears in the path to the Oracle Client’s home), I imported the registry entries into the Windows 8 Pro computer’s registry by double-clicking each of the files that were exported from the Windows 7 Pro computer.

The result after importing the registry entries and re-running the script with the 32 bit wscript:

Well, that is not good, another error message.  But wait, that was the same error message displayed when the 64 bit wscript was used to execute the script file.  Maybe we are making progress.  Let’s fix the LateBinding.vbs script file so that the strDatabase variable is set to a valid database name found in the 32 bit client’s tnsnames.ora file (I will use a database named sample, which has the Oracle sample schema loaded), the strUserName variable is set to a valid database username in the database (I will use the sh user), and the strPassword variable is set to the correct password for the username.

Let’s try executing the script again:

Oh, another error message.  ORA-28000: the account is locked.  I guess that some Oracle rules still apply.  Start up a SQL*Plus session in another Windows command prompt, connect to the database as the SYS AS SYSDBA user, and unlock the SH account (edit November 26, 2012: note that this account should probably be locked again once we finish experimenting):

ALTER USER SH ACCOUNT UNLOCK;

Let’s try executing the script again:

It Worked!  The 32 bit version of wscript on Windows 8 Pro 64 bit was able to connect to the 11.2.0.3 64 bit database running on the same computer and select the current date and time from the database.  But, does the Late Binding example code in Visual Basic 6 work?  Let’s check:

Nice!

[Headache Induced Rant] You may notice a nearly 3 hour time difference between the time output by wscript and the time output by Visual Basic 6.  That is not an error – I spent nearly three hours trying to put this blog article together in WordPress (total time putting together this article was close to 5 hours) on the Windows 8 computer AFTER successfully connecting to the database using the 32 bit version of wscript.  The Sony laptop designer who thought it would be a good idea to place the laptop’s left and right mouse buttons underneath the corners of the touch pad and then thought that it would be a good idea to enable zoom control when one finger is barely touching the location of the left mouse button while the other finger is attempting to scroll up and down the WordPress editor’s page should be taken out behind the building and slapped a couple of times.  I quite literally had to undo an unintended page zoom very close to 50 times while putting together this blog article.  The darn laptop has a touch screen… if I really want to zoom the screen, I will either manually select the zoom feature in the application or put two fingers on the screen and then separate those fingers.  (Edit November 26, 2012: Sony does a good job of burying the option to disable this touch pad feature – it is found in the same application that disables the touch pad’s tap to left-click “feature”.)

And which Microsoft developer thought that it would be a great idea to have a large clock pop up on screen in the lower left (taking away the typing entry focus from the application that was being used) on occasion when someone is trying to type on the computer?  That developer should also be taken out behind the building and slapped. [/Headache Induced Rant]

The above example code used early late (edit: July 24, 2014) binding, which defines variables as variants and then later assigns object types to the variables.  That technique, which now does work and is compatible with wscript, tends to result in poor performance.  Poor performance should be considered an application bug, so let’s build an example that uses early binding (note that the References mentioned earlier in this article must have been selected).  Add the following code to the Early Binding button (note that the code is attempting to connect to the sample database as sh user):

Dim intResult As Integer
Dim strSQL As String
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String

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

On Error Resume Next

Set dbDatabase = New ADODB.Connection

strDatabase = "sample" '"MyDB" 'From tnsnames.ora
strUserName = "sh" '"MyUserID"
strPassword = "sh" '"MyPassword"

'Connect to the database, specify that the default array fetch size should be 100 rows
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
dbDatabase.ConnectionTimeout = 40
dbDatabase.CursorLocation = adUseClient
dbDatabase.Open

If (dbDatabase.State <> 1) Or (Err <> 0) Then
    intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Oracle Connection Demo")
Else
    Set snpData = New ADODB.Recordset

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  COUNT(*) NUM_ROWS" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  CUSTOMERS"
    snpData.Open strSQL, dbDatabase

    If snpData.State = 1 Then
        If Not (snpData.EOF) Then
            MsgBox "Number of rows in the Customers table: " & Format(snpData("num_rows")), vbInformation
        Else
            MsgBox "No rows returned from the query.", vbCritical
        End If
        snpData.Close
    Else
        MsgBox "The query could not be executed.", vbCritical
    End If
End If

'When finished, clean up
If dbDatabase.State = 1 Then
    dbDatabase.Close
End If

Set snpData = Nothing
Set dbDatabase = Nothing

Let’s see the result of executing the above code:

Wow, 55,500 rows in that sample table, that is a lot of rows.

Finally, let’s add the code to the Query Database button.  This code will use early binding, bind variables, and present the result rows in the grid control.

Dim i As Integer
Dim intResult As Integer
Dim strSQL As String
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strOut As String

Dim snpData As ADODB.Recordset
Dim comData As ADODB.Command

On Error Resume Next

Set dbDatabase = New ADODB.Connection

strDatabase = "sample" '"MyDB" 'From tnsnames.ora
strUserName = "sh" '"MyUserID"
strPassword = "sh" '"MyPassword"

'Connect to the database, specify that the default array fetch size should be 100 rows
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
dbDatabase.ConnectionTimeout = 40
dbDatabase.CursorLocation = adUseClient
dbDatabase.Open

If (dbDatabase.State <> 1) Or (Err <> 0) Then
    intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Oracle Connection Demo")
Else
    Set comData = New ADODB.Command
    With comData
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  CUST_ID," & vbCrLf
        strSQL = strSQL & "  CUST_FIRST_NAME," & vbCrLf
        strSQL = strSQL & "  CUST_LAST_NAME," & vbCrLf
        strSQL = strSQL & "  CUST_GENDER," & vbCrLf
        strSQL = strSQL & "  CUST_YEAR_OF_BIRTH," & vbCrLf
        strSQL = strSQL & "  CUST_MARITAL_STATUS," & vbCrLf
        strSQL = strSQL & "  CUST_STREET_ADDRESS," & vbCrLf
        strSQL = strSQL & "  CUST_POSTAL_CODE," & vbCrLf
        strSQL = strSQL & "  CUST_CITY," & vbCrLf
        strSQL = strSQL & "  CUST_STATE_PROVINCE," & vbCrLf
        strSQL = strSQL & "  COUNTRY_ID" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  CUSTOMERS" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  CUST_FIRST_NAME= ?"

        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .Parameters.Append .CreateParameter("first_name", adVarChar, adParamInput, 20, "")
        .ActiveConnection = dbDatabase
    End With

    comData("first_name") = "Charles"
    Set snpData = comData.Execute

    If Not (snpData Is Nothing) Then
        If snpData.State = 1 Then
            If Not (snpData.EOF) Then
                grdOutput.Cols = snpData.Fields.Count
                grdOutput.FixedRows = 0
                grdOutput.FixedCols = 0
                grdOutput.Rows = 1

                strOut = ""
                For i = 0 To snpData.Fields.Count - 1
                    strOut = strOut & snpData(i).Name & vbTab
                Next i
                grdOutput.AddItem strOut 'Add the new heading row
                grdOutput.RemoveItem 0 'Remove whatever is on the first row

                Do While Not (snpData.EOF)
                    strOut = ""
                    strOut = strOut & Format(snpData("cust_id")) & vbTab
                    If Not (IsNull(snpData("cust_first_name"))) Then
                        strOut = strOut & snpData("cust_first_name") & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    If Not (IsNull(snpData("cust_last_name"))) Then
                        strOut = strOut & snpData("cust_last_name") & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    If Not (IsNull(snpData("cust_gender"))) Then
                        strOut = strOut & snpData("cust_gender") & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    If Not (IsNull(snpData("cust_year_of_birth"))) Then
                        strOut = strOut & Format(snpData("cust_year_of_birth")) & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    If Not (IsNull(snpData("cust_marital_status"))) Then
                        strOut = strOut & snpData("cust_marital_status") & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    If Not (IsNull(snpData("cust_street_address"))) Then
                        strOut = strOut & snpData("cust_street_address") & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    If Not (IsNull(snpData("cust_postal_code"))) Then
                        strOut = strOut & snpData("cust_postal_code") & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    If Not (IsNull(snpData("cust_city"))) Then
                        strOut = strOut & snpData("cust_city") & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    If Not (IsNull(snpData("cust_state_province"))) Then
                        strOut = strOut & snpData("cust_state_province") & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    If Not (IsNull(snpData("country_id"))) Then
                        strOut = strOut & snpData("country_id") & vbTab
                    Else
                        strOut = strOut & vbTab
                    End If
                    grdOutput.AddItem strOut 'Add the new data row to the grid control

                    snpData.MoveNext
                Loop
            Else
                MsgBox "No rows were returned by the query.", vbInformation
            End If
        Else
            MsgBox "The query could not be executed.", vbCritical
        End If
    Else
        MsgBox "The query could not be executed.", vbCritical
    End If
    dbDatabase.Close
End If

If grdOutput.Rows > 1 Then
    grdOutput.FixedRows = 1
End If

Set snpData = Nothing
Set dbDatabase = Nothing

What does the output look like when the above code is executed?

Nice!  Now the problem.  It is a very bad habit to repeatedly connect to and disconnect from the database, and such a habit may result in a variety of problems, including performance problems.  Ideally, the code to connect to the database should be relocated to a different part of the program, possibly the Form_Load subroutine.

Added December 7, 2012:

I was able to reproduce the above mentioned problem on a Windows 7 Pro computer when the 11.2.0.3 client was installed (without first installing the 11.2.0.1 or 11.2.0.2 client).  Even after importing the above mentioned registry entries, the program (and the VBScript) reported that the “Provider cannot be found”.  As mentioned, after verifying that the above indicated Oracle components were installed, I imported the following registry script (save as a plain text file, rename with a .reg extension, then double-click the file), which combines the three above mentioned registry scripts into a single file:

Windows Registry Editor Version 5.00
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}]
@="OraOLEDB.Oracle"
"OLEDB_SERVICES"=dword:ffffffff
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors]
@="Extended Error Lookup Service"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}]
@="Oracle OLE DB Error Lookup"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\InprocServer32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
"ThreadingModel"="Both"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\OLE DB Provider]
@="Oracle Provider for OLE DB"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ProgID]
@="OraOLEDB.Oracle.1"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\TypeLib]
@="{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\VersionIndependentProgID]
@="OraOLEDB.Oracle"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}]
@="Oracle OLE DB Error Lookup"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\InprocServer32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
"ThreadingModel"="Both"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\ProdID]
@="OraOLEDB.ErrorLookup.1"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\VersionIndependentProgID]
@="OraOLEDB.ErrorLookup.1"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\TypeLib\{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}\1.0\win32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"

The missing element that I neglected to mention in the original version of this article is that the Oracle 11.2.0.3 Client installer apparently forgets to register the OraOLEDB11.DLL file that is in the Oracle home.  The simple work-around for this problem is to manually register the DLL file, with a command similar to the following executed at a Windows command line (replace C:\Oracle\product\11.2.0\client_1 with the correct location of the Oracle client):

regsvr32 C:\Oracle\product\11.2.0\client_1\bin\OraOLEDB11.DLL

Please note that I have not fully verified that importing the three missing registry sections shown above fixes all problems with the 32 bit Oracle Client 11.2.0.3 on Windows 8 Pro 64 bit.





Finding the Next Primary Key Value, a Pre-fixed Solution

20 01 2012

January 20, 2012

A request for assistance came in from an ERP mailing list.  The original poster (OP) is running an unspecified version of Oracle Database 9i, and is in need of a solution to generate new part numbers with prefixed characters that describe the type of part, followed by a sequential number that is unique to the prefix.  The prefixes might be PAINT, BAR, BEARING, DRILL, etc.  Sample part numbers might include BAR0599, PAINT012, BEARING012345, etc.

When I first saw the request, my first thought was to create sequences for the different prefixes, similar to the following:

CREATE SEQUENCE PART_PAINT_ID START WITH 13 NOCACHE;
CREATE SEQUENCE PART_BAR_ID START WITH 600 NOCACHE;
CREATE SEQUENCE PART_BEARING_ID START WITH 12346 NOCACHE;
CREATE SEQUENCE PART_DRILL_ID START WITH 999 NOCACHE;
...

Once the above sequences are created, we could then find the next part number with a SQL statement similar to the following:

SELECT
  'PAINT'||PART_PAINT_ID.NEXTVAL NEXT_PART_ID
FROM
  DUAL;

NEXT_PART_ID
------------
PAINT13 

I suspected that there was a catch – for some reason the sample part numbers included a 0 before the sequence number, and I assumed that there could be a variable number of 0 digits before that sequence number for the different prefixes.  To fix the above, we might try working with the LPAD function to add leading zeros to the sequence number:

SELECT
  'PAINT'||LPAD(TO_CHAR(PART_PAINT_ID.NEXTVAL), 3, '0') NEXT_PART_ID
FROM
  DUAL;

NEXT_PAR
--------
PAINT014 

Need more leading zeros?  No problem, just adjust the number in the LPAD function:

SELECT
  'PAINT'||LPAD(TO_CHAR(PART_PAINT_ID.NEXTVAL), 6, '0') NEXT_PART_ID
FROM
  DUAL;

NEXT_PART_I
-----------
PAINT000015 

Need fewer zeros?:

SELECT
  'PAINT'||LPAD(TO_CHAR(PART_PAINT_ID.NEXTVAL), 1, '0') NEXT_PART_ID
FROM
  DUAL;

NEXT_P
------
PAINT1 

The above shows the first of several potential problems with this approach.  What else could go wrong?  What if a smart person decides that he does not need this “crutch” solution and creates 30 part numbers using the method of best guess or cheat sheet in the side drawer?  Because these are primary key values, the smart person might cause a number of problems that might not be detected for some time… until the Oracle sequence reaches one of the unexpected sequence numbers that were already used.

In addition to the suggestion of Oracle sequences, I offered a couple of other suggestions.  The ERP system offers a macro language that is a lot like VBScript.  The OP could create a VBScript that builds a HTML web page in real time, or possibly pull the next sequence number from a ASP (or similar) web page.  As an example of a HTML web page built in real time, this is an example that I created roughly three years ago:

Dim objIE
Dim objShell
Dim strHTML
Dim intFlag

On Error Resume Next

Set objShell = CreateObject("WScript.Shell")
strHTML = strHTML & "<form name=""Visual"">" & vbCrLf
strHTML = strHTML & "<input type=hidden id=txtOK value="" "">" & vbCrLf

strHTML = strHTML & "<table>" & vbCrLf
strHTML = strHTML & "<tr><td>Component<td><select size=""1"" id=""cboComponent"" name=""cboComponent"" style=""width:150"">" & vbCrLf
strHTML = strHTML & "<option value=""ACTIVATOR"">ACTIVATOR</option>" & vbCrLf
strHTML = strHTML & "<option value=""ACCELERATOR"">ACCELERATOR</option>" & vbCrLf
strHTML = strHTML & "<option value=""CATALYST"">CATALYST</option>" & vbCrLf
strHTML = strHTML & "<option value=""EPOXY PRIMER"">EPOXY PRIMER</option>" & vbCrLf
strHTML = strHTML & "<option value=""PRIMER"">PRIMER</option>" & vbCrLf
strHTML = strHTML & "<option value=""REDUCER"">REDUCER</option>" & vbCrLf
strHTML = strHTML & "<option value=""TOP COAT"">TOP COAT</option>" & vbCrLf
strHTML = strHTML & "</select></td></tr>" & vbCrLf

strHTML = strHTML & "<tr><td>Vendor<td><select size=""1"" id=""cboVendor"" name=""cboVendor"" style=""width:150"">" & vbCrLf
strHTML = strHTML & "<option value=""DUPONT"">DUPONT</option>" & vbCrLf
strHTML = strHTML & "<option value=""LILLY"">LILLY</option>" & vbCrLf
strHTML = strHTML & "<option value=""NILES CHEMICAL"">NILES CHEMICAL</option>" & vbCrLf
strHTML = strHTML & "<option value=""MANITOWOC"">MANITOWOC</option>" & vbCrLf
strHTML = strHTML & "<option value=""MAUTZ"">MAUTZ</option>" & vbCrLf
strHTML = strHTML & "<option value=""PAINTS AND SOLVENTS"">PAINTS AND SOLVENTS</option>" & vbCrLf
strHTML = strHTML & "<option value=""SHEBOYGAN"">SHEBOYGAN</option>" & vbCrLf
strHTML = strHTML & "</select></td></tr>" & vbCrLf

strHTML = strHTML & "<tr><td>Type<td><select size=""1"" id=""cboType"" name=""cboType"" style=""width:150"">" & vbCrLf
strHTML = strHTML & "<option value=""FLAT"">FLAT</option>" & vbCrLf
strHTML = strHTML & "<option value=""GLOSS"">GLOSS</option>" & vbCrLf
strHTML = strHTML & "<option value=""MED. GLOSS"">MED. GLOSS</option>" & vbCrLf
strHTML = strHTML & "</select></td></tr>" & vbCrLf

strHTML = strHTML & "<tr><td>Color<td><select size=""1"" id=""cboColor"" name=""cboColor"" style=""width:150"">" & vbCrLf
strHTML = strHTML & "<option value=""RED"">RED</option>" & vbCrLf
strHTML = strHTML & "<option value=""YELLOW"">YELLOW</option>" & vbCrLf
strHTML = strHTML & "<option value=""GREEN"">GREEN</option>" & vbCrLf
strHTML = strHTML & "<option value=""BLUE"">BLUE</option>" & vbCrLf
strHTML = strHTML & "<option value=""BLACK"">BLACK</option>" & vbCrLf
strHTML = strHTML & "<option value=""WHITE"">WHITE</option>" & vbCrLf
strHTML = strHTML & "<option value=""GRAY"">GRAY</option>" & vbCrLf
strHTML = strHTML & "</select></td></tr>" & vbCrLf
strHTML = strHTML & "</table>" & vbCrLf

strHTML = strHTML & "<p><center><input type=button value=""OK"" id=cmdOK onclick=""document.getElementById('txtOK').value='OK';""></center>" & vbCrLf
strHTML = strHTML & "</form>" & vbCrLf

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "about:blank"
objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title="Get Part Info"
objIE.Left = 0
objIE.Top = 0
objIE.Width = 400
objIE.Height = 400
objIE.Statusbar = False
objIE.Menubar = False
objIE.Toolbar = False

objIE.Visible = True

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

intFlag = 0

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

If intFlag = 1 Then
    'Copy in the values from the web page
    USER_1 = objIE.Document.Body.All.cboComponent.Value
    USER_2 = objIE.Document.Body.All.cboVendor.Value
    USER_3 = objIE.Document.Body.All.cboType.Value
    USER_4 = objIE.Document.Body.All.cboColor.Value

    objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing
 

The result of the above VBScript is an interactive interface that appears similar to the following:

Another option that I suggested to the OP is to use Excel to keep track of the last sequence number for each prefix – and use an Excel dialog displayed from a VBScript macro.  Roughly three years ago I created a sample macro with the following code:

Dim objExcel
Dim objForm
Dim objShell

On Error Resume Next

Set objExcel = CreateObject("Excel.Application")

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

With objExcel
    .Workbooks.Open "C:\ExcelMacroDialog.xls"

    If .Sheets("CalculateArea").Cells(1, 1).Value <> "" Then
        ID = .Sheets("CalculateArea").Cells(1, 1).Value
        DESCRIPTION = .Sheets("CalculateArea").Cells(2, 1).Value
        PRODUCT_CODE = .Sheets("CalculateArea").Cells(3, 1).Value
        COMMODITY_CODE = .Sheets("CalculateArea").Cells(4, 1).Value
        USER_5 = .Sheets("CalculateArea").Cells(5, 1).Value
        PURCHASED = True
        FABRICATED = False
    End If
End With

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Saved = True
objExcel.Quit
Set objExcel = Nothing  

The above macro is quite short, because most of the work is performed in the Excel spreadsheet.  The resulting Excel dialog appeared similar to this:

My first suggestion to the OP, not knowing the full scope of the problem, was to try coding a VBScript macro similar to the following:

Dim strPartID
Dim strPartIDNew
Dim strNumberOld
Dim strNumberNew
Dim i

strPartID = PART_ID
strNumberOld = ""

'strPartID = "PAINT0599"  'Remove this line after testing
strPartID = "PAINT0089"  'Remove this line after testing

For i = Len(strPartID) to 1 Step -1
  If (Mid(strPartID, i, 1) >= "0") And (Mid(strPartID, i, 1) <= "9") Then
    strNumberOld = Mid(strPartID, i, 1) & strNumberOld
  Else
    strNumberNew = cStr(cInt(strNumberOld) + 1)

    strPartIDNew = Left(strPartID, i)
    If Len(strNumberOld) > Len(strNumberNew) Then
      'Add Padding 0s
      strPartIDNew = strPartIDNew & String((Len(strNumberOld) - Len(strNumberNew)), "0")
    End If

    strPartIDNew = strPartIDNew & strNumberNew

    Exit For
  End If
Next

If strPartIDNew <> "" Then
  Msgbox "The New Part ID is " & strPartIDNew
Else
  Msgbox "Not a Valid Starting Point" & strPartID
End If 

The intention of the above macro is to locate the number 89 in the supplied strPartID variable, recognize that a 4 digit serial number is expected, and output:

The New Part ID is PAINT0090 

Nice, but that is not what the OP needs.  The highest currently sequenced number will not be provided – that value must be looked up in the database.  So close…

Let’s try a different approach, starting by creating a sample table with three sequences of part numbers with different prefixes:

CREATE TABLE T1 (
  ID VARCHAR2(30),
  DESCRIPTION VARCHAR2(40),
  PRIMARY KEY(ID));

INSERT INTO
  T1
SELECT
  'PAINT'||LPAD(TO_CHAR(ROWNUM), 6, '0') ID,
  'TESTING ' || TO_CHAR(ROWNUM) DESCRIPTION
FROM
  DUAL
CONNECT BY
  LEVEL<=35000;

INSERT INTO
  T1
SELECT
  'BAR'||LPAD(TO_CHAR(ROWNUM), 6, '0') ID,
  'TESTING ' || TO_CHAR(ROWNUM) DESCRIPTION
FROM
  DUAL
CONNECT BY
  LEVEL<=45000;

INSERT INTO
  T1
SELECT
  'BEARING'||LPAD(TO_CHAR(ROWNUM), 6, '0') ID,
  'TESTING ' || TO_CHAR(ROWNUM) DESCRIPTION
FROM
  DUAL
CONNECT BY
  LEVEL<=888;

COMMIT; 

Let’s find the next sequence number for the BAR prefix:

SELECT
  MAX(TO_NUMBER(SUBSTR(ID,4))) + 1 NN
FROM
  T1
WHERE
  ID BETWEEN 'BAR0' AND 'BAR99999999';

        NN
----------
     45001 

As long as ALL of the characters after the BAR keyword prefix are numbers, the above would tell us that the next number with BAR as the prefix is 45001.  On Oracle Database 10.1 and higher it would be a good idea to add an additional predicate to the WHERE clause that uses regular expressions to avoid potential problems where some unrelated ID column values start with the letters BAR, a number character, and then at some position to the right contain a letter character (that condition would cause the above SQL statement to fail).

Building onto the above SQL statement, we could just retrieve the next part number in the sequence from the database, when provided any existing prefixed part number as the starting point:

SELECT
  'BAR' || LPAD(TO_CHAR(MAX(TO_NUMBER(SUBSTR(ID,4))) + 1), 6, '0') NEXT_PART_ID
FROM
  T1
WHERE
  ID BETWEEN 'BAR0' AND 'BAR99999999';

NEXT_PART
---------
BAR045001 

We are able to take the above SQL statement and incorporate it into a VBScript macro to find the next prefixed sequential number for the primary key column:

Dim strPartID
Dim strPartIDNew
Dim strNumberOld
Dim strNumberNew
Dim i
Dim strUserName
Dim strPassword
Dim strDatabase
Dim strSQL
Dim dbDatabase
Dim snpData

On Error Resume Next

strUsername = "MyUserID"
strPassword = "MyPassword"
strDatabase = "MyDatabase"

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

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

strNumberOld = ""

'strPartID = PART_ID
strPartID = "BEARING0599"  'Remove when finished testing
'strPartID = "BAR0089"  'Remove when finished testing
'strPartID = "PAINT0089"  'Remove when finished testing

For i = Len(strPartID) to 1 Step -1
  If (Mid(strPartID, i, 1) >= "0") And (Mid(strPartID, i, 1) <= "9") Then
    strNumberOld = Mid(strPartID, i, 1) & strNumberOld
  Else
    strPartIDNew = Left(strPartID, i)

    strSQL = "SELECT" & VBCrLf
    strSQL = strSQL & "  '" & strPartIDNew & "' || LPAD(TO_CHAR(MAX(TO_NUMBER(SUBSTR(ID," & (i+1) &"))) + 1), 6, '0') NEXT_PART_ID" & VBCrLf
    strSQL = strSQL & "FROM" & VBCrLf
    strSQL = strSQL & "  T1" & VBCrLf
    strSQL = strSQL & "WHERE" & VBCrLf
    strSQL = strSQL & "  ID BETWEEN '" & strPartIDNew & "0' AND '" & strPartIDNew & "99999999'"

    snpData.Open strSQL, dbDatabase

    If snpData.State = 1 Then
      If Not(snpData.EOF) Then
        strPartIDNew = snpData("next_part_id")
      End If
      snpData.Close
    End If
    Exit For
  End If
Next

If strPartIDNew <> "" Then
  Msgbox "The New Part ID is " & strPartIDNew
Else
  Msgbox "Not a Valid Starting Point" & strPartID
End If

dbDatabase.Close
Set snpData = Nothing
Set dbDatabase = Nothing 

The OP put together a parallel solution that also used a VBScript macro.  The macro sent a SQL statement very similar to the following to the database:

SELECT
  ID
FROM
  T1
WHERE
  ID LIKE 'BAR%'; 

In the macro code the OP parsed each of the returned ID values to determine the highest sequence number, added 1 to that value, padded the new highest sequence number with “0′ digits and output the result.  Most likely due to curiosity, the OP asked why I did not simply use his VBScript macro as part of my proposed solution.  What reasons do you think that I gave to the OP?





SESSION_CACHED_CURSORS – Possibly Interesting Details

21 07 2011

July 21, 2011

Have you ever wondered about the V$OPEN_CURSOR view, the SESSION_CACHED_CURSORS parameter, and the two session-level statistics “session cursor cache count” and “session cursor cache hits”?  I did after reading from two different sources that stated essentially that a larger shared pool would be required when the value for the SESSION_CACHED_CURSORS parameter is increased.  The shared pool? – but the session’s cursors are cached in the UGA, which is typically in the PGA (unless shared server is used), and as far as I am aware the PGA is not in the shared pool.

So, what do we (think that we) know about the session cached cursors?

  • The per session limit for the number of cached cursors is specified by the SESSION_CACHED_CURSORS parameter.
  • Prior to the release of the Oracle Database 9.2.0.5 patchset the OPEN_CURSORS parameter controlled the number of cached cursors for PL/SQL execution, and as of 9.2.0.5 the SESSION_CACHED_CURSORS parameter controls the number of PL/SQL cursors that are cached per session (source).
  • In recent Oracle Database release versions the SESSION_CACHED_CURSORS parameter defaults to a value of 50.
  • Assuming that the SESSION_CACHED_CURSORS parameter is set to a value greater than 0, a SQL statement that is executed three times in the same session will be added to that session’s cursor cache and will remain open, even when explicitly closed by the session.
  • Starting in Oracle Database 11.1, if a 10046 trace is enabled for a session and a cursor is closed, a CLOSE # line will be written to the 10046 trace file, with the type= parameter indicating whether or not the cursor was added to the session cursor cache (and was thus actually not closed).
  • In V$MYSTAT, the session-level “session cursor cache count” statistic indicates the number of cursors that are cached for the current session, while the “session cursor cached hits” statistic indicates the number of times the current session has accessed those cached cursors.
  • V$OPEN_CURSOR shows the cursors that are currently open for all connected sessions.  Those cursors that are in the session cursor cache will also appear in V$OPEN_CURSOR.

The above bullet points are probably common knowledge, but is it all true?

Let’s check the Oracle Database 11.2 documentation for V$OPEN_CURSOR:

“V$OPEN_CURSOR lists cursors that each user session currently has opened and parsed, or cached.”

Nothing out of the ordinary with the above quote – it seems to be well aligned with the last of the above bullet points.

Let’s check the Oracle Database 11.2 documentation for SESSION_CACHED_CURSORS:

Default value: 50

“SESSION_CACHED_CURSORS specifies the number of session cursors to cache. Repeated parse calls of the same SQL (including recursive SQL) or PL/SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.”

The above confirms bullet points one and three that were listed above, and almost bullet point two.

Let’s check the behavior changes listed for Oracle Database 11.2 in the documentation library:

“Starting with Oracle Database 10g Release 1 (10.1), the number of cached cursors is determined by the SESSION_CACHED_CURSORS initialization parameter. In previous Oracle Database releases, the number of SQL cursors cached by PL/SQL was determined by the OPEN_CURSORS initialization parameter.”

Although the above quote states Oracle Database 10.1 rather than 9.2.0.5, the quote confirms the change mentioned in the second of the above bullet points.

Let’s take a look at part of the Performance Tuning Guide from the 11.2 documentation set:

“Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.”

Although the above states that a SQL statement must be parsed more than three times before it is added to the session cursor cache rather than executed three times, the quote basically confirms the fourth of the above bullet points.

Checking the statistic descriptions for Oracle Database 11.2:

session cursor cache count: Total number of cursors cached. This statistic is incremented only if SESSION_CACHED_CURSORS > 0. This statistic is the most useful in V$SESSTAT. If the value for this statistic in V$SESSTAT is close to the setting of the SESSION_CACHED_CURSORS parameter, the value of the parameter should be increased.

session cursor cache hits: Number of hits in the session cursor cache. A hit means that the SQL statement did not have to be reparsed. Subtract this statistic from “parse count (total)” to determine the real number of parses that occurred.”

The above quote seems to confirm the second to the last of the above bullet points.

Everyone happy, or should we test?

We need a table for a bit of experimentation, and we might as well flush the shared pool too:

CREATE TABLE T4 AS
SELECT
  *
FROM
  ALL_OBJECTS
WHERE
  ROWNUM<=10;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T4') 

ALTER SYSTEM FLUSH SHARED_POOL;

Now we need some sort of an elegant way to test the bulleted items that are listed at the start of this article.  Of course, a VBS script that is executed on the poor Windows client computer that is sitting under the finger smudged iPad is the perfect way to run a test.  In the script below replace MyUsername with a valid username for your database, replace MyPassword with the password for that user, and replace MyDatabase with the database SID as it appears in the TNSNAMES.ORA file.  The script performs the following actions:

  1. Connects to the database
  2. Determines the SID and SERIAL# for the session and writes those values to the C:\SessionCursorCache.txt text file.
  3. Enables a 10046 trace at level 1 (minimal detail with no wait events)
  4. Writes the session’s current values for the “session cursor cache hits” and “session cursor cache count” to the text file.
  5. Writes to the text file all SQL statements from V$OPEN_CURSOR where the SID matches the SID for the current session.
  6. Opens two cursors for the SQL statement “SELECT * FROM T4″
  7. Writes the session’s current values for the “session cursor cache hits” and “session cursor cache count” to the text file.  This should indicate if opening the cursors in step 6 triggered a hit on the cached cursors.
  8. Retrieves all rows from the two cursors and explicitly closes those cursors (potentially alternating between fetches from the two cursors if table T4 contained more than 1,000 rows).
  9. Repeat steps 4 through 8 two more times to see how repeated executions affect the statistics and the cursors that are cached.
  10. Disconnects from the database.

The script follows (script download, save with .vbs extension SessionCachedCursorsTest.vbs):

Dim strSQL
Dim strSQL2
Dim strSQL3
Dim strUsername
Dim strPassword
Dim strDatabase
Dim snpData
Dim snpData2
Dim snpDataCursorCache
Dim snpDataOpenSQL
Dim snpDataSID
Dim dbDatabase
Dim lngCnt
Dim intPass
Dim objFSO
Dim objFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile=objFSO.CreateTextFile("C:\SessionCursorCache.txt", True)

Set snpData = CreateObject("ADODB.Recordset")
Set snpData2 = CreateObject("ADODB.Recordset")
Set snpDataCursorCache = CreateObject("ADODB.Recordset")
Set snpDataOpenSQL = CreateObject("ADODB.Recordset")
Set snpDataSID = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")

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

dbDatabase.Open

strSQL = "SELECT" & VBCrLf
strSQL = strSQL & "  S.SID," & VBCrLf
strSQL = strSQL & "  S.SERIAL# SERIALN" & VBCrLf
strSQL = strSQL & "FROM" & VBCrLf
strSQL = strSQL & "  V$SESSION S," & VBCrLf
strSQL = strSQL & "  (SELECT" & VBCrLf
strSQL = strSQL & "    SID" & VBCrLf
strSQL = strSQL & "  FROM" & VBCrLf
strSQL = strSQL & "    V$MYSTAT" & VBCrLf
strSQL = strSQL & "  WHERE" & VBCrLf
strSQL = strSQL & "    ROWNUM=1) M" & VBCrLf
strSQL = strSQL & "WHERE" & VBCrLf
strSQL = strSQL & "  S.SID=M.SID" & VBCrLf

snpDataSID.Open strSQL, dbDatabase
If snpDataSID.State = 1 Then
  Do While Not(snpDataSID.EOF)
    objFile.Write "SID: " & snpDataSID("sid") & ",  SERIAL#: " & snpDataSID("serialn") & VBCrLf
    snpDataSID.MoveNext
  Loop
  snpDataSID.Close
  objFile.Write " " & VBCrLf
End If

dbDatabase.Execute "ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SessionCursorCache'"
dbDatabase.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1'"

strSQL2 = "SELECT" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME STAT_NAME," & VBCrLf
strSQL2 = strSQL2 & "  M.VALUE" & VBCrLf
strSQL2 = strSQL2 & "FROM" & VBCrLf
strSQL2 = strSQL2 & "  V$MYSTAT M," & VBCrLf
strSQL2 = strSQL2 & "  V$STATNAME SN" & VBCrLf
strSQL2 = strSQL2 & "WHERE" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME IN ('session cursor cache hits','session cursor cache count')" & VBCrLf
strSQL2 = strSQL2 & "  AND SN.STATISTIC#=M.STATISTIC#" & VBCrLf
strSQL2 = strSQL2 & "ORDER BY" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME"

strSQL3 = "SELECT" & VBCrLf
strSQL3 = strSQL3 & "  SQL_TEXT" & VBCrLf
strSQL3 = strSQL3 & "FROM" & VBCrLf
strSQL3 = strSQL3 & "  V$OPEN_CURSOR" & VBCrLf
strSQL3 = strSQL3 & "WHERE" & VBCrLf
strSQL3 = strSQL3 & "  SID=" & VBCrLf
strSQL3 = strSQL3 & "    (SELECT" & VBCrLf
strSQL3 = strSQL3 & "       SID" & VBCrLf
strSQL3 = strSQL3 & "     FROM" & VBCrLf
strSQL3 = strSQL3 & "       V$MYSTAT" & VBCrLf
strSQL3 = strSQL3 & "     WHERE" & VBCrLf
strSQL3 = strSQL3 & "       ROWNUM=1)" & VBCrLf
strSQL3 = strSQL3 & "ORDER BY" & VBCrLf
strSQL3 = strSQL3 & "  SQL_TEXT"

For intPass = 1 to 3
snpDataCursorCache.Open strSQL2, dbDatabase
If snpDataCursorCache.State = 1 Then
  objFile.Write "Before Pass #" & intPass & VBCrLf
  Do While Not(snpDataCursorCache.EOF)
    objFile.Write snpDataCursorCache("stat_name") & " " & snpDataCursorCache("value") & VBCrLf
    snpDataCursorCache.MoveNext
  Loop
  snpDataCursorCache.Close
  objFile.Write " " & VBCrLf
End If

snpDataOpenSQL.Open strSQL3, dbDatabase
lngCnt = 0
If snpDataOpenSQL.State = 1 Then
  Do While Not(snpDataOpenSQL.EOF)
    lngCnt = lngCnt + 1
    objFile.Write lngCnt & "  " & snpDataOpenSQL("sql_text") & VBCrLf
    snpDataOpenSQL.MoveNext
  Loop
  snpDataOpenSQL.Close
  objFile.Write " " & VBCrLf
End If

strSQL = "SELECT * FROM T4"
snpData.Open strSQL, dbDatabase
snpData2.Open strSQL, dbDatabase
snpDataCursorCache.Open strSQL2, dbDatabase
If snpDataCursorCache.State = 1 Then
  objFile.Write "After Open Pass #" & intPass & VBCrLf
  Do While Not(snpDataCursorCache.EOF)
    objFile.Write snpDataCursorCache("stat_name") & " " & snpDataCursorCache("value") & VBCrLf
    snpDataCursorCache.MoveNext
  Loop
  snpDataCursorCache.Close
  objFile.Write " " & VBCrLf
End If

lngCnt = 0
If snpData.State = 1 Then
  Do While Not(snpData.EOF)
    Do While (lngCnt < 1000) and Not(snpData.EOF)
      lngCnt = lngCnt + 1
      snpData.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData2.EOF)
      lngCnt = lngCnt + 1
      snpData2.MoveNext
    Loop
    lngCnt = 0
  Loop
End If

snpData.Close
snpData2.Close

Next

dbDatabase.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'"
objFile.Close

Set snpData = Nothing
Set snpData2 = Nothing
Set snpDataCursorCache = Nothing
Set snpDataOpenSQL = Nothing
Set snpDataSID = Nothing

dbDatabase.Close
Set dbDatabase = Nothing
Set objFile = Nothing
Set objFSO = Nothing

If you save the script as SessionCachedCursorsTest.vbs you can execute it from a Windows command line with the following command:

CSCRIPT SessionCachedCursorsTest.vbs

The script will generate a file named C:\SessionCursorCache.txt (if User Access Control is enabled on the Windows computer, you will need to modify the script to create the file in a different location).  What might you find in the generated file?  This is what I saw after the script ran shortly after flushing the shared pool (64 bit Oracle Database 11.2.0.2):

SID: 66,  SERIAL#: 7863

Before Pass #1
session cursor cache count 49
session cursor cache hits 240

1  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
2  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
3  select /*+ rule */ bucket, endpoint, col#, epvalue from hist
4  select /*+ rule */ bucket, endpoint, col#, epvalue from hist
5  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
6  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
7  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
8  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
9  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
10  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
11  select col#,intcol#,charsetid,charsetform from col$ where ob
12  select col#,intcol#,charsetid,charsetform from col$ where ob
13  select col#,intcol#,charsetid,charsetform from col$ where ob
14  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
15  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
16  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
17  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
18  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
19  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
20  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
21  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
22  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
23  select cols,audit$,textlength,intcols,property,flags,rowid f
24  select cols,audit$,textlength,intcols,property,flags,rowid f
25  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
26  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
27  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
28  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
29  select distinct(-privilege#),nvl(option$,0) from sysauth$ wh
30  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
31  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
32  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
33  select intcol#, toid, version#, intcols, intcol#s, flags, sy
34  select intcol#, toid, version#, intcols, intcol#s, flags, sy
35  select intcol#, toid, version#, intcols, intcol#s, flags, sy
36  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
37  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
38  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
39  select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l
40  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
41  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
42  select node,owner,name from syn$ where obj#=:1
43  select node,owner,name from syn$ where obj#=:1
44  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
45  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
46  select order#,columns,types from access$ where d_obj#=:1
47  select owner#,name,namespace,remoteowner,linkname,p_timestam
48  select rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, analy
49  select text from view$ where rowid=:1
50  select text from view$ where rowid=:1
51  select timestamp, flags from fixed_obj$ where obj#=:1
52  select timestamp, flags from fixed_obj$ where obj#=:1

After Open Pass #1
session cursor cache count 49
session cursor cache hits 279

Before Pass #2
session cursor cache count 49
session cursor cache hits 279

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
4  select /*+ rule */ bucket, endpoint, col#, epvalue from hist
5  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
6  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
7  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
8  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
9  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
10  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
11  select col#,intcol#,charsetid,charsetform from col$ where ob
12  select col#,intcol#,charsetid,charsetform from col$ where ob
13  select col#,intcol#,charsetid,charsetform from col$ where ob
14  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
15  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
16  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
17  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
18  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
19  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
20  select cols,audit$,textlength,intcols,property,flags,rowid f
21  select cols,audit$,textlength,intcols,property,flags,rowid f
22  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
23  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
24  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
25  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
26  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
27  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
28  select distinct(-privilege#),nvl(option$,0) from sysauth$ wh
29  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
30  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
31  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
32  select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
33  select intcol#, toid, version#, intcols, intcol#s, flags, sy
34  select intcol#, toid, version#, intcols, intcol#s, flags, sy
35  select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
36  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
37  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
38  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
39  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
40  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
41  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
42  select node,owner,name from syn$ where obj#=:1
43  select node,owner,name from syn$ where obj#=:1
44  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
45  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
46  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
47  select order#,columns,types from access$ where d_obj#=:1
48  select owner#,name,namespace,remoteowner,linkname,p_timestam
49  select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
50  select text from view$ where rowid=:1
51  select timestamp, flags from fixed_obj$ where obj#=:1
52  select type#,blocks,extents,minexts,maxexts,extsize,extpct,u

After Open Pass #2
session cursor cache count 50
session cursor cache hits 280

Before Pass #3
session cursor cache count 50
session cursor cache hits 281

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  SELECT * FROM T4
4  SELECT * FROM T4
5  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
6  select /*+ rule */ bucket, endpoint, col#, epvalue from hist
7  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
8  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
9  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
10  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
11  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
12  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
13  select col#,intcol#,charsetid,charsetform from col$ where ob
14  select col#,intcol#,charsetid,charsetform from col$ where ob
15  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
16  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
17  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
18  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
19  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
20  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
21  select cols,audit$,textlength,intcols,property,flags,rowid f
22  select cols,audit$,textlength,intcols,property,flags,rowid f
23  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
24  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
25  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
26  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
27  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
28  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
29  select distinct(-privilege#),nvl(option$,0) from sysauth$ wh
30  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
31  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
32  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
33  select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
34  select intcol#, toid, version#, intcols, intcol#s, flags, sy
35  select intcol#, toid, version#, intcols, intcol#s, flags, sy
36  select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
37  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
38  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
39  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
40  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
41  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
42  select node,owner,name from syn$ where obj#=:1
43  select node,owner,name from syn$ where obj#=:1
44  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
45  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
46  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
47  select order#,columns,types from access$ where d_obj#=:1
48  select owner#,name,namespace,remoteowner,linkname,p_timestam
49  select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
50  select text from view$ where rowid=:1
51  select timestamp, flags from fixed_obj$ where obj#=:1
52  select type#,blocks,extents,minexts,maxexts,extsize,extpct,u

After Open Pass #3
session cursor cache count 50
session cursor cache hits 284

Reading the above, before our newly created session even selected from table T4, the statistics indicate that somehow there were already 49 cursors in that session’s cursor cache (many of the SQL statements appeared multiple times) and there were already 240 hits on those cached cursors.  Did this session really perform more than 387 (49*3 + 240) executions of SQL statements before our first selection from T4, or does V$OPEN_CURSOR perform differently than described in the documentation?  By the time the two cursors selecting from T4 were opened, the number of cursor cache hits jumped by 39.  After the two cursors were closed (Before Pass #2) we are able to confirm that the select from table T4 was not added to the session cursor cache and there were still 49 cursors in that cache.

On the second pass, we are able to confirm that the select from table T4 is not yet in the session cursor cache.  Immediately after the two cursors selected from table T2 were opened, we see that the session experienced one more cursor cache hit, and another cursor was added to the session cursor cache.

At the start of the third pass, we see that our select from T2 is now in the session cursor cache, not once, but twice, there were 50 cursors in the session cursor cache, and there was one more session cursor cache hit.  When the two cursors selecting from table T4 were opened, we see that the session experienced three more cursor cache hits.

Let’s take a quick look at portions of the 10046 trace file that are related to the SQL statements that were executed by our script (just primarily focusing on the parse and close calls):

PARSING IN CURSOR #429272512 len=212 dep=0 uid=64 oct=3 lid=64 tim=2769436992465 hv=3255970530 ad='3ed509198' sqlid='c2a4dv3114ar2'
SELECT
  SN.NAME STAT_NAME,
  M.VALUE
FROM
  V$MYSTAT M,
  V$STATNAME SN
WHERE
  SN.NAME IN ('session cursor cache hits','session cursor cache count')
  AND SN.STATISTIC#=M.STATISTIC#
ORDER BY
  SN.NAME
END OF STMT
CLOSE #429272512:c=0,e=16,dep=0,type=0,tim=2769436996834

PARSING IN CURSOR #429272512 len=161 dep=0 uid=64 oct=3 lid=64 tim=2769437006931 hv=3838443513 ad='3eb511630' sqlid='babcumbkcmzzt'
SELECT
  SQL_TEXT
FROM
  V$OPEN_CURSOR
WHERE
  SID=
    (SELECT
       SID
     FROM
       V$MYSTAT
     WHERE
       ROWNUM=1)
ORDER BY
  SQL_TEXT
END OF STMT
CLOSE #429272512:c=0,e=19,dep=0,type=0,tim=2769437013363

PARSING IN CURSOR #429272512 len=16 dep=0 uid=64 oct=3 lid=64 tim=2769437025761 hv=1886046789 ad='3eba6b810' sqlid='49fgb3ts6pkk5'
SELECT * FROM T4
END OF STMT

PARSING IN CURSOR #441175000 len=16 dep=0 uid=64 oct=3 lid=64 tim=2769437031138 hv=1886046789 ad='3eba6b810' sqlid='49fgb3ts6pkk5'
SELECT * FROM T4
END OF STMT

PARSING IN CURSOR #440835720 len=212 dep=0 uid=64 oct=3 lid=64 tim=2769437036683 hv=3255970530 ad='3ed509198' sqlid='c2a4dv3114ar2'
SELECT
  SN.NAME STAT_NAME,
  M.VALUE
FROM
  V$MYSTAT M,
  V$STATNAME SN
WHERE
  SN.NAME IN ('session cursor cache hits','session cursor cache count')
  AND SN.STATISTIC#=M.STATISTIC#
ORDER BY
  SN.NAME
END OF STMT

CLOSE #441175000:c=0,e=11,dep=0,type=0,tim=2769437040272
CLOSE #429272512:c=0,e=8,dep=0,type=0,tim=2769437040299
CLOSE #440835720:c=0,e=6,dep=0,type=0,tim=2769437040318
=====================
PARSING IN CURSOR #429272512 len=212 dep=0 uid=64 oct=3 lid=64 tim=2769437040366 hv=3255970530 ad='3ed509198' sqlid='c2a4dv3114ar2'
SELECT
  SN.NAME STAT_NAME,
  M.VALUE
FROM
  V$MYSTAT M,
  V$STATNAME SN
WHERE
  SN.NAME IN ('session cursor cache hits','session cursor cache count')
  AND SN.STATISTIC#=M.STATISTIC#
ORDER BY
  SN.NAME
END OF STMT

CLOSE #429272512:c=0,e=13,dep=0,type=1,tim=2769437042471
=====================
PARSING IN CURSOR #441175000 len=161 dep=0 uid=64 oct=3 lid=64 tim=2769437042547 hv=3838443513 ad='3eb511630' sqlid='babcumbkcmzzt'
SELECT
  SQL_TEXT
FROM
  V$OPEN_CURSOR
WHERE
  SID=
    (SELECT
       SID
     FROM
       V$MYSTAT
     WHERE
       ROWNUM=1)
ORDER BY
  SQL_TEXT
END OF STMT
CLOSE #441175000:c=0,e=19,dep=0,type=0,tim=2769437051038
=====================
PARSING IN CURSOR #441175000 len=16 dep=0 uid=64 oct=3 lid=64 tim=2769437051137 hv=1886046789 ad='3eba6b810' sqlid='49fgb3ts6pkk5'
SELECT * FROM T4
END OF STMT

PARSING IN CURSOR #440600648 len=16 dep=0 uid=64 oct=3 lid=64 tim=2769437053863 hv=1886046789 ad='3eba6b810' sqlid='49fgb3ts6pkk5'
SELECT * FROM T4
END OF STMT

CLOSE #440600648:c=0,e=10,dep=0,type=1,tim=2769437060397
CLOSE #441175000:c=0,e=14,dep=0,type=1,tim=2769437060431
CLOSE #429272512:c=0,e=14,dep=0,type=3,tim=2769437060459
PARSE #429272512:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1338012530,tim=2769437060499
CLOSE #429272512:c=0,e=31,dep=0,type=3,tim=2769437062969
=====================
PARSING IN CURSOR #440639688 len=161 dep=0 uid=64 oct=3 lid=64 tim=2769437063067 hv=3838443513 ad='3eb511630' sqlid='babcumbkcmzzt'
SELECT
  SQL_TEXT
FROM
  V$OPEN_CURSOR
WHERE
  SID=
    (SELECT
       SID
     FROM
       V$MYSTAT
     WHERE
       ROWNUM=1)
ORDER BY
  SQL_TEXT
END OF STMT
CLOSE #440639688:c=0,e=20,dep=0,type=1,tim=2769437071488

PARSE #440600648:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2560505625,tim=2769437071547
PARSE #441175000:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2560505625,tim=2769437073533
PARSE #429272512:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1338012530,tim=2769437076014
CLOSE #441175000:c=0,e=7,dep=0,type=3,tim=2769437079257
CLOSE #440600648:c=0,e=4,dep=0,type=3,tim=2769437079281

What does the above show?  Notice that the numbers following type= on the CLOSE lines seem to change – what do those numbers mean?  If we refer to page 284 of the book “Secrets of the Oracle Database“, we find the following information:

type=0: cursor is actually closed.
type=1: cursor is added to the session cursor cache without pushing an existing cursor out of the session cursor cache.
type=2: cursor is added to the session cursor cache, in the process pushing an existing cursor out of the session cursor cache.
type=3: cursor was already in the session cursor cache

So, the above indicates that the cursors are actually added to the session cursor cache NOT during a parse call, but instead when the cursor is attempted to be explicitly closed.  The documentation describing the SESSION_CACHED_CURSORS parameter, referenced above, appears to be misleading.  It is a bit odd that our select from T4 was added to the session cursor cache twice.  It is also a bit odd that the large cursor numbers, introduced with Oracle Database 11.2.0.2 that I understand to represent the address of the cursor, were reused for different SQL statements (this might have significance in a later blog article).

Just for fun, let’s re-execute the VBS script:

CSCRIPT SessionCachedCursorsTest.vbs

This is now the script’s output:

SID: 192,  SERIAL#: 693

Before Pass #1
session cursor cache count 2
session cursor cache hits 1

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
4  select /*+ connect_by_filtering */ privilege#,level from sys
5  select privilege# from sysauth$ where (grantee#=:1 or grante

After Open Pass #1
session cursor cache count 4
session cursor cache hits 2

Before Pass #2
session cursor cache count 6
session cursor cache hits 3

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  SELECT * FROM T4
4  SELECT * FROM T4
5  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
6  select /*+ connect_by_filtering */ privilege#,level from sys
7  select privilege# from sysauth$ where (grantee#=:1 or grante

After Open Pass #2
session cursor cache count 6
session cursor cache hits 7

Before Pass #3
session cursor cache count 6
session cursor cache hits 8

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  SELECT * FROM T4
4  SELECT * FROM T4
5  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
6  select /*+ connect_by_filtering */ privilege#,level from sys
7  select privilege# from sysauth$ where (grantee#=:1 or grante

After Open Pass #3
session cursor cache count 6
session cursor cache hits 12

In the above, you will likely first notice that the SID and SERIAL# for our newly created session is not the same as it was earlier.  At the start of the output, before the first execution of the select from T4, we see that there were two cursors in the session’s cursor cache, and one hit on one of those two cursors – quite a change from the first execution of the script.  Immediately after opening cursors that selected from T4 we see that there were 4 cursors in the session cursor count, and 2 hits on those cursors.

Now another big change before the start of the second pass, we see that there were 6 cursors in the session cursor cache, including two selects from T4 –  previously, those cursors were added to the session cursor cache just before the start of the third pass.  After opening the two cursors selecting from T4, there were 4 more hits on the session cursor cache.

So, what are the apparent take-aways from this test case:

  • After flushing the shared pool a session’s cursor cache may contain a large number of SQL statements, duplicated in some cases, that may have only been executed a single time by the session at a depth greater than 0 (as indicated in a 10046 trace file).
  • After a SQL statement is parsed and closed 3 times the SQL statement is eligible for the session cursor cache when the SQL statement is closed for the third time.
  • If a SQL statement was previously parsed and closed 3 times by another session, the SQL statement is eligible for a different session’s cursor cache the first time that a cursor with that SQL statement is closed in the session.
  • Oracle Database 11.2.0.2’s huge cursor numbers that are written to 10046 trace files may be reused for entirely different SQL statements, and that does not necessarily indicate a cursor leak.
  • If a SQL statement is opened multiple times in a session without being closed between opens, it is quite possible that all of the cursors using the SQL statement will be added to the session cached cursors (the SQL statement will appear multiple times in V$OPEN_CURSOR) when those cursors are explicitly closed.

At this point, you are probably wondering what might happen when we open 12 cursors that select from T4.  Here is the modified script (script download, save with .vbs extension SessionCachedCursorsTest2.vbs):

Dim strSQL
Dim strSQL2
Dim strSQL3
Dim strUsername
Dim strPassword
Dim strDatabase
Dim snpData
Dim snpData2
Dim snpData3
Dim snpData4
Dim snpData5
Dim snpData6
Dim snpData7
Dim snpData8
Dim snpData9
Dim snpData10
Dim snpData11
Dim snpData12
Dim snpDataCursorCache
Dim snpDataOpenSQL
Dim snpDataSID
Dim dbDatabase
Dim lngCnt
Dim intPass
Dim objFSO
Dim objFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile=objFSO.CreateTextFile("C:\SessionCursorCache2.txt", True)

Set snpData = CreateObject("ADODB.Recordset")
Set snpData2 = CreateObject("ADODB.Recordset")
Set snpData3 = CreateObject("ADODB.Recordset")
Set snpData4 = CreateObject("ADODB.Recordset")
Set snpData5 = CreateObject("ADODB.Recordset")
Set snpData6 = CreateObject("ADODB.Recordset")
Set snpData7 = CreateObject("ADODB.Recordset")
Set snpData8 = CreateObject("ADODB.Recordset")
Set snpData9 = CreateObject("ADODB.Recordset")
Set snpData10 = CreateObject("ADODB.Recordset")
Set snpData11 = CreateObject("ADODB.Recordset")
Set snpData12 = CreateObject("ADODB.Recordset")
Set snpDataCursorCache = CreateObject("ADODB.Recordset")
Set snpDataOpenSQL = CreateObject("ADODB.Recordset")
Set snpDataSID = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")

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

dbDatabase.Open

strSQL = "SELECT" & VBCrLf
strSQL = strSQL & "  S.SID," & VBCrLf
strSQL = strSQL & "  S.SERIAL# SERIALN" & VBCrLf
strSQL = strSQL & "FROM" & VBCrLf
strSQL = strSQL & "  V$SESSION S," & VBCrLf
strSQL = strSQL & "  (SELECT" & VBCrLf
strSQL = strSQL & "    SID" & VBCrLf
strSQL = strSQL & "  FROM" & VBCrLf
strSQL = strSQL & "    V$MYSTAT" & VBCrLf
strSQL = strSQL & "  WHERE" & VBCrLf
strSQL = strSQL & "    ROWNUM=1) M" & VBCrLf
strSQL = strSQL & "WHERE" & VBCrLf
strSQL = strSQL & "  S.SID=M.SID" & VBCrLf

snpDataSID.Open strSQL, dbDatabase
If snpDataSID.State = 1 Then
  Do While Not(snpDataSID.EOF)
    objFile.Write "SID: " & snpDataSID("sid") & ",  SERIAL#: " & snpDataSID("serialn") & VBCrLf
    snpDataSID.MoveNext
  Loop
  snpDataSID.Close
  objFile.Write " " & VBCrLf
End If

dbDatabase.Execute "ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SessionCursorCache2'"
dbDatabase.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1'"

strSQL2 = "SELECT" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME STAT_NAME," & VBCrLf
strSQL2 = strSQL2 & "  M.VALUE" & VBCrLf
strSQL2 = strSQL2 & "FROM" & VBCrLf
strSQL2 = strSQL2 & "  V$MYSTAT M," & VBCrLf
strSQL2 = strSQL2 & "  V$STATNAME SN" & VBCrLf
strSQL2 = strSQL2 & "WHERE" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME IN ('session cursor cache hits','session cursor cache count')" & VBCrLf
strSQL2 = strSQL2 & "  AND SN.STATISTIC#=M.STATISTIC#" & VBCrLf
strSQL2 = strSQL2 & "ORDER BY" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME"

strSQL3 = "SELECT" & VBCrLf
strSQL3 = strSQL3 & "  SQL_TEXT" & VBCrLf
strSQL3 = strSQL3 & "FROM" & VBCrLf
strSQL3 = strSQL3 & "  V$OPEN_CURSOR" & VBCrLf
strSQL3 = strSQL3 & "WHERE" & VBCrLf
strSQL3 = strSQL3 & "  SID=" & VBCrLf
strSQL3 = strSQL3 & "    (SELECT" & VBCrLf
strSQL3 = strSQL3 & "       SID" & VBCrLf
strSQL3 = strSQL3 & "     FROM" & VBCrLf
strSQL3 = strSQL3 & "       V$MYSTAT" & VBCrLf
strSQL3 = strSQL3 & "     WHERE" & VBCrLf
strSQL3 = strSQL3 & "       ROWNUM=1)" & VBCrLf
strSQL3 = strSQL3 & "ORDER BY" & VBCrLf
strSQL3 = strSQL3 & "  SQL_TEXT"

For intPass = 1 to 3
snpDataCursorCache.Open strSQL2, dbDatabase
If snpDataCursorCache.State = 1 Then
  objFile.Write "Before Pass #" & intPass & VBCrLf
  Do While Not(snpDataCursorCache.EOF)
    objFile.Write snpDataCursorCache("stat_name") & " " & snpDataCursorCache("value") & VBCrLf
    snpDataCursorCache.MoveNext
  Loop
  snpDataCursorCache.Close
  objFile.Write " " & VBCrLf
End If

snpDataOpenSQL.Open strSQL3, dbDatabase
lngCnt = 0
If snpDataOpenSQL.State = 1 Then
  Do While Not(snpDataOpenSQL.EOF)
    lngCnt = lngCnt + 1
    objFile.Write lngCnt & "  " & snpDataOpenSQL("sql_text") & VBCrLf
    snpDataOpenSQL.MoveNext
  Loop
  snpDataOpenSQL.Close
  objFile.Write " " & VBCrLf
End If

strSQL = "SELECT * FROM T4"
snpData.Open strSQL, dbDatabase
snpData2.Open strSQL, dbDatabase
snpData3.Open strSQL, dbDatabase
snpData4.Open strSQL, dbDatabase
snpData5.Open strSQL, dbDatabase
snpData6.Open strSQL, dbDatabase
snpData7.Open strSQL, dbDatabase
snpData8.Open strSQL, dbDatabase
snpData9.Open strSQL, dbDatabase
snpData10.Open strSQL, dbDatabase
snpData11.Open strSQL, dbDatabase
snpData12.Open strSQL, dbDatabase

snpDataCursorCache.Open strSQL2, dbDatabase
If snpDataCursorCache.State = 1 Then
  objFile.Write "After Open Pass #" & intPass & VBCrLf
  Do While Not(snpDataCursorCache.EOF)
    objFile.Write snpDataCursorCache("stat_name") & " " & snpDataCursorCache("value") & VBCrLf
    snpDataCursorCache.MoveNext
  Loop
  snpDataCursorCache.Close
  objFile.Write " " & VBCrLf
End If

lngCnt = 0
If snpData.State = 1 Then
  Do While Not(snpData.EOF)
    Do While (lngCnt < 1000) and Not(snpData.EOF)
      lngCnt = lngCnt + 1
      snpData.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData2.EOF)
      lngCnt = lngCnt + 1
      snpData2.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData3.EOF)
      lngCnt = lngCnt + 1
      snpData3.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData4.EOF)
      lngCnt = lngCnt + 1
      snpData4.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData5.EOF)
      lngCnt = lngCnt + 1
      snpData5.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData6.EOF)
      lngCnt = lngCnt + 1
      snpData6.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData7.EOF)
      lngCnt = lngCnt + 1
      snpData7.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData8.EOF)
      lngCnt = lngCnt + 1
      snpData8.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData9.EOF)
      lngCnt = lngCnt + 1
      snpData9.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData10.EOF)
      lngCnt = lngCnt + 1
      snpData10.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData11.EOF)
      lngCnt = lngCnt + 1
      snpData11.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData12.EOF)
      lngCnt = lngCnt + 1
      snpData12.MoveNext
    Loop
    lngCnt = 0
  Loop
End If

snpData.Close
snpData2.Close
snpData3.Close
snpData4.Close
snpData5.Close
snpData6.Close
snpData7.Close
snpData8.Close
snpData9.Close
snpData10.Close
snpData11.Close
snpData12.Close

Next

dbDatabase.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'"
objFile.Close

Set snpData = Nothing
Set snpData2 = Nothing
Set snpData3 = Nothing
Set snpData4 = Nothing
Set snpData5 = Nothing
Set snpData6 = Nothing
Set snpData7 = Nothing
Set snpData8 = Nothing
Set snpData9 = Nothing
Set snpData10 = Nothing
Set snpData11 = Nothing
Set snpData12 = Nothing
Set snpDataCursorCache = Nothing
Set snpDataOpenSQL = Nothing
Set snpDataSID = Nothing

dbDatabase.Close
Set dbDatabase = Nothing
Set objFile = Nothing
Set objFSO = Nothing

The following shows the output that I received on the first execution (the shared pool was not flushed prior to executing the script):

SID: 159,  SERIAL#: 5853

Before Pass #1
session cursor cache count 7
session cursor cache hits 1

1  SELECT    S.SID,    S.SERIAL# SERIALN  FROM    V$SESSION S,
2  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
3  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
4  SELECT PARAMETER, VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHE
5  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
6  select /*+ connect_by_filtering */ privilege#,level from sys
7  select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U
8  select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON
9  select privilege# from sysauth$ where (grantee#=:1 or grante
10  select value$ from props$ where name = 'GLOBAL_DB_NAME'

After Open Pass #1
session cursor cache count 9
session cursor cache hits 2

Before Pass #2
session cursor cache count 21
session cursor cache hits 3

1  SELECT    S.SID,    S.SERIAL# SERIALN  FROM    V$SESSION S,
2  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
3  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
4  SELECT * FROM T4
5  SELECT * FROM T4
6  SELECT * FROM T4
7  SELECT * FROM T4
8  SELECT * FROM T4
9  SELECT * FROM T4
10  SELECT * FROM T4
11  SELECT * FROM T4
12  SELECT * FROM T4
13  SELECT * FROM T4
14  SELECT * FROM T4
15  SELECT * FROM T4
16  SELECT PARAMETER, VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHE
17  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
18  select /*+ connect_by_filtering */ privilege#,level from sys
19  select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U
20  select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON
21  select privilege# from sysauth$ where (grantee#=:1 or grante
22  select value$ from props$ where name = 'GLOBAL_DB_NAME'

After Open Pass #2
session cursor cache count 21
session cursor cache hits 17

Before Pass #3
session cursor cache count 21
session cursor cache hits 18

1  SELECT    S.SID,    S.SERIAL# SERIALN  FROM    V$SESSION S,
2  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
3  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
4  SELECT * FROM T4
5  SELECT * FROM T4
6  SELECT * FROM T4
7  SELECT * FROM T4
8  SELECT * FROM T4
9  SELECT * FROM T4
10  SELECT * FROM T4
11  SELECT * FROM T4
12  SELECT * FROM T4
13  SELECT * FROM T4
14  SELECT * FROM T4
15  SELECT * FROM T4
16  SELECT PARAMETER, VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHE
17  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
18  select /*+ connect_by_filtering */ privilege#,level from sys
19  select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U
20  select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON
21  select privilege# from sysauth$ where (grantee#=:1 or grante
22  select value$ from props$ where name = 'GLOBAL_DB_NAME'

After Open Pass #3
session cursor cache count 21
session cursor cache hits 32

Is that what you expected to see?

We still have one remaining question – is a larger shared pool required when increasing the SESSION_CACHED_CURSORS parameter (the parameter was set to the default value of 50 for this blog article).  To help you decide, refer to the following:

AskTom Article

Blog article written by Jonathan Lewis, specifically this comment:

“Every individiual holding a cursor open has an entry in x$kgllk – which is in the SGA – and these entries seem to be 172 bytes long in 10g (152 in 9i). So, clearly, if you hold more cursors open, you will be using more memory for these structures.”





Investigating Enqueues Burns CPU Cycles

30 03 2011

March 30, 2011

If you take a look at the Oracle Database Performance Tuning Guide, you will find the following SQL statement to help identify the session waiting in an enqueue and the session that currently prevents that session from continuing:

SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
          sid sess, id1, id2, lmode, request, type
   FROM V$LOCK
 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
   ORDER BY id1, request; 

If you look closely, you will see that the version of the query that appeared in the 11.1 release of the Performance Tuning Guide differs a little from what appeared in the 9.0.1 Performance Tuning Guide:

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess
     , id1, id2, lmode, request, type
  FROM V$LOCK
 WHERE (id1, id2, type) IN
             (SELECT id1, id2, type FROM V$LOCK WHERE lmode = 0)
 ORDER BY id1, request; 

The above SQL statements (at least the first one) are helpful, but it might be nice to know a little more about the sessions that are involved in the enqueue wait.  So, you might modify the SQL statement like this (feel free to replace SQL_HASH_VALUE with SQL_ID if you are running Oracle Database 10.1 or greater):

SELECT
  S.SID,
  S.USERNAME,
  S.PROGRAM,
  S.SQL_HASH_VALUE,
  L.REQUEST,
  L.ID1,
  L.ID2,
  L.LMODE,
  L.TYPE,
  L.BLOCK
FROM
  V$LOCK L,
  V$SESSION S
WHERE
  (L.ID1, L.ID2, L.TYPE) IN
  (SELECT
    ID1,
    ID2,
    TYPE
  FROM
    V$LOCK
  WHERE
    REQUEST > 0)
  AND L.SID=S.SID; 

Sometimes helpful is too helpful.  Assume that the above SQL statement is scripted to execute once an hour, once a minute, once a second, or multiple times per second.  What will happen?  Well, you could obtain useful information… but at a cost (we will take a look at that later).  Since we are assuming that the above SQL statement will be scripted, we could possibly use a SQL statement similar to the following, and if any rows are returned we could execute the more useful SQL statement above:

SELECT
  ID1,
  ID2,
  TYPE
FROM
  V$LOCK
WHERE
  REQUEST > 0; 

Another option would be to take advantage of an enhancement added to Oracle Database 10.1 – a couple of additional columns in V$SESSION, specifically the columns BLOCKING_SESSION and BLOCKING_SESSION_STATUS.  With the help of those columns, we could query V$SESSION and then if any rows are returned, use the enhanced version of the above query that returns the lock TYPE.  The SQL statement that would use the two columns from V$SESSION might look like this:

SELECT
  SID,
  BLOCKING_SESSION,
  BLOCKING_SESSION_STATUS
FROM
  V$SESSION
WHERE
  BLOCKING_SESSION IS NOT NULL; 

But now we have a problem.  We have three potential solutions, but no clue which approach will work most efficiently.  We could throw together a PL/SQL loop to help test the performance, but there is a chance that approach will yield performance results that are different from a solution that submits the SQL statements from a client-side application (or script).  But there is a simple solution: create the TEMP_STAT temporary table from the previous article, and then try the following script when several sessions (I used roughly 300) are connected to the database instance (as with previous scripts, replace MyDB, MyUser, and MyPassword with appropriate values:

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
Const adOpenKeyset = 1
Const adLockOptimistic = 3

Dim i
Dim lngCntLast
Dim strSQL
Dim snpDataLock1
Dim comDataLock1
Dim snpDataLock2
Dim comDataLock2
Dim snpDataLock3
Dim comDataLock3
Dim comDataInsert
Dim dbDatabase
Dim snpData
Dim objFSO
Dim objFileLog

Dim strDatabase
Dim strUsername
Dim strPassword

Set snpDataLock1 = CreateObject("ADODB.Recordset")
Set comDataLock1 = CreateObject("ADODB.Command")
Set snpDataLock2 = CreateObject("ADODB.Recordset")
Set comDataLock2 = CreateObject("ADODB.Command")
Set snpDataLock3 = CreateObject("ADODB.Recordset")
Set comDataLock3 = CreateObject("ADODB.Command")
Set comDataInsert = CreateObject("ADODB.Command")
Set dbDatabase = CreateObject("ADODB.Connection")
Set snpData = CreateObject("ADODB.Recordset")

strDatabase = "MyDB"
strUsername = "MyUser"
strPassword = "MyPassword"

'Connect to the database
'Oracle connection string
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

dbDatabase.ConnectionTimeout = 40
dbDatabase.CursorLocation = adUseClient
dbDatabase.open

dbDatabase.BeginTrans

With comDataLock1
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  S.SID," & vbCrLf
    strSQL = strSQL & "  S.USERNAME," & vbCrLf
    strSQL = strSQL & "  S.PROGRAM," & vbCrLf
    strSQL = strSQL & "  S.SQL_HASH_VALUE," & vbCrLf
    strSQL = strSQL & "  L.REQUEST," & vbCrLf
    strSQL = strSQL & "  L.ID1," & vbCrLf
    strSQL = strSQL & "  L.ID2," & vbCrLf
    strSQL = strSQL & "  L.LMODE," & vbCrLf
    strSQL = strSQL & "  L.TYPE," & vbCrLf
    strSQL = strSQL & "  L.BLOCK" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  V$LOCK L," & vbCrLf
    strSQL = strSQL & "  V$SESSION S" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  (L.ID1, L.ID2, L.TYPE) IN " & vbCrLf
    strSQL = strSQL & "  (SELECT" & vbCrLf
    strSQL = strSQL & "    ID1," & vbCrLf
    strSQL = strSQL & "    ID2," & vbCrLf
    strSQL = strSQL & "    TYPE" & vbCrLf
    strSQL = strSQL & "  FROM" & vbCrLf
    strSQL = strSQL & "    V$LOCK" & vbCrLf
    strSQL = strSQL & "  WHERE" & vbCrLf
    strSQL = strSQL & "    REQUEST > 0)" & vbCrLf
    strSQL = strSQL & "  AND L.SID=S.SID"

    'Set up the command properties
    .CommandText = strSQL
    .CommandType = adCmdText
    .CommandTimeout = 30

    .ActiveConnection = dbDatabase
End With

With comDataLock2
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  ID1," & vbCrLf
    strSQL = strSQL & "  ID2," & vbCrLf
    strSQL = strSQL & "  TYPE" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  V$LOCK" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  REQUEST > 0"

    'Set up the command properties
    .CommandText = strSQL
    .CommandType = adCmdText
    .CommandTimeout = 30

    .ActiveConnection = dbDatabase
End With

With comDataLock3
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  SID," & vbCrLf
    strSQL = strSQL & "  BLOCKING_SESSION," & vbCrLf
    strSQL = strSQL & "  BLOCKING_SESSION_STATUS" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  V$SESSION" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  BLOCKING_SESSION IS NOT NULL"

    'Set up the command properties
    .CommandText = strSQL
    .CommandType = adCmdText
    .CommandTimeout = 30

    .ActiveConnection = dbDatabase
End With

With comDataInsert
    strSQL = "INSERT INTO TEMP_STAT" & vbCrLf
    strSQL = strSQL & "SELECT" & vbCrLf
    strSQL = strSQL & "  ? CNT," & vbCrLf
    strSQL = strSQL & "  SN.NAME," & vbCrLf
    strSQL = strSQL & "  MS.VALUE" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  V$STATNAME SN," & vbCrLf
    strSQL = strSQL & "  V$MYSTAT MS" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  SN.STATISTIC#=MS.STATISTIC#" & vbCrLf
    strSQL = strSQL & "  AND SN.NAME IN (" & vbCrLf
    strSQL = strSQL & "    'CPU used by this session'," & vbCrLf
    strSQL = strSQL & "    'consistent gets'," & vbCrLf
    strSQL = strSQL & "    'parse count (total)'," & vbCrLf
    strSQL = strSQL & "    'DB time')"

    'Set up the command properties
    .CommandText = strSQL
    .CommandType = adCmdText
    .CommandTimeout = 30

    .ActiveConnection = dbDatabase

    .Parameters.Append .CreateParameter("cnt", adNumeric, adParamInput, 8 )
End With

comDataInsert("cnt") = 0
comDataInsert.Execute
'Before
comDataInsert("cnt") = 1
comDataInsert.Execute
For i = 1 To 1000
    Set snpDataLock1 = comDataLock1.Execute

    If Not (snpDataLock1 Is Nothing) Then
        If Not (snpDataLock1.EOF) Then
            'Found one
        Else
            'Did not have a lock
        End If
        snpDataLock1.Close
    End If

    WScript.Sleep 200
Next
'After
comDataInsert("cnt") = 2
comDataInsert.Execute

'Before
comDataInsert("cnt") = 3
comDataInsert.Execute
For i = 1 To 1000
    Set snpDataLock2 = comDataLock2.Execute

    If Not (snpDataLock2 Is Nothing) Then
        If Not (snpDataLock2.EOF) Then
            'Found one
        Else
            'Did not have a lock
        End If
        snpDataLock2.Close
    End If

    WScript.Sleep 200
Next
'After
comDataInsert("cnt") = 4
comDataInsert.Execute

'Before
comDataInsert("cnt") = 5
comDataInsert.Execute
For i = 1 To 1000
    Set snpDataLock3 = comDataLock3.Execute

    If Not (snpDataLock3 Is Nothing) Then
        If Not (snpDataLock3.EOF) Then
            'Found one
        Else
            'Did not have a lock
        End If
        snpDataLock3.Close
    End If

    WScript.Sleep 200
Next
'After
comDataInsert("cnt") = 6
comDataInsert.Execute

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  TS2.CNT," & vbCrLf
strSQL = strSQL & "  TS2.NAME," & vbCrLf
strSQL = strSQL & "  TS2.VALUE-TS1.VALUE DELTA" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  TEMP_STAT TS1," & vbCrLf
strSQL = strSQL & "  TEMP_STAT TS2" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  TS2.CNT=TS1.CNT+1" & vbCrLf
strSQL = strSQL & "  AND TS2.NAME=TS1.NAME" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  TS2.CNT," & vbCrLf
strSQL = strSQL & "  TS2.NAME"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileLog = objFSO.CreateTextFile("C:\LockResourceResults.txt", True)

snpData.open strSQL, dbDatabase
lngCntLast = 0
Do While Not snpData.EOF
    If lngCntLast <> CLng(snpData("cnt")) Then
        lngCntLast = CLng(snpData("cnt"))
        Select Case lngCntLast
            Case 2
                objFileLog.Write vbCrLf & "Full Lock Query with BLOCKED and BLOCKER" & vbCrLf

            Case 4
                objFileLog.Write vbCrLf & "Query of V$LOCK" & vbCrLf

            Case 6
                objFileLog.Write vbCrLf & "Query of V$SESSION" & vbCrLf
        End Select
    End If

    Select Case lngCntLast
        Case 2, 4, 6
            objFileLog.Write "  " & CStr(snpData("name")) & "   " & CStr(snpData("delta")) & vbCrLf
    End Select
    snpData.MoveNext
Loop
snpData.Close
objFileLog.Close

dbDatabase.CommitTrans 

The above script executes each of the three possible solutions 1,000 times, pausing 0.2 seconds between each execution.  Once the test is complete, the test results are written to a text file named C:\LockResourceResults.txt.  I executed the above script (at least) three times, and these are the results that I received for three test executions (note that the results from the tests are not necessarily displayed in the order of execution):

Full Lock Query with BLOCKED and BLOCKER
  CPU used by this session   13153
  DB time                    13159
  consistent gets                0
  parse count (total)         1000

Query of V$LOCK
  CPU used by this session    1475
  DB time                     1478
  consistent gets                0
  parse count (total)         1000

Query of V$SESSION
  CPU used by this session      66
  DB time                       66
  consistent gets                0
  parse count (total)         1000 

 

Full Lock Query with BLOCKED and BLOCKER
  CPU used by this session   12526
  DB time                    12531
  consistent gets                0
  parse count (total)         1000

Query of V$LOCK
  CPU used by this session     881
  DB time                      870
  consistent gets                0
  parse count (total)         1000

Query of V$SESSION
  CPU used by this session      17
  DB time                       21
  consistent gets                2
  parse count (total)         1001 

 

Full Lock Query with BLOCKED and BLOCKER
  CPU used by this session   13157
  DB time                    13216
  consistent gets                0
  parse count (total)         1000

Query of V$LOCK
  CPU used by this session    1368
  DB time                     1369
  consistent gets                0
  parse count (total)         1000

Query of V$SESSION
  CPU used by this session     331
  DB time                      340
  consistent gets                0
  parse count (total)         1000 

Just looking at the first resultset of results, 131.53 CPU seconds consumed for the first approach compared to 14.75 CPU seconds for the second approach, and 0.66 CPU seconds for the third approach.  Obviously, the first approach is the best, because it would probably take an extra 5 minutes to code in the logic to see if the SQL statement used in the third approach returned any rows, and if it did to execute the SQL statement used in the first approach – if you don’t use the CPU cycles, you lose them forever.  :-)





Watching Consistent Gets – 10200 Trace File Parser

24 01 2011

January 24, 2011

It happened again, another blog article that forced me to stop, think, and … hey, why did Oracle Database 11.2.0.2 do something different than Oracle Database 10.2.0.5?  What is different, even when the OPTIMIZER_FEATURES_ENABLE parameter is set to 10.2.0.4 (or 10.2.0.5)?  The number of consistent gets for a SQL statement is significantly different – we did see a similar difference between release version before, but for a different reason.  We need the help of Oracle Database trace event 10200 to determine why there is a difference.  Once we have the trace file, we need an easy way to process the trace file.

Excel Macro that will work with a trace file produced by Oracle Database running on Windows (also works in Microsoft Visual Basic 6.0 and earlier; for an Oracle Database running on Unix/Linux, open the  trace file with Wordpad first, and then save the trace file using Wordpad):
(Replace c:\or10s_ora_4256_watch_consistent.trc with the actual name of the generated trace file – the script as written seems to work with 10.2.0.x and 11.2.0.x)

Private Sub Read_10200_Trace1()
    Dim intFileNum As Integer             '10200 trace file
    Dim intFileNum2 As Integer            'Output file
    Dim strInput As String                'Line read from the 10200 trace file
    Dim strOutput As String               'Line to be written to the output file
    Dim strBlock(2000) As String          'Block read from the trace file
    Dim strBlockCounter(2000) As Integer  'Number of times read
    Dim intBlocks As Integer              'Total number of blocks
    Dim i As Integer                      'Loop counter
    Dim intFound As Integer               'Indicates whether or not the block was found

    intFileNum = FreeFile
    Open "c:\or10s_ora_4256_watch_consistent.trc" For Input As #intFileNum

    intFileNum2 = FreeFile
    Open "c:\watch_consistent.txt" For Output As #intFileNum2

    Do While Not EOF(intFileNum)
        Line Input #intFileNum, strInput
        If InStr(strInput, "started for block") > 0 Then
            strOutput = Trim(Right(strInput, Len(strInput) - InStr(InStr(strInput, "started for block"), strInput, ":")))
            'Find the number of times the block was accessed
            intFound = 0
            For i = 1 To intBlocks
                If strOutput = strBlock(i) Then
                    intFound = i
                    strBlockCounter(i) = strBlockCounter(i) + 1
                    Exit For
                End If
            Next i
            'If the block was not found, record it
            If intFound = 0 Then
                intBlocks = intBlocks + 1
                intFound = intBlocks
                strBlockCounter(intBlocks) = 1
                strBlock(intBlocks) = strOutput
            End If
            Print #intFileNum2, strOutput; vbTab; strBlockCounter(intFound)
        End If
    Loop
    Print #intFileNum2, ""
    For i = 1 To intBlocks
        Print #intFileNum2, strBlock(i); vbTab; strBlockCounter(i)
    Next i
    Close #intFileNum
    Close #intFileNum2
End Sub 

Excel Macro equivalent that will work with a trace file produced by Oracle Database running on Windows/Unix/Linux (also works in Microsoft Visual Basic 6.0 and earlier):
(Replace c:\or10s_ora_4256_watch_consistent.trc with the actual name of the generated trace file)

Private Sub Read_10200_Trace2()
    Dim strInput As String                'Line read from the 10200 trace file
    Dim strOutput As String               'Line to be written to the output file
    Dim strBlock(2000) As String          'Block read from the trace file
    Dim strBlockCounter(2000) As Integer  'Number of times read
    Dim intBlocks As Integer              'Total number of blocks
    Dim i As Integer                      'Loop counter
    Dim intFound As Integer               'Indicates whether or not the block was found

    Dim objFSO As Object                  'FileSystemObjects
    Dim objFile1 As Object                'The FileSystemObjects handle to the raw 10020 trace file
    Dim objFile2 As Object                'The FileSystemObjects handle to the output file

    Const ForReading = 1
    Const ForWriting = 2

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile1 = objFSO.OpenTextFile("c:\or10s_ora_4256_watch_consistent.trc", ForReading)
    Set objFile2 = objFSO.CreateTextFile("c:\watch_consistent.txt", True)

    Do While Not (objFile1.AtEndOfStream)
        strInput = objFile1.ReadLine
        If InStr(strInput, "started for block") > 0 Then
            strOutput = Trim(Right(strInput, Len(strInput) - InStr(InStr(strInput, "started for block"), strInput, ":")))
            'Find the number of times the block was accessed
            intFound = 0
            For i = 1 To intBlocks
                If strOutput = strBlock(i) Then
                    intFound = i
                    strBlockCounter(i) = strBlockCounter(i) + 1
                    Exit For
                End If
            Next i
            'If the block was not found, record it
            If intFound = 0 Then
                intBlocks = intBlocks + 1
                intFound = intBlocks
                strBlockCounter(intBlocks) = 1
                strBlock(intBlocks) = strOutput
            End If
            objFile2.Write strOutput & vbTab & strBlockCounter(intFound) & vbCrLf
        End If
    Loop
    objFile2.Write "" & vbCrLf
    For i = 1 To intBlocks
        objFile2.Write strBlock(i) & vbTab & strBlockCounter(i) & vbCrLf
    Next i
    objFile1.Close
    objFile2.Close
End Sub 

VBS Script Equivalent that will work with a trace file produced by Oracle Database running on Windows/Unix/Linux (also works in Excel and Microsoft Visual Basic 6.0 and earlier):
(Replace c:\or10s_ora_4256_watch_consistent.trc with the actual name of the generated trace file)

Dim strInput                'Line read from the 10200 trace file
Dim strOutput               'Line to be written to the output file
Dim strBlock(2000)          'Block read from the trace file
Dim strBlockCounter(2000)   'Number of times read
Dim intBlocks               'Total number of blocks
Dim i                       'Loop counter
Dim intFound                'Indicates whether or not the block was found

Dim objFSO                  'FileSystemObjects
Dim objFile1                'The FileSystemObjects handle to the raw 10020 trace file
Dim objFile2                'The FileSystemObjects handle to the output file

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile1 = objFSO.OpenTextFile("c:\or10s_ora_4256_watch_consistent.trc", ForReading)
Set objFile2 = objFSO.CreateTextFile("c:\watch_consistent.txt", True)

Do While Not (objFile1.AtEndOfStream)
    strInput = objFile1.ReadLine
    If InStr(strInput, "started for block") > 0 Then
        strOutput = Trim(Right(strInput, Len(strInput) - InStr(InStr(strInput, "started for block"), strInput, ":")))
        'Find the number of times the block was accessed
        intFound = 0
        For i = 1 To intBlocks
            If strOutput = strBlock(i) Then
                intFound = i
                strBlockCounter(i) = strBlockCounter(i) + 1
                Exit For
            End If
        Next
        'If the block was not found, record it
        If intFound = 0 Then
            intBlocks = intBlocks + 1
            intFound = intBlocks
            strBlockCounter(intBlocks) = 1
            strBlock(intBlocks) = strOutput
        End If
        objFile2.Write strOutput & vbTab & strBlockCounter(intFound) & vbCrLf
    End If
Loop
objFile2.Write "" & vbCrLf
For i = 1 To intBlocks
    objFile2.Write strBlock(i) & vbTab & strBlockCounter(i) & vbCrLf
Next
objFile1.Close
objFile2.Close 

—-

OK, now that we have the solution, we need an appropriate problem that must be solved with our solution.  The script below creates two test tables, each with a unique index on the ID column:

CREATE TABLE T1 AS
SELECT
  ROWNUM ID,
  TRUNC(DBMS_RANDOM.VALUE(1,300000)) N1,
  LPAD(ROWNUM,10,'0') SMALL_VC,
  RPAD('X',100) PADDING
FROM
  DUAL
CONNECT BY
  LEVEL <= 300000;

CREATE TABLE T2 AS
SELECT
  ROWNUM ID,
  TRUNC(DBMS_RANDOM.VALUE(1,300000)) N1,
  LPAD(ROWNUM,10,'0') SMALL_VC,
  RPAD('X',100) PADDING
FROM
  DUAL
CONNECT BY
  LEVEL <= 300000;

CREATE UNIQUE INDEX PAR_I1 ON T1(ID);
CREATE UNIQUE INDEX CHI_I1 ON T2(ID);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE) 

Now for the test SQL statement (hinted to help force a specific execution plan):

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.4';

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) INDEX(T1) INDEX(T2) */
  T1.ID,
  T1.N1,
  T2.ID,
  T2.N1
FROM
  T1,
  T2
WHERE
  T1.ID=T2.ID
  AND T1.ID BETWEEN 1 AND 200
  AND T2.N1 = 0;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST -ROWS -PREDICATE')); 

The execution plan that is output looks like this:

SQL_ID  1afa5ym56cagh, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) INDEX(T1) INDEX(T2) */
  T1.ID,   T1.N1,   T2.ID,   T2.N1 FROM   T1,   T2 WHERE   T1.ID=T2.ID   AND
T1.ID BETWEEN 1 AND 200   AND T2.N1 = 0

Plan hash value: 3072046012

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |      0 |00:00:00.03 |     408 |
|   1 |  NESTED LOOPS                |        |      1 |      0 |00:00:00.03 |     408 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    200 |00:00:00.01 |       6 |
|   3 |    INDEX RANGE SCAN          | PAR_I1 |      1 |    200 |00:00:00.01 |       2 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2     |    200 |      0 |00:00:00.02 |     402 |
|   5 |    INDEX UNIQUE SCAN         | CHI_I1 |    200 |    200 |00:00:00.01 |     202 |
---------------------------------------------------------------------------------------- 

In the above, there were 2 consistent gets for the PAR_I1 index, 4 consistent gets for the T1 table, 202 consistent gets for the CHI_I1 index, and 200 consistent gets for the T2 table.  While it might not be obvious from the above, the BLEVEL for both indexes is 1 (HEIGHT = 2 – see the quiz article linked to at the start of this article for an explanation).  When I first saw the quiz that is linked to at the start of this article, I mentally assumed that there would be about 400 consistent gets for the CHI_I1 index – for every Start of the INDEX UNIQUE SCAN operation, I expected the index root block and the index leaf block to count as a consistent get, while the above showed that did not happen.  Let’s trace the consistent gets to see why there were only 202 consistent gets and not roughly 400: 

ALTER SESSION SET TRACEFILE_IDENTIFIER='WATCH_CONSISTENT';
ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) INDEX(T1) INDEX(T2) */
  T1.ID,
  T1.N1,
  T2.ID,
  T2.N1
FROM
  T1,
  T2
WHERE
  T1.ID=T2.ID
  AND T1.ID BETWEEN 1 AND 200
  AND T2.N1 = 0; 

If we then process the resulting 10200 trace file through one of the above trace file parsers, we might see output like what is listed below (the RDBA in hex is listed first, followed by the number of times that block had been accessed by a consistent get to that point in the trace file):

0206e214 1
0206e215 1
01c0000c 1
01c72e14 1
01c72e15 1
01c003ec 1
01c72e14 2
01c72e15 2
01c003ec 2
01c72e15 3
01c003ec 3
01c72e15 4
01c003ec 4
...
01c72e15 56
01c003ec 56
01c72e15 57
01c003ec 57
01c0000d 1
01c72e15 58
01c003ed 1
01c72e15 59
01c003ed 2
01c72e15 60
01c003ed 3
01c72e15 61
...
01c72e15 113
01c003ed 56
01c72e15 114
01c003ed 57
01c0000e 1
01c72e15 115
01c003ee 1
01c72e15 116
01c003ee 2
01c72e15 117
...
01c72e15 170
01c003ee 56
01c72e15 171
01c003ee 57
01c0000f 1
01c72e15 172
01c003ef 1
01c72e15 173
01c003ef 2
01c72e15 174
01c003ef 3
...
01c72d95 199
01c003ef 28
01c72d95 200
01c003ef 29

01c72e15 199
01c003ef 28
01c72e15 200
01c003ef 29

0206e214 1
0206e215 1
01c0000c 1
01c72e14 2
01c72e15 200
01c003ec 57
01c0000d 1
01c003ed 57
01c0000e 1
01c003ee 57
01c0000f 1
01c003ef 29

At the bottom of the output is a summary that shows (in order) RDBA 0206e214 was accessed a total of 1 time, RDBA 0206e215 was accessed 1 time, RDBA 01c0000c was accessed 1 time, RDBA 01c72e14 was accessed 2 times, RDBA 01c72e15 was accessed 200 times, etc.  Nice, but what do those RDBA numbers represent?  We will get to that later.

Inside the raw 10200 trace file we might see something like this (I am able to identifysome items that appear in the raw trace file, but I do not yet fully understand the file):

...
*** SESSION ID:(146.18) 2011-01-23 14:36:18.700
Consistent read started for block 9 : 0206e214
  env: (scn: 0x0000.00135bfd  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 9sch: scn: 0x0000.00000000)
CR exa ret 2 on:  0000000004B99F38  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 9 : 206e214
Consistent read started for block 9 : 0206e215
  env: (scn: 0x0000.00135bfd  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 10sch: scn: 0x0000.00135bfd)
CR exa ret 9 on:  0000000004B99F38  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 9 : 206e215
Consistent read finished for block 9 : 206e215
Consistent read started for block 9 : 01c0000c
  env: (scn: 0x0000.00135bfd  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 10sch: scn: 0x0000.00135bfd)
CR exa ret 9 on:  0000000004B99F38  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 9 : 1c0000c
Consistent read finished for block 9 : 1c0000c
Consistent read started for block 9 : 01c72e14
  env: (scn: 0x0000.00135bfd  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 10sch: scn: 0x0000.00135bfd)
CR exa ret 2 on:  0000000004B99F38  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 9 : 1c72e14
Consistent read started for block 9 : 01c72e15
  env: (scn: 0x0000.00135bfd  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 10sch: scn: 0x0000.00135bfd)
CR exa ret 2 on:  0000000004B99F38  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 9 : 1c72e15
Consistent read started for block 9 : 01c003ec
  env: (scn: 0x0000.00135bfd  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 10sch: scn: 0x0000.00135bfd)
CR exa ret 2 on:  0000000004B99F38  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 9 : 1c003ec
Consistent read started for block 9 : 01c72e14
  env: (scn: 0x0000.00135bfd  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 10sch: scn: 0x0000.00135bfd)
CR exa ret 9 on:  0000000004B99F38  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 9 : 1c72e14
Consistent read finished for block 9 : 1c72e14
Consistent read started for block 9 : 01c72e15
  env: (scn: 0x0000.00135bfd  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 10sch: scn: 0x0000.00135bfd)
CR exa ret 2 on:  0000000004B99F38  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 9 : 1c72e15
... 

Now that we see the RDBA numbers again, I suppose that it is time to try to determine the objects that are referenced by the RDBA numbers.  We can try dumping the index structure to see which blocks are read, but first need to find the OBJECT_IDs for the two indexes:

SELECT
  OBJECT_NAME,
  OBJECT_ID
FROM
  DBA_OBJECTS
WHERE
  OBJECT_NAME IN ('PAR_I1','CHI_I1');

OBJECT_NAME  OBJECT_ID
----------- ----------
CHI_I1           48143
PAR_I1           48142 

With the OBJECT_IDs we are able to write the index structures to a trace file:

ALTER SESSION SET TRACEFILE_IDENTIFIER='TREE_DUMP_CHI_I1';
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 48143';

ALTER SESSION SET TRACEFILE_IDENTIFIER='TREE_DUMP_PAR_I1';
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 48142'; 

Partial output from the TREE_DUMP_PAR_I1 trace file might look like the section that follows (items appearing in bold were identified in the trace file summary):

----- begin tree dump
branch: 0x206e214 34005524 (0: nrow: 625, level: 1)
   leaf: 0x206e215 34005525 (-1: nrow: 520 rrow: 520)
   leaf: 0x206e216 34005526 (0: nrow: 513 rrow: 513)
   leaf: 0x206e217 34005527 (1: nrow: 513 rrow: 513)
   leaf: 0x206e218 34005528 (2: nrow: 513 rrow: 513) 
...
   leaf: 0x206ee0e 34008590 (623: nrow: 435 rrow: 435)
----- end tree dump

In the above 0x206e214 is a branch block (actually the root block) and 0x206e215 is the first leaf block.

Partial output from the TREE_DUMP_CHI_I1 trace file might look like the section that follows (items appearing in bold were identified in the trace file summary):

----- begin tree dump
branch: 0x1c72e14 29830676 (0: nrow: 625, level: 1)
   leaf: 0x1c72e15 29830677 (-1: nrow: 520 rrow: 520)
   leaf: 0x1c72e16 29830678 (0: nrow: 513 rrow: 513)
   leaf: 0x1c72e17 29830679 (1: nrow: 513 rrow: 513)
   leaf: 0x1c72e18 29830680 (2: nrow: 513 rrow: 513)
...
   leaf: 0x1c7308e 29831310 (623: nrow: 435 rrow: 435)
----- end tree dump 

In the above, 0x1c72e14 is a branch block (actually the root block) and 0x1c72e15 is the first leaf block.

If we take another look at the summary, we are now able to update the summary with the index block information:

0206e214 1     /* PAR_I1 Root block of index on T1 */
0206e215 1     /* PAR_I1 Leaf block of index on T1 */
01c0000c 1
01c72e14 2     /* CHI_I1 Root block of index on T2 */
01c72e15 200   /* CHI_I1 Leaf block of index on T2 */
01c003ec 57
01c0000d 1
01c003ed 57
01c0000e 1
01c003ee 57
01c0000f 1
01c003ef 29 

Let’s try to find the source of the rest of the blocks that were found in the summary (I guess that this could be the hard way to get the job done):

SELECT
  SEGMENT_NAME,
  HEADER_FILE,
  HEADER_BLOCK,
  BLOCKS,
  HEADER_BLOCK+BLOCKS-1 MAX_BLOCKS
FROM
  DBA_SEGMENTS
WHERE
  SEGMENT_NAME IN ('T1','T2');

SEGMENT_NAME HEADER_FILE HEADER_BLOCK     BLOCKS MAX_BLOCKS
------------ ----------- ------------ ---------- ----------
T1                     7           11       5504       5514
T2                     7         1003       5504       6506 

Taking the above HEADER_FILE, HEADER_BLOCK, and MAX_BLOCKS numbers and dumping the block contents to a trace file (this will work in this test case script because all of the extents for the table blocks are probably close to each other – looking back, it probably would have been a better idea to use DBA_EXTENTS rather than DBA_SEGMENTS and just dump the first extent for each object):

ALTER SESSION SET TRACEFILE_IDENTIFIER='TABLE_DUMP_T1';
ALTER SYSTEM DUMP DATAFILE 7 BLOCK MIN 11 BLOCK MAX 5514;

ALTER SESSION SET TRACEFILE_IDENTIFIER='TABLE_DUMP_T2';
ALTER SYSTEM DUMP DATAFILE 7 BLOCK MIN 1003 BLOCK MAX 6506; 

Partial output from the TABLE_DUMP_T1 trace file might look like the following (items appearing in bold were identified in the trace file summary):

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x01c0000a

buffer tsn: 9 rdba: 0x01c0000c (7/12)
scn: 0x0000.00135a07 seq: 0x02 flg: 0x04 tail: 0x5a070602
frmt: 0x02 chkval: 0x541c type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000080F4400 to 0x00000000080F6400
...
buffer tsn: 9 rdba: 0x01c0000d (7/13)
scn: 0x0000.00135a07 seq: 0x02 flg: 0x04 tail: 0x5a070602
frmt: 0x02 chkval: 0x42da type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000080F4400 to 0x00000000080F6400
...
buffer tsn: 9 rdba: 0x01c0000e (7/14)
scn: 0x0000.00135a07 seq: 0x02 flg: 0x04 tail: 0x5a070602
frmt: 0x02 chkval: 0x840f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000080F4400 to 0x00000000080F6400
...
buffer tsn: 9 rdba: 0x01c0000f (7/15)
scn: 0x0000.00135a07 seq: 0x02 flg: 0x04 tail: 0x5a070602
frmt: 0x02 chkval: 0x74ce type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000080F4400 to 0x00000000080F6400
... 

Partial output from the TABLE_DUMP_T1 TABLE_DUMP_T2 trace file might look like the following (items appearing in bold were identified in the trace file summary):

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x01c003ea

buffer tsn: 9 rdba: 0x01c003ec (7/1004)
scn: 0x0000.00135ac4 seq: 0x02 flg: 0x04 tail: 0x5ac40602
frmt: 0x02 chkval: 0x50a8 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000080F4400 to 0x00000000080F6400
...
buffer tsn: 9 rdba: 0x01c003ed (7/1005)
scn: 0x0000.00135ac4 seq: 0x02 flg: 0x04 tail: 0x5ac40602
frmt: 0x02 chkval: 0x2ef2 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000080F4400 to 0x00000000080F6400
...
buffer tsn: 9 rdba: 0x01c003ee (7/1006)
scn: 0x0000.00135ac4 seq: 0x02 flg: 0x04 tail: 0x5ac40602
frmt: 0x02 chkval: 0xbc00 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000080F4400 to 0x00000000080F6400
...
buffer tsn: 9 rdba: 0x01c003ef (7/1007)
scn: 0x0000.00135ac4 seq: 0x02 flg: 0x04 tail: 0x5ac40602
frmt: 0x02 chkval: 0x6c98 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000080F4400 to 0x00000000080F6400
...
buffer tsn: 9 rdba: 0x01c003f0 (7/1008)
scn: 0x0000.00135ac4 seq: 0x02 flg: 0x04 tail: 0x5ac40602
frmt: 0x02 chkval: 0xf228 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000080F4400 to 0x00000000080F6400
... 

Taking another look at the summary, now updated with the table blocks:

0206e214 1     /* PAR_I1 Root block of index on T1 */
0206e215 1     /* PAR_I1 Leaf block of index on T1 */
01c0000c 1     /* T1     Table block */
01c72e14 2     /* CHI_I1 Root block of index on T2 */
01c72e15 200   /* CHI_I1 Leaf block of index on T2 */
01c003ec 57    /* T2     Table block */
01c0000d 1     /* T1     Table block */
01c003ed 57    /* T2     Table block */
01c0000e 1     /* T1     Table block */
01c003ee 57    /* T2     Table block */
01c0000f 1     /* T1     Table block */
01c003ef 29    /* T2     Table block */

Those datafile dumps can be quite time consuming, is there anything else we can try?

We could try to find the RDBA for the ten blocks (note that there is a risk here if the first extent is only eight blocks in length) in the first extent of each segment using the DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS function (items appearing in bold were identified in the trace file summary):

SELECT
  SEGMENT_NAME,
  DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(HEADER_FILE,HEADER_BLOCK+RN) RDBA,
  TO_CHAR(DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(HEADER_FILE,HEADER_BLOCK+RN),'XXXXXXXXXX') HEX_RDBA
FROM
  DBA_SEGMENTS,
  (SELECT /*+ MATERIALIZE */
    ROWNUM-1 RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10)
WHERE
  SEGMENT_NAME IN ('T1','T2')
ORDER BY
  SEGMENT_NAME,
  RN;

SEGMENT_NAME       RDBA HEX_RDBA
------------ ---------- -----------
T1             29360139     1C0000B
T1             29360140     1C0000C
T1             29360141     1C0000D
T1             29360142     1C0000E
T1             29360143     1C0000F
T1             29360144     1C00010
T1             29360145     1C00011
T1             29360146     1C00012
T1             29360147     1C00013
T1             29360148     1C00014
T2             29361131     1C003EB
T2             29361132     1C003EC
T2             29361133     1C003ED
T2             29361134     1C003EE
T2             29361135     1C003EF
T2             29361136     1C003F0
T2             29361137     1C003F1
T2             29361138     1C003F2
T2             29361139     1C003F3
T2             29361140     1C003F4 

Or, we could try working from the opposite direction.  With the knowledge that the lower 22 bits of the RDBA is the block number and the upper ten bits of the RDBA is the relative file number, we can manually calculate the relative file number and the block number from the RDBA and then look up the object name associated with the file and block.  First, we need the decimal equivalent of  (binary) 1111111111111111111111:

(binary) 1111111111111111111111 = (decimal) 4194303 

So, if we BITAND the RDBA with 4194303 we can obtain the block number, and if we divide the RDBA by 4194304 we can determine the relative file number for two of the RDBA numbers that were listed in the trace file summary, as shown below:

SELECT
  TO_CHAR(L.RDBA,'XXXXXXXX') HEX_RDBA,
  L.RDBA,
  TRUNC(L.RDBA/4194304) DATA_FILE,
  BITAND(L.RDBA,4194303) DATA_BLOCK
FROM
  (SELECT
    TO_NUMBER('01c0000c', 'XXXXXXXX') RDBA
  FROM
    DUAL
  UNION ALL
  SELECT
    TO_NUMBER('01c003ec', 'XXXXXXXX') RDBA
  FROM
    DUAL) L;

HEX_RDBA        RDBA  DATA_FILE DATA_BLOCK
--------- ---------- ---------- ----------
  1C0000C   29360140          7         12
  1C003EC   29361132          7       1004 

Remembering the number 4194303 might be challenging, so we can just use the DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE and DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK functions instead.  Looking up the associated object names can be slow, unless we are able to limit the object names to a list of specific objects (ideally, we would also specify the DE.OWNER column in the WHERE clause):

SELECT /*+ LEADING(L) */
  TO_CHAR(L.RDBA, 'XXXXXXXX') RDBA_HEX,
  L.RDBA,
  DE.SEGMENT_NAME,
  DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(L.RDBA) DATA_FILE,
  DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(L.RDBA) DATA_BLOCK
FROM
  (SELECT
    TO_NUMBER('01c0000c', 'XXXXXXXX') RDBA
  FROM
    DUAL
  UNION ALL
  SELECT
    TO_NUMBER('01c003ec', 'XXXXXXXX') RDBA
  FROM
    DUAL) L,
  DBA_EXTENTS DE
WHERE
  DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(L.RDBA)=DE.FILE_ID
  AND DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(L.RDBA) BETWEEN DE.BLOCK_ID AND (DE.BLOCK_ID + DE.BLOCKS - 1)
  AND DE.SEGMENT_NAME IN ('T1','T2')
ORDER BY
  DE.SEGMENT_NAME;

RDBA_HEX        RDBA SEGMENT_NAME  DATA_FILE DATA_BLOCK
--------- ---------- ------------ ---------- ----------
  1C0000C   29360140 T1                    7         12
  1C003EC   29361132 T2                    7       1004  

————————————————-

Now let’s take a look at Oracle Database 11.2.0.2 – what has changed?  If we execute the test SQL statement, we see the following execution plan for the query:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST -ROWS -PREDICATE'));

SQL_ID  1afa5ym56cagh, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) INDEX(T1)
INDEX(T2) */   T1.ID,   T1.N1,   T2.ID,   T2.N1 FROM   T1,   T2 WHERE
T1.ID=T2.ID   AND T1.ID BETWEEN 1 AND 200   AND T2.N1 = 0

Plan hash value: 3072046012

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |      0 |00:00:00.01 |     215 |
|   1 |  NESTED LOOPS                |        |      1 |      0 |00:00:00.01 |     215 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    200 |00:00:00.01 |       6 |
|   3 |    INDEX RANGE SCAN          | PAR_I1 |      1 |    200 |00:00:00.01 |       2 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2     |    200 |      0 |00:00:00.01 |     209 |
|   5 |    INDEX UNIQUE SCAN         | CHI_I1 |    200 |    200 |00:00:00.01 |       9 |
---------------------------------------------------------------------------------------- 

Notice in the above that the 202 consistent gets that we saw for the CHI_I1 index in Oracle Database 10.2.0.5 oddly only required 9 consistent gets in Oracle Database 11.2.0.2.  But that is not the only change.  If we process the 10200 trace file through one of the trace file parsers, we might see something like this:

0x0200439b> objd: 0x00011711 1
0x0200439c> objd: 0x00011711 1
0x02000083> objd: 0x00011705 1
0x01c04d9b> objd: 0x00011710 1
0x01c04d9c> objd: 0x00011710 1
0x01c0389b> objd: 0x00011706 1
0x01c04d9b> objd: 0x00011710 2
0x01c04d9c> objd: 0x00011710 2
0x01c0389b> objd: 0x00011706 2
0x01c04d9c> objd: 0x00011710 3
0x01c0389b> objd: 0x00011706 3
0x01c04d9c> objd: 0x00011710 4
0x01c0389b> objd: 0x00011706 4
0x01c04d9c> objd: 0x00011710 5
0x01c0389b> objd: 0x00011706 5
0x01c04d9c> objd: 0x00011710 6
0x01c0389b> objd: 0x00011706 6
0x01c04d9c> objd: 0x00011710 7
0x01c0389b> objd: 0x00011706 7
0x01c0389b> objd: 0x00011706 8
0x01c0389b> objd: 0x00011706 9
0x01c0389b> objd: 0x00011706 10
0x01c0389b> objd: 0x00011706 11
0x01c0389b> objd: 0x00011706 12  
...
0x01c0389b> objd: 0x00011706 54
0x01c0389b> objd: 0x00011706 55
0x01c0389b> objd: 0x00011706 56
0x01c0389b> objd: 0x00011706 57
0x02000084> objd: 0x00011705 1
0x01c0389c> objd: 0x00011706 1
0x01c0389c> objd: 0x00011706 2
0x01c0389c> objd: 0x00011706 3
0x01c0389c> objd: 0x00011706 4
...
0x01c0389c> objd: 0x00011706 55
0x01c0389c> objd: 0x00011706 56
0x01c0389c> objd: 0x00011706 57
0x02000085> objd: 0x00011705 1
0x01c0389d> objd: 0x00011706 1
0x01c0389d> objd: 0x00011706 2
0x01c0389d> objd: 0x00011706 3
0x01c0389d> objd: 0x00011706 4
0x01c0389d> objd: 0x00011706 5
...
0x01c0389d> objd: 0x00011706 55
0x01c0389d> objd: 0x00011706 56
0x01c0389d> objd: 0x00011706 57
0x02000086> objd: 0x00011705 1
0x01c0389e> objd: 0x00011706 1
0x01c0389e> objd: 0x00011706 2
0x01c0389e> objd: 0x00011706 3
...
0x01c0389e> objd: 0x00011706 27
0x01c0389e> objd: 0x00011706 28
0x01c0389e> objd: 0x00011706 29

0x0200439b> objd: 0x00011711 1
0x0200439c> objd: 0x00011711 1
0x02000083> objd: 0x00011705 1
0x01c04d9b> objd: 0x00011710 2
0x01c04d9c> objd: 0x00011710 7
0x01c0389b> objd: 0x00011706 57
0x02000084> objd: 0x00011705 1
0x01c0389c> objd: 0x00011706 57
0x02000085> objd: 0x00011705 1
0x01c0389d> objd: 0x00011706 57
0x02000086> objd: 0x00011705 1
0x01c0389e> objd: 0x00011706 29

Interesting – it appears that Oracle Database 11.2.0.2 writes the DATA_OBJECT_ID that is related to the block, directly into the trace file so that we no longer need to execute several SQL statements to determine the object names related to the blocks.

Inside the raw 10200 trace file from 11.2.0.2 we might see something like the following:

ktrgtc2(): started for block <0x0009 : 0x0200439b> objd: 0x00011711
  env [0x000000001870BF5C]: (scn: 0x0000.0014c547  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.0014c527  flg: 0x00000661)
ktrexc(): returning 2 on:  0000000013C3D598  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0009 : 0x0200439b> objd: 0x00011711
ktrget2(): started for block  <0x0009 : 0x0200439c> objd: 0x00011711
env [0x000000001870BF5C]: (scn: 0x0000.0014c547  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.0014c547  ma-scn: 0x0000.0014c527  flg: 0x00000662)
ktrexf(): returning 9 on:  0000000013C3D598  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block  <0x0009 : 0x0200439c> objd: 0x00011711
ktrget2(): completed for  block <0x0009 : 0x0200439c> objd: 0x00011711
ktrget2(): started for block  <0x0009 : 0x02000083> objd: 0x00011705
env [0x000000001870BF5C]: (scn: 0x0000.0014c547  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.0014c547  ma-scn: 0x0000.0014c527  flg: 0x00000662)
ktrexf(): returning 9 on:  0000000013C3D598  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block  <0x0009 : 0x02000083> objd: 0x00011705
ktrget2(): completed for  block <0x0009 : 0x02000083> objd: 0x00011705
ktrgtc2(): started for block <0x0009 : 0x01c04d9b> objd: 0x00011710
  env [0x000000001870BF5C]: (scn: 0x0000.0014c547  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.0014c547  ma-scn: 0x0000.0014c527  flg: 0x00000662)
ktrexc(): returning 2 on:  0000000013C3D598  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0009 : 0x01c04d9b> objd: 0x00011710
ktrgtc2(): started for block <0x0009 : 0x01c04d9c> objd: 0x00011710
  env [0x000000001870BF5C]: (scn: 0x0000.0014c547  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.0014c547  ma-scn: 0x0000.0014c527  flg: 0x00000662)
ktrexc(): returning 2 on:  0000000013C3D598  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0009 : 0x01c04d9c> objd: 0x00011710  

The 10200 trace file in 11.2.0.2 provided the DATA_OBJECT_ID for the consistent reads, while the 10.2.0.5 trace file did not.  We can use this information to determine which objects were accessed, and in which order by pulling in the unique OBJD values from the summary:

SELECT
  OBJECT_NAME,
  DATA_OBJECT_ID,
  TO_CHAR(DATA_OBJECT_ID, 'XXXXX') HEX_DATA_OBJECT_ID
FROM
  DBA_OBJECTS
WHERE
  DATA_OBJECT_ID IN(
    TO_NUMBER('11711', 'XXXXX'),
    TO_NUMBER('11705', 'XXXXX'),
    TO_NUMBER('11710', 'XXXXX'),
    TO_NUMBER('11706', 'XXXXX'));

OBJECT_NAME DATA_OBJECT_ID HEX_DA
----------- -------------- ------
T1                   71429  11705
T2                   71430  11706
CHI_I1               71440  11710
PAR_I1               71441  11711 

Taking another look at the summary, now updated with the table and index blocks:

0x0200439b> objd: 0x00011711 1   /* PAR_I1 Root block of index on T1 */
0x0200439c> objd: 0x00011711 1   /* PAR_I1 Leaf block of index on T1 */
0x02000083> objd: 0x00011705 1   /* T1     Table block */
0x01c04d9b> objd: 0x00011710 2   /* CHI_I1 Root block of index on T2 */
0x01c04d9c> objd: 0x00011710 7   /* CHI_I1 Leaf block of index on T2 */
0x01c0389b> objd: 0x00011706 57  /* T2     Table block */
0x02000084> objd: 0x00011705 1   /* T1     Table block */
0x01c0389c> objd: 0x00011706 57  /* T2     Table block */
0x02000085> objd: 0x00011705 1   /* T1     Table block */
0x01c0389d> objd: 0x00011706 57  /* T2     Table block */
0x02000086> objd: 0x00011705 1   /* T1     Table block */
0x01c0389e> objd: 0x00011706 2   /* T2     Table block */ 

So, from the above, 2 of the consistent gets for the CHI_I1 index were for the root block of the index, and the remaining 7 were for the first leaf block.

Simple?

——————-

Anyone want to try creating a 10200 trace file parser in a different programming language and posting the source code here?





MS Query Teases You – Excel will Not Display Text Contained in Long Raw/Blob Column

30 10 2010

October 30, 2010

Older versions of the ERP package that I work with stored lengthy text data in LONG RAW columns.  Newer versions of the ERP package store lengthy text data in BLOB columns.  When tables containing those columns are queried using the Microsoft Query tool, which allows the data stored in tables to be retrieved into an Excel worksheet, the text found within the LONG RAW and BLOB columns appears in the Microsoft Query preview window, but those columns are silently excluded when the data is brought back into Microsoft Excel.  There must be a work around.  Sure, there is an easy solution for BLOB columns using a combination of the UTL_RAW.CAST_TO_VARCHAR2 and DBMS_LOB.SUBSTR functions, but is there a solution for LONG RAW columns?

First, we will build a table to somewhat mimic a table found in the old version of the ERP package (I think that I only omitted the WORKORDER_SUB_ID column):

CREATE TABLE T5(
  WORKORDER_TYPE CHAR(1),
  WORKORDER_BASE_ID VARCHAR2(30),
  WORKORDER_LOT_ID VARCHAR2(3),
  WORKORDER_SPLIT_ID VARCHAR2(3),
  SEQUENCE_NO NUMBER(12,0),
  TYPE CHAR(1),
  BITS_LENGTH NUMBER(12,0),
  BITS LONG RAW);

Now, let’s build a bit of code using the Visual Basic for Applications macro editor in Excel to populate the table.  I will use late binding, like what is required in VBS macros, so that I can use ADO without adding ADO references in the Visual Basic editor (see the previous articles in the Excel category for directions that permit early binding).

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
Const adOpenKeyset = 1
Const adLockOptimistic = 3

Sub CreateRows()
    Dim i As Integer

    Dim strUsername As String
    Dim strPassword As String
    Dim strDatabase As String
    Dim strSQL As String

    Dim intLength As Integer
    Dim strTempBits As String
    Dim bytBits() As Byte                         'An array of bytes

    Dim dynData As Object                         'ADO Recordset object used to retrieve the user's data
    Dim dbDatabase As Object                      'ADO database connection object

    On Error Resume Next

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

    strDatabase = "MyDB"
    strUsername = "MyUser"
    strPassword = "MyPassword"

    'Connect to the database
    'Oracle connection string
    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

    dbDatabase.ConnectionTimeout = 40
    dbDatabase.CursorLocation = adUseClient
    dbDatabase.Open

    'Retrieve a recordset with 0 rows that will allow us to insert into the table
    strSQL = "SELECT"
    strSQL = strSQL & "  WORKORDER_TYPE," & vbCrLf
    strSQL = strSQL & "  WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & "  WORKORDER_LOT_ID," & vbCrLf
    strSQL = strSQL & "  WORKORDER_SPLIT_ID," & vbCrLf
    strSQL = strSQL & "  SEQUENCE_NO," & vbCrLf
    strSQL = strSQL & "  TYPE," & vbCrLf
    strSQL = strSQL & "  BITS_LENGTH," & vbCrLf
    strSQL = strSQL & "  BITS" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  T5" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  1=2"
    dynData.Open strSQL, dbDatabase, adOpenKeyset, adLockOptimistic, adCmdText

    For i = 1 To 1000
        dynData.AddNew
        dynData("workorder_type") = "W"
        dynData("workorder_base_id") = "WO" & String(6 - Len(Format(i)), "0") & Format(i)
        dynData("workorder_lot_id") = "1"
        dynData("workorder_split_id") = "0"
        dynData("sequence_no") = 10
        dynData("type") = "D"

        strTempBits = "WO" & String(6 - Len(Format(i)), "0") & Format(i) & "/" & "1" & "  This is a very long description." & String(1000, ".")
        bytBits = StrConv(strTempBits, vbFromUnicode)
        intLength = Len(strTempBits)

        dynData("bits_length") = intLength
        dynData.Fields("bits").AppendChunk bytBits

        dynData.Update
    Next i

    dynData.Close
    dbDatabase.Close

    Set dynData = Nothing
    Set dbDatabase = Nothing
End Sub

When the above code is executed, the test table T5 should contain 1,000 rows.  Now let’s see if we are able to retrieve the rows from the database into Excel’s worksheet.  We will start out by switching to the Data tab in Excel (2010) and then select From Microsoft Query:

The next step is to select a suitable (32 bit, even on 64 bit computers) ODBC entry for the database.

Now let’s enter a simple SQL statement to retrieve the data contained in the test table T5 – note that Microsoft Query will complain if the ; character is included at the end of the SQL statement, but this is done to try to keep Microsoft Query from attempting to re-write the SQL statement (this occasionally eliminates a couple of problems):

As you can see, the Microsoft Query preview window shows the text data that is contained within the LONG RAW BITS column as we had hoped:

Now if we tell Microsoft Query to return the rows to Excel, we see that the LONG RAW BITS column was thrown away:

No problem, we will just try a trick to convert the LONG RAW column to a BLOB using the TO_LOB function and then convert the resulting BLOB to a VARCHAR2.  However, that trick simply does not work because the TO_LOB function can only be used in a INSERT INTO SELECT, or CREATE TABLEAS SELECT type SQL statement according to the documentation:

So, let’s create a new table to allow us to temporarily convert the LONG RAW column to a BLOB column:

Now, back in Microsoft Query, we change the SQL statement as follows:

SELECT
  WORKORDER_TYPE,
  WORKORDER_BASE_ID,
  WORKORDER_LOT_ID,
  WORKORDER_SPLIT_ID,
  SEQUENCE_NO,
  TYPE,
  BITS_LENGTH,
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) BITS
FROM
  T5_TEMP
WHERE
  TYPE='D';

When we return the query results into Excel this time, the LONG RAW column that was converted to a BLOB column in the second table, has its column values converted to a VARCHAR2, and those values actually makes it into Excel (although the column alias “BITS” is lost):

But, I don’t want to go through the process of creating a table to temporarily hold the results of a LONG RAW to BLOB conversion so that I can display the characters in Excel – that would be far too messy if there were many tables.  If we tell Excel to record a macro while we bring in data using the Microsoft Query tool, we see a macro that looks like this:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DSN=MyODBC;UID=MyUser;;DBQ=MyDB;DBA=W;APA=T;EXC=F;FEN=T;QTO=F;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NL" _
    ), Array("S;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;")), _
    Destination:=Range("$A$1")).QueryTable
    .CommandText = Array( _
    "SELECT" & Chr(13) & "" & Chr(10) & "  WORKORDER_TYPE," & Chr(13) & "" & Chr(10) & "  WORKORDER_BASE_ID," & Chr(13) & "" & Chr(10) & _
       "  WORKORDER_LOT_ID," & Chr(13) & "" & Chr(10) & "  WORKORDER_SPLIT_ID," & Chr(13) & "" & Chr(10) & "  SEQUENCE_NO," & _
       Chr(13) & "" & Chr(10) & "  TYPE," & Chr(13) & "" & Chr(10) & "  BITS_LENGTH," & Chr(13) & "" & Chr(10) & _
       "  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) " & Chr(13) & "" & Chr(10) & "FROM" & Chr(13) & "" & Chr(10) & "" _
       , "  T5_TEMP" & Chr(13) & "" & Chr(10) & "WHERE" & Chr(13) & "" & Chr(10) & "  TYPE='D';")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query_from_OR1125"
    .Refresh BackgroundQuery:=False
End With

While the above is interesting (and probably explains why the BITS column alias was lost), it is probably of little help for our problem.  We need something better, our own macro, something like the following (note that the following macro writes the data to a text file, and then uses an Excel function to quickly bring that text file into an Excel worksheet.  This approach should be much faster than visiting each cell in the worksheet to write the query results to each cell).

Sub DisplayData()
    Dim i As Integer
    Dim lngRow As Long
    Dim intFileNum As Integer

    Dim strUsername As String
    Dim strPassword As String
    Dim strDatabase As String
    Dim strSQL As String
    Dim strOut As String

    Dim intLength As Integer
    Dim strTempBits As String

    Dim snpData As Object                         'ADO Recordset object used to retrieve the user's data
    Dim dbDatabase As Object                      'ADO database connection object
    On Error Resume Next

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

    strDatabase = "MyDB"
    strUsername = "MyUser"
    strPassword = "MyPassword"

    'Connect to the database
    'Oracle connection string
    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

    dbDatabase.ConnectionTimeout = 40
    dbDatabase.CursorLocation = adUseClient
    dbDatabase.Open

    'Retrieve a recordset with 0 rows that will allow us to insert into the table
    strSQL = "SELECT"
    strSQL = strSQL & "  WORKORDER_TYPE," & vbCrLf
    strSQL = strSQL & "  WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & "  WORKORDER_LOT_ID," & vbCrLf
    strSQL = strSQL & "  WORKORDER_SPLIT_ID," & vbCrLf
    strSQL = strSQL & "  SEQUENCE_NO," & vbCrLf
    strSQL = strSQL & "  TYPE," & vbCrLf
    strSQL = strSQL & "  BITS_LENGTH," & vbCrLf
    strSQL = strSQL & "  BITS" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  T5" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  TYPE='D'"
    snpData.Open strSQL, dbDatabase

    Application.ScreenUpdating = False
    lngRow = 1

    intFileNum = FreeFile
    Open "C:\LongRawToVarchar.txt" For Output As #intFileNum

    strOut = ""
    For i = 0 To snpData.Fields.Count - 1
        strOut = strOut & snpData.Fields(i).Name & vbTab
        'ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Name
    Next i
    Print #intFileNum, strOut

    Do While Not snpData.EOF
        lngRow = lngRow + 1
        strOut = ""
        For i = 0 To snpData.Fields.Count - 2 'All Except the last column
            If Not (IsNull(snpData.Fields(i).Value)) Then
                'Switch out Ascii 13 & Ascii 10 combinations for just Ascii 10 so that line breaks do not cause problems in the resulting file
                strOut = strOut & Replace(snpData.Fields(i).Value, vbCrLf, vbLf) & vbTab
                'ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Value
            Else
                strOut = strOut & vbTab
            End If
        Next i

        'Handle the LONG RAW column
        strTempBits = Replace(StrConv(snpData("bits"), vbUnicode), vbCrLf, vbLf)
        strOut = strOut & strTempBits & vbTab
        'ActiveSheet.Cells(lngRow, snpData.Fields.Count).Value = strTempBits

        Print #intFileNum, strOut

        snpData.MoveNext
    Loop
    snpData.Close

    'Close the data file
    Close #intFileNum

    'Read the text file just written to disk into the worksheet
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\LongRawToVarchar.txt", Destination:=ActiveSheet.Range("A1"))
        .Name = "Page1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True                         'Tabs are the delimiter
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .Refresh BackgroundQuery:=False
    End With

    Application.ScreenUpdating = True

    dbDatabase.Close

    Set snpData = Nothing
    Set dbDatabase = Nothing
End Sub

If we did not want to write the data out to a text file, we could just comment out the section titled as “‘Read the text file just written to disk into the worksheet“, comment out the Print# lines, and uncomment the lines that begin with “‘ActiveSheet.Cells(“.

For example, if we do not want to write the results to a temp file, our macro would look like this:

Sub DisplayData2()
    Dim i As Integer
    Dim lngRow As Long

    Dim strUsername As String
    Dim strPassword As String
    Dim strDatabase As String
    Dim strSQL As String
    Dim strOut As String

    Dim intLength As Integer
    Dim strTempBits As String

    Dim snpData As Object                         'ADO Recordset object used to retrieve the user's data
    Dim dbDatabase As Object                      'ADO database connection object

    On Error Resume Next

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

    strDatabase = "MyDB"
    strUsername = "MyUser"
    strPassword = "MyPassword"

    'Connect to the database
    'Oracle connection string
    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

    dbDatabase.ConnectionTimeout = 40
    dbDatabase.CursorLocation = adUseClient
    dbDatabase.Open

    'Retrieve a recordset with 0 rows that will allow us to insert into the table
    strSQL = "SELECT"
    strSQL = strSQL & "  WORKORDER_TYPE," & vbCrLf
    strSQL = strSQL & "  WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & "  WORKORDER_LOT_ID," & vbCrLf
    strSQL = strSQL & "  WORKORDER_SPLIT_ID," & vbCrLf
    strSQL = strSQL & "  SEQUENCE_NO," & vbCrLf
    strSQL = strSQL & "  TYPE," & vbCrLf
    strSQL = strSQL & "  BITS_LENGTH," & vbCrLf
    strSQL = strSQL & "  BITS" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  T5" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  TYPE='D'"
    snpData.Open strSQL, dbDatabase

    Application.ScreenUpdating = False
    lngRow = 1

    strOut = ""
    For i = 0 To snpData.Fields.Count - 1
        ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Name
    Next i

    Do While Not snpData.EOF
        lngRow = lngRow + 1
        strOut = ""
        For i = 0 To snpData.Fields.Count - 2 'All Except the last column
            If Not (IsNull(snpData.Fields(i).Value)) Then
                'Switch out Ascii 13 & Ascii 10 combinations for just Ascii 10 so that line breaks do not cause problems in the resulting file
                strOut = strOut & Replace(snpData.Fields(i).Value, vbCrLf, vbLf) & vbTab
                ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Value
            Else
                strOut = strOut & vbTab
            End If
        Next i

        'Handle the LONG RAW column
        strTempBits = Replace(StrConv(snpData("bits"), vbUnicode), vbCrLf, vbLf)
        strOut = strOut & strTempBits & vbTab
        ActiveSheet.Cells(lngRow, snpData.Fields.Count).Value = strTempBits

        snpData.MoveNext
    Loop
    snpData.Close

    Application.ScreenUpdating = True

    dbDatabase.Close

    Set snpData = Nothing
    Set dbDatabase = Nothing
End Sub

The resulting worksheet would then need a bit more clean up than it did when we wrote out the data to a text file and brought the text file into the worksheet with a QueryTable:

So, now you know how to retrieve text contained in a LONG RAW column (our T5 table) or text contained in a BLOB column (our T5_TEMP table, as stored by the recent releases of the ERP package) and display the text in an Excel worksheet with other columns retrieved by a query.

I fully recognize that LONG RAW columns are deprecated, but is anyone able to identify a more direct way to transform a LONG RAW column value into a VARCHAR2 using just a SQL statement with built-in Oracle Database functions, without requiring an intermediate temporary table?








Follow

Get every new post delivered to your Inbox.

Join 142 other followers