GEOFS
asked on
Automate an Oracle update in Excel
Is it possible to set up an Excel macro to establish a connection to an Oracle database, update a couple of fields in a specific record and then disconnect. The update commands would look something like this:
UPDATE TABLE1 SET STATUS_CODE = 'A' WHERE ID_NUM = '1234567'
UPDATE TABLE1 SET STATUS_DATE = '01-JAN-2017' WHERE ID_NUM = '1234567'
I don't know enough about Excel to figure out if this is even possible, let alone how to go about it.
Does anyone out there know?
UPDATE TABLE1 SET STATUS_CODE = 'A' WHERE ID_NUM = '1234567'
UPDATE TABLE1 SET STATUS_DATE = '01-JAN-2017' WHERE ID_NUM = '1234567'
I don't know enough about Excel to figure out if this is even possible, let alone how to go about it.
Does anyone out there know?
In the example in that link, it uses the Microsoft ODBC drivers. The Microsoft drivers for Oracle have been deprecated. Use the native Oracle drivers.
Also note that example uses a query with a result set. As such, you'll need to look for an example that performs DML. It may or may not be the Execute method you need. I cannot remember.
Also note that example uses a query with a result set. As such, you'll need to look for an example that performs DML. It may or may not be the Execute method you need. I cannot remember.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for your suggestions. While I don't have the final solution yet, your posts provided some additional search terms that have brought me enough information to proceed.
It has been many years since I did it and back then it used the ODBC drivers. I'm not sure if Excel supports any of the newer drivers or not.
You'll need an Oracle Client. I recommend the Instant Client:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
You'll need to correct bit version for Excel (Not your OS). If you want the ODBC drivers, it is a separate download.