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





Name that Table’s Column

9 11 2012

November 9, 2012

I have not had a lot of time to browse through forums lately, but I noticed an interesting thread in the comp.databases.oracle.server Usenet group.  The OP in the thread was curious why quotes (double quotes) were required around a particular column when referenced in a SQL statement, because specifying quotes around the column name is apparently a little challenging in the PHP scripting language.

I thought about this issue a bit, wondering “how did that happen” and then thought about the benefits of this approach.  In theory, a sophisticated programmer could build a 16 column table using a single four character column name (with different letters capitalized).  Such an approach is sure to evoke a couple of choice four letter words!

I thought that I would throw together a quick example table:

CREATE TABLE T1(
  My_Data NUMBER,
  "My_Data" NUMBER,
  "my_data" NUMBER,
  "MY_DATA" NUMBER);

That almost worked:

SQL> CREATE TABLE T1(
  2    My_Data NUMBER,
  3    "My_Data" NUMBER,
  4    "my_data" NUMBER,
  5    "MY_DATA" NUMBER);
  "MY_DATA" NUMBER)
  *
ERROR at line 5:
ORA-00957: duplicate column name

Columns 1 and 4 have the same name.  Let’s fix that problem and try again:

CREATE TABLE T1(
  My_Data NUMBER,
  "My_Data" NUMBER,
  "my_data" NUMBER,
  "MY_DATa" NUMBER);

Table created.

That worked, now we have a table with four columns, where all of the column names are the same.  Notice that the first column name was not wrapped in quotes.

Let’s insert a row into the table:

INSERT INTO T1 VALUES(
  1,
  2,
  3,
  4);

1 row created.

Let’s see what happens when we query the table:

SELECT
  *
FROM
  T1
WHERE
  MY_DATA=1;

   MY_DATA    My_Data    my_data    MY_DATa
---------- ---------- ---------- ----------
         1          2          3          4

Notice that a row was returned, even though the upper/lower case of the column in the WHERE clause did not match the first column name when the T1 table was created (when the table was created, for the first column, the column name was created as if were specified in all uppercase letters).

Let’s see if a row is returned if we try the following (notice that the attempt at querying with the fourth column based on matching that column’s capitalization in the WHERE clause failed to restrict the query results based on the contents of the fourth column in the table):

SELECT
  *
FROM
  T1
WHERE
  MY_DATa=1;

   MY_DATA    My_Data    my_data    MY_DATa
---------- ---------- ---------- ----------
         1          2          3          4

Let’s try again, this time wrapping the column name found in the WHERE clause in quotes:

SELECT
  *
FROM
  T1
WHERE
  "MY_DATA"=1;

   MY_DATA    My_Data    my_data    MY_DATa
---------- ---------- ---------- ----------
         1          2          3          4

A row was still returned (as expected).  So then, how do we determine which rows in the table have a value of 1 in the fourth column?  We need to place the column name found in the WHERE clause within quotes as follows:

SELECT
  *
FROM
  T1
WHERE
  "MY_DATa"=1;

no rows selected

Notice that this time no rows were returned (as intended, and expected).

I guess that the lesson here is to be careful when creating tables in Oracle Database.  Just because other database products may place column names within quotes as standard practice, that does not indicate that you, as the developer, should continue the practice when working with Oracle Databases.  Now get out there and create some 16 column tables with choice four letter words.  :-)








Follow

Get every new post delivered to your Inbox.

Join 137 other followers