topic Connection with oracle data base in QlikView

Connection with oracle data base

Tue, 26 Apr 2011 08:10:35 GMT

Dear All,

I have to make a connection with the Oracle data base and i have no idea how to proceed.

While connecting to SAP BW, we use sap connector and provide server ip, client, user name and the connection is made.

for Oracle , we use odbc and connect to the data base.

What my question is that... for cube extractions and qvd generation for BW cubes, i downloaded this application from here which downloadd the meta data and then the cube and its master data and generated qvd.

What should i do to extract data and generate QVDs from tables in the oracle data base?

I will be thank ful for a quick response.

Regards,

Maria

Connection with oracle data base

Tue, 26 Apr 2011 08:24:49 GMT

Hi Maria,

Once you connected to Oracle DB using ODBC, you`d be getting the connection string in QlikView.

Once you get the connection string, select your table by clicking on the "SELECT" button which is below "Connect" in Edit Script.

Once you say select you`ll get the list of Tables, Synonyms, View, etc. which are available in your DB. Select your table from the DB and Create QVD using "Store Command".

For Example, Let assume you have selected order table from DB, Your code should look like below.

Order_Table:

Load * ;

Select * from DB;

Store [Order_Table] into ;

This will create a QVD for Order table.

Not sure any predefined temple is available for oracle to extract data from DB. But you could use above method to extract data from oracle DB.

Hope this helps you.

- Sridhar

Connection with oracle data base

Miguel_Angel_Baeyens — Tue, 26 Apr 2011 08:26:28 GMT

Hello Maria,

If you mean connection to raw tables in Oracle, and if the driver is properly configured, the usual script syntax should work

Table: // LabelLOAD Field1, Field2; // Next lines are the SQL statement passed on to the driver and to the databaseSQL SELECT Field1, Field2FROM Table;

Is that what you are looking for?

SV:Connection with oracle data base

gandalfgray — Tue, 26 Apr 2011 08:34:00 GMT

Hi Maria

After the connection is made use the "Create Select Statement" wizard.

This wizard is opened when you press "Select..." button in the script editor.

In the wizard you can browse the tables, views etc in the database you are connected to.

If you want to filter the data at the database level you can manually add a "where clause" after the select statement is generated by the wizard.

Connection with oracle data base

sushil353 — Tue, 26 Apr 2011 08:34:38 GMT

Hi Mariakesc,

go through the document attached, it might help you.

Thanks..HTH

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4442.QlikView-Connection-to-Oracle.doc:550:0]

Connection with oracle data base

Tue, 26 Apr 2011 11:53:54 GMT

Thanks everyone. While connecting, i got this error, can anyone tell me why am i getting this error and how to resolve this?

i already had oracle client installed.

Thanks .

Connection with oracle data base

Tue, 26 Apr 2011 13:16:19 GMT

I believe you also need to add the location. Like "c:\...\ord" etc. So not just "ord".

Connection with oracle data base

Tue, 26 Apr 2011 13:32:02 GMT

Hello Mariakesc,

Please make sure the entries in the file TNSNAMES.ORA are ok. It's located in ORACLE_HOME/network/admin/tnsnames.ora

This is an configuration file which the oracle driver uses tot make a connection.

format:

<addressname> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>)) ) (CONNECT_DATA = (SERVICE_NAME = <sid>) )

example:

MYDATABASE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = MYDATABASE) ))

Greetings Kenneth

Connection with oracle data base

Wed, 27 Apr 2011 07:09:54 GMT

the file is placed in that path only. and the driver configuration file is in the same format..

this is how the tsnames file looks like:

ord =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.100.7)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
(SERVER = DEDICATED)
)
)

ind =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.100.7)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ind)
(SERVER = DEDICATED)
)
)

Rep60_SHAKEEL_APP,Rep60_SHAKEEL_APP.world=(ADDRESS=(PROTOCOL=tcp)(HOST=SHAKEEL_APP)(PORT=1949))

any corrections?