Oracle ORA-14019 Error: Causes and Solutions Complete Guide

ORA-14019: Partition Bound Element Must Be a String, Datetime, Number, or MAXVALUE

ORA-14019 is thrown by Oracle when you specify an invalid value as a partition boundary during a CREATE TABLE or ALTER TABLE DDL statement. Oracle strictly allows only literal values — strings, datetime literals, numbers, or the keyword MAXVALUE — as partition bound elements. Any attempt to use a function call, bind variable, or runtime expression in that position triggers this error immediately.

---

Top 3 Causes

1. Using SQL Functions (e.g., SYSDATE) as Partition Bounds

This is the most common cause. Developers often try to use SYSDATE, TO_DATE(), or TRUNC() directly inside a VALUES LESS THAN clause, which Oracle does not permit.

Incorrect:

-- ORA-14019: SYSDATE is not allowed as a partition bound
CREATE TABLE sales (
    sale_id   NUMBER,
    sale_date DATE
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_old VALUES LESS THAN (SYSDATE),
    PARTITION p_new VALUES LESS THAN (MAXVALUE)
);

Correct:

-- Use a DATE literal instead
CREATE TABLE sales (
    sale_id   NUMBER,
    sale_date DATE
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01'),
    PARTITION p_max  VALUES LESS THAN (MAXVALUE)
);

---

2. Incorrect Date Literal Format

When partitioning on a DATE column, a plain string like '2024-01-01' is not recognized as a valid date literal. You must use the DATE 'YYYY-MM-DD' keyword syntax or a proper TIMESTAMP literal.

Incorrect:

-- ORA-14019: plain string is not a valid date bound
CREATE TABLE orders (
    order_id   NUMBER,
    order_date DATE
)
PARTITION BY RANGE (order_date) (
    PARTITION p_q1 VALUES LESS THAN ('2024-04-01'),
    PARTITION p_q2 VALUES LESS THAN ('2024-07-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

Correct:

-- Use the DATE keyword for proper literal syntax
CREATE TABLE orders (
    order_id   NUMBER,
    order_date DATE
)
PARTITION BY RANGE (order_date) (
    PARTITION p_q1  VALUES LESS THAN (DATE '2024-04-01'),
    PARTITION p_q2  VALUES LESS THAN (DATE '2024-07-01'),
    PARTITION p_q3  VALUES LESS THAN (DATE '2024-10-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

-- For TIMESTAMP columns, use the TIMESTAMP literal
CREATE TABLE event_log (
    event_id   NUMBER,
    event_time TIMESTAMP
)
PARTITION BY RANGE (event_time) (
    PARTITION p_h1  VALUES LESS THAN (TIMESTAMP '2024-07-01 00:00:00'),
    PARTITION p_h2  VALUES LESS THAN (TIMESTAMP '2025-01-01 00:00:00'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

---

3. Using Bind Variables or PL/SQL Variables in Partition DDL

Some developers attempt to pass a PL/SQL variable or a bind variable (:var) into a partition bound inside EXECUTE IMMEDIATE. Oracle's DDL parser does not support bind variables for partition bounds — only string-concatenated literals work.

Incorrect:

-- ORA-14019: bind variable not allowed in partition bound
DECLARE
    v_date DATE := DATE '2025-01-01';
BEGIN
    EXECUTE IMMEDIATE
        'ALTER TABLE sales ADD PARTITION p_2025 VALUES LESS THAN (:1)'
    USING v_date;
END;
/

Correct:

-- Concatenate the literal value into the dynamic SQL string
DECLARE
    v_part_name VARCHAR2(30) := 'P_2025_Q1';
    v_bound     VARCHAR2(20) := '2025-04-01';
    v_sql       VARCHAR2(500);
BEGIN
    v_sql := 'ALTER TABLE sales ADD PARTITION ' || v_part_name
          || ' VALUES LESS THAN (DATE ''' || v_bound || ''')';

    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
    EXECUTE IMMEDIATE v_sql;
    DBMS_OUTPUT.PUT_LINE('Partition added successfully.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;
END;
/

---

Quick Fix Summary

Situation	Wrong	Right
Current date boundary	SYSDATE	DATE '2025-01-01'
Date column string	'2024-04-01'	DATE '2024-04-01'
Timestamp column	'2024-01-01 00:00:00'	TIMESTAMP '2024-01-01 00:00:00'
Dynamic partition	USING v_date	String concatenation

---

Prevention Tips

1. Use Interval Partitioning to eliminate manual partition DDL.
Oracle 11g+ Interval Partitioning automatically creates partitions as data arrives, removing the need to write repeated ALTER TABLE ADD PARTITION statements and the risk of hitting ORA-14019.

-- Monthly auto-partitioning — no manual bounds needed after the first
CREATE TABLE sales_auto (
    sale_id   NUMBER,
    sale_date DATE
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION p_init VALUES LESS THAN (DATE '2024-01-01')
);

2. Always test partition DDL in a development environment first.
Build a checklist for DDL reviews that explicitly flags any non-literal value in a VALUES LESS THAN or VALUES IN clause. Running partition scripts in a lower environment before production deployment will catch ORA-14019 long before it causes downtime.

---

Related Oracle Errors

• ORA-14020 – Invalid physical attribute for a partition.
• ORA-14021 – MAXVALUE used in a LIST partition (not allowed).
• ORA-14036 – Partition bound value too large for the column data type.
• ORA-00922 – Missing or invalid option in DDL syntax.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.