SQL Statement Failed in ATG

Question

I am working in an ATG application. Today, i got the below SQL Exception in /atg/userprofiling/ProfileAdapterRepository and this is because of the violation of unique constraint.

atg.repository.RepositoryException; SOURCE:java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (DCS_USR_ACTVPROM_P) violated

Exception occurs when insert query for the user promotion table dcs_usr_actvpromo of ATG is executed. I searched for when and where this query is executed, what values are passed but i could not find till now.

handleLogin method of atg.scenario.userprofiling.ScenarioProfileFormHandler have been called from the custom FormHandler. I could not find the flow after this method call. After this method call, the exception occurs.

Could anyone let me know what happens inside this method and from where SQL queries are executed in ATG for the above table please?

Answer 1

You did not add the actual sql you are trying to run, but the error message pretty much says it all. You are breaching a unique constraint.

Let's suppose you have a table called Foo and you have a unique column, called bar. If you intend to insert a record into Foo, having a bar value of 'loremipsum', then the statement will not be successful if your Foo table already has a record where bar has the value of 'loremipsum'.

As a result, the insert of:

insert into Foo(bar)
values('loremipsum');

has the risk of failing. To prevent that risk of happening, you have to check whether the value already exists, like this:

insert into Foo(bar)
select 'loremipsum'
from Foo
where 'loremipsum' not in (select distinct bar from Foo)