Multi tool use

Errors when connecting oracle DB with Power BI

I am trying to use Power BI to connect with Oracle 12c Data source, I have looked for multiple solutions and followed the instruction on Microsoft documentation

https://docs.microsoft.com/en-us/power-bi/desktop-connect-oracle-database#installing-the-oracle-client

But I think there is something missing in work .

where I am installing fresh new Oracle and powerbi on the same laptop where server should be in this format "ServerName/SID" as described on documentation, so mine will be:

localhost/testdb

then adding Database user and password

and I get this error

I have just installed Oracle 12c 64 bit on my laptop to test the connection

also I am using Power Bi 64 bit on

also I am on windows 10 64 bit

I have also installed "64-bit Oracle Data Access Components (ODAC)" from Oracle website:
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

where I have added my database to DSN using both "OraDB12Home1" and "OraClient12Home2"

and also for both DSN I have tested the connection and it was successful

then inserting username and password for database

then I get this Error

and when I connect with OraDB12Home1 I get this error:

I have also clear all data source connections on power bi because sometime it just display the error without sending me to next screen to enter user and password

These are the details of tnsnames.ora :

# tnsnames.ora Network Configuration File:
C:appAhmadssbvirtualproduct12.2.0dbhome_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_TESTDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.babader.com)
)
)


I don't know what am I missing I even have exported the data from oracle as dump file and convert it as sql but it seems like this is not supported now on power bit and this is another situation.

I need to get my database data into PowerBi the data what, am I missing and what should I do to make it connect successfully?

as suggested on comments to change localhost/testdb to only testdb (also tested in capital TESTDB) since it should be typed on server. But this also didn't work with me and still getting the same error check the following pictures:

it seems like the connection to oracle is not working, is there something I should do in oracle to make this work?

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1512)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=testdb)));

TESTDB

1 Answer
1

It follows the same connection architecture as SQL Plus, so the Server field in Power BI should contain whatever follows the @ sign in your successful SQL Plus test.

So I would try TESTDB in the Server field when making an Oracle connection from Power BI, not localhost/testdb. If that works I presume you could ignore ODBC.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.