Oracle Database Discussions

Hello,

I have the following table in my oracle database :

create table FLTRPTDATA
(
esn VARCHAR2(10) default 'ALL' not null,
periodid NUMBER not null,
yearid NUMBER default 2007 not null,
reportid NUMBER not null,
entryid NUMBER not null,
operatorid VARCHAR2(30) default 'XXX' not null,
param1 VARCHAR2(200),
param2 VARCHAR2(200),
param3 VARCHAR2(200),
param4 VARCHAR2(200),
param5 VARCHAR2(200),
param6 VARCHAR2(200),
param7 VARCHAR2(200),
param8 VARCHAR2(200),
param9 VARCHAR2(200),
param10 VARCHAR2(200),
acid VARCHAR2(8) default 'ALL' not null
)

and also have a composite primary key for the table as follows :

add constraint PK_FLTRPTDATA primary key (ESN, PERIODID, YEARID, REPORTID, ENTRYID, OPERATORID, ACID)
using index

So I expect that I can only insert a new row into the table if the combination of values for ESN,PERIODID,YEARID,REPORTID,ENTRYID,OPERATORID and ACID are unique.

I am running some software that inserts new rows into the table (via an INSERT SQL statement).

When the software runs, it inserts a certain number of rows, but then stops when inserting one of the rows with a Violation of PRIMARY KEY constraint 'PK_FLTRPTDATA'. Cannot insert duplicate key in object 'fltRPTDATA' error.

This implies that the row being inserted contains values for the composite key columns that are NOT unique. However, when I look at the data it fails to insert, I can see that there are NO rows in the table for this combination of column values.

If I then try to MANUALLY insert the same row myself (via my own SQL statement in SQL Developer), the row is inserted with no error.

When I run the software, the first thing it does is delete any rows in the table that it will subsequently insert, so there cannot be any duplicate entries in the table before the inserts take place. I have run the SQL statement that does the deletion myself manually, and it does delete all of the relevant rows.

I have checked the code (I am not the author), and I cannot see anything that would cause the same INSERT to be executed twice.

I have checked the log file generated by the software, and the INSERT is only attempted once (according to the log).

I don't understand why the insert fails when I run the software, and I am wondering if anyone can shed any light on the issue? Has anyone seen this scenario before where you get a PRIMARY KEY constraint violation on a composite key, where the data being inserted IS unique?

Thanks for any help and apologies if this is in the wrong forum,

Jason.