Oracle Data Types (The Containers) 📦¶
Prerequisites: SQL Intro
Mentor's Note: Choosing a data type is like picking a container for your luggage. You don't put a suit in a lunchbox. Using the wrong type wastes space and slows down your database. 💡
💻 1. String Types (Text)¶
| Type | Usage | Scenario |
|---|---|---|
| VARCHAR2(n) | Variable length | Name, Address (The Gold Standard) |
| CHAR(n) | Fixed length | Country Codes ('IND', 'USA') |
| NVARCHAR2(n) | Unicode strings | Supporting Gujarati or Hindi text |
💻 2. Numeric Types¶
Oracle uses one main type for everything numeric: NUMBER.
NUMBER(precision, scale)
-- Example: NUMBER(10, 2)
-- Means: 10 digits total, 2 after the decimal (e.g., 99999999.99)
💻 3. Date & Time Types 📅¶
| Type | Detail | Scenario |
|---|---|---|
| DATE | Date + Time | Birthday, Hire Date |
| TIMESTAMP | With milliseconds | Exact login time ⏱️ |
| INTERVAL | A period of time | "Stayed for 3 days and 2 hours" |
🏗️ Architect's Note: VARCHAR2 vs CHAR 🛡️¶
In many databases, CHAR is faster. In Oracle, this is NOT necessarily true.
- The Architect's Secret: CHAR pads your data with blank spaces. If you store 'VD' in a CHAR(10), Oracle stores 'VD '.
- The Trap: When you search WHERE name = 'VD', it might fail because of the extra spaces!
- Tip: Always use VARCHAR2 unless you have a very specific reason for fixed-length strings.
📊 Summary Table¶
| Category | Recommended Type |
|---|---|
| Whole Numbers | NUMBER(10) |
| Money | NUMBER(12, 2) |
| Names/Emails | VARCHAR2(255) |
| Simple Dates | DATE |