oracle multiple insert gives duplicate index error

Viewed 51

I'm using table for input and i'm doing a insert with select but it gives me error. inp_tab ==> is the input table

Types:

create or replace TYPE                      UT_ACCOUNT_EXTENSION_INP_OBJ AS OBJECT 
(    account_no                          VARCHAR2(9 CHAR)
    ,account_name                         VARCHAR2(40 CHAR)    
); 

Procedure:

create or replace TYPE INP_TAB AS TABLE OF INP_OBJ;

 WHILE (i <= inp_tab.count )
      LOOP

       INSERT INTO table1(account_id ,account_name)

        SELECT           tab2.ACCOUNT_ID ,inp_tab(i).account_name   
                       
        FROM
        TABLE (inp_tab )
        JOIN table2 tab2
        ON table2.account_nbr = inp_tab(i).account_no;
            
        i := i +1;
    
END LOOP;

when I run the procedure with one input record the insert works fine but when I run the procedure with 2 inputs in INP_TAB it fails for the 1st record it self with below error.

-1 Duplicate Insert not allowed in Index

000210 ORA-00001: unique constraint (table1_PK) violated

PL/SQL procedure successfully completed.

0 Answers
Related