Syntax and Data Types

The schema this track uses#

Every later page in this track queries, extends, or procedurally manipulates the same two tables: departments and employees. It's deliberately the same shape as Oracle's own long-standing sample HR schema, because that shape — employees who belong to a department and optionally report to another employee — is exactly what's needed to demonstrate joins, analytic functions, hierarchical queries, and PL/SQL procedures without inventing a new domain on every page.

CREATE TABLE departments (
    department_id   NUMBER(4)       CONSTRAINT pk_departments PRIMARY KEY,
    department_name VARCHAR2(50)    NOT NULL,
    location        VARCHAR2(50)
);

CREATE TABLE employees (
    employee_id     NUMBER(6)       CONSTRAINT pk_employees PRIMARY KEY,
    first_name      VARCHAR2(30)    NOT NULL,
    last_name       VARCHAR2(30)    NOT NULL,
    email           VARCHAR2(100)   NOT NULL,
    hire_date       DATE            DEFAULT SYSDATE NOT NULL,
    job_title       VARCHAR2(50),
    salary          NUMBER(8,2)     CHECK (salary > 0),
    department_id   NUMBER(4),
    manager_id      NUMBER(6),
    CONSTRAINT uq_employees_email UNIQUE (email),
    CONSTRAINT fk_employees_department FOREIGN KEY (department_id)
        REFERENCES departments (department_id),
    CONSTRAINT fk_employees_manager FOREIGN KEY (manager_id)
        REFERENCES employees (employee_id)
);

Keep this schema in mind — queries-and-joins, the analytic-functions page, PL/SQL, stored procedures, indexing, transactions, and views all reuse employees/departments directly rather than re-explaining a new schema each time. manager_id referencing employees itself (a self-referencing foreign key) is what makes the hierarchical CONNECT BY queries later in this track possible — every employee optionally points at another row in the same table as their manager.

Oracle's core data types#

Oracle's type system looks superficially like other databases' but has enough real differences to trip up anyone arriving from MySQL or PostgreSQL.

Character data: VARCHAR2, CHAR, and why not VARCHAR#

VARCHAR2(n) is Oracle's standard variable-length string type, and it's what you should reach for by default — n is a maximum length (in bytes by default, or characters depending on the database's NLS_LENGTH_SEMANTICS setting), not a fixed allocation. Oracle also has a plain VARCHAR type, but the documentation has warned for decades that it's reserved for potential future redefinition and should never be used — always write VARCHAR2, never VARCHAR, even though many other databases treat that name as the normal choice.

CHAR(n) is fixed-length and blank-pads shorter values out to n characters on storage — CHAR(1) is genuinely useful for a true single-character flag ('Y'/'N'), but using CHAR for anything longer wastes space and introduces trailing-space comparison surprises that VARCHAR2 doesn't have.

Numbers: NUMBER(p,s)#

NUMBER(precision, scale) is Oracle's one general-purpose numeric type — there's no separate INT/FLOAT/DECIMAL family the way MySQL or PostgreSQL have (Oracle does provide INTEGER and a few others as subtypes, but they're implemented as NUMBER underneath). precision is the total number of significant digits allowed; scale is how many of those sit after the decimal point.

salary NUMBER(8,2)   -- up to 8 significant digits, 2 after the decimal: max 999999.99

NUMBER with no precision/scale at all stores any value Oracle can represent, positive or negative, with up to 38 digits of precision — convenient, but it's worth declaring precision and scale explicitly on money and quantity columns so an application bug (accidentally storing a huge or overly-precise value) fails fast as a constraint violation instead of silently succeeding.

Dates and times: DATE vs TIMESTAMP#

This is the single most common surprise for anyone new to Oracle. DATE in Oracle always stores a year, month, day, hour, minute, and second — there is no "just a date with no time" type. A column declared DATE with no time explicitly given defaults its time portion to midnight (00:00:00), which matters the moment you filter on it:

-- Misses any hire_date stored with a non-midnight time component, e.g. '2026-03-15 09:30:00'
SELECT * FROM employees WHERE hire_date = DATE '2026-03-15';

TIMESTAMP extends this with fractional seconds (TIMESTAMP(6) stores microsecond precision by default), and TIMESTAMP WITH TIME ZONE / TIMESTAMP WITH LOCAL TIME ZONE additionally carry time zone information. For a column that's genuinely a business date (hire_date, order_date) DATE is the conventional and correct choice in Oracle, despite always carrying a time component — for anything needing sub-second precision or explicit time zones, reach for TIMESTAMP.

Large objects: CLOB and BLOB#

VARCHAR2 tops out at 4,000 bytes in a table column by default (32,767 with MAX_STRING_SIZE = EXTENDED enabled, an instance-level setting). For genuinely large text — a document body, a long JSON payload — use CLOB (character large object). For binary data — an image, a PDF, a file upload — use BLOB (binary large object). Both are stored efficiently out-of-line from the row itself and streamed rather than loaded as one in-memory value, which is what makes them suitable for genuinely large content in a way a wide VARCHAR2 isn't.

ROWID#

Every row in an Oracle table has a ROWID — a pseudo-column giving the row's physical address on disk (the data file, block, and row-within-block it currently lives in). It isn't a column you define; every table has one implicitly, and it's usually the fastest possible way to re-locate a specific row you already fetched a moment ago:

SELECT ROWID, employee_id, last_name FROM employees WHERE department_id = 10;

ROWID is genuinely fast to look up by, but it isn't a stable long-term identifier — operations like moving a row between partitions, an export/import, or certain table reorganizations can change a row's ROWID. Never store a ROWID value in another table as if it were a durable foreign key; that's what the primary key is for. (ROWID does show up as a legitimate, deliberate tool for one specific job — deleting duplicate rows — on the tricky-queries page later in this track.)

Constraints#

Oracle supports the standard constraint set, and the schema above already uses all of them:

• PRIMARY KEY — uniquely identifies each row; Oracle automatically creates a unique index backing it.
• FOREIGN KEY ... REFERENCES ... — enforces that a column's value must exist in the referenced table's key column (or be NULL), maintaining referential integrity between employees and departments.
• NOT NULL — rejects a row where that column is left unset.
• UNIQUE — like a primary key, but a table can have several, and (unlike the primary key) the column is still allowed to be NULL.
• CHECK — an arbitrary boolean expression a row must satisfy, such as CHECK (salary > 0) above.

Naming constraints explicitly with CONSTRAINT constraint_name (rather than leaving them anonymous) is worth the extra typing — an anonymous constraint gets an unreadable system-generated name like SYS_C0012345, which makes a constraint-violation error message far harder to diagnose months later than one that says violated constraint (HR.FK_EMPLOYEES_DEPARTMENT).

ALTER TABLE#

Schemas evolve. Adding a column, adding a constraint after the fact, and modifying an existing column all use ALTER TABLE:

ALTER TABLE employees ADD (phone_number VARCHAR2(20));

ALTER TABLE employees ADD CONSTRAINT chk_employees_salary_positive CHECK (salary > 0);

ALTER TABLE employees MODIFY (job_title VARCHAR2(80));

Adding a NOT NULL column to a table that already has rows requires either a DEFAULT value (so existing rows get populated automatically) or doing it in two steps — add it nullable, backfill it, then add the NOT NULL constraint — since Oracle otherwise has no value to put in the new column for rows that already exist.

Common mistakes#

• Writing VARCHAR instead of VARCHAR2 out of habit from another database — it happens to work today, but it's explicitly documented as reserved for a future, different meaning. Always use VARCHAR2.
• Treating DATE as if it only stores a calendar date, then being confused when an equality filter against a literal date misses rows — remember every DATE value carries a time component, defaulting to midnight only if none was given.
• Declaring NUMBER with no precision or scale on a money column, then discovering an application bug quietly stored 19.999999 instead of failing the way NUMBER(8,2) would have.
• Leaving constraints anonymous and only noticing when a cryptic SYS_C00... constraint name shows up in a production error log with no indication which business rule it was protecting.