I am trying to insert some values in table throught the application and get issue ORA-00001: unique constraint violated. I see that sequences are out of sync with the highest id of the table, but even after fixing the sequence number the error still persists. How can I debug this error more, does oracle logs give more error? how can I see the oracle logs? Thanks Priyank
update: we are using the audit logging plugin and in the domain class for User we catch the save event and log the entry into the audit log
So in User class we do:
class User {
//some attributes, constraints, mappings
def onSave = {
Graaudit aInstance = new Graaudit();
aInstance.eventType= "GRA User Create"
aInstance.eventDescription = "GRA User Created"
aInstance.objectid = username
aInstance.objecttype = 'GRAUSER'
aInstance.user_id = RequestContextHolder.currentRequestAttributes().session.username
aInstance.withTransaction{
aInstance.save()
}
}
}
When we dont have the above code in the onSave event the User is created successfully.
I am assuming its related to hibernate transaction which we are using on aInstance, thats dying or the current transaction is dying due to that save.
If we dont use the transaction we get an exception "org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here"
Not sure how to fix this issue..
Thanks
This question is related to
oracle
hibernate
transactions
This ORA error is occurred because of violation of unique constraint.
ORA-00001: unique constraint (constraint_name) violated
This is caused because of trying to execute an INSERT
or UPDATE
statement that has created a duplicate value in a field restricted by a unique index.
You can resolve this either by
Oracle's error message should be somewhat longer. It usually looks like this:
ORA-00001: unique constraint (TABLE_UK1) violated
The name in parentheses is the constrait name. It tells you which constraint was violated.
Error message looks like this
Error message => ORA-00001: unique constraint (schema.unique_constraint_name) violated
ORA-00001 occurs when: "a query tries to insert a "duplicate" row in a table". It makes an unique constraint to fail, consequently query fails and row is NOT added to the table."
Solution:
Find all columns used in unique_constraint, for instance column a, column b, column c, column d collectively creates unique_constraint and then find the record from source data which is duplicate, using following queries:
-- to find <<owner of the table>> and <<name of the table>> for unique_constraint
select *
from DBA_CONSTRAINTS
where CONSTRAINT_NAME = '<unique_constraint_name>';
Then use Justin Cave's query (pasted below) to find all columns used in unique_constraint:
SELECT column_name, position
FROM all_cons_columns
WHERE constraint_name = <<name of constraint from the error message>>
AND owner = <<owner of the table>>
AND table_name = <<name of the table>>
-- to find duplicates
select column a, column b, column c, column d
from table
group by column a, column b, column c, column d
having count (<any one column used in constraint > ) > 1;
you can either delete that duplicate record from your source data (which was a select query in my particular case, as I experienced it with "Insert into select") or modify to make it unique or change the constraint.
The error message will include the name of the constraint that was violated (there may be more than one unique constraint on a table). You can use that constraint name to identify the column(s) that the unique constraint is declared on
SELECT column_name, position
FROM all_cons_columns
WHERE constraint_name = <<name of constraint from the error message>>
AND owner = <<owner of the table>>
AND table_name = <<name of the table>>
Once you know what column(s) are affected, you can compare the data you're trying to INSERT
or UPDATE
against the data already in the table to determine why the constraint is being violated.
Source: Stackoverflow.com