How to Use Data Types in Oracle

What are the data types in Oracle and how do you choose them?

Oracle data types define how values are stored, validated, and processed in a table column.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What are the core Oracle data types?

Oracle groups data types into character (CHAR, VARCHAR2, CLOB), numeric (NUMBER, FLOAT, BINARY_FLOAT), date‐time (DATE, TIMESTAMP, INTERVAL), and binary (RAW, BLOB). Each type controls storage format, valid operations, and index behavior, so choosing correctly prevents conversion overhead.

Which character type should I use?

VARCHAR2 is ideal for variable-length strings up to 32,767 bytes and is the default choice. CHAR pads to fixed length and suits codes like country_id. Use CLOB only for text beyond 32 KB to avoid LOB management overhead.

How do I choose between NUMBER and FLOAT?

NUMBER(precision, scale) offers exact arithmetic—critical for prices and quantities. FLOAT and BINARY_FLOAT use IEEE binary precision, trading rounding accuracy for speed. Prefer NUMBER for financial figures and FLOAT only for scientific or large-range values.

When should I use DATE vs TIMESTAMP?

DATE stores date and time to the second without fractional seconds or time zone. TIMESTAMP adds fractional seconds; TIMESTAMP WITH TIME ZONE keeps session-independent offsets—essential for multi-region order tracking.

Can character columns exceed 4,000 bytes?

Yes. Enable MAX_STRING_SIZE = EXTENDED (requires restart) to push VARCHAR2 and NVARCHAR2 to 32,767 bytes. Otherwise Oracle silently limits them to 4,000 bytes, forcing you to use CLOB for larger values.

How to declare data types in a CREATE TABLE statement?

Specify each column’s data type and optional precision, scale, length, or time zone. Constraints like NOT NULL or DEFAULT follow the type definition.

Best practices for data type selection

Match data type to real-world domain; avoid generic VARCHAR2 for numbers or dates. Declare precision and scale on NUMBER to help Oracle choose efficient storage. Use TIMESTAMP WITH TIME ZONE for cross-region apps. Store large free text in CLOB, not VARCHAR2.

Common mistakes and how to avoid them

Mistake 1 – Using VARCHAR2 for numeric IDs: Forces implicit conversions in joins and slows indexes. Fix by declaring NUMBER(10) or the smallest suitable integer size.

Mistake 2 – Ignoring time zones: Storing shipment times in DATE causes confusion across regions. Fix by switching to TIMESTAMP WITH TIME ZONE or storing offset separately.

Quick reference example

CREATE TABLE Orders ( id NUMBER(10) PRIMARY KEY, customer_id NUMBER(10) NOT NULL, order_date TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP, total_amount NUMBER(12,2) CHECK (total_amount >= 0) );

Need more?

Query ALL_TAB_COLUMNS to view existing column data types and use DESC table_name for quick checks.

FAQ

Does VARCHAR2 store UTF-8?

Yes, when the database character set is AL32UTF8 or UTF8. No extra codepage column is necessary.

How large can NUMBER be?

NUMBER supports up to 38 digits of precision, with scale from ‑84 to 127, letting you store anything from micro-units to astronomical figures.

What happens if data exceeds precision?

Oracle raises ORA-01438 at insert time, rejecting the row. Increase precision/scale or ROUND before insert.

Why How to Use Data Types in Oracle is important

How to Use Data Types in Oracle Example Usage


-- Storing a new product with correct data types
INSERT INTO Products (id, name, price, stock)
VALUES (301, 'Wireless Mouse', NUMBER '24.99', 150);

-- Querying orders within a specific day using TIMESTAMP
SELECT id, customer_id, total_amount
FROM Orders
WHERE order_date BETWEEN
      TO_TIMESTAMP_TZ('2024-06-19 00:00:00 -05:00','YYYY-MM-DD HH24:MI:SS TZH:TZM')
  AND TO_TIMESTAMP_TZ('2024-06-19 23:59:59 -05:00','YYYY-MM-DD HH24:MI:SS TZH:TZM');

How to Use Data Types in Oracle Syntax


CREATE TABLE table_name (
  column_name data_type [ (precision [, scale]) | (length) ]
  [WITH TIME ZONE | WITH LOCAL TIME ZONE]
  [DEFAULT expr]
  [NOT NULL | UNIQUE | PRIMARY KEY | CHECK (...)]
);

-- Example
CREATE TABLE OrderItems (
  id          NUMBER(10) PRIMARY KEY,
  order_id    NUMBER(10)   NOT NULL,
  product_id  NUMBER(10)   NOT NULL,
  quantity    NUMBER(5)    DEFAULT 1,
  CONSTRAINT chk_qty CHECK (quantity > 0)
);

Common Mistakes

Using VARCHAR2 for numeric data causes implicit conversions and index inefficiency. Declare NUMBER(precision,scale) instead.
Storing timestamps in DATE loses time-zone info, leading to reporting errors. Use TIMESTAMP WITH TIME ZONE to preserve offsets.