Linking Qlikview and Oracle using ODBC

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.

qlikview Introduction

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)

QlikView Edit Script

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.

Qlikview Connect ODBC

Click Machine Data Source and Click New.

Select QlikView data source

Choose Oracle Instant Client (or any name like that) to be able to connect to an Oracle DB.

Choose Oracle Instant Client

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.

ODBC Driver Configuration

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.

Error Oracle TNS Service Name Garbage

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.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP Address or HostName)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SID Input)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(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

12 Comments

  1. Murray Wiseman December 14, 2010
  2. Siddeshwar March 17, 2011
    • techieDan March 17, 2011
  3. matias March 23, 2011
    • techieDan March 24, 2011
  4. Kirstin February 6, 2012
    • techieDan February 9, 2012
  5. Bhavani March 12, 2012
  6. Segun May 26, 2012
  7. Lorena July 27, 2012
  8. André August 1, 2012
    • techieDan August 2, 2012

Leave a Reply