Simple VBS Script to Retrieve Data from Oracle

12 12 2009

December 12, 2009

All recent releases of the Windows operating system include the Windows Scripting Host, which allows executing program commands that look much like the macro language used in Excel, Access, and Word, as well as the original line of Microsoft’s Visual Basic (before the introduction of .Net).  Scripts intended to be excuted by the Windows Scripting Host have an extension of .VBS and are executed either with cscript that outputs to a command line window, or the default wscript that outputs to Windows popup messages.

A simple script that connects to an Oracle database (without using ODBC), queries a table, and then performs a comparison on the values retrieved from the table follows:

'Save as ConnectDB.vbs
Dim strSQL
Dim strUsername
Dim strPassword
Dim snpData
Dim dbMyDBConnection
Dim ORDER_ID         'Note that this variable was omitted originally as it was automatically supplied by an ERP package

Set snpData = CreateObject("ADODB.Recordset")
Set dbMyDBConnection = CreateObject("ADODB.Connection")
strUsername = "MyUser"
strPassword = "MySecret"

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyDB;User ID=" & strUsername & ";Password=" & strPassword & ";"
dbMyDBConnection.Open

ORDER_ID = "MYORDERID123"  'Note that this variable value was omitted originally as it was automatically supplied by an ERP package
strSQL = "SELECT C1, C2 FROM MY_TABLE WHERE PURC_ORDER_ID='" & ORDER_ID & "'"

snpData.Open strSQL, dbMyDBConnection

If Not(snpData.EOF) Then
    If cInt(snpData("c1")) < cInt(snpData("c2")) Then
        MsgBox "C1 is Less than C2"
    End If
    If cInt(snpData("c1")) > cInt(snpData("c2")) Then
        MsgBox "C1 is Greater than C2"
    End If
    If cInt(snpData("c1")) = cInt(snpData("c2")) Then
        MsgBox "C1 is Equal to C2"
    End If

    MsgBox cInt(snpData("c1")) - cInt(snpData("c2"))
Else
    MsgBox "Ut oh, No Matching Records"
End If

snpData.Close
dbMyDBConnection.Close

Set snpData = Nothing
Set dbMyDBConnection = Nothing

Once the script is saved, double-clicking the script should automatically execute it using wscript.  Alternatively, open a Windows command prompt and type the following (assuming that the script is named ConnectDB.vbs and is saved in the root of the C:\ drive):

cscript c:\ConnectDB.vbs

« »


Actions

Information

  • Date : December 12, 2009
  • Categories : Excel, VBS

4 responses

30 03 2010
joel garry (13:00:06) :

I don’t quite understand how ORDER_ID is set.

Reply
30 03 2010
Charles Hooper (14:06:58) :

Joel,

The above VBS script was adapted from a sample macro script that I created for an ERP package. The ERP package automatically supplied a number of different variables with pre-filled values, and one of those was the ORDER_ID variable. When I created the plain VBS version of the script for this blog I neglected to define the ORDER_ID variable. Based on your feedback I have adjusted the script to define that variable and set a value for the variable.

Thanks for the feedback.

Reply
13 10 2017
Sharaz (08:55:54) :

Hi ,

Here the connection string is using only provider , data source and credentials.I am able to open a successful connection using these details. But Could you please explain how to fetch table values from a particular table in a specific database.I don’t see any line specifying the name of database in the above code.Can you please help me

Reply
13 10 2017
Charles Hooper (09:29:59) :

Sharaz,

In the example provided in this article, the following line appears:

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyDB;User ID=" & strUsername & ";Password=" & strPassword & ";"

In this case, all recordsets and commands variables that use the dbMyDBConnection variable will work with the database named MyDB (using the example code, you will change this name to be the name of one of the databases that are defined in the tnsnames.ora file). What that means is that everything will be selected from the same database. If you need to select from multiple databases, then you either need to create multiple variables like dbMyDBConnection (ex: dbDatabase1, dbDatabase2, etc.) and connect to each database, or have the DBA set up a database link from the database that you connect to in your script to the other databases (see the documentation: https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12083 ). With method 1, to send the SQL statement to a different database (once connected to the different databases), you would just specify the correct connection variable (dbMyDBConnection, dbDatabase1, dbDatabase2, etc.) in the snpData.Open line. Using the second method, you would only have a single database connection in the script, but after the table name in the SQL statement, you would add @ and then the database link name (with no spaces in between).

Reply

Leave a comment