It took a few hard days for TechieDan to finally established the ODBC connection between Oracle and Qlikview using the ODBC method. After testing with OLE DB and ODBC, both of them require different methods to connect to the Oracle DB.
What is QlikView and Oracle?
Qlikview? View more of it at it’s original website. It is more like a reporting tool as it gathers the information from a csv file or a database table and then populates it onto a dashboard or exported out onto a file for the target user.
How to start? In every new installation of Qlikview, there’s a tutorial to guide new users. What was not taught though is how to specify certain connection to a specific database with other drivers and configuration.
How to Create Connection
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.
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
Do note that sometimes the DB connection of certain users like Scott can happen. You may also check out on other Oracle problems if it’s nothing related to Qlikview.
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
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
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.
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
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
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
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.
Thank you for taking time to publish this. Saved my day.
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
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
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
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