Oracle Database 23ai introduced a useful SQL improvement which is the ability to use a VALUES(...) clause as an inline table within a query.

The problem

You need a small, hardcoded set of rows, a handful of status codes, a reference list or some test data and ideally without creating a temporary table or a view so you may have written something like this:

SELECT 'A' AS code, 'Active'   AS description FROM DUAL
UNION ALL
SELECT 'I',         'Inactive'                FROM DUAL
UNION ALL
SELECT 'P',         'Pending'                 FROM DUAL; 

The 23ai solution

Table Value Constructors: a VALUES(...) produces an inline, named, derived table with syntax which maybe familiar if you’ve used SQL Server or PostgreSQL.

SELECT v.code, v.description
FROM (VALUES
  ('A', 'Active'),
  ('I', 'Inactive'),
  ('P', 'Pending')
) v(code, description);

Cleaner, more readable, and trivially extended. Adding a row is a single line. Column aliases are declared once, at the end of the VALUES block, not repeated on every row.

Multi-row INSERT

The same syntax applies to INSERT statements which you can use to load multiple rows in a single statement:

INSERT INTO status_codes (code, description)
VALUES
  ('A', 'Active'),
  ('I', 'Inactive'),
  ('P', 'Pending');

Joining and filtering inline data

No real surprises here as joins work as expected.

SELECT
  o.order_id,
  o.status_code,
  v.label,
  v.sort_order
FROM orders o
JOIN (
  VALUES
    ('A', 'Active',    1),
    ('P', 'Pending',   2),
    ('C', 'Complete',  3),
    ('X', 'Cancelled', 4)
) v(code, label, sort_order)
  ON v.code = o.status_code
ORDER BY v.sort_order;

as does filtering

SELECT v.code, v.label
FROM (
  VALUES
    ('A', 'Active'),
    ('I', 'Inactive'),
    ('P', 'Pending')
) v(code, label)
WHERE v.code != 'P';