Oracle DB

--- name: Oracle DB description: Write Oracle SQL and PL/SQL with proper syntax, hints, and performance patterns. metadata: {"clawdbot":{"emoji":"🔴","requires":{"anyBins":["sqlplus","sql"]},"os":["linux","darwin","win32"]}} ---

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

• NVL(col, default) for null replacement—faster than COALESCE for two args
• NVL2(col, if_not_null, if_null) for conditional—common Oracle pattern
• Empty string is NULL—LENGTH('') returns NULL, not 0
• NULLIF(a, b) returns NULL if equal—useful for avoiding division by zero

Dates

• SYSDATE for current datetime—no parentheses
• TO_DATE('2024-01-15', 'YYYY-MM-DD') for string to date—format required
• TO_CHAR(date, 'YYYY-MM-DD HH24:MI:SS') for date to string
• Date arithmetic in days—SYSDATE + 1 is tomorrow, SYSDATE + 1/24 is one hour

Sequences

• Create: CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1
• Get next: seq_name.NEXTVAL—SELECT seq_name.NEXTVAL FROM dual
• Current value: seq_name.CURRVAL—only after NEXTVAL in same session
• 12c+: identity columns—GENERATED ALWAYS AS IDENTITY

Hierarchical Queries

• CONNECT BY PRIOR child = parent for tree traversal
• START WITH parent IS NULL for root nodes
• LEVEL pseudo-column shows depth—WHERE LEVEL <= 3 limits depth
• SYS_CONNECT_BY_PATH(col, '/') builds path string

Bind Variables

• Always use bind variables—literals cause hard parse every time
• PL/SQL: :variable_name syntax
• Performance critical—literal values fill shared pool, cause contention
• CURSOR_SHARING=FORCE as workaround but not recommended long-term

Hints

• /+ INDEX(table idx_name) / forces index use
• /+ FULL(table) / forces full table scan
• /+ PARALLEL(table, 4) / enables parallel query
• Hints inside SELECT /+ hint /—common placement after SELECT keyword

PL/SQL Blocks

• Anonymous block: BEGIN ... END; with / on new line to execute
• DBMS_OUTPUT.PUT_LINE() for debug output—SET SERVEROUTPUT ON first
• Exception handling: EXCEPTION WHEN OTHERS THEN—always handle or log
• EXECUTE IMMEDIATE 'sql string' for dynamic SQL—beware injection

Transactions

• No auto-commit by default—must COMMIT explicitly
• SAVEPOINT name then ROLLBACK TO name for partial rollback
• DDL auto-commits—CREATE TABLE commits any pending transaction
• SELECT FOR UPDATE WAIT 5 waits 5 seconds for lock—avoids indefinite hang

Performance

• EXPLAIN PLAN FOR sql; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)—shows plan
• V$SQL and V$SESSION for monitoring—requires privileges
• Avoid SELECT *—fetches all columns including LOBs
• Index hint when optimizer chooses wrong—/+ INDEX(t idx) /

Common Traps

• MINUS instead of EXCEPT—Oracle uses MINUS for set difference
• DECODE is Oracle-specific—use CASE for portability
• Implicit type conversion—WHERE num_col = '123' works but prevents index use
• ROWID is physical—don't store or rely on across transactions