Oracle DB

Syntax Differences

• ROWNUM for limiting rows—WHERE ROWNUM <= 10; 12c+ supports FETCH FIRST 10 ROWS ONLY
• DUAL table for expressions—SELECT sysdate FROM dual
• VARCHAR2 not VARCHAR—VARCHAR is reserved, VARCHAR2 is the standard
• String concatenation with ||—not CONCAT for multiple values
• Empty string equals NULL—'' IS NULL is true; breaks logic from other databases

Pagination

• ROWNUM assigned before ORDER BY—wrap in subquery: SELECT * FROM (SELECT ... ORDER BY x) WHERE ROWNUM <= 10
• Offset requires nested subquery: SELECT * FROM (SELECT a.*, ROWNUM rn FROM (...) a WHERE ROWNUM <= 20) WHERE rn > 10
• 12c+: OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY—cleaner, use when available

NULL Handling