UI Options in Spreadsheet Add-ins

Spreadsheet Add-ins provide common UI elements across multiple features and additional controls specific to individual panels.

Connection Information

The top section of most add-in panels displays information about the active connection, including the Database URL, schema, connection status, and the remaining session time before the connection expires. For OCI IAM connections, it also displays the connected user.

Note:

Though the add-in maintains an active connection with the database, the data retrieved is local to the spreadsheet. In case of inactivity or connection time-out, the add-in displays a notification and suggests that you reconnect to the database.

Using the SQL Query Editor

The SQL query editor is the area where you write, edit, and view SQL statements before running them.

You can enter a query manually or select tables, views, and columns from the object browser to help create the SQL statement. The SQL query editor displays line numbers and syntax highlighting for the queries.

The actions available in the query editor depend on the add-in feature you are using. In general, you can use the following options with the SQL query editor:

  • Run

    The first button, displayed as a circle with a triangle, is the Run button. Click this button to execute the SQL statement and display the query results in the selected worksheet.

  • Clear SQL Content

    The eraser icon next to Run removes the current SQL statement from the query editor.

  • Retrieve query from sheet

    This button retrieves the SQL query from the active worksheet and displays it in the SQL query editor. You can then edit and run the query again.

  • Share as Table Hyperlink

    This option is available only for query results generated using Direct SQL and Data Analysis. Create a Table Hyperlink and share it with other users so they can load the read-only query results into a spreadsheet.

Note:

Add-in for Excel provides a floating menu with additional options, such as reloading the add-in window. Click the small arrow on the right side of the add-in window to open the floating menu.

Select a Worksheet for Query Results

You can use the default sheet or select a different sheet to display the results.

Note:

If you run another query in the same worksheet, the add-in refreshes the worksheet with the result from the new query.

To use another sheet for a different query, follow any of these:

  • From Spreadsheet Add-ins click the plus (+) button, specify a name under Add new worksheet and click the tick button. The add-in inserts the new sheet when you run the query. The left arrow button is to cancel and go back.

  • Insert a new sheet using the options in Excel or Google Sheets.

You can then select the new sheet from Select worksheet and view the results.

Query Information

Along with the query results, the worksheet also displays the following query information:

  • the database URL

  • the database user or schema who creates and runs the query

  • the actual SQL Query

  • the refresh configuration

  • the date and time of the query

Use Bind Variables in SQL Queries

You can use bind variables in a SQL query to provide values at run time. Define a bind variable in the SQL statement by using a colon (:) followed by a variable name.

For example

SELECT *
FROM employees
WHERE city = :ct;

When you run the query, the add-in prompts you to enter a value for the bind variable, :ct. After you provide a value, for example, Austin, the query runs using that value. The query information displayed with the results includes the bind variable assignment, for example, :ct=Austin.

You can include multiple bind variables in a query. The add-in retains recently used values for each bind variable. When you run the query again, you can select a recent value or enter a new value. You can also clear previously saved values from the Query Parameters dialog.

Refresh Query Results

You can use Refresh Query to rerun a saved query and update the results in a worksheet without opening the Direct SQL, Data Analysis, or Natural Language panels.

To refresh query results:

  1. Select a worksheet that contains query results generated by Spreadsheet Add-ins.

  2. Click Refresh Query.

    The add-in reruns the saved query and updates the current worksheet with the latest results.

If the workbook contains additional worksheets with refreshable query results, the add-in displays the number of remaining worksheets and prompts you to refresh them. After the refresh completes, the add-in displays a report that indicates the worksheets that were refreshed successfully and identifies any worksheets that could not be refreshed.

Note:

A worksheet can be refreshed if the corresponding database connection is saved and connected. Spreadsheet Add-ins refreshes each worksheet using its saved connection information, even if that connection is not the active connection.

A refresh operation can fail in the following scenarios:

  • The saved visualization no longer matches the columns returned by the query.

  • No saved connection matches the database URL and user associated with the worksheet.

  • The required database connection is disconnected.

The refresh report provides details for any failed worksheets. Depending on the cause, you can update the query or visualization, add or import a matching saved connection, or reconnect the required database connection before refreshing again.

Handling Large Data

For queries with large data sets, for example a result containing more than 10,000 records, the add-in displays a window for confirmation if you want to view the entire result.

Note:

While working with Pivot tables, load the complete data for accurate results from the aggregation.

Parent topic: Using Spreadsheet Add-ins Features