SQL & PL/SQL

Hi,

I am getting this ORA-01847: day of month must be between 1 and last day of month type of error but I have checked my data all are correct.

I am inserting the data in the merge query. It is giving the above error.

but when i insert the data like that

insert into dt(start_date) select TO_DATE (tariff_start_date, 'DD/MM/RRRR') from ext_zpp0a871;

It does not give any error.

Please find the below code

DECLARE
l_sv_error_msg VARCHAR2 (1000);
BEGIN
FOR i IN (SELECT condition_type, sales_org, division_channel, division,
price_list, ean_upc, amount, unit_of_measure1,
tariff_start_date, tariff_end_date
FROM ext_zpp0a871--This is external table
WHERE condition_type = 'ZPP0' AND ROWNUM < 200)
LOOP
BEGIN
MERGE INTO sap_tariff st
USING (SELECT i.price_list pl, i.ean_upc upc,
TO_DATE (i.tariff_start_date, 'DD/MM/RRRR') tsd,
TO_DATE (i.tariff_end_date, 'DD/MM/RRRR') ted
FROM DUAL) du
ON (st.prod_ean_cuni = du.upc
AND st.pricelist = du.pl
AND st.tariff_start_date = du.tsd
AND st.tariff_end_date = du.ted)
WHEN MATCHED THEN
UPDATE
SET st.condition_type = i.condition_type,
st.sales_org = i.sales_org,
st.division_channel = i.division_channel,
st.division = i.division,
st.amount =
TO_NUMBER (REPLACE (REPLACE (i.amount, '.', NULL),
',',
'.'
)
),
st.uom = i.unit_of_measure1
WHEN NOT MATCHED THEN
INSERT (condition_type, sales_org, division_channel, division,
pricelist, prod_ean_cuni, amount, uom,
tariff_start_date, tariff_end_date)
VALUES (i.condition_type, i.sales_org, i.division_channel,
i.division, i.price_list, i.ean_upc,
TO_NUMBER (REPLACE (REPLACE (i.amount, '.', NULL),
',',
'.'
)
),
i.unit_of_measure1,
TO_DATE (i.tariff_start_date, 'DD/MM/RRRR'),
TO_DATE (i.tariff_end_date, 'DD/MM/RRRR'));
/*INSERT INTO sap_tariff
(condition_type, sales_org, division_channel,
division, pricelist, prod_ean_cuni,
amount,
uom,
tariff_start_date,
tariff_end_date
)
VALUES (i.condition_type, i.sales_org, i.division_channel,
i.division, i.price_list, i.ean_upc,
TO_NUMBER (REPLACE (REPLACE (i.amount, '.', NULL),
',',
'.'
)
),
i.unit_of_measure1,
TO_DATE (i.tariff_start_date, 'DD/MM/RRRR'),
TO_DATE (i.tariff_end_date, 'DD/MM/RRRR')
);*/
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE sap_tariff
SET condition_type = i.condition_type,
sales_org = i.sales_org,
division_channel = i.division_channel,
division = i.division,
amount =
TO_NUMBER (REPLACE (REPLACE (i.amount, '.', NULL),
',',
'.'
)
),
uom = i.unit_of_measure1;
WHEN OTHERS
THEN
l_sv_error_msg := SQLERRM (SQLCODE);

INSERT INTO sap_tariff_log
(date_of_load, condition_type, sales_org,
division_channel, division, price_list,
prod_ean_cuni, amount, uom,
tariff_start_date, tariff_end_date,
rejection_reason
)
VALUES (SYSDATE, i.condition_type, i.sales_org,
i.division_channel, i.division, i.price_list,
i.ean_upc, i.amount, i.unit_of_measure1,
i.tariff_start_date, i.tariff_end_date,
l_sv_error_msg
);
END;
END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_sv_error_msg := SQLERRM (SQLCODE);
neo_pro_log ('Others Error', l_sv_error_msg);
END;