Merge involving a collection

From: citte <nicola.amerio_at_gmail.com>
Date: Fri, 21 Sep 2007 04:54:53 -0700
Message-ID: <1190375693.721550.260890@19g2000hsx.googlegroups.com>

Hallo,
I need some help for the syntax of a MERGE instruction with a COLLECTION e a source for data.
I have problems because I'm not so used with MERGE and CAST instructions.

In my code CHECKS_TAB is the collection (indexed by Varchar2), and I'm trying something like this

MERGE INTO UNIT_CHECKS
  USING TABLE (CAST(CHECKS_TAB as CHECKS_TAB_TYPE)) CHECKS_TAB   ON (
    UNIT_NO = CHECKS_TAB.UNIT_NO AND
    START_DT = CHECKS_TAB.START_DT AND
    INTERVAL_NO = CHECKS_TAB.INTERVAL_NO   )
  WHEN MATCHED THEN UPDATE SET

    CHK_PC_PREQ = CHECKS_TAB.CHK_PC_PREQ

, CHK_PC_MAG = CHECKS_TAB.CHK_PC_MAG
, CHK_PROD_GRAD = CHECKS_TAB.CHK_PROD_GRAD
  WHEN NOT MATCHED THEN INSERT VALUES (
    CHECKS_TAB.UNIT_NO, CHECKS_TAB.START_DT, CHECKS_TAB.INTERVAL_NO
, CHECKS_TAB.CHK_PC_PREQ, CHECKS_TAB.CHK_PC_MAG,
CHECKS_TAB.CHK_PROD_GRAD
);

I'm trying this on Oracle 9.2, to be compatible with 10g

Thanks! Received on Fri Sep 21 2007 - 06:54:53 CDT