I want to export data from Intellij as INSERT statements. And then import them to oracle. I keep getting ORA-01843: not a valid month error. How can I get it to work? My .sql file is over 600 MB large so I can't just manually correct all the statements.
Those are the fields that are problematic:
"CREATED" TIMESTAMP (6) WITH TIME ZONE,
"TIMESTAMP" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE,
Of course in reality it has much more but for this question I only include those that cause problems.
In Intellij I run SELECT * FROM table_name and then in results view I click export data. In Export Data window I choose Extractor: SQL Inserts and then click Export to File. In that file there are SQL statements where CREATED is for example '2023-02-27 11:54:51.0 UTC' and TIMESTAMP is for example '2023-02-20 14:57:49.66 +00:00'
When I try to import this file using Oracle SQL Developer then all those inserts fail because ORA-01843: not a valid month
Is there something that I can do to either make Intellij export in Oracle friendly format or to make Oracle SQL Developer accept format that I currently have?
In SQL Developer you can do
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF TZR';
before running the inserts.
You can also change that in the preferences, but if you only want it to apply for the current session use alter.
Depending on your other columns and how they are formatted in your script you might also want to do:
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';