Oracle Cloud Infrastructure Documentation

Oracle Autonomous Database for Excel

The Oracle Autonomous Database for Excel integrates Microsoft Excel spreadsheets with the Autonomous Database to retrieve and analyze data from Analytic Views in the database. You can also directly run SQL queries to view their results in the worksheet.

Note

  • The Download Microsoft Excel/Google Sheets add-in is available to you under the Downloads menu of your Database Actions instance only if you have the ADPUSER role.
  • You require ADMIN privileges to grant roles.
  • To grant yourself an ADPUSER role, you must log into your Database Actions instance and enter the following command in the SQL worksheet area displayed in the SQL tab under Development tools present in the Launchpad: 
    Grant ADPUSER to USER;

Parent topic: The Data Analysis Tool

Using Oracle Autonomous Database for Excel

After you install the add-in, a new ribbon tab, Oracle Autonomous Database appears in MS Excel.

You can connect to multiple Autonomous Databases, work with Analytic Views, tables, and Views, and view the data in the worksheet.

This ribbon provides buttons that let you connect to the Autonomous Database.

Click Connections to connect to an Autonomous Database. You must Refer to the Connection management for more details.

Click Settings to view the logging level settings of the Excel Add-in. You can also clear the logs or export the log files by copying the logging information to the clipboard.

Click About to view the Add-in and the supported Excel versions. The About window also displays whether the spreadsheet is connected to the database. It also shows version information for the database and Oracle Rest Data Services.

Click Direct SQL to run custom SQL queries. See

Click Data Analysis to query an existing Analytic View and run queries.

Click Natural Language to run Natural Language Queries on Tables.

Read and Access Data Using Table Hyperlinks in Excel Sheet

Table Hyperlinks in Oracle Autonomous Database provide secure, preauthenticated URLs that allow read-only access to data stored in tables, views, or the result of SQL queries.

They enable easy sharing and retrieval of database data without requiring additional authentication beyond the hyperlink itself. You can read and access data in a Microsoft Excel sheet using a Table Hyperlink through the Oracle Autonomous Database Add-in for Excel, which allows direct retrieval and viewing of tabular data from the Autonomous Database within Excel.

You can use Table Hyperlinks with the add-in.

For more information of Table Hyperlinks, refer to the Use Table Hyperlinks for Read Only Data Access on Autonomous Database Chapter.
Note

This feature allows read-only access to data through secure, table hyperlink URLs without requiring an additional database connections or an Oracle Autonomous Database account.

Use Table Hyperlinks to read and access data and view the data in a tabular format.

To return data from the add-in using Table Hyperlinks:
  1. On the Excel sheet, select the menu Autonomous Database.
  2. Select Table Hyperlinks.


    Description of table-hyperlinks.png follows
    Description of the illustration table-hyperlinks.png

  3. Selecting Table Hyperlinks opens a Table Hyperlinks panel.


    Description of th-panel.png follows
    Description of the illustration th-panel.png

  4. On the Input Hyperlink field, enter the input Table Hyperlink the producer shares with you. A Producer generates a PAR URL in their database and provides it to you. For example, https://dataaccess.adb.eu-frankfurt-1.oraclecloudapps.com/adb/p/F5Sn....

    You can create Table Hyperlinks in Oracle Autonomous Database Serverless using the DBMS_DATA_ACCESS PL/SQL package and share them with the consumers.

    See DBMS_DATA_ACCESS Package for more information on this topic.

    Select Worksheet: Enter the name of the sheet where you want to display the results. For example, Sheet1.

    You can click the + sign beside the Select worksheet drop-down to display the results in a new worksheet.

  5. Click the Play button to load the Autonomous Data table in the worksheet.

    The result data gets retrieved in the worksheet. You can view only first 10000 records in the first load.


    Description of results-th.png follows
    Description of the illustration results-th.png

    After selecting the play icon, along with the results retrieved in the worksheet, you can also see the following generated fields:
    • Load More Data:

      Select Load More Data to retrieve the next 10000 rows from the table.

    • Color Settings:

      Select this icon to enable color in the column from a predefined pool of colors. If the feature runs out of colors, the pool is refilled with the same colors for the new value keys.

      Click the icons you want to add color to. In this example, select JOB_TITLE and BUSINESS_UNIT.


      Description of select-columns.png follows
      Description of the illustration select-columns.png

    • Select Enable Color to color the selected columns in the data.


      Description of enable-color.png follows
      Description of the illustration enable-color.png

      You can select Reset Color to reset your current selection, remove colors from all the selected columns and choose different columns you wish to enable color to.

    • Auto Refresh Data:

      Enable this option to automatically update data at regular intervals with the latest data from its source database. This automatic refresh keeps the tabular data in worksheet in sync with the current data within a specific refresh frequency.

      You can select the frequency from the frequency slider where the selection varies from a range of 5 minutes to 1 day.

    • Reload: Select Reload to reload the preloaded table hyperlink data.
      Note

      This button loads a maximum of 50,000 records.
    • Reset: Select Reset to clear all data from the sheet, reset your selection of all the current options and enter the Input hyperlink again.