How to check the user role in Oracle

Establishing a connection between an Oracle database and Power BI Desktop

  • 5 minutes to read

To connect to an Oracle database, you must have the correct Oracle client software installed on the computer running Power BI Desktop. The Oracle client software you use depends on which version of Power BI Desktop you have installed: 32-bit or 64-bit. Your version of Oracle Server is also critical.

Supported Oracle versions:

  • Oracle Server 9 and higher
  • Oracle Data Access Client (ODAC) 11.2 and higher

Before you can connect to an Oracle database using Power BI, you must install the Oracle client software, version 8.1.7 or later, on your computer. To install the 32-bit version of the Oracle client software, go to 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (12.1.0.2.4) (in English). To install the 64-bit version of the Oracle client, switch to 64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64 (in English).

If you want to configure an Oracle database for Power BI Desktop, an on-premises gateway, or the Power BI report server, see the Oracle connection type article for more information.

Find out which version of Power BI Desktop is installed

To find out which version of Power BI Desktop is installed, select in the ribbon Help the option info and then check the line version. In the following illustration, a 64-bit version of Power BI Desktop is installed:

Install the Oracle client

  • Download and install the 32-bit Oracle client for the 32-bit version of Power BI Desktop.

  • Download and install the 64-bit Oracle client for the 64-bit version of Power BI Desktop.

Note

Select a version of Oracle Data Access Client (ODAC) that is compatible with your Oracle server. For example, ODAC 12.x does not always support version 9 of Oracle Server. Select the Windows installer of the Oracle client. When setting up the Oracle client, make sure you have Configure ODP.NET and / or Oracle Providers for ASP.NET at machine-wide level by checking the appropriate box in the setup wizard. Some versions of the Oracle Client Wizard select the check box by default, while others do not. Make sure the box is checked so that Power BI can connect to your Oracle database.

Connect to an Oracle database

After installing the appropriate Oracle client driver, you can connect to an Oracle database. To connect to an Oracle database through the local data gateway, the correct Oracle client software must be installed on the computer running the gateway. The Oracle client software used depends on the version of the Oracle server, but always corresponds to that of the 64-bit gateway. For more information, see Managing the Data Source - Oracle.

Supported functions

  • import
  • DirectQuery
  • Expanded options
    • Command timeout in minutes
    • SQL statement
    • Include relationship columns
    • Navigate using the full hierarchy

To establish the connection, do the following:

  1. Select in the ribbon begin the option Call up data out.

  2. In the window that appears, select Call up data the option More off (if necessary), then Database > Oracle database, and subsequently Connect.

  3. In the dialog box that appears, type Oracle database the name of the Servers and choose OK out. If a SID is required, set it using the following format: Server name / SID, in which SID corresponds to the unique name of the database. If the format Server name / SID not working, use Server name / service name, in which Service name is the alias used when making the connection.

    Note

    If you are using a local database or stand-alone database connections, you may need to enclose the server name in quotation marks to avoid connection errors.

  4. Select the data connectivity mode Import or DirectQuery out. In the remainder of these example steps, the data connectivity mode "Import" will be used. For more information about DirectQuery, see Use DirectQuery in Power BI Desktop.

  5. If you want to import data using a native database query, add your query in the field SQL statement a. This will be shown when you click in the dialog box Oracle database the Advanced options fade in. Power BI Desktop does not support native Oracle queries that execute a stored procedure, and native Oracle queries in BEGIN ... END blocks do not return result sets.

  6. After you have the required information in the dialog box Oracle database (including any optional information such as SID or native database query), select OK to establish the connection.

  7. If you require credentials to log in to the Oracle database, enter them in the dialog box when prompted.

Troubleshooting

Various errors can occur in Oracle when the name syntax is either incorrect or not configured properly:

  • ORA-12154: TNS: could not resolve the connect identifier specified. The specified connection identifier could not be resolved.
  • ORA-12514: TNS: listener does not currently know of service requested in connect descriptor. (Listener cannot find the service requested in the connection description at the moment.)
  • ORA-12541: TNS: no listener
  • ORA-12170: TNS: Connect timeout occurred. A connection time-out has occurred.
  • ORA-12504: TNS: listener was not given the SERVICE_NAME in CONNECT_DATA. (SERVICE_NAME in CONNECT_DATA was not passed to the listener.)

These errors can occur if the Oracle client has not been properly installed or configured. Once the client is installed, verify that the tnsnames.ora file is configured correctly and that the net_service_name is the correct value. Also, make sure that the net_service_name value on the computer running Power BI Desktop and the computer with the gateway match. For more information, see Installing the Oracle Client.

In addition, there may be compatibility problems between the version of the Oracle server and that of the Oracle Data Access Client. As a rule, these versions should be coordinated with one another, as some combinations are not compatible. For example, ODAC 12.x does not support version 9 of Oracle Server.

If you downloaded Power BI Desktop from the Microsoft Store, you may not be able to connect to Oracle databases due to an issue with the Oracle driver. When this problem occurs, the following error message is returned: Object reference not set (The object reference is not set). This issue affects the way the Oracle driver works in Windows UWP apps. To fix the problem, do the following:

  • Download Power BI Desktop from the Download Center instead of the Microsoft Store.

If you get the error message in Power BI Gateway when connecting to an Oracle database Object reference not set (The object reference is not set) is displayed, follow the instructions in the Manage the data source - Oracle article.

If you want to use the Power BI Report Server, see the article Oracle Connection Type ((Report Builder and Power BI Report Server) | Microsoft Documentation) for instructions.