Oracle Instant Client 10 / VBS Oracle access

Main source files: Instant client repository
Version detailed 10.2.0.4

PART I - Instant Oracle client 10.2.0.4 for Win32
ODBC installation guide

I decided to give a detailed installation guide for this version which will help you save some time and use easily Instant Oracle. It allows to avoid installing the full Oracle client on any computer or server which is not totally seamless.

I- Download the proper files

Create first a directory on C:\ drive C:\Instant_oracle, put all the files in this directory.

• Go to the link above and download the proper version for the suitable platform.
• For the win32 platform, download the dll file here (Not needed under Windows XP).
• Download the reg file here as well.
• Download this sample TNSNAMES.ORA file here.

You should get normally the following files:

• instantclient-basic-win32-10.2.0.4.zip
• instantclient-odbc-win32-10.2.0.4.zip
• mfc71.dll
• Reg_tns.reg
• tnsnames.ora

II- Installation steps

• Unzip all the files into the same directory*

*It can work with the basic lite version of the archive, but you need first to follow carefully this procedure with the files mentioned before at the end only keeping the lite archive content.

• Apply the Reg_tns.reg file by double clicking on it and accepting to load it into the registry.

• Update the path environment variable with "C:\Instant_oracle".

Go under System Property > Advanced > Environment variables > System Variables > Path
and Add ";C:\Instant_oracle" at the end of this variable without the brackets.

• Launch a command window with Start > Run > Cmd, go to the C:\Instant_oracle folder.
• Execute the odbc_install.exe file. If everything works properly, you should get a success message.

PART II - ODBC System DSN creation and
VBS Oracle sample code

III- Creation of the ODBC link to an Oracle database

• Go first to :

Start > Control Panel > Administrative Tools > Data Sources (ODBC)

• Go to the System DSN tab and click Add...

• Select the Oracle in instantclient10_2 driver then you will see appearing a configuration window.
• In this configuration window, you will have to fill in mainly:

*The Data Source Name, a Description, the TNS Service Name, a User ID
*Note: The TNS service name matches what is defined in the TNSNAMES.ORA file. Don't think that the scroll down list will propose you all the TNS service name, you have to type it manually without taking care of the junk available in the choices.

• You can simply reproduce what is mentioned below:

• At the end of the configuration, you can use the Test Connection option. If everything works well you should get a successful connection message.
  

Everything is now ready to prepare the software part and to use your database.
In order to illustrate this connection, I have added a simple VBS sample which illustrate how use this configuration.

IV- Sample VBS usage of this ODBC link

• Here is a sample VBS code, just copy it to a text file and save as test.vbs, then double click on it.

Source Code

'*******************************************
'Script illustrating a simple Oracle query
' DdTf: 12/08
'*******************************************
Dim connection, connectionString, myCommand, commandString, rst
Dim myDSN, myUID, myPWD

'Command type - 1 is for standard query
const cnstCommand = 1

'*******************************************
'Modification section
'*******************************************
'Data Source Name
myDSN="orat2"
'User ID and Password
myUID="TEST"
myPWD="TEST"
'*******************************************

'Connection to the database
connectionString = "Data Source=" & myDSN & ";UID=" & myUID & ";PWD=" & myPWD & ";"
Set connection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
Set rst = CreateObject("ADODB.Recordset")
connection.Open connectionString

'Query part
commandString = "select sysdate from dual;"
mycommand.CommandText = commandString
mycommand.CommandType = cnstCommand
mycommand.ActiveConnection = connection
Set rst = mycommand.Execute

'Result display
While Not(rst.EOF)
wscript.echo rst("sysdate")
rst.MoveNext
Wend

Wscript.quit

• If everything is working, you should be able to get the date in a message box.
  

Well done !