Oracle Database – SELECT Essentials

Your SELECT knowledge transfers to Oracle almost intact. This post is about the parts that don't: pagination, null handling, date formatting and the handful of idioms you will see in every Oracle codebase.

DUAL

Oracle requires a FROM clause, so scalar expressions select from DUAL — a built-in table with one column and exactly one row.

SELECT 1 + 1                        FROM dual;
SELECT sysdate                      FROM dual;
SELECT order_seq.NEXTVAL            FROM dual;
SELECT user, sys_context('USERENV','DB_NAME') FROM dual;

It is genuinely one row, which makes it useful as a row generator when combined with CONNECT BY — handy for filling a date range that has gaps in the data:

-- Every day in February 2024, whether or not an order exists
SELECT DATE '2024-02-01' + LEVEL - 1 AS day
FROM   dual
CONNECT BY LEVEL <= 29;

Pagination: FETCH FIRST, not ROWNUM

Oracle has no LIMIT. Since 12c there is standard row-limiting syntax, and it is what you should write:

-- Top 10
SELECT * FROM orders ORDER BY total DESC FETCH FIRST 10 ROWS ONLY;

-- Page 3, 20 per page
SELECT * FROM orders ORDER BY id OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- Include everyone tied at the boundary — may return more than 10 rows
SELECT * FROM orders ORDER BY total DESC FETCH FIRST 10 ROWS WITH TIES;

-- Percentage
SELECT * FROM orders ORDER BY total DESC FETCH FIRST 5 PERCENT ROWS ONLY;

Why ROWNUM catches people out

You will still meet ROWNUM constantly in older code, so you need to know how it behaves. ROWNUM is assigned as rows are produced, before ORDER BY runs. So this looks like "the 10 biggest orders" and is not:

-- WRONG: takes 10 arbitrary rows, THEN sorts those 10
SELECT * FROM orders WHERE rownum <= 10 ORDER BY total DESC;

-- RIGHT: sort first in an inline view, then filter
SELECT * FROM (SELECT * FROM orders ORDER BY total DESC) WHERE rownum <= 10;

And the reason OFFSET needs the nested form is that ROWNUM only increments when a row is returned, so rownum > 40 is never true — row 1 is rejected, so no row ever becomes row 1. The classic three-level idiom, which is what OFFSET … FETCH replaced:

SELECT * FROM (
  SELECT t.*, rownum AS rn FROM (
    SELECT * FROM orders ORDER BY id
  ) t WHERE rownum <= 60          -- offset + pagesize
) WHERE rn > 40;                   -- offset

One thing ROWNUM is still good for: WHERE rownum = 1 as a cheap "does any row exist" probe, and capping a runaway ad-hoc query.

Always order by something unique when paginating. ORDER BY total DESC with ties gives Oracle no defined order within the tie, so the same row can appear on page 1 and page 2. ORDER BY total DESC, id fixes it.

NULL handling

NVL(phone, 'none')                    -- 2 args, Oracle-specific, evaluates both
NVL2(phone, 'has phone', 'no phone')  -- if-not-null / if-null
COALESCE(mobile, phone, 'none')       -- ANSI, n args, short-circuits
NULLIF(a, b)                          -- NULL when a = b, else a
LNNVL(total > 100)                    -- TRUE when the condition is false OR null

Prefer COALESCE: it is standard, takes any number of arguments, and stops evaluating once it finds a non-null. NVL evaluates both arguments always, so NVL(x, expensive_function(y)) pays for the function call even when x is not null.

Sorting: NULL sorts last in ASC and first in DESC, which is the opposite of Postgres' default. Say what you mean:

ORDER BY ship_date ASC NULLS FIRST      -- unshipped orders at the top
ORDER BY total DESC NULLS LAST

Conditional expressions

-- CASE: standard, readable, handles ranges. Use this.
SELECT order_id,
       CASE WHEN total >= 1000 THEN 'LARGE'
            WHEN total >= 100  THEN 'MEDIUM'
            ELSE 'SMALL'
       END AS bucket
FROM   orders;

-- DECODE: Oracle-only, equality only. You will read it; don't write it.
SELECT DECODE(status, 'A', 'Active', 'C', 'Closed', 'Unknown') FROM customers;

One quirk in DECODE's favour: it treats two NULLs as equal, which CASE does not. That is the only situation where reaching for it is defensible.

Dates in and out

Never rely on NLS_DATE_FORMAT. It is a session setting, it differs between your machine and the server, and code that depends on it fails in production with ORA-01861: literal does not match format string.

-- Explicit literals
DATE '2024-02-19'
TIMESTAMP '2024-02-19 14:30:00'

-- Explicit conversion
to_date('19/02/2024', 'DD/MM/YYYY')
to_char(order_date, 'YYYY-MM-DD HH24:MI:SS')
to_char(order_date, 'Day, DD Month YYYY', 'NLS_DATE_LANGUAGE=English')

Format mask details that cause real bugs:

MM is month, MI is minute. MM where you meant MI parses without complaint and gives nonsense.
HH is 12-hour; HH24 is what you want. HH without AM silently loses the afternoon.
YYYY vs RRRR: with a two-digit year, RRRR guesses the century (55 → 1955, 15 → 2015) and YYYY uses the current one (55 → 2055).
to_number(str, '999.99') exists too, and matters where the decimal separator is a comma.

Strings

first_name || ' ' || last_name          -- concatenation, and it ignores NULLs
SUBSTR(sku, 1, 3)                       -- 1-based, not 0-based
INSTR(email, '@')                       -- position, 0 when absent
LENGTH(name)                            -- characters; LENGTHB for bytes
UPPER / LOWER / INITCAP
TRIM(name) / LTRIM / RTRIM
LPAD(id, 8, '0')                        -- '00000042'
REPLACE(phone, '-', '')
TRANSLATE(code, 'OI', '01')             -- character-by-character mapping
REGEXP_LIKE(email, '^[^@]+@[^@]+\.[a-z]{2,}$', 'i')
REGEXP_SUBSTR(log_line, 'user=(\w+)', 1, 1, NULL, 1)
REGEXP_REPLACE(text, '\s+', ' ')

|| treating NULL as an empty string is a real convenience — a || b is never null unless both are — and it is a direct consequence of empty string being null.

Grouping, and LISTAGG

SELECT customer_id, count(*) AS orders, sum(total) AS revenue
FROM   orders
GROUP  BY customer_id
HAVING sum(total) > 5000
ORDER  BY revenue DESC;

LISTAGG is Oracle's string aggregation, and it is used constantly:

SELECT customer_id,
       LISTAGG(status, ', ') WITHIN GROUP (ORDER BY order_date) AS statuses
FROM   orders
GROUP  BY customer_id;

It returns a VARCHAR2, so it overflows at 4000 bytes with ORA-01489. From 12.2 you can ask it to truncate instead of failing:

LISTAGG(status, ', ' ON OVERFLOW TRUNCATE '…' WITH COUNT) WITHIN GROUP (ORDER BY order_date)

LISTAGG(DISTINCT …) works from 19c. Before that, deduplicate in a subquery.

Two grouping extensions worth knowing, because hand-writing the equivalent UNION ALL is a waste of an afternoon:

-- Subtotals per level plus a grand total
SELECT region, product, sum(total)
FROM   sales GROUP BY ROLLUP (region, product);

-- Every combination
SELECT region, product, sum(total)
FROM   sales GROUP BY CUBE (region, product);

-- GROUPING() tells you which rows are the subtotals
SELECT region, GROUPING(region) AS is_total, sum(total)
FROM   sales GROUP BY ROLLUP (region);

MERGE

Oracle's upsert. There is no INSERT … ON CONFLICT; MERGE is the tool, and it does more than an upsert because it can also delete.

MERGE INTO customers c
USING (SELECT :email AS email, :name AS last_name FROM dual) src
ON    (c.email = src.email)
WHEN MATCHED THEN
  UPDATE SET c.last_name = src.last_name
WHEN NOT MATCHED THEN
  INSERT (email, last_name) VALUES (src.email, src.last_name);

Merging one table into another is where it earns its keep:

MERGE INTO customers c
USING customers_staging s ON (c.email = s.email)
WHEN MATCHED THEN
  UPDATE SET c.last_name = s.last_name, c.status = s.status
  WHERE  c.last_name <> s.last_name OR c.status <> s.status   -- skip no-op updates
  DELETE WHERE s.status = 'PURGE'
WHEN NOT MATCHED THEN
  INSERT (email, last_name, status) VALUES (s.email, s.last_name, s.status);

That WHERE on the UPDATE is worth the two lines: without it, every matched row is rewritten and generates redo even when nothing changed.

MERGE needs the join key to be unique in the source. If two staging rows match the same target row you get ORA-30926: unable to get a stable set of rows — the fix is deduplicating the source, not retrying.

Bulk INSERT and RETURNING

-- Multi-row insert: not VALUES (...), (...) — use INSERT ALL
INSERT ALL
  INTO customers (email, last_name) VALUES ('a@x.com', 'Alpha')
  INTO customers (email, last_name) VALUES ('b@x.com', 'Beta')
SELECT * FROM dual;

-- Or from a query
INSERT INTO customers_archive SELECT * FROM customers WHERE status = 'CLOSED';

-- Get the generated key back (PL/SQL and JDBC)
INSERT INTO customers (email, last_name) VALUES ('c@x.com', 'Gamma')
RETURNING id INTO :new_id;

Oracle does not accept the multi-row VALUES list that MySQL and Postgres do. That surprises people porting scripts; INSERT ALL is the equivalent.

WITH clauses

Common table expressions work as expected, and they matter for readability once queries grow:

WITH monthly AS (
  SELECT to_char(order_date, 'YYYY-MM') AS mth, sum(total) AS revenue
  FROM   orders GROUP BY to_char(order_date, 'YYYY-MM')
), ranked AS (
  SELECT mth, revenue, RANK() OVER (ORDER BY revenue DESC) AS rnk
  FROM   monthly
)
SELECT * FROM ranked WHERE rnk <= 3;

Recursive CTEs also work from 11gR2, though a lot of Oracle code still uses the older CONNECT BY hierarchical syntax:

-- CONNECT BY: Oracle's own, with LEVEL and SYS_CONNECT_BY_PATH for free
SELECT LPAD(' ', 2 * (LEVEL - 1)) || last_name AS org,
       SYS_CONNECT_BY_PATH(last_name, '/')     AS path
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Joins, including the (+) syntax you will find all over legacy Oracle SQL and the NOT IN trap that silently returns nothing.