How do I figure out what is causing an error in a procedure that I am executing
Posted on 2011-02-21
here is the section of the procedure that I receive the error from:
l_insert_filePath := 'INSERT INTO ';
l_insert_filePath := l_insert_filePath || targetSchema;
l_insert_filePath := l_insert_filePath || '.MULTI_PAGES (SITE, PDF_NAME, PDF_PAGE_FILE_PATH, BH_ID, DIR_NUM, DB_INSERTED, DB_UPDATED) ';
l_insert_filePath := l_insert_filePath || ' SELECT REPLACE( REPLACE(NVL(ORG_PERFORMING_BCO, ''N/A''), ''-BCO''), ''-SNIM''), ID || ''_sti_document'' , FILE_NAME, ';
l_insert_filePath := l_insert_filePath || iCounter || ', 0, sysdate, NULL FROM '
l_insert_filePath := l_insert_filePath || sourceSchema;
l_insert_filePath := l_insert_filePath || '.WS_STI_RECORD WHERE id = ' || stiRec
execute immediate l_insert_filePath;
There are 2 triggers on the multi_pages table. One trigger populates the ID field when a record is added to the table using nextval. The other triggers populate other fields such as date and foreign key values and one trigger that creates a record in another table. I don't know what is causing the below error. The procedure is not trying to insert a value into the ID field and the trigger that populates that field is functioning properly as far as I can tell.
SQL Error: ORA-00001: unique constraint (TEMS_ADM_ORIG.PK_MP) violated
ORA-06512: at "TEMS_WS_TEST.COPYSTIRECORDTOBIBHOLDINGS", line 145
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
Could anyone help me to figure out what could be the problem and how to resolve it?