Oracle database

Summary

Property	Value
Connector	Oracle database
Products	Excel Power BI (semantic models) Power Query Online
Release state	General availability
Authentication kinds	Windows (desktop/online) Database (desktop) Basic (online) Microsoft account (desktop)
Data source kind	Oracle
Function Reference Documentation	Oracle.Database

Prerequisites

Supported Oracle versions:

• Oracle Database Server 12c (12.1.0.2) and later
• Oracle Autonomous Database - all versions

Starting with the April 2025 version of Power BI Desktop and the May 2025 version of on-premises data gateway, you can use the built-in Oracle managed ODP.NET driver to connect to the Oracle database. When you enable this feature, you don't need to install the OCMT manually. Starting in June 2026, you can also create a cloud connection to Oracle Autonomous Database or other Oracle cloud endpoints without a gateway. Learn more about this feature.

If you don't use the new built-in Oracle driver, you must use an enterprise on-premises data gateway and install the 64-bit Oracle Client for Microsoft Tools (OCMT) on the computer running the gateway or Power BI Desktop. For more information, see Manage your data source - Oracle.

Supported capabilities

• Import
• DirectQuery (Power BI semantic models)
• Advanced options
  • Command timeout in minutes
  • SQL statement
  • Include relationship columns
  • Navigate using full hierarchy

Connect from Power Query Desktop

On-premises database

To make the connection, take the following steps:

1. Select the Oracle database option in the connector selection.

2. Specify the Oracle net service name/TNS alias or Easy Connect (Plus) connection string to connect to in Server. Easy Connect is the simplest to use by setting the Server value to your Oracle Database server Hostname/ServiceName, where ServiceName is the global database name. The following screenshot uses a net service name.

   

3. If you're connecting from Power BI Desktop, select either the Import or DirectQuery data connectivity mode. The rest of these example steps use the Import data connectivity mode. To learn more about DirectQuery, go to Use DirectQuery in Power BI Desktop.

4. If you're connecting to this Oracle database for the first time, select the authentication type you want to use, and then enter your credentials. The authentication types available are:

   • Windows (Windows authentication)
   • Database (Username and password)
   • Microsoft account (Microsoft Entra ID)

   For more information about authentication, go to Authentication with a data source.

   

5. In Navigator, select the data you require, then either select Load to load the data or Transform Data to transform the data.

Connect from Power Query Online

On-premises database

To make the connection, take the following steps:

1. Install and setup an on-premises data gateway.

2. From Power Query Online, select the Oracle database option in the data sources selection.

3. In the Oracle database dialog that appears, specify the Oracle net service name/TNS alias, Easy Connect Plus connection string, or connect descriptor to connect to in Server.

   

4. Provide a Connection name, such as "testoracleserver".

5. Select the name of your on-premises data gateway.

6. If you're connecting to this Oracle database for the first time, select the type of credentials for the connection in Authentication kind. Choose Basic if you plan to sign in with an Oracle username and password. Choose Windows when using Windows operating system authentication and with both the Oracle client and server running on Windows.

7. Enter your credentials.

8. Select Next to continue.

9. In Navigator, select the data you require, then select Transform data to transform the data in Power Query Editor.

Additional information

Using Oracle Client for Microsoft Tools (OCMT) option

Oracle Client for Microsoft Tools installs and configures Oracle Data Provider for .NET (ODP.NET) to support 32-bit and 64-bit Microsoft tool connections with Oracle on-premises and cloud databases, including Oracle Autonomous Database (ADB). OCMT is a graphical installer that automates the Oracle Database Client setup process. It supports connecting with Power BI Desktop, Power BI service, Fabric (Dataflow Gen2), Excel, SQL Server Analysis Services, SQL Server Data Tools, SQL Server Integration Services, SQL Server Reporting Services, and BizTalk Server.

OCMT is free software. It can be downloaded from the Oracle Client for Microsoft Tools page. For 64-bit Power BI Desktop and Power BI service, use 64-bit OCMT. For 32-bit Power BI Desktop, use 32-bit OCMT.

Even if you already have an Oracle Client or ODP.NET installed on your Power BI client, we highly recommend you use the OCMT installer to properly complete all the configuration steps Power BI requires to work with Oracle database.

• OCMT 64-bit and 32-bit downloads
• OCMT installation and setup instructions

Connect to an Oracle Autonomous Database

To connect Power BI to an Oracle Autonomous Database, you need the following accounts and apps:

• An Oracle.com account (Oracle.com Account Signup)
• An Oracle Cloud account (Oracle Cloud Account Signup)
• An Oracle Autonomous Database (Get an Always Free Autonomous Database)
• Power BI Desktop (Get Power BI Desktop) or Power BI service account (Licensing the Power BI service for users in your organization)

Download your client credentials

The first step in setting up a connection to the Oracle Autonomous database is to download your client credentials.

To download your client credentials:

1. In your Oracle Autonomous database details page, select DB Connection.

   

2. From the Database Connection page, select Download Wallet.

   

3. Enter a password you would like to use with this wallet, confirm the password, then select Download.

   

Configure Oracle ADB credentials

1. On your Windows machine, go to the folder where you downloaded your Oracle ADB credentials from Download your client credentials.

2. Unzip the credentials into the directory you specified in OCMT as the Oracle Configuration File Directory. In this example, the credentials are extracted to c:\data\wallet\wallet_contosomart.

   

   Note

   The tnsnames.ora file defines your Oracle Autonomous Database address and connection information.

3. Open sqlnet.ora in an editor, such as Notepad.

4. Under WALLET_LOCATION, change the path to your wallet folder under the Directory option. In this example:

   WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=c:\data\wallet\Wallet_ContosoMart)))

5. Save and close the sqlnet.ora file.

Open the tnsnames.ora file in the wallets folder. The file contains a list of ADB net service names that you can connect to. In this example, the names are contosomart_high, contosomart_low, and contosomart_medium. Your ADB net service names are different.

Connect Power BI Desktop to Oracle ADB

1. Open Power BI Desktop.

2. Select Get Data.

3. From Get Data, select Database > Oracle database.

4. Enter the net service name of the Oracle Autonomous Database server you want to connect to. In this example, the Server is contosomart_high. Then select OK.

   

5. If you're signing in to this server from Power BI Desktop for the first time, you're asked to enter your credentials. Select Database, then enter the user name and password for the Oracle database. The credentials you enter here are the user name and password for the specific Oracle Autonomous Database you want to connect to. In this example, the database's initial administrator user name and password are used. Then select Connect.

   

   Note

   You can use Microsoft Entra ID authentication to sign on to Oracle Autonomous Database via the Microsoft account option.

At this point, the Navigator appears and displays the connection data.

You might also come across one of several errors because the configuration isn't properly set up. These errors are discussed in Troubleshooting.

One error that might occur in this initial test takes place in Navigator, where the database appears to be connected, but contains no data. Instead, an Oracle: ORA-28759: failure to open file error appears in place of the data.

If this error occurs, be sure that the wallet folder path you supplied in sqlnet.ora is the full and correct path to the wallet folder.

Advanced options

Power Query Desktop and Power Query Online provide a set of advanced options that you can add to your query if needed.

The following table lists all of the advanced options you can set in Power Query Desktop and Power Query Online.

Once you select the advanced options you require, select OK in Power Query Desktop or Next in Power Query Online to connect to your Oracle database.

Use Microsoft Entra ID single sign-on for Oracle cloud connections

The Power BI service supports Microsoft Entra ID single sign-on (SSO) for direct cloud connections to Oracle databases.

To use cloud SSO, a Fabric administrator must turn on the Enable Oracle SSO tenant setting in the admin portal. Configure your Oracle environment to accept Microsoft Entra ID tokens. For more information, see Use Microsoft Entra ID with Oracle.

Use the built-in Oracle driver

For Import Mode in Power BI Desktop

For the June 2026 version of Power BI Desktop or on-premises data gateway, the default is to use a built-in Oracle managed ODP.NET driver for connectivity. This feature removes the necessity for users to install and manage the driver. You can still opt out of this feature by using the following instructions.

To not use this built-in driver in Power BI Desktop, go to Options and settings (under the File tab) > Options > Preview features, and then clear the checkbox to enable the Enable using bundled Oracle Managed ODP Provider for Import Mode option.

For DirectQuery mode in Power BI Desktop (Preview)

For DirectQuery mode, using the built-in Oracle managed ODP.NET driver is still in preview. To opt in, use the following instructions.

To use this built-in driver in Power BI Desktop, go to Options and settings (under the File tab) > Options > Preview features, and then clear the checkbox to enable the Enable using bundled Oracle Managed ODP Provider for DirectQuery Mode option.

For Import mode in on-premises gateway

Starting with the June 2026 version, the on-premises data gateway uses the built-in Oracle managed ODP.NET driver for connectivity by default. To not use this built-in driver in the on-premises data gateway for Import mode, change the gateway configurations to update the MashupFlight_DisableOracleBundledOdacProviderV2 setting by using the following steps:

1. On the local machine where the on-premises data gateway is installed, navigate to C:\Program Files\On-premises data gateway.
2. Make a backup of the configuration file named Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config.
3. Open the original Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config configuration file and locate the MashupFlight_DisableOracleBundledOdacProviderV2 entry.
4. Update the MashupFlight_DisableOracleBundledOdacProviderV2 value to True.
5. Restart your gateway.

<Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayCoreSettings>
   ...
   <setting name="MashupFlight_DisableOracleBundledOdacProviderV2" serializeAs="String">
      <value>True</value>
   </setting>
   ...
</Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayCoreSettings>    

For DirectQuery mode in on-premises gateway (Preview)

The on-premises data gateway can use the built-in Oracle managed ODP.NET driver for DirectQuery. This feature is in preview.

Configure FetchSize for performance

The built-in Oracle driver uses the following default FetchSize values:

For gateway DirectQuery, the November 2026 gateway release is planned to increase the default FetchSize to 8 MB and support the same ODAC.config configuration as Import mode.

Starting with the October 2026 gateway release, you can configure FetchSize for Import mode in ODAC.config. The file is in the ADO.NET Providers subfolder of the gateway installation folder.

To configure FetchSize:

1. Back up ODAC.config, and then open the file in a text editor.

2. Add or update the FetchSize entry under oracle.manageddataaccess.client > version > settings. Specify the value in bytes. The following example sets FetchSize to 16,777,216 bytes (16 MB):

   <configuration>
     <oracle.manageddataaccess.client>
       <version number="*">
         <settings>
           <setting name="FetchSize" value="16777216" />
         </settings>
       </version>
     </oracle.manageddataaccess.client>
   </configuration>
   

   Merge the setting into your existing configuration. Keep other settings, such as TNS_ADMIN.

3. Save ODAC.config, and then restart the on-premises data gateway service.

Gateway upgrades overwrite ODAC.config. Keep a copy of your configuration changes so you can reapply them after an upgrade.

Supported ways to specify TNS_ADMIN with the built-in Oracle driver

There are three options to specify TNS_ADMIN:

• Configure the TNS_ADMIN in OADC.config
• Use an environment variable
• Use settings from a previously installed OADC

Configure TNS_ADMIN in ODAC.config

If you are using the Built-in Oracle Driver and have access to modify program files, you can specify TNS_ADMIN in configuration file ODAC.config used by Built-in Oracle Driver. The file is located in Power BI Desktop or on-premises data gateway installed location under “ADO.NET Providers” subfolder. Following is an example of specifying TNS_ADMIN path that is "C:\network\admin".

This config value takes precedence over other options.

Example configuration

<configuration>
 ...
 <oracle.manageddataaccess.client>
  <version number="*">
   <settings>
    <setting name="TNS_ADMIN" value="C:\network\admin" />
   </settings>
  </version>
 </oracle.manageddataaccess.client>
 ...
</configuration>

Note that TNS_ADMIN configured in the ODAC.config takes precedence over a TNS_ADMIN setting in the environment.

Use an environment variable

If the user does not have access to modify ODAC.config, they can specify TNS_ADMIN by adding a TNS_ADMIN environment variable. The environment variable can be a system environment variable or a user environment variable for the user running Power BI Desktop or the On-premises Data Gateway. Set the TNS_ADMIN value to "C:\network\admin", for example.

Setting TNS_ADMIN in the environment (especially at the system level) can cause other applications running on this machine to pick up the network configuration files (such as tnsnames.ora) from the location pointed to by TNS_ADMIN.

Use settings from a previously installed ODAC

If ODAC is installed previously and TNS_ADMIN is configured, Power BI will automatically discover TNS_ADMIN.

The remaining configurations to connect to an Oracle database from Power Query Desktop are the same as described in the previous sections.

Allowed property list

Users can specify Oracle server names by using connect descriptors such as (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service_name))). The system enforces which properties can be used in Desktop and Gateway. The default allowed properties in ODAC.config are listed in the following section.

<configuration>
 ...
  <AllowLists>
    <!--
      Oracle connection strings can contain connection properties, for example  CONNECT_TIMEOUT.   Only connection properties explicitly set to true in the list below will
      be allowed with Power BI Desktop and the On Premises Data Gateway. Using our example, you can add "CONNECT_TIMEOUT":true to the list to enable it. To disable it you
      can remove it from the list, or set the value to false. This list will be enforced on all connection string types including connect descriptors and aliases
      referencing a tnsnames.ora entry. The use of any disallowed property in a connection string will result in an ORA-50122 error. Restart Power BI Desktop or the On-premises
      Data Gateway service after applying ODAC.config changes.
      For more information see https://docs.oracle.com/en/database/oracle/oracle-database/26/odpnt/InstallConnectionConfigurationRestriction.html
    -->
    <OnPremAllowList>{"DataSource":{"HOST":true,"PORT":true,"PROTOCOL":true,"HTTPS_PROXY":true,"HTTPS_PROXY_PORT":true,"ENABLE":true,"EXPIRE_TIME":true,"FAILOVER":true,"LOAD_BALANCE":true,"RECV_BUF_SIZE":true,"SDU":true,"SEND_BUF_SIZE":true,"SOURCE_ROUTE":true,"TYPE_OF_SERVICE":true,"COLOCATION_TAG":true,"CONNECTION_ID_PREFIX":true,"FAILOVER_MODE":true,"GLOBAL_NAME":true,"HS":true,"INSTANCE_NAME":true,"POOL_BOUNDARY":true,"POOL_CONNECTION_CLASS":true,"POOL_NAME":true,"POOL_PURITY":true,"RDB_DATABASE":true,"SHARDING_KEY":true,"SHARDING_KEY_ID":true,"SUPER_SHARDING_KEY":true,"SERVER":true,"SERVICE_NAME":true,"SID":true,"TUNNEL_SERVICE_NAME":true,"SSL_CLIENT_AUTHENTICATION":true,"SSL_CERTIFICATE_ALIAS":true,"SSL_CERTIFICATE_THUMBPRINT":true,"SSL_VERSION":true,"SSL_SERVER_DN_MATCH":true,"SSL_SERVER_CERT_DN":true,"WALLET_LOCATION":true,"CONNECT_TIMEOUT":true,"RETRY_COUNT":true,"RETRY_DELAY":true,"TRANSPORT_CONNECT_TIMEOUT":true,"RECV_TIMEOUT":true,"COMPRESSION":true,"COMPRESSION_LEVELS":true,"USE_SNI":true,"AUTHENTICATION_SERVICE":true,"IGNORE_ANO_ENCRYPTION_FOR_TCPS":true,"OCI_CONFIG_FILE":true,"OCI_DATABASE":true,"OCI_IAM_URL":true,"OCI_PROFILE":true,"OCI_TENANCY":true,"PASSWORD_AUTH":true,"REDIRECT_URI":true,"TENANT_ID":true,"TLS_VERSION":true,"TOKEN_AUTH":true,"TOKEN_LOCATION":true}}
    </OnPremAllowList>
  </AllowLists>
 ...
</configuration>

Users can modify this allowed property list if they need to add new properties or remove properties from the list to enforce stricter rules.

Limitations and considerations

Power BI sessions can still be active on your Oracle database for approximately 30 minutes after a semantic model refresh to that Oracle database. Only after approximately 30 minutes do those sessions become inactive/removed on the Oracle database. This behavior is by design.

Troubleshooting

You might come across any of several errors from Oracle when the naming syntax is either incorrect or not configured properly:

• ORA-12154: TNS: could not resolve the connect identifier specified.
• ORA-12514: TNS: listener does not currently know of service requested in connect descriptor.
• ORA-12541: TNS: no listener.
• ORA-12170: TNS: connect timeout occurred.
• ORA-12504: TNS: listener was not given the SERVICE_NAME in CONNECT_DATA.

These errors might occur if the Oracle tnsnames.ora database connect descriptor is misconfigured, the net service name provided is misspelled, or the Oracle database listener isn't running or not reachable, such as a firewall blocking the listener or database port. Be sure you're meeting the minimum installation prerequisites. More information: Prerequisites

Visit the Oracle Database Error Help Portal to review common causes and resolutions for the specific Oracle error you encounter. Enter your Oracle error in the portal search bar.

If you downloaded Power BI Desktop from the Microsoft Store, you might be unable to connect to Oracle databases because of an Oracle driver issue. If you encounter this issue, the error message returned is: Object reference not set. To address the issue, download Power BI Desktop from the Download Center instead of Microsoft Store.

If the Object reference not set error message occurs in Power BI when you connect to an Oracle database by using the on-premises data gateway, follow the instructions in Manage your data source - Oracle.

If you're using Power BI Report Server, consult the guidance in the Oracle Connection Type article.

Known issues with the built-in Oracle driver

The following known issues are specific to the built-in Oracle managed ODP.NET driver. For configuration options, see Use the built-in Oracle driver.

TNS aliases on a network share aren't resolved

Applies to the on-premises data gateway, in both Import and DirectQuery.

When your Oracle network configuration files (tnsnames.ora or the TNS_ADMIN directory) are stored on a network share (UNC path), the built-in driver consistently fails to resolve the net service name (TNS alias). You might see ORA-12154: TNS:could not resolve the connect identifier specified or ORA-50201: Failed to connect to server. The same alias resolves correctly with the ODP.NET unmanaged driver, or when the files are on a local path. The built-in managed driver doesn't reliably read Oracle network configuration files from a UNC location.

Use one of the following workarounds:

• Copy tnsnames.ora (and sqlnet.ora if you use it) to a folder on a local drive, and then point the driver at that folder. See Supported ways to specify TNS_ADMIN with the built-in Oracle driver. For gateway DirectQuery, set TNS_ADMIN at the system (machine) scope, because the gateway service account can't read user-scoped variables, and then restart the gateway service.
• Ensure that the service account running the gateway has access to the network share.
• Enter an Easy Connect descriptor directly in Server (for example, host:port/service_name) instead of a tnsnames.ora alias, so no network configuration file is needed.
• Turn off the built-in driver to fall back to the ODP.NET unmanaged driver, which can read the files from the share.

NLS_LANG is ignored

Applies to Power BI Desktop, the on-premises data gateway, and Power BI service cloud connections, in both Import and DirectQuery.

With the built-in driver, Oracle session language, territory, and number and date formats follow the machine's .NET culture (Windows regional and format settings), not the NLS_LANG environment variable. Setting or changing NLS_LANG has no effect. Depending on the machine's region, this behavior can cause numbers or dates to parse with the wrong separators or date order, queries to return zero rows or incorrect values, or errors such as ORA-01722, ORA-01858, ORA-01861, or ORA-01843. For example, a report authored on a machine set to English (United States) might return zero rows or errors on a machine set to English (United Kingdom). The managed ODP.NET driver derives its National Language Support (NLS) settings from the .NET thread culture rather than from NLS_LANG, unlike the ODP.NET unmanaged driver, which honors NLS_LANG.

Use one of the following workarounds:

• Match the machine's regional settings to the locale that your data and queries were designed for.
• Turn off the built-in driver to fall back to the ODP.NET unmanaged driver, which honors NLS_LANG.

For background on the managed driver's globalization behavior, see Oracle's Globalization Support documentation.

Related content

Optimize Power Query when expanding table columns