📋

Description

The ORA-30084 error occurs in Oracle Database when using a time zone modifier with an invalid datetime primary data type. This typically happens during data manipulation or definition language (DML/DDL) operations involving time zones.

💬

Error Message

ORA-30084: invalid data type for datetime primary with time zone modifier

🔍

Known Causes

2 known causes

⚠️

Incorrect Data Type

The datetime primary is not of type TIME, TIME WITH TIME ZONE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE. ⚠

⚠️

Missing Time Zone

A time zone modifier is specified but the underlying data doesn't support it. 🌐

🛠️

Solutions

3 solutions available

1. Correct Datetime Data Type Usage easy

Ensure you are using the correct Oracle datetime data types for columns with time zone information.

1

Identify the column(s) causing the ORA-30084 error. This typically occurs when trying to insert or manipulate data in a column defined with a datetime data type that does not support time zone information, but you are providing time zone details.

2

Review the data type of the problematic column. If it's `DATE` or `TIMESTAMP` (without time zone), and you intend to store time zone information, you need to alter the table.

3

Alter the table to use a `TIMESTAMP WITH TIME ZONE` or `TIMESTAMP WITH LOCAL TIME ZONE` data type for the column.

ALTER TABLE your_table_name MODIFY your_column_name TIMESTAMP WITH TIME ZONE;

4

If the issue is during data insertion or update, ensure the values you are providing are compatible with the chosen datetime data type. For `TIMESTAMP WITH TIME ZONE`, values should include time zone information.

INSERT INTO your_table_name (your_column_name) VALUES (TIMESTAMP '2023-10-27 10:00:00.000000 -05:00');

2. Review and Adjust Input Data Format medium

Verify that the datetime values being inserted or updated correctly match the target column's data type, especially regarding time zone.

1

Examine the SQL statements or application code that is performing the insert or update operation leading to the ORA-30084 error.

2

If the target column is `TIMESTAMP WITH TIME ZONE` or `TIMESTAMP WITH LOCAL TIME ZONE`, ensure the input string or value explicitly includes a valid time zone offset or name.

3

Use the `TO_TIMESTAMP_TZ` function to explicitly convert string literals to a `TIMESTAMP WITH TIME ZONE` data type, specifying the correct format mask.

INSERT INTO your_table_name (your_column_name) VALUES (TO_TIMESTAMP_TZ('2023-10-27 10:00:00 US/Eastern', 'YYYY-MM-DD HH24:MI:SS TZR'));

4

If the input data is coming from an external source (e.g., CSV, JSON), ensure the parsing logic correctly extracts and formats the time zone information before it's passed to the Oracle database.

3. Explicitly Specify Time Zone for Non-TZ Types medium

When using `TIMESTAMP` (without time zone) and needing to represent a specific time zone, explicitly convert it to a timezone-aware type.

1

Identify if you are trying to insert or manipulate data into a `TIMESTAMP` (without time zone) column, but the source data has time zone context that you are implicitly trying to store.

2

If the source data is a string with time zone information, convert it using `TO_TIMESTAMP_TZ` before inserting into a `TIMESTAMP WITH TIME ZONE` column.

INSERT INTO your_table_name (your_column_name) VALUES (TO_TIMESTAMP_TZ('2023-10-27 10:00:00+05:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));

3

If the source data represents a specific time zone but the target column is `TIMESTAMP` (without time zone), you must decide whether to store the UTC equivalent or to convert it to `TIMESTAMP WITH TIME ZONE`.

4

To convert a `TIMESTAMP` to `TIMESTAMP WITH TIME ZONE` using a session time zone or a specified time zone:

ALTER SESSION SET TIME_ZONE = 'America/New_York';
INSERT INTO your_table_name (your_column_name) VALUES (SYSTIMESTAMP AT TIME ZONE 'UTC');

Oracle ORA-30084: Invalid Datetime Type