DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01867 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01867: The Interval Is Invalid — Causes, Fixes, and Prevention

ORA-01867 is thrown by Oracle Database when an INTERVAL literal or string conversion contains a value that falls outside the permitted range or uses an incorrect format. This error commonly surfaces during date/time arithmetic, ETL pipelines, or when migrating data from external systems that do not strictly enforce Oracle's INTERVAL syntax rules.

Top 3 Causes and Fixes

1. Malformed INTERVAL Literal

Oracle's INTERVAL literals follow strict field-range rules: months must be 0–11, hours 0–23, minutes and seconds 0–59. Violating these ranges instantly raises ORA-01867.

-- BAD: Month part exceeds 11 in YEAR TO MONTH literal
SELECT INTERVAL '1-12' YEAR TO MONTH FROM DUAL; -- ORA-01867

-- BAD: Hour part exceeds 23 in DAY TO SECOND literal
SELECT INTERVAL '1 24:00:00' DAY TO SECOND FROM DUAL; -- ORA-01867

-- GOOD: Express 12 months as 1 year, 0 months
SELECT INTERVAL '1-0' YEAR TO MONTH FROM DUAL;

-- GOOD: Express 24 hours as 1 additional day
SELECT INTERVAL '2 00:00:00' DAY TO SECOND FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Fix: Always keep each field within its valid range. Convert overflow values to the next higher unit before constructing the literal.

2. Wrong String Format Passed to TO_YMINTERVAL / TO_DSINTERVAL

TO_YMINTERVAL expects exactly 'YEARS-MONTHS' and TO_DSINTERVAL expects 'DAYS HH:MI:SS[.FF]'. Any deviation — wrong delimiter, missing component, or extra characters — triggers ORA-01867.

-- BAD: Wrong delimiter (slash instead of hyphen)
SELECT TO_YMINTERVAL('2/6') FROM DUAL;      -- ORA-01867

-- BAD: Incorrect separator for DAY TO SECOND
SELECT TO_DSINTERVAL('1:12:00:00') FROM DUAL; -- ORA-01867

-- GOOD: Correct formats
SELECT TO_YMINTERVAL('2-6')        FROM DUAL; -- 2 years, 6 months
SELECT TO_DSINTERVAL('1 12:30:00') FROM DUAL; -- 1 day, 12 hours, 30 minutes

-- Safe wrapper with exception handling in PL/SQL
DECLARE
    v_result INTERVAL YEAR TO MONTH;
BEGIN
    v_result := TO_YMINTERVAL('2-6');
    DBMS_OUTPUT.PUT_LINE('Converted: ' || v_result);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Conversion failed: ' || SQLERRM);
END;
/
Enter fullscreen mode Exit fullscreen mode

Fix: Validate and sanitize all external strings before passing them to Oracle's INTERVAL conversion functions. Use a shared wrapper function that catches ORA-01867 and logs the bad input.

3. Value Exceeds Column Precision

When an INTERVAL column is defined with a leading precision (e.g., DAY(2)), inserting a value larger than that precision allows — such as 100 days into a DAY(2) column — will raise ORA-01867.

-- Create a table with limited DAY precision
CREATE TABLE project_log (
    id       NUMBER,
    duration INTERVAL DAY(2) TO SECOND  -- allows up to 99 days only
);

-- BAD: 100 days exceeds DAY(2) precision
INSERT INTO project_log VALUES (1, INTERVAL '100' DAY); -- ORA-01867

-- FIX: Alter the column to DAY(3) to allow up to 999 days
ALTER TABLE project_log
MODIFY (duration INTERVAL DAY(3) TO SECOND);

-- GOOD: Insert now succeeds
INSERT INTO project_log VALUES (1, INTERVAL '100' DAY(3));
COMMIT;

-- Verify stored data
SELECT id,
       duration,
       EXTRACT(DAY    FROM duration) AS days,
       EXTRACT(HOUR   FROM duration) AS hours,
       EXTRACT(MINUTE FROM duration) AS minutes
FROM project_log;
Enter fullscreen mode Exit fullscreen mode

Fix: Check existing column precision with USER_TAB_COLUMNS and use ALTER TABLE ... MODIFY to increase the leading precision before inserting large values.

Quick Prevention Tips

Validate before converting. Never pass raw external data directly to TO_YMINTERVAL or TO_DSINTERVAL. Build a centralized validation function that uses an exception block to catch ORA-01867 and return a meaningful error to the application layer before bad data reaches the database.

Design with headroom. When defining INTERVAL columns, always set the leading precision higher than your current maximum expected value. Prefer DAY(4) over DAY(2) and YEAR(4) over YEAR(2). Changing column precision on a live production table carries downtime risk — getting it right at design time is always cheaper.

Related Oracle Errors

Error Code Description
ORA-01843 Invalid month — often appears alongside INTERVAL issues
ORA-01873 Leading precision of the interval is too small
ORA-01850 Hour must be between 0 and 23
ORA-01874 Time zone hour must be between -12 and 14

📖 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.

Top comments (0)