Subscribe to rss rss

Linking Qlikview and Oracle using ODBC

Posted by : | On : 12-08-2010 | Comments 12

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

Related Posts with Thumbnails

(012) Comments

  1. Murray Wiseman said on

    I cannot get QlikView to connect to my local Oracle Express 10g database. The DSN texts OK in the ODBC Data Source Administrator (accessed with SysWOW64\odbcad.exe. However in QlikView10 when I try the same DSN it fails the “Test Connection” with

    “SQL##f – SqlState: 08004, ErrorCode: 12154, ErrorMsg: [Oracle][ODBC][Ora-12145: TNS:could not resove the connect identifier specified”.

    I enabled the “Force 32 Bit” check box.

    With your experience, Techie Dan, would you know what else I should try?

    Murray

  2. Siddeshwar said on

    I’m facing the following error on reload:
    SQL##f – SqlState: 28000, ErrorCode: 1005, ErrorMsg: [Oracle][ODBC][Ora]ORA-01005: null password given; logon denied

    ODBC CONNECT TO [odbcConnection;DBQ=orcl_instance]
    Please suggest.
    -Sid

    • techieDan said on

      If you’re using Qlikview 10, here are some of the upgrades from previous Qlikview which might hinder.

      “It is no longer possible to use blank password for Custom Users in the Publisher.”

      Of course, I wouldn’t know whether to call it upgrades since it forces the user to change a few things.

  3. matias said on

    I cant start de odbc_install… y changed de path library but star a DOS but its close inmediatly.
    Then… do i have to pay for the full version of qlv in order to connect to oracle database o can i do something else at least to practice?
     
    thank you very much
     
    matias… matias.pinal@watea.com.ar

    • techieDan said on

      Hello Matias,

      Something must be wrong. That odbc_install is a bridge for connectivity towards Oracle database. What version of Windows you’re using?

      By the way, the above example I posted was done using a non-full version of qlikview. It works for me

  4. Kirstin said on

    We are using Oracle ODBC direct and can connect if the client pc is logged in as Administrator but not if it is logged in as the local User.

    Any clues

    Qlikview v11 

    • techieDan said on

      Problem with the new windows, if Qlikview was installed under administrator account, it definitely have to be run under administrator rights. This happens for Windows Vista and 7.

      One way is to run Qlikview as Administrator.

  5. Bhavani said on

    Thank you for taking time to publish this. Saved my day.

  6. Segun said on

    Thank you for this…

    I must add in case it helps anyone that I had to unzip the “instantclient” directly into the “instant client-odbc” folder before I could successfully install odbc_install.exe 

  7. Lorena said on

    This application is rubbish becuase it is oppose the Relational database principals. Qlickview is not going far than this . They would go maximum another year then nobody would know thier name even

  8. André said on

    Hi all,
    I’m facing the following error on connection to Oracle BD:
    SQL##f – SqlState: NA000, ErrorCode: 1019, ErrorMsg: [Microsoft][ODBC driver for Oracle][Oracle]Error while trying to retrieve text for error ORA-01019
    What am I doing wrong?… I have searched about this error and found some technotes saying that my problem is memory … I don’t believe in that.. Please, anyone ever faced this trouble…?
    I’m using Oracle and QlikView Personal Edition.

    Thanks a lot

    André Watanabe

    • techieDan said on

      It might be the case of your naming conventions in tnsnames.ora. Do view the above example. Make sure that there are no spaces before the first text at the beginning of the file

Write a comment