It took a few hard days for TechieDan to finally established the ODBC connection between Oracle ODBC and Qlikview. Tested both OLE DB and ODBC, both requiring different methods to connect to an Oracle DB.
What is QlikView? View more of it at it’s original website. Sort of a reporting tool as it gathers the information from a csv file or a database table.
How to start? In every new installation of Qlikview, there’s a tutorial to guide new users. What they didn’t teach though is further specific connection to a specific database with other drivers and configuration.
To create a new connection, Open File > Edit Script (Ctrl + E)
A screen will appear before you to choose desired connection. Click ODBC and Connect and a Connect to Data Source screen will appear. Choose Create ODBC Source… if the desired Data Source is not available.
Click Machine Data Source and Click New.
Choose Oracle Instant Client (or any name like that) to be able to connect to an Oracle DB.
How to get Oracle Instant Client? The steps are laid down below.
- Go to Oracle InstantClient Download site and download the InstantClient available to your Operating System.
- Download also the Instant Client Package – ODBC too.
- Unzip the packages to a directory name “instantclient” or any other desired name.
- Set the library loading path in your environment to the directory in Step 2 (“instantclient”). On many UNIX platforms, LD_LIBRARY_PATH is the appropriate environment variable. On Windows, PATH should be used.
- Go to instantclient folder and run odbc_install.exe
Now let’s proceed to the next step.You will now be at the Oracle ODBC Driver Configuration where you will enter your Data Source Name, TNS Service Name and click Test Connection to input username and password and to determine the connection is successful.
There might be an error though for users if they’re connecting to a database in another server. TechieDan has faced with this error where the TNS Service Name was showing gibberish or garbage names on the drop down menu.
After enquiry and searching on the Internet, it was found that with multiple Oracle installation, the situation may arise. So go ahead once again to your Set Environment Variables and do the following.
Create TNS_ADMIN with value [oracle path]\network\admin (example – a:\oracle\network\admin)
This is where one has to change the tnsnames.ora which can only be found in an oracle installation or created the file itself. My tnsnames.ora was found at my A:\oracle\product\10.2.0\db_2\network\ADMIN so it might not be the same for you.
(ADDRESS = (PROTOCOL = TCP)(HOST = IP Address or HostName)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = SID Input)
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(SID = PLSExtProc)
(PRESENTATION = RO)
Finally now after you’ve done all these, Qlikview should be able to connect to the Oracle DB and you’re able to generate views and reports for yourself or your client. If you would like to connect to Oracle using OLE DB in QlikView, do view the next post regarding this.
Update Sept 1st 2010 : Remember to delete any obstructing space at the beginning of the TNSNAMES.ORA file as it might result in having an unsuccessful connection