Master VARCHAR2
, NUMBER
, DATE
, and more for efficient data storage and retrieval in Oracle Database.
VARCHAR2
The VARCHAR2
data type stores variable-length character strings. It is commonly used for text data where the length can vary, saving storage space.
Example 1: VARCHAR2 Declaration
CREATE TABLE employees (
employee_id NUMBER(6),
first_name VARCHAR2(20) -- Stores employee's first name, up to 20 characters
);
Explanation This code creates a table employees
with a first_name
column defined as VARCHAR2(20)
, allowing it to store character strings up to 20 bytes long.
Example 2: Inserting VARCHAR2 Data
INSERT INTO employees (employee_id, first_name)
VALUES (101, 'John'); -- Inserts 'John' into the first_name column
Explanation This statement inserts a record into the employees
table, populating the first_name
column with the string 'John'.
Example 3: Updating VARCHAR2 Data
UPDATE employees
SET first_name = 'Jonathan'
WHERE employee_id = 101; -- Updates the first_name for employee_id 101 to 'Jonathan'
Explanation This SQL updates the first_name
of the employee with employee_id
101 to 'Jonathan'.
Example 4: Selecting VARCHAR2 Data
SELECT first_name
FROM employees
WHERE employee_id = 101; -- Retrieves the first_name for employee_id 101
Explanation This query selects and displays the first_name
of the employee whose employee_id
is 101.
Example 5: VARCHAR2 with NULL
INSERT INTO employees (employee_id, first_name)
VALUES (102, NULL); -- Inserts NULL into the first_name column
Explanation This example demonstrates inserting a NULL
value into the first_name
column, indicating the absence of a value.
NVARCHAR2
The NVARCHAR2
data type stores variable-length Unicode character strings. It is ideal for storing multilingual data, ensuring proper character representation across different languages.
Example 1: NVARCHAR2 Declaration
CREATE TABLE products (
product_id NUMBER(6),
product_name NVARCHAR2(50) -- Stores product name in Unicode, up to 50 characters
);
Explanation This code creates a products
table with a product_name
column defined as NVARCHAR2(50)
for storing Unicode characters.
Example 2: Inserting NVARCHAR2 Data
INSERT INTO products (product_id, product_name)
VALUES (1, 'Café'); -- Inserts 'Café' (with an accent) into product_name
Explanation This statement inserts a record into the products
table, populating the product_name
column with the Unicode string 'Café'.
Example 3: Updating NVARCHAR2 Data
UPDATE products
SET product_name = '컴퓨터'
WHERE product_id = 1; -- Updates product_name to a Korean word
Explanation This SQL updates the product_name
for product_id
1 to a Korean word, demonstrating NVARCHAR2
's Unicode capability.
Example 4: Selecting NVARCHAR2 Data
SELECT product_name
FROM products
WHERE product_id = 1; -- Retrieves the Unicode product name
Explanation This query selects and displays the product_name
from the products
table for product_id
1.
Example 5: NVARCHAR2 Length Semantics
CREATE TABLE descriptions (
item_id NUMBER,
item_desc NVARCHAR2(10 CHAR) -- Defines length in characters, not bytes
);
Explanation This example shows NVARCHAR2
defined with CHAR
length semantics, meaning it stores up to 10 characters regardless of byte length.
CHAR
The CHAR
data type stores fixed-length character strings. If the inserted string is shorter than the defined length, it is padded with spaces to the maximum length.
Example 1: CHAR Declaration
CREATE TABLE categories (
category_id CHAR(2), -- Stores category ID as a fixed 2-character string
category_name VARCHAR2(30)
);
Explanation This code creates a categories
table with a category_id
column defined as CHAR(2)
, which will always store exactly 2 characters.
Example 2: Inserting CHAR Data
INSERT INTO categories (category_id, category_name)
VALUES ('A1', 'Electronics'); -- Inserts 'A1' into category_id
Explanation This statement inserts a record into the categories
table, populating the category_id
column with 'A1'.
Example 3: CHAR Padding
INSERT INTO categories (category_id, category_name)
VALUES ('B', 'Books'); -- 'B' will be padded to 'B '
Explanation When 'B' is inserted into the CHAR(2)
column, Oracle pads it with a space to make it 'B '.
Example 4: Comparing CHAR Data
SELECT category_name
FROM categories
WHERE category_id = 'A1'; -- Comparison handles padding implicitly
Explanation This query retrieves category_name
where category_id
is 'A1'; Oracle handles the comparison correctly even if the stored value is padded.
Example 5: CHAR with Fixed-Length Codes
CREATE TABLE status_codes (
code CHAR(3), -- Ideal for fixed-length codes like 'ACT', 'PEN'
description VARCHAR2(50)
);
Explanation This example demonstrates CHAR
used for code
where a fixed length of 3 characters is expected, suitable for status codes.
NUMBER
The NUMBER
data type stores fixed-point or floating-point numbers. It offers high precision and scale, suitable for various numerical data including currency and scientific measurements.
Example 1: NUMBER Declaration (Precision and Scale)
CREATE TABLE accounts (
account_id NUMBER(10), -- Stores integers up to 10 digits
balance NUMBER(10, 2) -- Stores numbers with 10 total digits, 2 after decimal
);
Explanation This code defines balance
as NUMBER(10, 2)
, allowing up to 8 digits before the decimal point and exactly 2 after it.
Example 2: Inserting NUMBER Data
INSERT INTO accounts (account_id, balance)
VALUES (1001, 12345.67); -- Inserts a number with precision and scale
Explanation This statement inserts 12345.67
into the balance
column, which fits the NUMBER(10, 2)
definition.
Example 3: NUMBER with Implicit Scale
INSERT INTO accounts (account_id, balance)
VALUES (1002, 500); -- Inserts 500, which becomes 500.00 due to scale 2
Explanation When 500 is inserted into a NUMBER(10, 2)
column, it is stored as 500.00, automatically adjusting to the defined scale.
Example 4: NUMBER for Integers (No Scale)
CREATE TABLE orders (
order_id NUMBER(8), -- Stores integers up to 8 digits
quantity NUMBER(3) -- Stores integers up to 3 digits
);
Explanation This example uses NUMBER(8)
for order_id
and NUMBER(3)
for quantity
, both implicitly having a scale of 0 (integers).
Example 5: NUMBER for Large Precision
CREATE TABLE sensor_readings (
reading_id NUMBER, -- Defaults to maximum precision and scale
temperature NUMBER(38, 10) -- Stores very precise temperature readings
);
Explanation This code demonstrates NUMBER(38, 10)
for very precise numerical data, allowing up to 38 digits with 10 decimal places.
INTEGER
The INTEGER
data type is a subtype of NUMBER
used to store whole numbers. It typically maps to NUMBER(38,0)
, providing a wide range for integer values.
Example 1: INTEGER Declaration
CREATE TABLE products_inventory (
product_id INTEGER, -- Stores product ID as a whole number
stock_level INTEGER -- Stores stock quantity as a whole number
);
Explanation This code defines product_id
and stock_level
as INTEGER
columns, suitable for whole number values.
Example 2: Inserting INTEGER Data
INSERT INTO products_inventory (product_id, stock_level)
VALUES (1001, 500); -- Inserts integer values
Explanation This statement inserts integer values into the product_id
and stock_level
columns.
Example 3: INTEGER with Negative Values
INSERT INTO products_inventory (product_id, stock_level)
VALUES (1002, -10); -- Inserts a negative integer
Explanation This example demonstrates that INTEGER
can store negative whole numbers.
Example 4: INTEGER in Calculations
UPDATE products_inventory
SET stock_level = stock_level - 10
WHERE product_id = 1001; -- Performs arithmetic on an INTEGER column
Explanation This SQL updates stock_level
by subtracting 10, demonstrating arithmetic operations on INTEGER
columns.
Example 5: INTEGER and Truncation
INSERT INTO products_inventory (product_id, stock_level)
VALUES (1003, 10.5); -- 10.5 will be implicitly truncated to 10
Explanation When a decimal value is inserted into an INTEGER
column, Oracle automatically truncates the decimal part.
FLOAT
The FLOAT
data type stores floating-point numbers with binary precision. It is used for numbers requiring approximate precision, such as scientific calculations where exact decimal representation is not critical.
Example 1: FLOAT Declaration
CREATE TABLE scientific_data (
data_id NUMBER,
measurement FLOAT(126) -- Stores floating-point numbers with maximum precision
);
Explanation This code defines measurement
as FLOAT(126)
, allowing it to store floating-point numbers with up to 126 binary digits of precision.
Example 2: Inserting FLOAT Data
INSERT INTO scientific_data (data_id, measurement)
VALUES (1, 3.14159265); -- Inserts a floating-point number
Explanation This statement inserts a floating-point value into the measurement
column.
Example 3: FLOAT Precision Differences
INSERT INTO scientific_data (data_id, measurement)
VALUES (2, 0.3333333333333333); -- Demonstrates potential precision limitations
Explanation This example shows how FLOAT
stores an approximation, as the exact decimal value may not be perfectly represented in binary.
Example 4: FLOAT in Mathematical Operations
SELECT measurement * 2
FROM scientific_data
WHERE data_id = 1; -- Performs multiplication on a FLOAT column
Explanation This query performs a multiplication operation on the measurement
column, demonstrating FLOAT
's use in calculations.
Example 5: FLOAT and Comparison Caution
SELECT data_id
FROM scientific_data
WHERE measurement = 3.14159265; -- Direct equality comparison with FLOAT can be problematic
Explanation Direct equality comparisons with FLOAT
values can be unreliable due to their approximate nature; consider using a range-based comparison.
DATE
The DATE
data type stores date and time information, including year, month, day, hour, minute, and second. It is commonly used for storing timestamps for events or records.
Example 1: DATE Declaration
CREATE TABLE events (
event_id NUMBER,
event_date DATE -- Stores date and time of the event
);
Explanation This code defines event_date
as a DATE
column, capable of storing both date and time components.
Example 2: Inserting Current Date
INSERT INTO events (event_id, event_date)
VALUES (1, SYSDATE); -- Inserts the current system date and time
Explanation This statement inserts the current system date and time into the event_date
column using SYSDATE
.
Example 3: Inserting Specific Date
INSERT INTO events (event_id, event_date)
VALUES (2, TO_DATE('2025-06-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS')); -- Inserts a specific date and time
Explanation This example uses TO_DATE
to insert a specific date and time string into the event_date
column.
Example 4: Date Arithmetic
SELECT event_date + 7
FROM events
WHERE event_id = 1; -- Adds 7 days to the event date
Explanation This query adds 7 days to the event_date
of the event with event_id
1, demonstrating date arithmetic.
Example 5: Extracting Date Components
SELECT TO_CHAR(event_date, 'YYYY-MM-DD HH:MI:SS AM')
FROM events
WHERE event_id = 1; -- Formats the date for display
Explanation This query uses TO_CHAR
to format the event_date
into a readable string, extracting various components.
TIMESTAMP
The TIMESTAMP
data type extends DATE
by including fractional seconds precision. It is ideal for applications requiring high precision time tracking, such as logging or financial transactions.
Example 1: TIMESTAMP Declaration
CREATE TABLE logs (
log_id NUMBER,
log_time TIMESTAMP(6) -- Stores timestamp with 6 decimal places for seconds
);
Explanation This code defines log_time
as TIMESTAMP(6)
, allowing for nanosecond precision in the time component.
Example 2: Inserting Current Timestamp
INSERT INTO logs (log_id, log_time)
VALUES (1, SYSTIMESTAMP); -- Inserts the current system timestamp with fractional seconds
Explanation This statement inserts the current system timestamp, including fractional seconds, into the log_time
column.
Example 3: Inserting Specific Timestamp
INSERT INTO logs (log_id, log_time)
VALUES (2, TO_TIMESTAMP('2025-06-15 10:30:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF')); -- Inserts a specific timestamp
Explanation This example uses TO_TIMESTAMP
to insert a specific timestamp with fractional seconds into the log_time
column.
Example 4: TIMESTAMP with Time Zone
CREATE TABLE appointments (
appt_id NUMBER,
appt_time TIMESTAMP WITH TIME ZONE -- Stores timestamp with time zone information
);
Explanation This code defines appt_time
as TIMESTAMP WITH TIME ZONE
, crucial for applications spanning multiple time zones.
Example 5: TIMESTAMP Arithmetic
SELECT log_time + INTERVAL '1' HOUR
FROM logs
WHERE log_id = 1; -- Adds one hour to the timestamp
Explanation This query adds one hour to the log_time
of the log with log_id
1, demonstrating timestamp arithmetic.
CLOB
The CLOB
(Character Large Object) data type stores large blocks of character data, up to 4 gigabytes or more depending on system configuration. It is used for storing extensive text, such as documents or articles.
Example 1: CLOB Declaration
CREATE TABLE documents (
doc_id NUMBER,
doc_content CLOB -- Stores large text documents
);
Explanation This code defines doc_content
as a CLOB
column, suitable for storing very large amounts of text.
Example 2: Inserting CLOB Data (Short String)
INSERT INTO documents (doc_id, doc_content)
VALUES (1, 'This is a short test document for CLOB storage.'); -- Inserts a small string into CLOB
Explanation Even small strings can be inserted into a CLOB
column, which handles them efficiently.
Example 3: Inserting CLOB from File (conceptual)
-- This is a conceptual example. Actual CLOB loading often involves client-side programming
-- or using Oracle utilities like SQL*Loader or DBMS_LOB package.
-- INSERT INTO documents (doc_id, doc_content) VALUES (2, EMPTY_CLOB()) RETURNING doc_content INTO :clob_locator;
-- Then, in client application, write file content to :clob_locator
Explanation Loading large CLOB
data usually involves streaming content from a file using specific programming interfaces or Oracle utilities.
Example 4: Updating CLOB Data
UPDATE documents
SET doc_content = doc_content || CHR(10) || 'Appended more content.'
WHERE doc_id = 1; -- Appends text to existing CLOB content
Explanation This SQL appends additional text to the doc_content
of the document with doc_id
1.
Example 5: Selecting CLOB Data (Partial)
SELECT SUBSTR(doc_content, 1, 50) AS partial_content
FROM documents
WHERE doc_id = 1; -- Retrieves the first 50 characters of the CLOB
Explanation This query retrieves only the first 50 characters of the CLOB
content, useful for previewing large documents.
BLOB
The BLOB
(Binary Large Object) data type stores large blocks of unstructured binary data, such as images, audio, or video files. Like CLOB
, it can store up to 4 gigabytes or more.
Example 1: BLOB Declaration
CREATE TABLE media_files (
file_id NUMBER,
file_data BLOB -- Stores binary data like images or videos
);
Explanation This code defines file_data
as a BLOB
column, designed for storing raw binary information.
Example 2: Inserting BLOB Data (conceptual)
-- Similar to CLOB, BLOB data is typically loaded from files
-- using client-side programming or Oracle utilities.
-- INSERT INTO media_files (file_id, file_data) VALUES (1, EMPTY_BLOB()) RETURNING file_data INTO :blob_locator;
-- Then, in client application, write binary file content to :blob_locator
Explanation Inserting large BLOB
data involves streaming binary content from a file using client applications or specialized Oracle tools.
Example 3: BLOB for Image Storage
INSERT INTO media_files (file_id, file_data)
VALUES (2, HEXTORAW('89504E470D0A1A0A0000000D49484452...')); -- Example of inserting a small piece of binary data (PNG header)
Explanation This example shows a conceptual insertion of binary data (represented as hexadecimal) into a BLOB
column.
Example 4: Updating BLOB Data (conceptual)
-- Updating BLOB often involves replacing the entire content or using DBMS_LOB functions for partial updates.
-- UPDATE media_files SET file_data = EMPTY_BLOB() WHERE file_id = 1;
-- Then, reload the new binary data.
Explanation Updating BLOB
data usually means replacing the entire binary content, often done via client-side code interacting with DBMS_LOB
.
Example 5: Retrieving BLOB Size
SELECT DBMS_LOB.GETLENGTH(file_data) AS blob_size_bytes
FROM media_files
WHERE file_id = 1; -- Retrieves the size of the BLOB in bytes
Explanation This query uses DBMS_LOB.GETLENGTH
to determine the size of the BLOB
data in bytes, useful for managing storage.
RAW
The RAW
data type stores binary data of variable length, similar to VARCHAR2
but for uninterpreted byte sequences. It is often used for storing data that does not have a character set, such as encrypted data or object IDs.
Example 1: RAW Declaration
CREATE TABLE encrypted_data (
data_id NUMBER,
encrypted_value RAW(2000) -- Stores raw binary data up to 2000 bytes
);
Explanation This code defines encrypted_value
as RAW(2000)
, allowing it to store up to 2000 bytes of uninterpreted binary data.
Example 2: Inserting RAW Data (Hexadecimal)
INSERT INTO encrypted_data (data_id, encrypted_value)
VALUES (1, HEXTORAW('0123456789ABCDEF')); -- Inserts binary data from a hexadecimal string
Explanation This statement inserts binary data into the encrypted_value
column by converting a hexadecimal string using HEXTORAW
.
Example 3: Generating RAW (SYS_GUID)
INSERT INTO encrypted_data (data_id, encrypted_value)
VALUES (2, SYS_GUID()); -- Inserts a globally unique identifier (RAW)
Explanation This example inserts a unique 16-byte RAW
value generated by SYS_GUID()
, commonly used for unique keys.
Example 4: Selecting RAW Data (Hexadecimal)
SELECT RAWTOHEX(encrypted_value) AS hex_representation
FROM encrypted_data
WHERE data_id = 1; -- Retrieves RAW data as its hexadecimal representation
Explanation This query converts the RAW
data to its hexadecimal string representation for display using RAWTOHEX
.
Example 5: RAW for Hash Values
CREATE TABLE user_hashes (
user_id NUMBER,
password_hash RAW(32) -- Stores a 32-byte hash value
);
Explanation This example uses RAW(32)
to store fixed-length cryptographic hash values, which are typically binary.
ROWID
The ROWID
data type stores the physical address of a row in an Oracle database. It is a pseudocolumn, meaning it's not a true column but a unique identifier for each row's physical location.
Example 1: Selecting ROWID
SELECT employee_id, first_name, ROWID
FROM employees
WHERE employee_id = 101; -- Retrieves the ROWID for a specific row
Explanation This query retrieves the ROWID
along with other column data for the employee with employee_id
101.
Example 2: Using ROWID for Fast Access (Caution)
SELECT *
FROM employees
WHERE ROWID = 'AAARn0AAFAAAABSAGA'; -- Accesses a row directly using its ROWID (example ROWID)
Explanation Using ROWID
in a WHERE
clause provides the fastest possible access to a specific row, as it points directly to the row's physical location.
Example 3: ROWID Uniqueness
SELECT COUNT(DISTINCT ROWID) AS unique_rowids, COUNT(*) AS total_rows
FROM employees; -- Demonstrates that ROWID is unique for each row
Explanation This query shows that ROWID
is unique for every row in a table, even if other column values are duplicated.
Example 4: ROWID and Table Movement
-- ROWID can change if the row is moved (e.g., due to table reorganization, export/import).
-- Example: Altering a table to move data segment
-- ALTER TABLE employees MOVE; -- This operation can change ROWIDs
Explanation It's important to know that ROWID
is a physical address and can change if the table's physical storage is altered (e.g., ALTER TABLE MOVE
).
Example 5: ROWID in JOIN (Less Common)
-- Although possible, joining on ROWID is rare and generally not recommended
-- unless there's a specific, performance-critical reason and deep understanding of ROWID behavior.
-- SELECT e.first_name, d.doc_content
-- FROM employees e, documents d
-- WHERE e.ROWID = d.ROWID; -- Very unlikely to match
Explanation While syntactically possible, joining tables based on their ROWID
is highly unusual and not practical for most scenarios, as ROWID
is table-specific.