Applies when writing or reviewing Oracle SQL and PL/SQL. Covers NUMBER/VARCHAR2, MERGE, bulk operations, partitioning, and SQL Plan Management.
NUMBER(p,s) with explicit precision. NUMBER(10) for integers, NUMBER(19,4) for money. Unqualified NUMBER hides intent.VARCHAR2 over CHAR. CHAR pads with spaces, wastes storage, causes comparison surprises.TIMESTAMP WITH TIME ZONE for cross-timezone temporal data. TIMESTAMP WITH LOCAL TIME ZONE for auto-conversion to session timezone.CLOB/BLOB for large objects, not LONG (deprecated, one per table, no indexing).DATE includes a time component (unlike SQL standard). Use TIMESTAMP when you need sub-second precision.GENERATED ALWAYS AS IDENTITY (12c+) for auto-increment PKs. Falls back to SEQUENCE objects for cross-table sharing.MERGE for upserts:
MERGE INTO inventory tgt
USING (SELECT 101 AS product_id, 5 AS qty FROM dual) src
ON (tgt.product_id = src.product_id)
WHEN MATCHED THEN UPDATE SET tgt.qty = tgt.qty + src.qty
WHEN NOT MATCHED THEN INSERT (product_id, qty) VALUES (src.product_id, src.qty);
CONNECT BY for portability. Include a depth counter for termination.CREATE INDEX ix_upper_email ON users (UPPER(email)).ALTER INDEX ix_name INVISIBLE to test dropping without committing.FORALL for bulk DML. Sends all DML as a single round-trip:
FORALL i IN 1..ids.COUNT
DELETE FROM orders WHERE id = ids(i);
BULK COLLECT ... LIMIT for batch fetching. Control memory with LIMIT 1000.DROP PARTITION.DBMS_SPM) for plan stability.DBMS_STATS.GATHER_TABLE_STATS after large data changes. Stale statistics are the #1 cause of bad plans.CURSOR_SHARING = FORCE as last resort.WHERE date_col = '2025-01-01' depends on NLS_DATE_FORMAT. Always use DATE '2025-01-01' or TO_DATE('2025-01-01', 'YYYY-MM-DD').'' IS NULL is TRUE. Non-standard; plan for this when porting.ROWNUM filters before ORDER BY. Wrap in subquery: SELECT * FROM (SELECT ... ORDER BY col) WHERE ROWNUM <= 10. Or use FETCH FIRST N ROWS ONLY (12c+).FOR UPDATE SKIP LOCKED (11g+) for job-queue patterns.