ANSI SQL Execution (Preview)

oracle_analytics-execute_oac_ansi_sql

Input Schema

{
  "type": "object",
  "properties": {
    "maxRows": {
      "type": "number"
    },
    "query": {
      "type": "string"
    }
  }
}

Executes an ANSI grammar SQL query against Oracle Analytics Cloud and streams the results. Use this as the default query tool for standard analytical queries.

When to Use ANSI SQL

Use ANSI SQL for standard analytical queries, including grouping, filtering, multiple measures, calculated fields, and Top-N queries that use ORDER BY and FETCH FIRST.

Use oracle_analytics-execute_logical_sql instead when the query requires Oracle Analytics Cloud semantic time functions such as AGO, TODATE, or PERIODROLLING; advanced ranking or analytics; running calculations; forecasting; or a reliable non-default aggregation.

Important Parameters

Parameter	Type	Notes
query	string	ANSI SQL query. Required. Don't add the OBIS_ENABLE_ANSI_SQL prefix because the tool adds it automatically.
maxRows	number	Maximum number of rows to stream. Keep this less than or equal to the query FETCH FIRST limit.

ANSI SQL Rules to Follow

• Use fully qualified column names and explicit aliases.
• Use an explicit GROUP BY for projected dimensions when the query includes aggregate expressions.
• Each leaf subquery must include exactly one subject area in its FROM clause.
• Use projected expressions in ORDER BY.
• Keep row limits bounded with FETCH FIRST n ROWS ONLY.
• For XSA datasets, use an unquoted source expression, such as FROM XSA('namespace'.'dataset').
• For XSA column references, copy the fullyQualifiedName value from oracle_analytics-describe_data exactly.
• Avoid QUALIFY, quantified SOME or EVERY, OVERLAPS, direct subject-area/CTE comma combinations, and direct CROSS JOIN with a subject area or XSA source.

Example

{
  "jsonrpc": "2.0",
  "id": 50,
  "method": "tools/call",
  "params": {
    "name": "oracle_analytics-execute_oac_ansi_sql",
    "arguments": {
      "query": "SELECT \"Sales History Subject Area\".\"CHANNELS\".\"CHANNEL_DESC\" AS channel, SUM(\"Sales History Subject Area\".\"SALES\".\"AMOUNT_SOLD\") AS revenue FROM \"Sales History Subject Area\" GROUP BY \"Sales History Subject Area\".\"CHANNELS\".\"CHANNEL_DESC\" ORDER BY revenue DESC FETCH FIRST 100 ROWS ONLY",
      "maxRows": 100
    }
  }
}

Output

Returns row data. If the query fails, the response includes error details.

Troubleshooting

If the service returns REQUEST_ERROR: Connection reset, retry the same query once.

If grouped XSA results contain repeated identical dimension and aggregate rows, treat the result as suspect. Retry the query once with Logical SQL, using the exact XSA fullyQualifiedName expressions.

For a predefined measure, AVG, MAX, or MIN might resolve to the model’s default aggregation. If MIN, AVG, MAX, and SUM return the same value, don't rely on the requested statistic. Use Logical SQL or retrieve lowest-grain rows and calculate the result outside Oracle Analytics Cloud.

Safety

This tool is read-only and doesn't require approval. However, queries can be expensive or expose data. Use selective filters, deterministic ordering, FETCH FIRST, and maxRows to keep results bounded.