Re: MERGE statement not working with use of hints

"Dereck L. Dietz" <dietzdl_at_ameritech.net> schreef in bericht news:pc3eh.20172$9v5.12764_at_newssvr29.news.prodigy.net...
> The following code has been written under Oracle 10g R2.
>
> The first merge statement
> will insert rows into the stg_provider table. However, if any hint at all
> is used
> nothing will be written. I have other procedures using merge statements
> with hints
> which work just fine. Any suggestions on where I should look would be
> appreciated.
>
>
> MERGE INTO stg_provider a
>
> USING TABLE( load_stg_provider() ) b
> ON (
> a.prpr_id = b.prpr_id
> AND a.effective_date = b.effective_date
> AND a.terminated_date = b.terminated_date
> AND a.sys_contract_mbr = b.sys_contract_mbr
> AND a.mbr_contrived_key = b.mbr_contrived_key
> AND a.subscriber = b.subscriber
> AND a.suffix = b.suffix
> AND a.elig_class_prod_cat = b.elig_class_prod_cat
> )
>
> WHEN NOT MATCHED THEN
>
> INSERT
> (
> prpr_id,
> effective_date,
> terminated_date,
> sys_contract_mbr,
> mbr_contrived_key,
> subscriber,
> suffix,
> elig_class_prod_cat
> )
> VALUES
> (
> b.prpr_id,
> b.effective_date,
> b.terminated_date,
> b.sys_contract_mbr,
> b.mbr_contrived_key,
> b.subscriber,
> b.suffix,
> b.elig_class_prod_cat
> );
>
> COMMIT;
>
>
> ==
> Merge statement with hint which doesn't write anything.
> ==
>
> MERGE /*+ APPEND */ INTO stg_provider a
>
> USING TABLE( load_stg_provider() ) b
> ON (
> a.prpr_id = b.prpr_id
> AND a.effective_date = b.effective_date
> AND a.terminated_date = b.terminated_date
> AND a.sys_contract_mbr = b.sys_contract_mbr
> AND a.mbr_contrived_key = b.mbr_contrived_key
> AND a.subscriber = b.subscriber
> AND a.suffix = b.suffix
> AND a.elig_class_prod_cat = b.elig_class_prod_cat
> )
>
> WHEN NOT MATCHED THEN
>
> INSERT
> (
> prpr_id,
> effective_date,
> terminated_date,
> sys_contract_mbr,
> mbr_contrived_key,
> subscriber,
> suffix,
> elig_class_prod_cat
> )
> VALUES
> (
> b.prpr_id,
> b.effective_date,
> b.terminated_date,
> b.sys_contract_mbr,
> b.mbr_contrived_key,
> b.subscriber,
> b.suffix,
> b.elig_class_prod_cat
> );
>
> COMMIT;
>

This question can be found on
http://asktom.oracle.com/pls/ask/f?p=4950:8:2676350870401824501::NO::F4950_P8_DISPLAYID,F4950_P8_B:5318183934935,Y

Older versions, but may still be valid. Unfortunately, ASKTOM crashes at this moment with: AN ORACLE ERROR!!!!!!!

Shakespeare
(What's in a merge)

>
>
>
>
Received on Fri Dec 08 2006 - 03:56:17 CST