Have an interesting situation.
All columns in both tables are NUMBER or VARCHAR, no DATE columns.
This fails:
`insert into tx_angler_catch (`
`major_area_code, station_code,`
`completion_dttm,`
`interview_id_txt, interview_time_num,`
`activity_code,`
`species_code,`
`total_caught_num,`
`minor_bay_of_catch_code,`
`length_num,`
`event_id, upload_seq)`
select major_area_code, station_code,
com pletion_dttm,
interview_id_txt, interview_time_num,
activity_code,
species_code,
total_caught_num,
minor_bay_of_catch_code,
length_num,
event_id, upload_seq
from tx_angler_catch_i
where event_id = :p_event_id;
with ORA-01843: not a valid month
Now IF COMPLETION_DTTM was a DATE column - and it is NOT it is VARCHAR2 - I would understand that, the strings are not consistent Oracle date formats, changing from 1/1/2025 to 1/12/2025 to 10/1/2025 to 10/12/2025 - plus the time string. You get the idea. That is why we left it as VARCHAR2 in both tables.
Yet this works:
`insert into tx_angler_catch (`
`major_area_code, station_code,`
`--- skip the varchar2 date-time string column ---completion_dttm,`
`interview_id_txt, interview_time_num,`
`activity_code,`
`species_code,`
`total_caught_num,`
`minor_bay_of_catch_code,`
`length_num,`
`event_id, upload_seq)`
select major_area_code, station_code,
--- skip the varchar2 date-time string column --- completion_dttm,
interview_id_txt, interview_time_num,
activity_code,
species_code,
total_caught_num,
minor_bay_of_catch_code,
length_num,
event_id, upload_seq
from tx_angler_catch_i
where event_id = :p_event_id;
This works fine, 7300+ rows inserted.
The table declarations:
TX_ANGLER_CATCH_I:
Name Null? Type
----------------------- -------- ------------
MAJOR_AREA_CODE NUMBER
STATION_CODE NUMBER
COMPLETION_DTTM VARCHAR2(30)
INTERVIEW_ID_TXT VARCHAR2(50)
INTERVIEW_TIME_NUM NUMBER
ACTIVITY_CODE NUMBER
SPECIES_CODE NUMBER
TOTAL_CAUGHT_NUM NUMBER
MINOR_BAY_OF_CATCH_CODE NUMBER
LENGTH_NUM VARCHAR2(50)
EVENT_ID NUMBER(10)
UPLOAD_SEQ NOT NULL NUMBER
and
TX_ANGLER_CATCH:
Name Null? Type
----------------------- -------- ------------
MAJOR_AREA_CODE NUMBER
STATION_CODE NUMBER
COMPLETION_DTTM VARCHAR2(30)
INTERVIEW_ID_TXT VARCHAR2(50)
INTERVIEW_TIME_NUM NUMBER
ACTIVITY_CODE NUMBER
SPECIES_CODE NUMBER
TOTAL_CAUGHT_NUM NUMBER
MINOR_BAY_OF_CATCH_CODE NUMBER
LENGTH_NUM VARCHAR2(50)
EVENT_ID NOT NULL NUMBER(10)
UPLOAD_SEQ NOT NULL NUMBER
What am I missing here? Does DTTM in the column name trigger some internal date-time conversion?
This should be a very simple INSERT INTO statement.
Suggestions welcome!
My next step of course will be to change the name of the COMPLETION_DTTM column, but really I should not have to.
Thank you, Karen