Oracle guide

Turn questions into Oracle SQL.

Q: Does selecting Oracle guarantee a working database connection?

No. SQL generation uses the project dialect and schema, while connection and execution also depend on deployment prerequisites, credentials, network access and database permissions. Complete the prerequisites described in this guide.

Q: Does the TTSQL API execute Oracle queries?

No. POST /api/v1 generates SQL and returns executed: false. Review the query; a separate manual Run action is available in the workspace when the connection and policies allow it.

Oracle SQL rewards precise date types, schema names and row-limiting instructions. These examples target Oracle Database 12c or later and keep business assumptions visible.

TTSQL team Updated September 5, 2026

Try TTSQL free

Before connecting Oracle

TTSQL's Oracle adapter requires Erlang ODBC, an ODBC driver manager and an installed Oracle Instant Client ODBC driver on the TTSQL server. Selecting Oracle as the project dialect does not install those runtime dependencies.

Provide host, port, database connection identifier, username and password, plus the configured driver name where necessary. Confirm the service connection string and schema ownership with your database administrator; network reachability and database grants are separate prerequisites.

Give the prompt the right dialect and context

Oracle folds unquoted identifiers to uppercase; double-quoted identifiers retain their exact case. String values use single quotes. Use the actual owner when referring to tables outside the connected user's schema.

Oracle DATE stores a time of day as well as the calendar date. DATE '2026-02-01' is midnight, so use a half-open range to include a full month. TIMESTAMP supports fractional seconds; be explicit about time zone conversion when your source type carries a zone.

Schema used in these examples

customers(id INTEGER PRIMARY KEY, name TEXT, country TEXT); orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), ordered_at TIMESTAMP, status TEXT, total DECIMAL(12,2)). Each order has one customer. total is the order amount in a single reporting currency; paid means recognized revenue in this teaching dataset. Timestamps use UTC. These are example tables, not tables TTSQL creates.

Translate TEXT and TIMESTAMP to equivalent column types for your engine. Use your own database, schema and table qualifiers. Each example states its reporting grain and sorting rule.

Review these Oracle details

Oracle treats an empty string as NULL for character data. A predicate status = '' will not identify missing statuses; use IS NULL when that matches the requirement.

Use explicit date literals or bind values rather than implicit string-to-date conversion. A query that happens to work with one NLS_DATE_FORMAT can fail or change meaning under another session configuration.

Generate, review, then choose whether to run

Select the matching database type and confirm your project's schema context before generating. Review the returned SQL against the schema and the metric you intended. A successful connection test does not prove that every table or operation is authorized.

TTSQL's POST /api/v1 endpoint generates SQL and returns executed: false. Query execution is a separate manual Run action in the workspace, subject to the configured connection and policies. The examples below are educational SELECT statements, not live results from your database.

Official Oracle references

Check your engine version and account configuration when adapting a function or connection setting.

Worked SQL examples

Read the sample schema and assumptions alongside each query. Adapt names, dates, and definitions to your own database before running SQL.

01 / Oracle Database 12c+

Truncate a timestamp to its month

Show paid revenue by calendar month in stored timestamps, oldest first.

SELECT TRUNC(ordered_at, 'MM') AS month,
       SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY TRUNC(ordered_at, 'MM')
ORDER BY month;

TRUNC with MM produces the first day of the month. The typed month bucket preserves year and chronological ordering. Avoid comparing its formatted display string to a date: client date formats and session NLS settings vary.

02 / Oracle Database 12c+

Limit after sorting

Return the five largest paid orders, breaking ties by ascending ID.

SELECT id, total
FROM orders
WHERE status = 'paid'
ORDER BY total DESC, id
FETCH FIRST 5 ROWS ONLY;

FETCH FIRST follows ORDER BY and applies to its sorted result. A naive ROWNUM <= 5 in the same query block can select rows before sorting and return the wrong top five. The row-limiting clause requires Oracle 12c or later.

03 / Oracle Database 12c+

Filter an analytic ROW_NUMBER

Show one latest paid order per customer, resolving equal timestamps by the highest order ID. Sort by customer ID.

SELECT customer_id, id, total
FROM (
  SELECT customer_id, id, total,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY ordered_at DESC, id DESC
         ) AS rn
  FROM orders
  WHERE status = 'paid'
) ranked
WHERE rn = 1
ORDER BY customer_id;

The subquery computes the analytic function before the outer filter. Oracle versions targeted here do not use AS for a table or subquery alias, hence ') ranked'. Include the ID tie-breaker so equal timestamps produce one reproducible choice.

Answers before you connect a database.

Does selecting Oracle guarantee a working database connection?