Menu

Important: This documentation is about an older version. It's relevant only to the release noted, many of the features and functions have been updated or replaced. Please view the current version.

Documentationbreadcrumb arrow Pluginsbreadcrumb arrow Oracle data sourcebreadcrumb arrow Oracle query editor
Enterprise

Oracle query editor

Grafana provides a query editor for Oracle. Queries can contain macros which simplify syntax and allow for dynamic parts.

Query editor options

You can create a query directly in the query editor window. The Format as option allows you to query Oracle and return results as time series data or as a table. Click Show Help for a sample query, a list of macros and other helpful information to help you get started writing SQL queries.

Query as time series

When you set Format as to Time series, the query must return a column called time that returns either a SQL datetime data type or any numeric data type representing UNIX epoch time in seconds. Grafana interprets DATE and TIMESTAMP columns without an explicit timezone as UTC time. Any column except time and metric is treated as a value column. You can return a column named metric that is used as metric name for the value column.

Example with metric column:

sql 
SELECT
  $__timeGroup(time_date_time, '5m') AS time,
  MIN(value_double),
  'MIN' as metric
FROM test_data
WHERE $__timeFilter(time_date_time)
GROUP BY $__timeGroup(time_date_time, '5m')
ORDER BY time

Query as table

When you set the Format as query option to Table, you can create any type of SQL query. The table panel automatically shows the results of whatever columns and rows your query returns. You can change the name of a column by using the AS SQL command, which renames the column (or table) with an alias.

Macros

To simplify syntax and to allow for dynamic parts, like date range filters, you can add macros to your query.

Macro exampleDescription
$__time(dateColumn)Will be replaced by an expression to rename the column to time. For example, dateColumn as time
$__timeEpoch(dateColumn)Will be replaced by an expression to rename the column to time and converting the value to unix timestamp (in milliseconds).
$__timeFilter(dateColumn)Will be replaced by a time range filter using the specified column name. For example, dateColumn BETWEEN TO_DATE('19700101','yyyymmdd') + (1/24/60/60/1000) * 1500376552001 AND TO_DATE('19700101','yyyymmdd') + (1/24/60/60/1000) * 1500376552002
$__timeFrom()Will be replaced by the start of the currently active time selection converted to DATE data type. For example, TO_DATE('19700101','yyyymmdd') + (1/24/60/60/1000) * 1500376552001
$__timeTo()Will be replaced by the end of the currently active time selection converted to DATE data type.
$__timeGroup(dateColumn,‘5m’)Will be replaced by an expression usable in GROUP BY clause.
$__timeGroup(dateColumn,‘5m’[, fillvalue])Will be replaced by an expression usable in GROUP BY clause. Providing a fillValue of NULL or floating value will automatically fill empty series in timerange with that value. For example, $__timeGroup{createdAt, ‘1m’, 0}.
$__timeGroup(dateColumn,‘5m’, 0)Same as above but with a fill parameter so missing points in that series will be added by grafana and 0 will be used as value.
$__timeGroup(dateColumn,‘5m’, NULL)Same as above but NULL will be used as value for missing points.
$__timeGroup(dateColumn,‘5m’, previous)Same as above but the previous value in that series will be used as fill value if no value has been seen yet NULL will be used.
$__unixEpochFilter(dateColumn)Will be replaced by a time range filter using the specified column name with times represented as unix timestamp (in milliseconds). For example, dateColumn >= 1500376552001 AND dateColumn <= 1500376552002
$__unixEpochFrom()Will be replaced by the start of the currently active time selection as unix timestamp. For example, 1500376552001
$__unixEpochTo()Will be replaced by the end of the currently active time selection as unix timestamp. For example, 1500376552002

The plugin also supports notation using curly brackets {}. Use this notation when queries are required inside parameters.

Note

Use one notation type per query. If the query needs braces, all macros in the query will need to use braces.
SQL 
$__timeGroup{dateColumn,'5m'}
$__timeGroup{SYS_DATE_UTC(SDATE),'5m'}
$__timeGroup{FROM_TZ(CAST(SDATE as timestamp), 'UTC'), '1h'}