Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> what do I know Oracle 9i MERGE statement is done via insert or update

what do I know Oracle 9i MERGE statement is done via insert or update

From: Peter vu <huskeyPeter_at_yahoo.com>
Date: 21 Feb 2004 14:27:52 -0800
Message-ID: <581e7cc6.0402211427.1a90d640@posting.google.com>


After the completion of oracle 9i MERGE statement, I would like to do some processing depend on whethere the data is insert or updated.

What is the best way to do it?

The value of SQL%ROWCOUNT seems to be always greater than 0 if the merge finished succesfully.

Any help is greatly appriciated.

      MERGE INTO core_shipment a
            USING (
                SELECT  v_shipment.PICKUP_DATE pickupDate
                        FROM dual ) B
            ON ( a.PICKUP_DATE = B.pickupDate)
            WHEN NOT MATCHED THEN
                INSERT ( 
                    SHIPMENT_SEQUENCE_NUMBER,
                    SHIPPER_SEQUENCE_ID,
                    RECORD_CREATE_DATE) 
                    VALUES(
                    SHIPMENT_SEQUENCE_NUMBER.nextval,  
                    v_shipment.SHIPPER_SEQUENCE_ID,
                   sysdate)
            WHEN MATCHED THEN
                UPDATE  SET 
                a.SHIPPER_SEQUENCE_ID =
v_shipment.SHIPPER_SEQUENCE_ID,
                a.CONSIGNEE_SEQUENCE_ID =
v_shipment.consignee_sequence_ID,
                a.RECORD_CREATE_DATE = SYSDATE;

            IF SQL%ROWCOUNT = 0 THEN 
                n_new_record_cnt := n_new_record_cnt + 1;
            ELSE
                n_mod_record_cnt := n_mod_record_cnt + 1;
            END IF;
Received on Sat Feb 21 2004 - 16:27:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US