Using Advanced Query Creator

In this mode the SQL query box is shown as a free-text editor allowing you to create standard SELECT SQL queries of your own design and complexity. This is available for SQL Explorer, Data Object Explorer, Database Explorer, Host Explorer, and Exadata Explorer.

With Advanced Query Creator you can save the bind parameters as filters and load them into the data explorer. Additionally Bind parameters allow filters to be added within the dashboard for advanced explorer queries.

Write a Query

With the Advanced query creator you can write standard SELECT free-text queries, allowing you to join different views together into a single query. In the main query panel to update and enter new queries follow these steps:

Note

When developing queries the following must be taken into consideration:

SELECT or WITH must be the first keyword used in the advanced SQL query box.
If the WITH clause is used, it must be used at the start of the query, a nested WITH clause is not supported.
If the data object name needs to be referred to more than once in the query, you need to define a WITH clause CTE name, and use the CTE name in the query. For example: WITH MYDO AS (SELECT * FROM OPSIDO$HOSTINSIGHTS$HOST_CPU_AND_MEMORY_DAILY))

Under Mode, select Advanced to access the free-text query editor.
Click Clear to clear the current query.
Write a new standard SELECT query.
Click Run to execute the new query.
Toggle between different chart type using the Visualization panel to the right of the query results. This will allow you to visualize your data in different manners.

Use Views, Columns, and Sample Queries

Predefined sample queries and view and column name are available for the various types of Data Objects, and can be used as a base for your personalized queries. To begin using the sample queries in Advanced mode, click on the ? on the upper-right side of the SQL editor window. This opens the View & Column and sample queries pop up screen.

Predefined views, columns, and sample queries are available for the different data object types and can be used as a starting point for your personalized queries. In Advanced mode, expand View & columns and sample queries above the SQL editor to view this information.

Use Views and columns to review available views and columns, including their type and description. You can expand a view to see the columns associated with it. To use a view or column in your query, copy its name and paste it into the SQL editor.

Use the Search field to search for specific view names or column names. Select a Resource type to filter the available data objects and view or column information.

Resource types are available for the following explorers:

SQL Explorer: Oracle and MySQL
Database Explorer: Oracle and MySQL
Data Object Explorer: Database - Oracle, Database - MySQL, Exadata, and Host.

Use Sample queries to review query examples. Copy a sample query if you want to use it as the basis for your own query, and then continue editing it in the SQL editor.

Using Bind Parameters in the Advanced Query Creator

You can add bind parameters into the advanced free-form query, this allows you to use the : query notation as a placeholder and enter data values at a later moment. With Advanced Query Creator you can save the bind parameters as filters and load them into the data explorer.

Under Mode, select Advanced to access the free-text query editor.
Click Add bind parameter. This will open the Add bind parameter panel. You can select to use either:
- Choose existing bind parameter: Select the Widget compartment and then select the filter of your choice. Click Add.
- Configure new parameter: Select one of two options:
  1. List of values based on data object: Select the Data object, the desired Dimension field, that will be populated with the distinct values for this field. Enter a Bind parameter name. Under Parameter settings the following options are configurable:
    - Selection mode: Determines if bind variable drop-down supports single or multiple selections.
    - Is required: Determines if the bind parameter value requires a selection.
    - Auto-replace empty value: Determines if empty bind parameter selections should automatically be handled. When auto-replace is enabled, empty bind variable values that used after '=' (Equals To operator) are automatically handled at runtime. You can view auto-replace examples within the panel by expanding the Samples section.
      Note
      
      Only applicable when the parameter is not required.
  2. User specified value: Enter the Bind parameter name and Type (STRING, NUMBER, DATETIME). Under Parameter settings the following options are configurable:
    - Is required: Determines if the bind parameter value requires a selection.
    - Auto-replace empty value: Determines if empty bind parameter selections should automatically be handled. Only applicable when the parameter is not required.
Once all information has been entered, click Add, this will add the bind parameter to the Data Explorer. You will now see the Bind Parameters options. You can select a value for the bind parameter, or manually enter the values for free-text input box.
Bind parameters only work when used within a query using the ':' annotation in front of the bind parameter name in order for it to be used properly within the query. Update the query, and then click Run; this will execute the query with the bind parameters
Additionally you can save the current search as well as the bind parameter as a saved search filter by clicking Save or Save as.
Saved searches that link bind parameters will automatically load the linked bind parameters in Explorer. Likewise, when adding a saved search to a dashboard, the bind parameters filters will be added automatically. Bind parameters will only be added automatically to a dashboard when it is required for the saved search.

To add optional bind parameter to a dashboard widget, see Add an Optional Bind Parameter in a Dashboard.

Oracle Cloud Infrastructure Documentation

Using Advanced Query Creator