VBScript and Oracle

Written on 09 May 2006

Fairly recently I was approached to create a system which parses data out of a CSV file and dumps said data into an Oracle table so that it could be reported on. The standard procedure for this is to send the source files to a Unix system and run them through with SQL Loader, but I didn't fancy going down that route...

There's a fair amount of information around The Internet on getting VBScript to communicate with an Oracle database and to perform various operations, but it's all a little fragmented. I've tried to consolidate what I've learnt from my experience, covering:

Connecting to an Oracle database with VBScript
Sending INSERT statements to the Oracle database
Executing a stored procedure on the Oracle database to manipulate the inserted data

Connecting to an Oracle database

Getting VBScript to connect to Oracle is a pretty easy step - it's just a bit tricky to get your head around the various constants involved, and the order in which to setup various aspects of the connection:

Dim connection, connectionString, theCommand, commandString
connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=user;Password=password;"
Set connection = CreateObject("ADODB.Connection")
Set theCommand = CreateObject("ADODB.Command")
connection.Open connectionString

Depending on your ODBC setup, the driver name might be slightly different from "Microsoft ODBC for Oracle", so you may need to change it. The above script should connect your script to your given database, provided all the connection parameters are correct.

Sending an INSERT statement

Having an active connection to Oracle is pretty pointless unless you're actually going to send a query to it. We'll just work with a basic INSERT statement here, to get the point accross - obviously you'd need to adapt this to your situation:

const cnstCommand = 1 'Command type - 1 is for standard query
commandString = "INSERT INTO people (name, email) VALUES ('John Doe', '[email protected]');"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
thecommand.ActiveConnection = connection
thecommand.Execute

As an example, this would create a new row in the people table with the given values - easy.

Executing a stored procedure

The database driver also allows you to execute stored procedures on your Oracle database. In my scenario, I sent data to a staging table, where it was then manipulated by a stored procedure which enforced some rules and output to a final table, which is used for the reports.

const cnstStoredProcedure = 4 'Command type - 4 is for stored procedure
commandString = "STORED_PROCEDURE_NAME"
thecommand.CommandText = commandString
thecommand.CommandType = cnstStoredProcedure
thecommand.ActiveConnection = connection
thecommand.Execute

Voila, stored procedure executed.

I'm happy to go into slightly more detail in another post or two, if anyone needs further elaboration or to see a full program structure for running this properly. You may also want to implement some error trapping - you never know when someone else might lock out your table or user account!