SQL & PL/SQL

We have this merge statement that is created dynamically. If I use the hint in the merge clause (not the selects) it falls over ORA 12801 Error in parallel server query AND ORA 30563 Outer join operator (+) not allowed in select list.

Real environment has partition tables and 50M counts per partition but this breaks on non partition test tables with small record numbers as well.

Am I missing something???


MERGE /*+PARALLEL(m 4) */
INTO neil_Ar_Spcf_Fncl_Stat m
USING (SELECT CASE
WHEN rec_action_cde = 'I'
THEN ar_id * -1
ELSE ar_id
END ar_id,
fncl_stat_type_cde,
fncl_stat_cde
FROM (
SELECT ar_id, fncl_stat_type_cde, fncl_stat_cde, rec_action_cde,
COUNT (1) OVER (PARTITION BY ar_id, fncl_stat_type_cde, fncl_stat_cde)
rec_cnt
FROM (SELECT /*+PARALLEL(m 4) */
m.ar_id,
m.fncl_stat_type_cde,
m.fncl_stat_cde,
'U' rec_action_cde
FROM Ar_Spcf_Fncl_Stat m
WHERE m.start_dte <=
TO_DATE ('20050401', 'YYYYMMDD')
AND end_dte >=
TO_DATE ('20050401', 'YYYYMMDD')
AND fncl_stat_type_cde IN
('ACIRB', 'ACUIRB', 'ACSTD', 'ACUSTD')
and ar_id in (288387,18714055)
UNION ALL
SELECT 288387 ar_id, 'ASIRB' fncl_stat_type_cde,
'RTL' fncl_stat_cde,
'I' rec_action_cde
FROM dual
) t
GROUP BY ar_id, fncl_stat_type_cde, fncl_stat_cde, rec_action_cde) t
WHERE rec_cnt < 2) t
ON ( (CASE
WHEN m.ar_id < 0
THEN m.ar_id * -1
ELSE m.ar_id
END) = t.ar_id
AND m.fncl_stat_type_cde = t.fncl_stat_type_cde
AND m.fncl_stat_cde = t.fncl_stat_cde)
WHEN MATCHED THEN
UPDATE
SET m.end_dte = (TRUNC (TO_DATE ('20050401', 'YYYYMMDD') - 1))
WHEN NOT MATCHED THEN
INSERT (m.ar_id, m.fncl_stat_type_cde, m.fncl_stat_cde, m.start_dte,
m.end_dte)
VALUES (t.ar_id * -1, t.fncl_stat_type_cde, t.fncl_stat_cde,
TRUNC (TO_DATE ('20050401', 'YYYYMMDD')),
TO_DATE ('31-DEC-2999', 'DD-MON-YYYY'))