DATA TYPES 10

 NUMBER

1. Used to store numeric values (integers and decimals).
2. Syntax: NUMBER(p,s) — where p = precision, s = scale.
3. Can store very large or very small numbers.
4. Defaults to floating-point if precision and scale are not specified.
5. Supports arithmetic operations directly.
6. Precision can be up to 38 digits.
7. Scale can be negative for rounding.
8. Commonly used for IDs, quantities, prices, etc.
9. Indexable and supports constraints.
10. Compatible with most numeric SQL functions.

 

CHAR

1. Fixed-length character data type.
2. Padding with spaces if input is shorter than defined length.
3. Syntax: CHAR(n) — n = number of characters.
4. Max size: 2000 bytes.
5. Useful for storing codes (e.g., country codes).
6. Case-sensitive.
7. Comparisons consider trailing spaces.
8. Slower than VARCHAR2 for varying-length text.
9. Can be indexed.
10. Stores character data based on the database character set.

 

VARCHAR2

1. Variable-length character data.
2. Syntax: VARCHAR2(n) — n = max number of characters.
3. Max size: 4000 bytes (extended up to 32767 in some configurations).
4. More efficient than CHAR for dynamic-length text.
5. Does not pad trailing spaces.
6. Common for names, addresses, descriptions, etc.
7. Case-sensitive.
8. Can be indexed.
9. Supports functions like UPPER, LOWER, SUBSTR.
10. Most commonly used string type in Oracle.

 

NCHAR

1. Fixed-length Unicode character type.
2. Stores data in the national character set.
3. Syntax: NCHAR(n) — stores exactly n characters.
4. Max size: 2000 bytes.
5. Good for multilingual data storage.
6. Pads with Unicode spaces if input is shorter.
7. Slower than CHAR due to Unicode handling.
8. Case-sensitive and locale-aware.
9. Can be indexed.
10. Useful for global applications.

 

DATE

1. Stores date and time (down to seconds).
2. Default format: 'DD-MON-YY' (can vary by NLS settings).
3. Range: Jan 1, 4712 BC to Dec 31, 9999 AD.
4. Includes time (HH24:MI:SS).
5. Can use functions like SYSDATE, ADD_MONTHS, LAST_DAY.
6. Commonly used for timestamps and scheduling.
7. Indexable.
8. Supports arithmetic (e.g., date1 - date2 gives number of days).
9. Time zone not stored.
10. Most basic temporal type in Oracle.

 

TIMESTAMP

1. Extends DATE to include fractional seconds.
2. Syntax: TIMESTAMP [(fractional_seconds_precision)].
3. Default fractional precision is 6 digits.
4. No time zone info stored.
5. Useful when high-precision time is required.
6. Can compare and perform arithmetic like DATE.
7. Supports functions like CURRENT_TIMESTAMP.
8. Indexable.
9. Can be cast to/from DATE.
10. Suitable for detailed event logging.

 

TIMESTAMP WITH TIME ZONE

1. Adds time zone to the TIMESTAMP data.
2. Syntax: TIMESTAMP WITH TIME ZONE.
3. Stores exact point in time globally.
4. Time zone offset saved with data.
5. Good for global systems (e.g., booking platforms).
6. Handles daylight saving time.
7. Supports CURRENT_TIMESTAMP with time zone.
8. Can convert to/from local time.
9. Allows comparing times across zones.
10. Used in distributed systems and audits.

 

TIMESTAMP WITH LOCAL TIME ZONE

1. Stores time in database time zone, shows in user's local time zone.
2. Time zone info is not stored permanently.
3. Useful when display should reflect user's location.
4. Ideal for user-facing applications.
5. Converted automatically on insert/retrieval.
6. Prevents confusion due to time zone differences.
7. Not suitable if you need to know original time zone.
8. Reduces data size vs. WITH TIME ZONE.
9. Indexable.
10. Helps maintain uniform storage with flexible display.

 

INTERVAL YEAR TO MONTH

1. Stores a duration in years and months.
2. Syntax: INTERVAL YEAR (p) TO MONTH.
3. Used to represent elapsed time (e.g., 2 years 3 months).
4. Cannot be added directly to TIMESTAMP.
5. Works with ADD_MONTHS, NUMTODSINTERVAL, etc.
6. Precise for billing cycles or age calculations.
7. Can be positive or negative.
8. Not affected by varying days in months.
9. Useful for contracts and subscriptions.
10. Not timezone-aware.

 

INTERVAL DAY TO SECOND

1. Stores a duration in days, hours, minutes, seconds.
2. Syntax: INTERVAL DAY (p1) TO SECOND (p2).
3. Useful for representing precise durations.
4. Can include fractional seconds.
5. Often used for tracking elapsed time.
6. Supports date arithmetic.
7. Positive or negative durations supported.
8. Works with NUMTODSINTERVAL.
9. Great for measuring performance or delays.
10. Time zone-independent.

 

RAW

1. Stores binary or byte data (not interpreted as characters).
2. Syntax: RAW(n) — n = number of bytes.
3. Max size: 2000 bytes.
4. Used for UUIDs, keys, encryption data.
5. Not human-readable.
6. Not converted between systems.
7. Cannot be indexed using B-tree.
8. Supports HEXTORAW, RAWTOHEX.
9. Works with PL/SQL collections.
10. Use when exact binary values must be preserved.

 

LONG RAW

1. Like RAW, but supports larger binary data.
2. Max size: 2 GB.
3. Deprecated in favor of BLOB.
4. Used for legacy systems storing binary data.
5. Cannot be indexed.
6. Only one LONG RAW column per table.
7. Not supported in many SQL functions.
8. Harder to work with compared to LOBs.
9. Stored out-of-line in large objects area.
10. Consider migrating to BLOB.

 

CLOB (Character LOB)

1. Stores large blocks of text (up to 4 GB).
2. Supports character data (based on DB charset).
3. Suitable for documents, descriptions, XML, etc.
4. Supports streaming reads/writes in PL/SQL.
5. Stored out-of-line by default.
6. Can be indexed (with limitations).
7. Supports functions like DBMS_LOB.
8. Slower than VARCHAR2 for short text.
9. Often used in web apps and APIs.
10. Requires special handling in SQL queries.

 

NCLOB (National CLOB)

1. Like CLOB, but stores Unicode characters.
2. Useful for multilingual large text.
3. Stored using national character set.
4. Max size: 4 GB.
5. Slower than CLOB due to Unicode.
6. Supports full LOB operations.
7. Used in internationalized applications.
8. Not all clients support NCLOB well.
9. Requires special care for charset conversions.
10. Rarely used unless multi-language is essential.

 

BLOB (Binary LOB)

1. Stores large binary data (images, videos, etc.).
2. Max size: 4 GB.
3. Not human-readable.
4. Can be accessed via DBMS_LOB.
5. Stored out-of-line.
6. Useful for file storage in DB.
7. Supports streaming and chunking.
8. Cannot be directly queried like text.
9. Often used with base64 encoding in apps.
10. Performance depends on LOB storage options.

 

BFILE

1. Stores reference to a binary file in the OS.
2. File is outside the database.
3. Read-only access.
4. Max size: 4 GB.
5. Useful for external file management.
6. Needs directory object defined in DB.
7. Faster access than BLOB for static files.
8. Not backed up with DB by default.
9. Security must be managed at OS level.
10. Great for integration with file systems.

 

ROWID

1. Represents physical address of a row in a table.
2. Unique within a table.
3. Fastest way to access a row.
4. Format is block.row.file.
5. Changes if row is moved.
6. Used internally by Oracle.
7. Can be selected in queries.
8. Helps in debugging and performance tuning.
9. Cannot be used across tables.
10. Not human-readable.

 

UROWID (Universal ROWID)

1. Extends ROWID to support index-organized tables (IOTs).
2. Can represent logical row addresses.
3. Useful when physical ROWID doesn't exist.
4. Useful in distributed databases.
5.  Format is longer than ROWID.
6. Can reference rows in foreign systems.
7. Can be stored as column data.
8. Suitable for object-relational features.
9. Human-readable (to an extent).
10. Preferred when ROWID isn't sufficient.

 

XMLTYPE

1. Used to store and query XML data.
2. Can store XML as CLOB or object-relational.
3. Enables XPath and XQuery support.
4. Ideal for web services, APIs, configs.
5. Supports indexing with XML indexes.
6. Can validate against XML Schema.
7. Can extract parts of XML using EXTRACTVALUE, XMLTABLE.
8. Supported in PL/SQL and SQL.
9. Useful for structured documents.
10. Performance depends on storage model (binary vs. structured).