# Data Types ## About Oracle provides a rich set of data types that extend beyond the standard SQL types to support advanced features and performance optimizations. In Oracle Database, **data types** define the kind of values a column, variable, or expression can hold. Every data element in Oracle must be declared with an appropriate type that governs: * The format and size of stored data * The set of valid operations * The amount of space allocated in memory or disk * Compatibility with other types during operations like joins or comparisons Oracle offers both **standard scalar types** (such as numbers, text, and dates) and **advanced or abstract types** (LOBs, XML, objects). These types are broadly grouped into categories, each optimized for a specific domain of data representation. ## 1. Character (Text) Data Types ### Description Character types are used to store text in either fixed or variable length. Oracle supports both **single-byte character sets** (like ASCII) and **multi-byte Unicode character sets**. * **Character semantics vs byte semantics**: Length can be interpreted in number of characters or number of bytes. * **Padding behavior**: Fixed-length types are padded with spaces to meet their defined size. * **Encoding awareness**: `NCHAR`, `NVARCHAR2`, and `NCLOB` are designed for internationalization. {% hint style="info" %} `VARCHAR` is reserved for possible future standardization and should be avoided in production schemas. For Unicode support, Oracle recommends using `AL32UTF8` character set with `NCHAR`/`NVARCHAR2` {% endhint %} ### Types

Data Type	Description	Key Characteristics	Example Value
`CHAR(n)`	Fixed-length character string, space-padded to length `n`	- Always stores `n` bytes - Padded with spaces if shorter	`'Y'` is stored as `'Y '` in case CHAR(2)
`VARCHAR2(n)`	Variable-length character string up to `n` bytes	- Efficient storage - No padding	`'Hello'` stored as `'Hello'`
`VARCHAR(n)`	ANSI SQL equivalent, internally treated as `VARCHAR2`	- Supported but not recommended - Future behavior undefined	`'Hi'` stored as `'Hi'`
`NCHAR(n)`	Fixed-length Unicode character string	- Unicode-aware - Always `n` characters (not bytes)	`'नमस्ते'` stored with padding
`NVARCHAR2(n)`	Variable-length Unicode character string up to `n` characters	- Unicode-aware - No padding	`'你好'` stored as-is

### **Byte and Character Limits** Oracle imposes specific **maximum limits** depending on the data type and database settings (especially character set and semantics). {% hint style="info" %} **Character semantics**: If the database or column uses character semantics, the limits apply to characters instead of bytes. This is useful in multibyte/Unicode character sets. {% endhint %}

Data Type	Max Length (Bytes)	Max Length (Characters)	Notes
`CHAR(n)`	2000 bytes	Depends on byte-per-char	Fixed-length; may waste space if short data is stored.
`VARCHAR2(n)`	4000 bytes (SQL), 32767 bytes (PL/SQL)	Depends on byte-per-char	Most common string type; `n` is byte count unless `CHAR` semantics used.
`NCHAR(n)`	2000 bytes	1000 characters (UTF-16)	Always uses national character set (usually UTF-16).
`NVARCHAR2(n)`	4000 bytes (SQL), 32767 bytes (PL/SQL)	2000 characters (UTF-16)	Used for Unicode multilingual data.
`VARCHAR(n)`	Same as `VARCHAR2`	Deprecated	Reserved by Oracle; avoid using.

{% hint style="success" %} In Oracle, `VARCHAR2(36 BYTE)` and `VARCHAR2(36 CHAR)` both define a column with a maximum length of 36 characters. However, they differ in how they handle character encoding. 1. `VARCHAR2(36 BYTE)`: This specifies that the column can store up to 36 bytes of data. In a single-byte character set (like ASCII or ISO-8859-1), each character occupies one byte. So, in this case, we could store up to 36 characters if each character requires only one byte of storage. However, in a multi-byte character set (like UTF-8), some characters may require more than one byte to store. Therefore, we may not be able to store as many characters as the specified length suggests. 2. `VARCHAR2(36 CHAR)`: This specifies that the column can store up to 36 characters. Regardless of the character set being used, Oracle will ensure that we can store up to 36 characters. If we are using a single-byte character set, this behaves the same as `VARCHAR2(36 BYTE)`. But if we are using a multi-byte character set, Oracle will allocate enough space to store up to 36 characters, even if it requires more than 36 bytes. In summary, the difference lies in how Oracle determines the maximum storage space for characters. The `BYTE` qualifier considers the number of bytes, while the `CHAR` qualifier considers the number of characters, potentially leading to different behavior in multi-byte character sets. {% endhint %} ### **Storage and Performance Implications**

Data Type	Storage Efficiency	Performance Impact	Best Use Case
`CHAR(n)`	Low (always fixed)	Slower for short values	Fields with consistent, small size (e.g., 'Y'/'N')
`VARCHAR2(n)`	High	Fast and efficient	General-purpose strings
`NCHAR(n)`	Medium	Slight overhead (Unicode)	Fixed-width internationalized values
`NVARCHAR2(n)`	Medium-High	More space needed (UTF-16)	Multilingual applications
`VARCHAR(n)`	Same as `VARCHAR2`	No gain; discouraged	Not recommended

## 2. Numeric Data Types ### Description Numeric types in Oracle are highly flexible and configurable. They support exact (fixed-point) and approximate (floating-point) representations. * **Precision (p)**: Total number of significant digits. * **Scale (s)**: Number of digits to the right of the decimal point. * **Overflow and rounding**: Operations that exceed scale may be rounded or truncated based on context. * **Binary vs decimal**: Oracle supports both binary floats and precise decimal arithmetic. ### Types Oracle provides two main categories of numeric types: 1. **Exact Numeric Types** * `NUMBER` * `INTEGER`, `INT`, `SMALLINT`, `DEC`, `DECIMAL`, `NUMERIC` (ANSI-compatible synonyms) 2. **Approximate Numeric Types** * `BINARY_FLOAT` * `BINARY_DOUBLE` ### `NUMBER(p, s)` – General Purpose Numeric Type * A variable-precision numeric type that can store integers and decimals. * `p` is **precision** (total number of significant digits). * `s` is **scale** (number of digits to the right of the decimal point). #### Ranges * Maximum precision: **38 digits** * Scale range: `-84` to `127` #### Examples

Declaration	Description	Acceptable Values
`NUMBER`	Maximum flexibility. Default precision/scale.	`42`, `3.1415`, `-9999.99`
`NUMBER(5)`	Up to 5 digits. No decimal.	`12345`, `-9999`
`NUMBER(7, 2)`	7 digits total, 2 after decimal.	`12345.67`
`NUMBER(*, 0)`	Any number of digits, no decimal.	Integer values only

#### Behavior * If the number exceeds the specified precision, Oracle raises an error. * If scale is higher than actual decimals, Oracle appends trailing zeroes. * If scale is lower, Oracle rounds the value. **ANSI-Compatible Synonyms** These are alternative names mapped to `NUMBER`:

Synonym	Oracle Equivalent
`INTEGER`	`NUMBER(38,0)`
`INT`	`NUMBER(38,0)`
`SMALLINT`	`NUMBER(38,0)`
`DEC`	`NUMBER(p,s)`
`DECIMAL`	`NUMBER(p,s)`
`NUMERIC`	`NUMBER(p,s)`

### `FLOAT(p)` – Approximate with Decimal Precision * Floating-point number with `p` bits of binary precision. * `FLOAT(p)` is internally treated as `NUMBER(p)`. {% hint style="info" %} Although named `FLOAT`, Oracle does **not** use IEEE floating point here unless we use `BINARY_FLOAT` or `BINARY_DOUBLE`. {% endhint %} #### Example * `FLOAT(10)` means up to 10 digits of precision. Use `FLOAT` with caution as its behavior may be misleading—use `BINARY_FLOAT` or `BINARY_DOUBLE` if we truly need floating-point arithmetic. **Example 1: Basic FLOAT column** ```sql CREATE TABLE sample_float_table ( id NUMBER, value FLOAT -- same as NUMBER with default precision (usually 126) ); ``` We can insert values like: ```sql INSERT INTO sample_float_table (id, value) VALUES (1, 12345.6789); INSERT INTO sample_float_table (id, value) VALUES (2, -0.000345); ``` **Example 2: FLOAT with precision** ```sql CREATE TABLE precise_float_table ( id NUMBER, value FLOAT(10) -- same as NUMBER(10) ); ``` This means the column `value` can store **up to 10 significant digits**, e.g., ```sql INSERT INTO precise_float_table VALUES (1, 123456.7890); -- OK INSERT INTO precise_float_table VALUES (2, 1234567890.12); -- Error or rounded ``` ### `BINARY_FLOAT` and `BINARY_DOUBLE` – Binary Floating Point Types These types use IEEE 754 format for representing approximate decimal numbers.

Data Type	Size	Precision	Use Case
`BINARY_FLOAT`	32 bits	~6 digits	Fast approximate calculations
`BINARY_DOUBLE`	64 bits	~15 digits	Higher precision computations

#### Key Characteristics * Support **NaN**, **positive/negative infinity**, and **subnormal numbers**. * **Faster** than `NUMBER` due to hardware-level operations. * Cannot be indexed using B-tree indexes. * Not exact—do not use in financial or accounting systems. #### Examples ```sql CREATE TABLE temperature_data ( city_name VARCHAR2(100), avg_temp BINARY_FLOAT ); INSERT INTO temperature_data VALUES ('Delhi', 42.57); ``` ### NUMBER vs BINARY\_FLOAT/DOUBLE | Feature | `NUMBER` | `BINARY_FLOAT` / `DOUBLE` | | ----------- | ------------------ | ------------------------- | | Precision | Exact (up to 38 d) | Approximate | | Storage | Variable | Fixed (4 or 8 bytes) | | Performance | Slower | Faster | | Usage | Financial | Scientific, statistical | | Indexing | B-tree supported | Not supported | | Rounding | Controlled | IEEE-based | ## 3. Date & Time Data Types ### Description Temporal types store points in time or durations. Oracle aligns partially with SQL standards but also adds its own enhancements for timezone-aware storage and interval management. * **Oracle’s `DATE` includes time**: Unlike standard SQL, `DATE` in Oracle includes both date and time down to seconds. * **Fractional seconds and precision**: Timestamps support up to 9 digits of fractional seconds. * **Timezone handling**: Oracle supports both session-relative and absolute time zones. * **Intervals as durations**: Represent spans of time, not absolute moments. ### `DATE` Stores **both date and time values**, accurate to the **second**. This is the most basic and commonly used temporal type in Oracle. * Internally stored as **7 bytes**: * Century, Year, Month, Day, Hour, Minute, Second * Default format: `DD-MON-YY` (can be changed via `NLS_DATE_FORMAT`) * Does **not** support fractional seconds or time zones. ```sql DATE '2024-06-05' TO_DATE('2024-06-05 14:30:00', 'YYYY-MM-DD HH24:MI:SS') ``` ### `TIMESTAMP` Extends `DATE` by including **fractional seconds** (up to 9 digits of precision). Used where higher accuracy is required. * Default fractional precision is **6 digits**. * Internally stored as **11 bytes**. * No time zone support. #### Syntax: ```sql TIMESTAMP [(fractional_seconds_precision)] ``` ```sql TIMESTAMP '2024-06-05 14:30:00.123456' ``` ### `TIMESTAMP WITH TIME ZONE` (abbreviated: `TIMESTAMP WITH TZ`) Adds **time zone offset** information to a timestamp, making it suitable for storing absolute moments in time globally. * Stores time in **UTC** and retains the time zone offset. * Time zone can be expressed as an offset (e.g., `+05:30`) or region name (e.g., `'Asia/Kolkata'`). * Useful for **distributed systems**. #### Syntax: ```sql TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE ``` ```sql TIMESTAMP '2024-06-05 14:30:00.123456 +05:30' ``` ### `TIMESTAMP WITH LOCAL TIME ZONE` (abbreviated: `TIMESTAMP WITH LTZ`) Stores the timestamp in **UTC internally**, but **displays it in the user's session time zone**. Useful for applications with users across time zones. * Time zone information is **not stored**, but inferred from session. * Automatically adjusted during storage and retrieval. * Ideal for **user-facing logs or event timestamps**. #### Syntax: ```sql TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE ``` ```sql TIMESTAMP '2024-06-05 14:30:00.123456' AT LOCAL ``` ### `INTERVAL YEAR TO MONTH` Represents a **duration** (not a point in time) in terms of years and months. * Used to **add/subtract** months or years to/from a date. * Cannot include days, hours, or minutes. * Useful in **business date arithmetic** (e.g., contract terms). ```sql INTERVAL YEAR [(precision)] TO MONTH ``` ```sql INTERVAL '2-3' YEAR TO MONTH -- 2 years, 3 months ``` ### `INTERVAL DAY TO SECOND` Represents a **duration** in days, hours, minutes, seconds, and fractional seconds. * Supports up to 9 digits of fractional second precision. * Used for **time spans**, e.g., tracking elapsed time. #### Syntax: ```sql INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] ``` ```sql INTERVAL '5 12:30:15.123456' DAY TO SECOND -- 5 days, 12 hours, 30 minutes, 15.123456 seconds ``` ## 4. Large Object (LOB) Data Types ### Description Large Object (LOB) data types in Oracle are designed to store **large blocks of unstructured data**, such as: * Large text (e.g. articles, emails, XML) * Binary files (e.g. images, PDFs, videos) * External file references (e.g. stored on the OS filesystem) LOBs are essential for enterprise systems dealing with document management, media storage, or data lakes inside databases. * Can store data up to **4 GB** and beyond (depending on configuration and database block size). * Stored **separately from table rows**, though the row holds a locator to the LOB data. * Supports **streaming APIs** for efficient reading/writing. * Supports **transactional consistency** (except `BFILE`). * Can be **deferred-loaded** (loaded only when accessed), saving memory. ### LOB Types

LOB Type	Description	Use Case
`CLOB` (Character LOB)	Stores large character data in database character set	Text documents, HTML, XML
`NCLOB` (National CLOB)	Same as CLOB, but uses national character set (Unicode)	Multilingual documents
`BLOB` (Binary LOB)	Stores large binary data	Images, videos, PDFs, encrypted files
`BFILE`	Stores a pointer to a file in the OS file system (read-only)	External file referencing

### Internal LOB vs External LOB


Feature	Internal LOB (`BLOB`, `CLOB`, `NCLOB`)	External LOB (`BFILE`)
Stored In	Database tablespace	OS file system
Read/Write	Fully readable and writable	Read-only from SQL
Transactional	Yes	No
Secure	Protected under Oracle security	Depends on OS permissions
Backup	Included in database backup	Must back up separately

### Storage Characteristics Oracle stores LOB data **out-of-line** by default (separate from table row), but: * If the LOB data is small (less than \~4 KB), it can be stored **in-line** using `ENABLE STORAGE IN ROW`. * LOBs can use **basicfiles** (legacy) or **securefiles** (modern, better performance & compression). ### When to Use Which LOB Type? | Requirement | Recommended LOB | | ------------------------------ | --------------- | | Long text documents | `CLOB` | | Multilingual/unicode documents | `NCLOB` | | Images, media, encrypted files | `BLOB` | | Link to file outside database | `BFILE` | ### SecureFile LOBs vs BasicFile LOBs Oracle 11g+ introduced **SecureFile LOBs** for better performance and manageability: | Feature | BasicFile | SecureFile | | ------------- | --------- | ----------- | | Compression | No | Yes | | Encryption | No | Yes | | Deduplication | No | Yes | | Space-saving | Minimal | Significant | | Performance | Lower | Higher | Enable SecureFiles like this: ```sql CREATE TABLE image_store ( id NUMBER, img BLOB ) LOB (img) STORE AS SECUREFILE (COMPRESS HIGH ENCRYPT); ``` ### Example #### Step 1: Oracle Table Definition ```sql CREATE TABLE user_photos ( id NUMBER PRIMARY KEY, username VARCHAR2(100), photo BLOB ); ``` #### Step 2: JDBC Code – Insert Image into BLOB ```java import java.sql.*; import java.io.*; public class ImageUploader { public static void main(String[] args) { String url = "jdbc:oracle:thin:@//localhost:1521/orclpdb"; String username = "your_username"; String password = "your_password"; String imagePath = "/path/to/photo.jpg"; try ( Connection conn = DriverManager.getConnection(url, username, password); PreparedStatement pstmt = conn.prepareStatement( "INSERT INTO user_photos (id, username, photo) VALUES (?, ?, ?)"); FileInputStream fis = new FileInputStream(new File(imagePath)) ) { pstmt.setInt(1, 101); pstmt.setString(2, "john_doe"); pstmt.setBinaryStream(3, fis, fis.available()); // set BLOB as stream int rows = pstmt.executeUpdate(); System.out.println("Rows inserted: " + rows); } catch (Exception e) { e.printStackTrace(); } } } ``` #### Step 3: Retrieve Image from Oracle and Save as File ```java import java.sql.*; import java.io.*; public class ImageDownloader { public static void main(String[] args) { String url = "jdbc:oracle:thin:@//localhost:1521/orclpdb"; String username = "your_username"; String password = "your_password"; String outputFile = "/path/to/output.jpg"; try ( Connection conn = DriverManager.getConnection(url, username, password); PreparedStatement pstmt = conn.prepareStatement( "SELECT photo FROM user_photos WHERE id = ?"); ) { pstmt.setInt(1, 101); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { InputStream input = rs.getBinaryStream("photo"); OutputStream output = new FileOutputStream(outputFile); byte[] buffer = new byte[4096]; int bytesRead; while ((bytesRead = input.read(buffer)) != -1) { output.write(buffer, 0, bytesRead); } System.out.println("Image saved to: " + outputFile); output.close(); input.close(); } } catch (Exception e) { e.printStackTrace(); } } } ``` ## Best Practices ### 1. Character (Text) Data Types

Practice	Recommendation
Use variable-length strings	Prefer `VARCHAR2` over `CHAR` to save space and avoid padding overhead.
Avoid deprecated types	Do not use `VARCHAR`; use `VARCHAR2` instead, as Oracle may redefine `VARCHAR`.
Enable character semantics	Use `CHARACTER SET` and `CHARACTER LENGTH SEMANTICS` wisely, especially in multilingual applications.
Use `NCHAR`/`NVARCHAR2` for Unicode	When storing multilingual data (e.g., Chinese, Arabic), use national character types.
Set proper length	Avoid allocating the max (e.g., 4000) unless necessary; oversized columns waste memory and I/O.
Normalize case if required	Store and compare text in consistent case (e.g., all UPPER) for indexing and querying.

### 2. Numeric Data Types

Practice	Recommendation
Use `NUMBER(p,s)` for precision	Always define precision and scale to avoid unexpected rounding or overflow.
Avoid overly large precision	Don't default to `NUMBER` without limits; specify `NUMBER(10,2)` for currency, for example.
Choose integer types wisely	For whole numbers, use `NUMBER(p,0)` (e.g., `NUMBER(5,0)` for counters).
Use `BINARY_FLOAT`/`BINARY_DOUBLE` only for scientific computing	They are faster but less precise—avoid for financial or critical data.
Avoid `FLOAT` for exact values	It's an approximate representation—use only when precision is not critical.
Use constraints for validation	Apply `CHECK` constraints for acceptable numeric ranges when business rules allow.

### 3. Date & Time Data Types | Practice | Recommendation | | --------------------------------------------------------------------------- | ---------------------------------------------------------------------------------- | | Use `TIMESTAMP` instead of `DATE` when fractional seconds matter | `DATE` only stores up to seconds; `TIMESTAMP` supports micro/nanoseconds. | | Prefer `TIMESTAMP WITH TIME ZONE` for globally relevant data | It ensures consistent time tracking across regions. | | Avoid `TIMESTAMP WITH LOCAL TIME ZONE` unless session-based logic is needed | Its behavior depends on user sessions and can lead to confusion. | | Use `INTERVAL` types for durations, not `DATE` subtraction | `INTERVAL` types clearly express intent and avoid conversion issues. | | Normalize time zones in application logic | Ensure consistent time zone use between DB and application layer. | | Avoid storing as `CHAR` or `VARCHAR2` | Store actual dates/times using native types for indexing, sorting, and formatting. | ### 4. Large Object (LOB) Data Types | Practice | Recommendation | | --------------------------------------------- | ------------------------------------------------------------------------------------------------- | | Prefer `CLOB`/`BLOB` over `LONG`/`LONG RAW` | The latter are deprecated and unsupported in many modern features. | | Use `BLOB` for binary content (images, audio) | Do not store binary files as `RAW` or encode them as base64 in text fields. | | Use `CLOB` for large text (documents, logs) | Enables full-text indexing and large-capacity storage. | | Consider chunked access for very large LOBs | Use streaming APIs (`DBMS_LOB`) to read/write in pieces instead of loading full content. | | Use SecureFile LOBs if available | They provide better performance and features (e.g., compression, deduplication). | | Avoid unnecessary LOB indexing | Index only if you need full-text search; LOBs are heavy and slow to scan. | | Store large static files outside database | Use `BFILE` for read-only access to large external content when database storage is not required. | --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://www.pranaypourkar.co.in/the-programmers-guide/database/platform-specific-features/oracle/data-types.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.