Advanced Toolware's Solutions
(UMRA) - User Management Resource Administrator: Download || More Information
(ESSOM) - Enterprise Single Sign-On Manager: Download || More Information
(SSRPM) - Self Service Reset Password Managment: Download || More Information
Other Solutions

Now, it's been awhile since I've blogged about something other then a UMRA Function. This time around I think I'm going to switch it up a bit and talk about "connectors". Now the word "connector" can really be translated into many different meanings in the IT world, so my definition of a "connector" is... a mechanism in which a "source" of data can be bound to for data manipulation or data mining. So with that said, as many of you know UMRA can connect to most any ODBC compliant database. Now, I could start with some of the most basic connectors, however, most recely I've noticed a flux in automation projects that require more then your standard ODBC windows connection. Oracle seems to be a rather tricky ODBC install to get setup, with that said, I will show you how I did a UMRA ODBC Connector to an Oracle Database.

Client Enviorment: Server: Server 2008 32bit
Oracle DB: 11g
UMRA: Version 10.3 Build 1601

Install Steps:
Step 1: Depending on your Oracle Version, and Windows OS version, download the correct ODBC Driver from Oracles website: Oracle ODBC Drivers
Step 2: Install the Oracle ODBC Drivers
Step 3: Now Setup a System DSN START>Administrative Tools>Data Sources (ODBC), when this window comes up click on "Sytem DSN" and then click "Add"
Step 4: Select from the list of drivers, your Oracle Driver (In my case it's called Oracle in instantclient11_1)
Step 5: Fill in your Oracle ODBC Information (See Below Example)
Data Source Name: Your Source Name (Anything you want)
Description: Your Data Source Description (Anything you want)
TNS Service Name: rac1 = location : 1521 = port / Oracle Server Name (See Image Below) User ID: User ID to access data

Step 6: Now that the ODBC System DSN has been created, its time to connect to it through UMRA, either open up a new UMRA project or existing UMRA project. Use a "Generate Gerneric Table" action.
Step 7. Double click on the "Generate Gerneric Table" action, select "Database query from the Table Type option. Click on the Database tab, then the Configure button. THen select "All Other Databases" from the Database Types Drop down, Then click on the Database conneciton tab. Next, click on the Configure tab, now from this selection select " Microsoft OLE DB Provider for ODBC Drivers", click next.
Step 8. Now enter your datasource name into the source name option, then enter your username/password as well. Click "Allow saving Password", then click test connection, you should get a "Connection Successful" prompt.(See Below Screen Shot)

Step 9: Now check your Database connection string, that you just setup within the wizard. It should look something like this, if not, copy this if you have a system setup simular to mine.

Provider=MSDASQL.1;
Password=ORACLE PASSWORD;
Persist Security Info=True;
User ID=ORACLE USER ID;
Data Source=SYSTEM DSN DATASOURCE NAME;
Extended Properties="DSN=DSN NAME;
UID=ORACLE USER ID;
PWD=ORACLE PASSWORD;
DBQ=YOUR TSN PATH;
DBA=W;
APA=T;
EXC=F;
FEN=T;
QTO=T;
FRC=10;
FDL=10;
LOB=T;

Step 10: Enter your Query Into the "QUery" Tab in your Generate Gerneric Table Action. Click the Run Test Tab, to check your results.

And there you have it, a quick and easy way to setup a UMRA ODBC Connection to Oracle through a system DSN. If you have any question please feel free to email me or, post a message below.