Oracle SQL dialect

How to connect.

There is no Oracle-specific endpoint and nothing to switch on for the dialect. You connect exactly as any other SQL client does, and the Oracle spellings are understood on arrival. Two doors, same behaviour:

• The PostgreSQL wire protocol — port 5432, TLS required, SCRAM-SHA-256. Any PostgreSQL driver works: JDBC’s PostgreSQL driver, psycopg, node-postgres, pgx, libpq, the PostgreSQL ODBC driver. Setup and per-client walkthroughs are on Connect a SQL client.
• The HTTP /sql endpoint — one bearer token, no driver at all. See the SQL reference.

The dialect work happens in the SQL translator, above the door, on the one seam both surfaces share — so a statement that is accepted over HTTP is accepted over the wire, and refused the same way.

# A PostgreSQL client, talking Oracle SQL. Nothing Oracle-specific here.
psql "host=<your-database-host> port=5432 dbname=postgres \
      user=<user> password=<password> sslmode=require"

=> SELECT NVL(region, 'unknown') AS region FROM sales.orders MINUS
   SELECT NVL(region, 'unknown') FROM sales.returns;

What the three tiers mean.

The failure that matters in a dialect is not a refusal — it is a construct that is accepted and answered differently than Oracle would answer it, because you then get a wrong number with a 200 next to it. The engine is built to refuse loudly instead. So:

• Generally available — shipped, and Oracle-equivalent within the scope named in the row.
• Preview — shipped and reachable, but deliberately narrower than Oracle. The served shape is described; everything outside it is refused with a 400 that names the construct, never answered approximately.
• Not supported — not accepted today. The rewrite that gets you the same answer is in the row.

A boundary in the “preview” column is a real boundary, not a caveat for form’s sake. Read the row before you port a statement that depends on it.

Generally available.

These behave as Oracle does within the scope described. Two dialect-wide differences apply throughout and are worth internalising once: a zero-length string is not NULL here (Oracle treats '' as NULL), and a chosen operand is returned with its own type rather than coerced to the first argument’s type — so keep the operands of one call in one type.

Preview.

Shipped and reachable, narrower than Oracle. Each row names the shape that is served; outside it the statement is refused rather than translated into a different question.

Not supported.

Not accepted today. Each row carries the rewrite that gets you the same answer, so you can plan the port rather than discover the gap at cutover.

Worked examples.

Real Oracle statements, and what happens to them here. Each block runs unchanged over the HTTP /sql endpoint or through a PostgreSQL driver.

1. A sequence-backed insert

Both spellings work and share one durable counter, so you can port the Oracle spelling and migrate it later — or not at all.

CREATE SEQUENCE hr.emp_seq START WITH 1000 INCREMENT BY 1;

-- The Oracle pseudocolumn spelling: ships as-is.
INSERT INTO hr.emp (id, ename) VALUES (hr.emp_seq.NEXTVAL, 'KING');

-- The function spelling: the same counter, same durability.
INSERT INTO hr.emp (id, ename) VALUES (nextval('hr.emp_seq'), 'CLARK');

-- Read the value the session last took.
SELECT hr.emp_seq.CURRVAL FROM dual;

-- REFUSED - this is the table-scanning case, and it would mean
-- "one sequence value per scanned row", which this surface does not do:
--   INSERT INTO hr.emp (id, ename) SELECT hr.emp_seq.NEXTVAL, ename FROM staging.emp;

2. NULL handling and FROM DUAL

The one difference to remember: an empty string is not NULL here, so NVL('', 'x') is '' rather than Oracle’s 'x'.

SELECT NVL(commission, 0)                    AS commission,
       NVL2(manager_id, 'reports', 'top')    AS position,
       DECODE(dept, 10, 'ops', 20, 'sales', 'other') AS dept_name
FROM   hr.emp;

-- FROM DUAL is recognised and stripped:
SELECT SYSDATE FROM dual;            -- answers in UTC
SELECT 1 + 1 FROM sys.dual;          -- either spelling, any case

3. Row caps and pagination

The cap and the nested pagination idiom port. The combination that would silently change meaning is refused, and the refusal names both rewrites.

-- Works: a plain cap.
SELECT id FROM hr.emp WHERE ROWNUM <= 10;

-- Works: the classic nested pagination idiom, rows 11-20.
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM ( SELECT id, ename FROM hr.emp ORDER BY id ) a
  WHERE ROWNUM <= 20
) WHERE rn > 10;

-- Matches NOTHING - exactly as in Oracle, where the counter only
-- advances on a returned row. It is not an OFFSET.
SELECT id FROM hr.emp WHERE ROWNUM > 1;

-- REFUSED: ROWNUM is assigned BEFORE the sort, so this is
-- "10 arbitrary rows, then sorted" in Oracle - not the 10 smallest.
--   SELECT id FROM hr.emp WHERE ROWNUM <= 10 ORDER BY id;
-- Write the top-n you actually meant:
SELECT id FROM hr.emp ORDER BY id FETCH FIRST 10 ROWS ONLY;

4. Hierarchy and set difference

CONNECT BY does the walk; LEVEL does not ship yet, so carry depth yourself if you need it.

-- Walks the reporting tree from the top down.
SELECT id, ename
FROM   hr.emp
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

-- The portable spelling of the same walk, with depth carried as a column:
WITH RECURSIVE tree AS (
  SELECT id, ename, 1 AS depth FROM hr.emp WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.ename, t.depth + 1 FROM hr.emp e JOIN tree t ON e.manager_id = t.id
)
SELECT id, ename, depth FROM tree;

-- MINUS is a spelling of EXCEPT and inherits its behaviour exactly.
SELECT region FROM sales.orders
MINUS
SELECT region FROM sales.returns;

5. The (+) outer join

The two-table shape is rewritten to a LEFT OUTER JOIN. Anything wider is refused rather than turned into an inner join behind your back.

-- Served: two tables, all marks on one side, plain col = col.
SELECT e.ename, d.dname
FROM   hr.emp e, hr.dept d
WHERE  e.dept_id = d.id(+);

-- Which is exactly this, and you may prefer to write it directly:
SELECT e.ename, d.dname
FROM   hr.emp e LEFT OUTER JOIN hr.dept d ON e.dept_id = d.id;

Next.

Everything the base SQL surface serves — joins, aggregates, window functions, CTEs, constraints and transactions — is on the SQL reference, and it is what you fall back to wherever an Oracle spelling stops. To get a client connected, start at Connect a SQL client. If a statement you depend on is refused, the message names the construct — send it to us and it becomes a tracked gap rather than a surprise.