oracle

What this skill does

# Oracle Database Core Knowledge

> **Deep Knowledge**: Use `mcp__documentation__fetch_docs` with technology: `oracle` for comprehensive documentation.

## Data Types

| Type | Description | Example |
|------|-------------|---------|
| `NUMBER(p,s)` | Numeric (p=precision, s=scale) | `NUMBER(10,2)` |
| `VARCHAR2(n)` | Variable-length string | `VARCHAR2(100)` |
| `CHAR(n)` | Fixed-length string | `CHAR(10)` |
| `NVARCHAR2(n)` | Unicode variable-length | `NVARCHAR2(100)` |
| `DATE` | Date and time (to seconds) | `DATE` |
| `TIMESTAMP` | Date/time with fractions | `TIMESTAMP(6)` |
| `TIMESTAMP WITH TIME ZONE` | With timezone | `TIMESTAMP WITH TIME ZONE` |
| `CLOB` | Large text (up to 4GB) | `CLOB` |
| `BLOB` | Binary data (up to 4GB) | `BLOB` |
| `RAW(n)` | Binary data (up to 2000) | `RAW(16)` |
| `INTERVAL` | Time interval | `INTERVAL YEAR TO MONTH` |

```sql
CREATE TABLE example (
    id NUMBER(10) PRIMARY KEY,
    code VARCHAR2(20) NOT NULL,
    name NVARCHAR2(100),
    price NUMBER(10,2),
    created_date DATE DEFAULT SYSDATE,
    updated_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    description CLOB,
    data BLOB
);
```

## Sequences

```sql
-- Create sequence
CREATE SEQUENCE emp_seq
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999999
    NOCYCLE
    CACHE 20;

-- Use sequence
INSERT INTO employees (id, name) VALUES (emp_seq.NEXTVAL, 'John');

-- Get current value (must call NEXTVAL first in session)
SELECT emp_seq.CURRVAL FROM DUAL;

-- Reset sequence
ALTER SEQUENCE emp_seq RESTART START WITH 1;

-- Drop sequence
DROP SEQUENCE emp_seq;

-- Identity column (12c+)
CREATE TABLE employees (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    name VARCHAR2(100)
);

-- Or GENERATED BY DEFAULT
CREATE TABLE employees (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(100)
);
```

## Synonyms

```sql
-- Private synonym (current schema)
CREATE SYNONYM emp FOR hr.employees;

-- Public synonym (all users)
CREATE PUBLIC SYNONYM emp FOR hr.employees;

-- Use synonym
SELECT * FROM emp;

-- Drop synonym
DROP SYNONYM emp;
DROP PUBLIC SYNONYM emp;
```

## DUAL Table

```sql
-- Built-in single-row table for SELECT without FROM
SELECT SYSDATE FROM DUAL;
SELECT 1 + 1 FROM DUAL;
SELECT USER FROM DUAL;
SELECT SYS_GUID() FROM DUAL;  -- Generate UUID

-- Oracle 23c+ allows SELECT without FROM
SELECT SYSDATE;  -- Works in 23c+
```

## Date/Time Functions

```sql
-- Current date/time
SELECT SYSDATE FROM DUAL;                    -- DATE (no timezone)
SELECT SYSTIMESTAMP FROM DUAL;               -- TIMESTAMP WITH TIME ZONE
SELECT CURRENT_DATE FROM DUAL;               -- Session timezone
SELECT CURRENT_TIMESTAMP FROM DUAL;          -- Session timezone

-- Date arithmetic
SELECT SYSDATE + 7 FROM DUAL;                -- Add 7 days
SELECT SYSDATE - 30 FROM DUAL;               -- Subtract 30 days
SELECT date1 - date2 FROM DUAL;              -- Days between dates

-- Add months
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;

-- Truncate date
SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;    -- First of month
SELECT TRUNC(SYSDATE, 'YEAR') FROM DUAL;     -- First of year

-- Format date
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;

-- Parse date
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_TIMESTAMP('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Extract parts
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;
```

## String Functions

```sql
-- Concatenation
SELECT 'Hello' || ' ' || 'World' FROM DUAL;
SELECT CONCAT('Hello', ' World') FROM DUAL;  -- Only 2 args

-- Substring
SELECT SUBSTR('Hello World', 1, 5) FROM DUAL;  -- 'Hello' (1-based)
SELECT SUBSTR('Hello World', -5) FROM DUAL;    -- 'World' (from end)

-- Length
SELECT LENGTH('Hello') FROM DUAL;              -- 5
SELECT LENGTHB('Hello') FROM DUAL;             -- Bytes

-- Case
SELECT UPPER('hello') FROM DUAL;
SELECT LOWER('HELLO') FROM DUAL;
SELECT INITCAP('hello world') FROM DUAL;       -- 'Hello World'

-- Trim
SELECT TRIM('  hello  ') FROM DUAL;
SELECT LTRIM('  hello') FROM DUAL;
SELECT RTRIM('hello  ') FROM DUAL;
SELECT TRIM('x' FROM 'xxxhelloxxx') FROM DUAL;

-- Replace
SELECT REPLACE('hello', 'l', 'L') FROM DUAL;

-- Padding
SELECT LPAD('123', 10, '0') FROM DUAL;         -- '0000000123'
SELECT RPAD('hello', 10, '.') FROM DUAL;       -- 'hello.....'

-- Position
SELECT INSTR('hello world', 'o') FROM DUAL;    -- 5 (first occurrence)
SELECT INSTR('hello world', 'o', 1, 2) FROM DUAL;  -- 8 (second occurrence)
```

## NULL Handling

```sql
-- NVL: Replace NULL with value
SELECT NVL(commission, 0) FROM employees;

-- NVL2: Different value if NULL vs not NULL
SELECT NVL2(commission, salary + commission, salary) FROM employees;

-- COALESCE: First non-NULL (ANSI SQL)
SELECT COALESCE(phone, mobile, email, 'N/A') FROM contacts;

-- NULLIF: Return NULL if equal
SELECT NULLIF(value1, value2) FROM table1;

-- DECODE (Oracle-specific CASE)
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM users;
```

## Hierarchical Queries

```sql
-- CONNECT BY (Oracle-specific, use recursive CTE in modern code)
SELECT
    employee_id,
    LPAD(' ', 2 * (LEVEL - 1)) || first_name AS name,
    LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY first_name;

-- SYS_CONNECT_BY_PATH
SELECT
    employee_id,
    SYS_CONNECT_BY_PATH(first_name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Modern alternative: Recursive CTE (11g R2+)
WITH hierarchy (employee_id, name, level_num) AS (
    SELECT employee_id, first_name, 1
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.first_name, h.level_num + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM hierarchy;
```

## Partitioning

### Range Partitioning

```sql
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2022 VALUES LESS THAN (DATE '2023-01-01'),
    PARTITION sales_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION sales_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

-- Interval partitioning (auto-create partitions)
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
    PARTITION p_initial VALUES LESS THAN (DATE '2024-01-01')
);
```

### List Partitioning

```sql
CREATE TABLE orders (
    order_id NUMBER,
    region VARCHAR2(20),
    amount NUMBER(10,2)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES ('NY', 'MA', 'CT'),
    PARTITION p_south VALUES ('FL', 'GA', 'TX'),
    PARTITION p_west VALUES ('CA', 'WA', 'OR'),
    PARTITION p_other VALUES (DEFAULT)
);
```

### Hash Partitioning

```sql
CREATE TABLE customers (
    customer_id NUMBER,
    name VARCHAR2(100)
)
PARTITION BY HASH (customer_id)
PARTITIONS 4;
```

### Partition Management

```sql
-- Add partition
ALTER TABLE sales ADD PARTITION sales_2025
    VALUES LESS THAN (DATE '2026-01-01');

-- Drop partition
ALTER TABLE sales DROP PARTITION sales_2022;

-- Truncate partition
ALTER TABLE sales TRUNCATE PARTITION sales_2022;

-- Split partition
ALTER TABLE sales SPLIT PARTITION sales_future
    AT (DATE '2026-01-01')
    INTO (PARTITION sales_2025, PARTITION sales_future);

-- Merge partitions
ALTER TABLE sales MERGE PARTITIONS sales_2022, sales_2023
    INTO PARTITION sales_old;

-- Exchange partition (swap with table)
ALTER TABLE sales EXCHANGE PARTITION sales_2024
    WITH TABLE sales_2024_staging;
```

## Index Types

```sql
-- B-tree (default)
CREATE INDEX idx_emp_name ON employees(last_name);

-- Unique index
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

--