使用 Power BI Desktop 連線至 Oracle 資料庫Connect to an Oracle database with Power BI Desktop

若要使用 Power BI Desktop 連接到 Oracle 資料庫,則執行 Power BI Desktop 的電腦上必須安裝正確的 Oracle 用戶端軟體。To connect to an Oracle database with Power BI Desktop, the correct Oracle client software must be installed on the computer running Power BI Desktop. 您使用的 Oracle 用戶端軟體取決於已安裝的 Power BI Desktop 版本:32 位元或 64 位元。The Oracle client software you use depends on which version of Power BI Desktop you've installed: 32-bit or 64-bit. 其也取決於 Oracle Server 版本。It also depends on your version of Oracle server.

支援的 Oracle 版本:Supported Oracle versions:

  • Oracle Server 9 和更新版本Oracle Server 9 and later
  • Oracle 資料存取用戶端 (ODAC) 軟體 11.2 和更新版本Oracle Data Access Client (ODAC) software 11.2 and later

注意

如果要設定適用於 Power BI Desktop、內部部署的資料閘道或 Power BI 報表伺服器的 Oracle 資料庫,請參閱 Oracle 連線類型一文中的資訊。If you're configuring an Oracle database for Power BI Desktop, On Premises Data Gateway or Power BI Report Server, consult the information in the Oracle Connection Type article.

判斷您已安裝的 Power BI Desktop 版本Determining which version of Power BI Desktop is installed

若要判斷您已安裝的 Power BI Desktop 版本,請選取 [檔案] > [說明] > [關於],然後檢查 [版本] 行。To determine which version of Power BI Desktop is installed, select File > Help > About, then check the Version line. 在下圖中,已安裝 64 位元版本的 Power BI Desktop:In the following image, a 64-bit version of Power BI Desktop is installed:

Power BI Desktop 版本

安裝 Oracle 用戶端Install the Oracle client

注意

選擇與 Oracle Server 相容的 Oracle 資料存取用戶端 (ODAC) 版本。Choose a version of Oracle Data Access Client (ODAC) which is compatible with your Oracle Server. 例如,ODAC 12.x 並不一定支援 Oracle Server 第 9 版。For instance, ODAC 12.x does not always support Oracle Server version 9. 選擇 Oracle 用戶端的 Windows Installer。Choose the Windows installer of the Oracle Client. 在設定 Oracle 用戶端期間,請務必在安裝精靈期間選取對應的核取方塊,以啟用「在全機器層級設定適用於 ASP.NET 的 ODP.NET 和/或 Oracle 提供者」。During the setup of the Oracle client, make sure you enable Configure ODP.NET and/or Oracle Providers for ASP.NET at machine-wide level by selecting the corresponding checkbox during the setup wizard. 有些 Oracle 用戶端精靈版本根據預設會選取核取方塊,其他則否。Some versions of the Oracle client wizard selects the checkbox by default, others do not. 請務必確認已選取核取方塊,讓 Power BI 能夠連線到 Oracle 資料庫。Make sure that checkbox is selected so that Power BI can connect to your Oracle database.

連接到 Oracle 資料庫Connect to an Oracle database

安裝相符的 Oracle 用戶端驅動程式之後,您可以連接到 Oracle 資料庫。After you install the matching Oracle client driver, you can connect to an Oracle database. 請採取下列步驟建立連線:To make the connection, take the following steps:

  1. 從 [首頁] 索引標籤,選取 [取得資料]。From the Home tab, select Get Data.

  2. 從顯示的 [取得資料] 視窗,依序選取 [更多] (如有必要) 和 [資料庫] > [Oracle 資料庫],然後選取 [連接]。From the Get Data window that appears, select More (if necessary), select Database > Oracle database, and then select Connect.

    Oracle 資料庫連接

  3. 在顯示的 [Oracle 資料庫] 對話方塊中,提供 [伺服器] 的名稱,然後選取 [確定]。In the Oracle database dialog that appears, provide the name of the Server, and select OK. 如果需要 SID,請使用以下格式加以指定:ServerName/SID,其中 SID 是資料庫的唯一名稱。If a SID is required, specify it by using the format: ServerName/SID, where SID is the unique name of the database. 如果 ServerName/SID 格式沒有用,請使用 ServerName/ServiceName,其中 ServiceName 是用來連接的別名。If the ServerName/SID format doesn't work, use ServerName/ServiceName, where ServiceName is the alias you use to connect.

    輸入 Oracle 伺服器名稱

  4. 如果您想要使用原生資料庫查詢來匯入資料,請將您的查詢放在展開 [Oracle 資料庫] 對話方塊的 [進階選項] 區段時所顯示 [SQL 陳述式] 方塊中。If you want to import data by using a native database query, put your query in the SQL statement box, which appears when you expand the Advanced options section of the Oracle database dialog.

    展開 [進階選項]

  5. 在 [Oracle 資料庫] 對話方塊中輸入 Oracle 資料庫資訊之後 (包括任何選擇性資訊,例如 SID 或原生資料庫查詢),請選取 [確定] 進行連接。After you've entered your Oracle database information in the Oracle database dialog (including any optional information such as a SID or a native database query), select OK to connect.

  6. 如果 Oracle 資料庫需要資料庫使用者認證,出現提示時,請在對話方塊中輸入這些認證。If the Oracle database requires database user credentials, input those credentials in the dialog when prompted.

疑難排解Troubleshooting

當命名語法不正確或未正確設定時,可能會遇到來自 Oracle 的下列任一種錯誤:You might encounter any of several errors from Oracle when the naming syntax is either incorrect or not configured properly:

  • ORA-12154:TNS:無法解析指定的連接識別碼。ORA-12154: TNS:could not resolve the connect identifier specified.
  • ORA-12514:TNS:接聽程式目前不了解連接描述元中要求的服務。ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.
  • ORA-12541:TNS:沒有任何接聽程式。ORA-12541: TNS:no listener.
  • ORA-12170:TNS:發生連接逾時。ORA-12170: TNS:connect timeout occurred.
  • ORA-12504:TNS 接聽程式在 CONNECT_DATA 中未得到 SERVICE_NAME。ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA.

若未安裝或未正確設定 Oracle 用戶端,便可能會發生這些錯誤。These errors might occur if the Oracle client either isn't installed or isn't configured properly. 若已安裝,請驗證 tnsnames.ora 檔案已正確設定,且您使用的是適當的 net_service_name。If it's installed, verify that the tnsnames.ora file is properly configured and you're using the proper net_service_name. 您也必須確定使用 Power BI Desktop 的電腦與執行閘道的電腦所使用的 net_service_name 相同。You also need to make sure that the net_service_name is the same between the machine that uses Power BI Desktop and the machine that runs the gateway. 如需詳細資訊,請參閱安裝 Oracle 用戶端For more information, see Install the Oracle client.

您也可能遭遇 Oracle Server 版本與 Oracle 資料存取用戶端版本之間的相容性問題。You might also encounter a compatibility issue between the Oracle server version and the Oracle Data Access Client version. 一般而言,由於某些組合不相容,所以您會希望這些版本能夠相符。Typically, you want these versions to match, as some combinations are incompatible. 例如,ODAC 12.x 並不支援 Oracle Server 第 9 版。For instance, ODAC 12.x does not support Oracle Server version 9.

若您從 Microsoft Store 下載了 Power BI Desktop,則可能因 Oracle 驅動程式問題,而無法連線到 Oracle 資料庫。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, do one of these steps:

  • 下載中心 (而不是 Microsoft Store) 下載 Power BI Desktop。Download Power BI Desktop from the Download Center instead of Microsoft Store.

  • 如果您想要使用來自 Microsoft Store 的版本:請在您的本機電腦上,將 oraons.dll 從 12.X.X\client_X 複製到 12.X.X\client_X\bin,其中 X 代表版本和目錄號碼。If you want to use the version from Microsoft Store: on your local computer, copy oraons.dll from 12.X.X\client_X to 12.X.X\client_X\bin, where X represents version and directory numbers.

如果您在連接到 Oracle 資料庫時,於 Power BI Gateway 中看到錯誤訊息「未設定物件參考」,請遵循管理您的資料來源 - Oracle 中的指示進行。If you see the error message, Object reference not set, in the Power BI Gateway when you connect to an Oracle database, follow the instructions in Manage your data source - Oracle.

如要使用 Power BI 報表伺服器,請參閱 Oracle 連線類型一文中的指引。If you're using Power BI Report Server, consult the guidance in the Oracle Connection Type article.