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