Hi - I have a procedure which is supposed to insert the records to table1 using merge statement,while trying to execute the proc,am getting "ORA-01861: literal does not match format string".
My understanding is that it might be due to mismatch with data defined columns where the source table has null values.
here DATE_CREATED column from source has null values as well as the date format is'14-JAN-2018.
create or replace PROCEDURE PROC1 AS
BEGIN
MERGE INTO table1 u
USING view m
ON (u.USER_ID = m.userID)
WHEN MATCHED THEN UPDATE SET
u.ACTIVE = m.ACTIVE,
u.DATE_CREATED = m.DATE_CREATED,
u.CREATED_YEAR_MON = to_char(m.DATE_CREATED, 'YYYY-MM'),
u.CREATED_YEAR = to_char(m.DATE_CREATED, 'YYYY'),
u.CREATED_MON = to_char(m.DATE_CREATED, 'MM'),
u.SECTOR = m.SECTOR,
u.REGION = m.REGION
WHEN NOT MATCHED THEN INSERT
VALUES (m.userID, m.ACT, m.DATE_CREATED, to_char(m.DATE_CREATED, 'YYYY-MM'), to_char(m.DATE_CREATED, 'YYYY'),
to_char(m.DATE_CREATED, 'MM'), m.SECTOR, m.REGION, CURRENT_DATE);
END;