Oracle.Database

Accessing Data

Returns a navigation table of tables and views from an Oracle database.

Examples on this page use shared sample tables. View them to understand the input data before reading the examples below.

Syntax

Oracle.Database(server as text, optional options as nullable record) as table

Parameters

NameTypeRequiredDescription
servertextYesThe Oracle server connection string. Can be a TNS alias, an Easy Connect string (e.g., "myserver/ORCL" or "myserver:1521/ORCL"), or a full TNS descriptor.
optionsrecordNoAn optional record with connection options such as CommandTimeout, CreateNavigationProperties, HierarchicalNavigation, and Query.

Return Value

table — A navigation table of tables, views, and other objects from the specified Oracle database.

Remarks

Oracle.Database connects to an Oracle Database instance and returns a navigation table of tables, views, and other database objects visible to the authenticated user. Objects are organized by schema (Oracle database user), and you navigate by schema name and object name to access the data.

Driver requirements: Power BI Desktop bundles an Oracle Data Provider for .NET (ODP.NET) managed driver, which works for most configurations without requiring a separate Oracle client installation. However, for connecting to older Oracle servers or using TNS aliases that require a tnsnames.ora file, you may need to install Oracle Data Access Components (ODAC) on the machine. For 32-bit Excel, you must install the 32-bit Oracle client separately. Ensure the installed client bitness matches the Power Query host: 64-bit for Power BI Desktop, 32-bit or 64-bit for Excel depending on version.

Server string formats: The server parameter accepts several formats: - Easy Connect: "myserver/ORCL" (host/service name, assumes default port 1521) or "myserver:1521/ORCL" (explicit port). - TNS alias: "PROD_DB" — requires a tnsnames.ora file configured on the client machine that resolves the alias. - Full TNS descriptor: "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))" — no tnsnames.ora required.

Authentication: Database username and password authentication is most common. Configure credentials in the Power Query data source credentials dialog — do not embed credentials in the M query. Windows-integrated authentication (Kerberos) is supported when the Oracle server is configured for it (sqlnet.authentication_services = (NTS)).

Query folding: Oracle supports full query folding. Filters, projections, sorting, joins, groupings, and aggregations are translated into Oracle SQL and executed on the server.

Key options: - CommandTimeout (duration) — maximum time to wait for a query to complete. - CreateNavigationProperties (logical) — generate navigation properties based on Oracle foreign key constraints. - HierarchicalNavigation (logical) — group the navigation table by schema owner. - Query (text) — execute a native Oracle SQL query directly instead of navigating to a table.

Power BI Service: Oracle is not supported for direct cloud refresh. An on-premises data gateway with the Oracle client installed is required for scheduled refresh.

Examples

Example 1: Connect using Easy Connect syntax

```powerquery

Oracle.Database("myserver/ORCL")

Example 2: Connect with explicit host, port, and service name

```powerquery

Oracle.Database("myserver:1521/ORCL")

Example 3: Connect using a TNS alias

```powerquery

Oracle.Database("PROD_DB")

Example 4: Navigate to a specific table in a named schema

```powerquery

let
    Source = Oracle.Database("myserver/ORCL"),
    SalesOrders = Source{[Schema="SALES", Item="ORDERS"]}[Data]
in
    SalesOrders

Example 5: Run a native Oracle SQL query using the Query option

```powerquery

Oracle.Database(
    "myserver/ORCL",
    [Query = "SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE FROM SALES.ORDERS WHERE ROWNUM <= 1000"]
)

Related Functions

Compatibility

✓ Power BI Desktop✗ Power BI Service✓ Excel Desktop✗ Excel Online✓ Dataflows✗ Fabric Notebooks