Connect To Oracle Database

[jmiridium]

I need a connection solution for Oracle. This shouldn't be this difficult. I am trying to connec to an Oracle server and get the following:

However, the Oracle client is installed. I try to connect:

When I connect I get the following error:

[sophiexu]

Get data/ oracle database, then use this string to server: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)))

see https://blogs.solidq.com/en/businessanalytics/get-data-in-power-bi-from-oracle-database/. succeed to connect to oracle now.

[ketakibavbande]

Hello,

I am new to learning Power BI. I need to connect Oracle Cloud Database to Power Bi.Can anyone pls guide me steps as how should i bring data to power bi and create reports. I have only found that this can be done using API's. Hence, i connected power Bi with Powershell and using those cmdlets i have login into power bi. Am i on the right path?What should i do further to connect with Oracle Cloud database

[Greg_Deckler]

I'm not an Oracle guy but my impression from being around it is that the words "Oracle" and "not difficult" do not belong in the same sentence. 🙂

Perhaps this will help? http://www.dbasupport.com/forums/showthread.php?61511-ORA-12504-TNS-listener-was-not-given-the-SERVI...

One other thing, do the bits match between Power BI Desktop and Oracle Client?

[sqldusty]

This made me laugh.

[dniedrauer]

It ended up being a firewall issue. Everything was done correctly. 

[jmiridium]

Did all the steps get a different failure:

[jmiridium · Accepted solution]

Figured it out. After finding the path for the .ora file I entered the information in the file. Then in PowerBI I added the connection name from the .ora file to the connection string

[JimKingPowerBI]

What? Huh?

So you identified where the TNSNames.ora file was after you installed the Oracle client, right?

What do you mean by "I entered the information in the file"? What information? What file?

What do you mean by "I added the connection name from the .ora file to the connection string"? What connection name? What connection string?

Here's a TNSNames.ora entry:

XXXP4J,
XXXP4J.ORACLEOUTSOURCING.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXodmcp1234.oracleoutsourcing.com )(PORT = 5010))
)
(CONNECT_DATA =
(INSTANCE_NAME = XXXP4J)
(SERVICE_NAME = XXXP4J.ORACLEOUTSOURCING.COM)
)
)

[JimKingPowerBI]

What? Huh?

So you identified where the TNSNames.ora file was after you installed the Oracle client, right?

What do you mean by "I entered the information in the file"? What information? What file?

What do you mean by "I added the connection name from the .ora file to the connection string"? What connection name? What connection string?

Here's a TNSNames.ora entry:

XXXP4J,
XXXP4J.ORACLEOUTSOURCING.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXodmcp1234.oracleoutsourcing.com )(PORT = 5010))
)
(CONNECT_DATA =
(INSTANCE_NAME = XXXP4J)
(SERVICE_NAME = XXXP4J.ORACLEOUTSOURCING.COM)
)
)

[Brindha]

I have the same error Details: "Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"

and i couldn't Fix it . I am New to power BI . Can you please tell me how to fix it

[jmiridium]

OK what you do is install the 12c client. When it is installing make sure you are aware of the installation paths. You will need to modify things later.

Install the client then edit with admin rights the file tnsnames.ora. You will need to add the following connection:

YOURSERVERDESC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ServerName or IP Address)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SERVICENAME)
    )
  )

Once you do that test this connection with your ODBC data source. Use admin rights to add a connection:

The Oracle ODBC Driver Configuration will come up. Put in your connection information and click Test Conection:

Once you have verified via ODBC the same connection should work:

[dmvega]

Great Post... thanks a lot!!!

[dniedrauer]

ORA-12154: TNS:could not resolve the connect identifier specified 

that's what I get after the ODBC steps

[jmiridium]

You edit the ora file then try to connect what you named the identifier as?

[dniedrauer]

Not sure, but here's everything in tnsnames.ora: 

BI_PRICE_USER1 XXX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lxx2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxxx.company.com)))

Does "identifier" = "service name" or does "identifer" refer to something else in the tnsnames.ora file? 

FYI - on Excel connect to Oracle, I get a timeout error instead. 

[jmiridium]

So you're connecting using this?

BI_PRICE_USER

In my case I'm using servicename.domain.xxx so I connect to servicename.domain.xxx

[dniedrauer]

BI_PRICE_USER is the "connection name" in the info given. 

So should I have something else there instead like xxxx.company.com? 

The server name goes at the beginning? Should my tnsnames.ora file look like:? 

du1p.company.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lira2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = du1p)))

In either case, i get the exact same error....

[jmiridium]

Try IP address?

[jmiridium]

IP address in HOST.

I have created serveral conections to different Oracle databases using this connection method without issue

[dniedrauer]

where would I find the Oracle DB ip address? 

I get access denied when I try to query it in sql developer. 

I had the old DB admin ping it as xx.xx.x.x a few months ago before turnover and turmoil turned everything into a mess where it's not even clear if there is a DB admin anymore. 

Now the error is: 

Details: "Oracle: ORA-12170: TNS:Connect timeout occurred"

tnsnames.ora file is now: 

du1p.company.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ipaddress)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = du1p.company.com)))

When Power BI asks for a "server", I tried typing in: 

(1) the ip address

(2) du1p.company.com

(3) du1p 

In each case, the error was: 

Details: "Oracle: ORA-12170: TNS:Connect timeout occurred"