ClawHub

Oracle DB

Other

Write Oracle SQL and PL/SQL with proper syntax, hints, and performance patterns.

Install

openclaw skills install 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

  • 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