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', 'john@doe.com');" 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!
Correct me if I am wrong but didn’t you forget the line
‘ set theCommand = CreateObject(”ADODB.Command”)’ Code did not work without it. Other than that it worked GREAT. Very helpful. Thanks
Thanks for that Spivey - corrected the code above.
I’m just starting to learn VBScript so this may be a dumb question. Wouldn’t I have to close the connection when I’ve finished running the SP?
Hi Ed. It would be good practice to close off the connection, but not necessary. I suspect any DBAs for Oracle might encourage it, though.
how you can define to connect with the DRIVER for Oracle 10g.
You think this way will works?
Thanks.
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
I’m able to execute the stored procedure but having problems when parameters need to be assigned?
Great site. Very useful stuff. Quick question tho…
After updating or inserting data into the database what is the easiest way to check that things were updated or inserted properly? (Other than just running a query to see if the row or data is there…) Thanks!
Thank you for the example. It sure helped. My setup:
Oracle = Oracle 10g Express Edition
OS = Windows 2003
I was getting Oracle error “ORA-00911: invalid character”. Tried many things. The bottom line that I found (I may be off here) was the semicolon “;” at the end of
“INSERT INTO people (name, email) VALUES (’John Doe’, ‘john@doe.com’);”
It should NOT be there.
Once removed the ORA errors goes away. The rows are inserted fine in Oracle.
PLEASE NOTE: One need the “;” when doing INSERT on Oracle command line utilities (e.g. SqlPlus).
Once again that you for puting a example here, it helped.
Suhail.
Can you do this also with Oracle 10g 64 Bit running on a 64 Bit Windows 2003 Server (x86) ?
As fas ar i know, you don’t have Microsoft ODBC for Oracle.
Instead, you have to install the Oracle Instant Client 11.1
But whatever i try, i cannot connect my VBScript to Oracle.
My ConnectionString looks like this:
“DRIVER={Oracle in instantclient11_1};OSAuthent=1;QTO=F;”
I have tried many variations, but i always get errors like:
ADODB.Connection: Provider is not specified and there is no designated default provider.
ADODB.Connection: Provider cannot be found. It may not be properly installed.
Thank for your exact, neat and clear information
Hello
I am new to oracle. I am doing following things
During installation of App we need to create Database and we are uing WIX as instllalation script.
We are using VBScript to create Database in SQL and Oracle.
Version: Oracle 9.2
There is a problem is to crate DB on Oracle server using VB Script.
1) it seems on connection script Database name need to be provided. And we are creating new DB - no db will present on DB Server.
I tried with the example given above but it raise error about “Could not resolve service name…”
i tried from remote machine where oracle clinet is installed and also oracle server it self.
i tried “DBA Privilege=SYSDBA” also in connection string but no change. “…Driver SQLSetAttr failed..”
Any input will be very helpfull.
hi I was trying to run a stored procedure by making an ODBC from VB script.
I got this error “Requested operation requires an OLE DB Session object, which is not supported by the current provider.”
as soon as I got to
thecommand.ActiveConnection = connection
not sure what it is..and how to resolve it.
Could you throw some light on it please.
The stored procedure works fine from SQLPlus though.