Duplicate rows in Oracle

V Rickert

What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.

fac586

V Rickert wrote:
What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.

Is there an APEX dimension to this? If so, tell us the full APEX and DB versions, and provide full details of what the APEX app is doing (a debug trace of page accept processing is the obvious place to start).

The most likely explanations are:

1. There is no duplicate in the table but there is a join problem in the query reporting on it, resulting in the appearance of a duplicate in the reults. Have you confirmed that the duplicate is really a physical row in the table?

2. There is an APEX page/application process containing an insert on the table that is unexpectedly running on page accept due to it having no condition or an incorrect condition. This will be visible in the Debug trace.

InoL

What would cause Oracle to insert duplicate rows into a table?

Nothing. It's is the programmer that created bad code (if duplicate rows are not supposed to be inserted). If there is no table trigger, than some other code must be responsible for the insert.

BTW. Is this related to your other post:
2533049
Here you did actually create a trigger B-)

Edited by: InoL on May 2, 2013 12:08 PM

V Rickert

I'm thinking it is the use of a unique INDEX on a table that is causing APEX to insert extra rows. The INDEX is not the primary key. Example: A row in an INVOICE table contains invoice information such as invoice number, vendor, date, purchase order number, Work Order number, etc. A sequence number is the primary key as all other data items can be updated by the user. To prevent the same invoice number from being duplicated, a unique composite key/index was created on Invoice, Vendor, and Work Order. If the user, however, needs to change the work order, then APEX creates another row instead of updating the current row. I think the index is the issue and I am investigating that theory.

InoL

If the user, however, needs to change the work order, then APEX creates another row instead of updating the current row.

What kind of page did you create in Apex? Is it a form with report based on the INVOICE table?
How did you specify the Primary Key Type? Managed by database (ROWID) or Primary key columns? In the 2nd case, check if you specified the correct PK column and PK generation method.

Just another question:

a unique composite key/index was created on Invoice, Vendor, and Work Order

Is Invoice the sequence generated invoice number (the PK)? In that case creating a unique index on these 3 columns is not very useful, since Invoice is already always unique. Not that this would explain your duplicate record, though.

Edited by: InoL on May 3, 2013 12:52 PM

V Rickert

The APEX report is a form with report based upon a sql query of the Invoice Table. It contains Header HTML page items and then a detail section where you 'Add a Row' for detail (that part works fine.) The primary key specified is not the APEX-defined ROWID, but a Sequence(Invoice.Seq_id) with associated trigger that I have specified. (So technically the row is not a duplicate as the Invoice.Seq_id is unique. It is the invoice_number that is repeated.) I think I need a unique index on Invoice_number to prevent the 'duplicates'? I do not have that specified.

InoL

Sorry, I'm a bit confused now.

where you 'Add a Row'

So, you are adding a row? Before you had a problem with updating a row.

I think I need a unique index on Invoice_number to prevent the 'duplicates'?

You could do that, but it will just give you an error message. However, it could point to the process that is trying to insert a record when you are only updating a record.

One more thing:

a form with report based upon a sql query of the Invoice Table

So, you are not using the INVOICE table directly as a source? What is the query you are using? It's not a view by any chance, with INSTEAD OF triggers?

Edited by: InoL on May 3, 2013 3:22 PM

V Rickert

I'm sorry. I am just not explaining myself very well!

Here is the scenario.
The invoice table contains a sequence number as the primary key. The sequence number is unique and never duplicated. SO technically, APEX/ORACLE is not erroneously entering rows into the table. My problem is: We cannot use 'invoice_number' as the primary key because it is a vendor-issued invoice number and you never know if Vendor A will use the same invoice number as Vendor B. I am trying to prevent an exact replica of Invoice Number/Vendor A showing up on two different rows: Example Seq_no = 1, invoice=ABC, Vendor=A; seq_no=2, invoice=ABC,Vendor=A. Technically, this is not a duplicate. But logically in our system, it is. Therefore, I am hoping that an index on Invoice/Vendor will prevent the invoice/vendor combination from being replicated at all. So, question: will a unique index on invoice_number/vendor prevent 'duplicates'?

Edited by: V Rickert on May 3, 2013 1:29 PM

InoL

will a unique index on invoice_number/vendor prevent 'duplicates'?

Yes it will.
But is is something completely different than your original post, where, from my understanding, you said that a row was inserted when you updated a record.

V Rickert

The effect to the user is - it is! The user does not see the sequnce_id, only the fact that the invoice number and vendor now occur twice! So for them, the users, duplicates exist. Sorry for the confusion!

Edited by: V Rickert on May 3, 2013 2:04 PM