Errors when connecting oracle DB with Power BI

Ahmad Saleh picture Ahmad Saleh · Jul 1, 2018 · Viewed 14.4k times · Source

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

enter image description here

then adding Database user and password enter image description here

and I get this error enter image description here

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

also I am using Power Bi 64 bit on enter image description here also I am on windows 10 64 bit enter image description here

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

enter image description here

where I have added my database to DSN using both "OraDB12Home1" and "OraClient12Home2" enter image description here

and also for both DSN I have tested the connection and it was successful enter image description here

  • Next I test connecting using ODBC OraClient12Home2:

enter image description here

then inserting username and password for database enter image description here

then I get this Error enter image description here

and when I connect with OraDB12Home1 I get this error: enter image description here

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 enter image description here

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:

enter image description here

enter image description here

enter image description here

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

Answer

Ahmad Saleh picture Ahmad Saleh · Jul 31, 2018

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:

enter image description here

enter image description here

enter image description here

enter image description here

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

next on Power BI select get source:

  • select ODBC

enter image description here

  • select the DSN name that you provided above

enter image description here

  • 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 enter image description here

  • then it'll display the table data preview, click load enter image description here

  • congratulation, you are done enter image description here

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

enter image description here

  • 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

enter image description here

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