Insert and delete in oracle 11g procedure

Question

I need to write a procedure with input parameters like groupid and item ids(array) I need to insert into a table with values(groupid,itemid[0]) all items like (1,11),(1,12),(1,13),etc. I have table with two columns (groupid,itemid). please help me to sort out this problem. I am trying to write merge but getting error

CREATE OR REPLACE PROCEDURE update_product_group_prc ( in_product_group_key IN INT,
in_product_key_array IN dbms_utility.maxname_array ) AS indx pls_integer := in_product_key_array.FIRST; BEGIN

WHILE(INDX IS NOT NULL) LOOP

  MERGE INTO   DIM_PRODUCT_X_GROUP PXG
  USING(SELECT IN_PRODUCT_GROUP_KEY ,
               IN_PRODUCT_KEY_ARRAY(INDX)  IN_PRODUCT_KEY_ARRAY
        FROM DUAL) IN_TBL
  ON(IN_TBL.IN_PRODUCT_GROUP_KEY=PXG.PRODUCT_GROUP_KEY)
  WHEN MATCHED THEN

   -- DELETE --need to delete source records which r not here in array.
   -- WHERE 
  WHEN NOT MATCHED THEN
    INSERT (PRODUCT_GROUP_KEY,PRODUCT_KEY)
    VALUES (IN_TBL.IN_PRODUCT_GROUP_KEY,IN_TBL.IN_PRODUCT_KEY_ARRAY);`

INDX := IN_PRODUCT_KEY_ARRAY.NEXT(INDX); END LOOP;

END update_product_group_prc;

Error(16,41): PL/SQL: ORA-00936: missing expression

The block is a bit strange, you loop over INDX but the value is nowhere used in the loop. So, you exectue exactly the same statement several times. Which is line 16 in your block? — Wernfried Domscheit
you missing INDEX in your VALUES clause: think it's this: VALUES (IN_TBL.IN_PRODUCT_GROUP_KEY,IN_TBL.IN_PRODUCT_KEY_ARRAY(INDEX)); — Richard
please have a look on the procedure.I am very new to oracle.I hope my requirement u got it please give any other solution also.I am not able to create temp table and while inserting values throw loop, getting object not found.It is just one idea to perform my task. — user3048396

mcalmeida mcalmeida · Accepted Answer · 2013-11-29T10:00:05

You have a syntax error. Comment the "WHEN MATCHED THEN" fragment. http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF55028

Insert and delete in oracle 11g procedure

1 Answers