Errors when connecting oracle DB with Power BI

Question

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 .

• First trying to connect via Oracle Database:

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

• Next I test connecting using ODBC OraClient12Home2:

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:\app\Ahmadssb\virtual\product\12.2.0\dbhome_1\network\admin\tnsnames.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?

---

• Update: trying testdb on Server field:

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?

Solution 1

I have formatted windows, reinstall everything from beginning and connected via ODBC and it works (yet I get the normal error DataSource.Error: ODBC: ERROR [07006] [Oracle][ODBC]Restricted data type attribute violation)

but with select query for each table needed, I get it work

• downloaded Oracle Database 12c Release 2

• install normally

• from start menu -> Windows Administrative Tools > ODBC Data Sources (64-bit)

• in user DSN tap, follow the next pictures:

since connection was successful, then click "OK" to save it

next on Power BI select get source:

• select ODBC

• select the DSN name that you provided above

• now don't click on OK because it may give you an error I'll explain it below.
• click on advanced option

• type a SELECT query for specific table and click OK

• then it'll display the table data preview, click load

• congratulation, you are done

in case you didn't type a select query and clicked OK then you added your username and password

• it'll connect and display list of tables, but you may end up with this error (the provided solution on the internet was the above one ):

https://community.powerbi.com/t5/Integrations-with-Files-and/ODBC-Connection-error-07006/td-p/278165

that's how i get it to connect finally, still i have small issue not related to this topic I put it on another question, but hopefully this answer will help others to connect

Solution 2

I was finally able to get PowerBI to work with the Oracle driver by uninstalling the PowerBI from the Microsoft Store and downloading the msi file from the advanced download options on https://powerbi.microsoft.com/en-us/desktop/. I suspect it's some security restriction with a store app that prevents using the Oracle libraries.