cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmiridium
Helper IV
Helper IV

Connect To Oracle Database

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:

oracleissue.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

oracleissue2.JPG

 

When I connect I get the following error:

 

oracleissue3.JPG

1 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

View solution in original post

26 REPLIES 26
sophiexu
Frequent Visitor

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
New Member

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
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

This made me laugh.

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

Did all the steps get a different failure:

 

oracle4.JPG

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

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)
)
)

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)
)
)

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

 

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:

 

oracle1.JPGoracle2.JPG

 

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

 

oracle3.JPGoracle4.JPGoracle5.JPG

 

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

 

oracle6.JPG

dmvega
Frequent Visitor

Great Post... thanks a lot!!!

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

 

that's what I get after the ODBC steps

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

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. 

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

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....

Try IP address?

IP address in HOST.

 

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

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"

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors