The OLEDB object can be configured to support connections to Oracle databases. The OLEDB object uses the Jet OLE DB 4.0 connection capability, and so supports a number of OLEDB standard databases. By design the Provider String uses a generic format which is designed to cater for basic OLEDB connections. Oracle, however, requires slightly different parameters to the standard connection string.
The standard connection string looks like this in Blue Prism:
Configuring the ‘DATA – OLEDB’ object
To create a Connection String for Oracle, edit the “Set Connection” stage of the Object “Data – OLEDB” to account for the specific connection string that Oracle needs.
The Connection String format used to connect to an Oracle Express 11g database running on the local machine might look like this:
The “dbq” setting could be added an an additional configurable Data Item if preferred. In this example the Oracle schema has the same name as the user name set up in the database.
Here is an example Connection String for Oracle 11g Express Edition using the in-built default database schema ‘HR’ hosted on the local machine (‘localhost’)’:
For more examples of Oracle Connection String formats, see this web site.
Creating a Process to connect to Oracle
Here is an example Process used to connect to Oracle using the Connection String, open the Connection, and then run a simple SQL query:
In the Set Connection stage parameters are passed to the ‘Data – OLEDB’ object, and that object’s ‘Set Connection’ action is called:
If you receive the message: “‘OraOLEDB.Oracle’ provider is not registered on the local machine” this indicates that the OLEDB Oracle Data Provider cannot be located.
This may be due to the location of the required files in respect of their installation directory (e.g. 32-bit vs 64-bit install locations). It may also indicate that the OLEDB Provider for Oracle is not installed. The provider can be installed via the Oracle Data Access Components (ODAC).
ODAC for 64-bit operating systems: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
ODAC for 32-bit operating systems: http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html