ORA-00001: unique constraint exception

[chandrakanth boga]

HI ..

I am getting the following exception when i try to insert a new row in a table:--
===========================================================================

2007-12-11 12:47:27,898 WARN

[net.sf.hibernate.util.JDBCExceptionReporter] SQL Error: 1, SQLState:

23000
2007-12-11 12:47:27,898 ERROR

[net.sf.hibernate.util.JDBCExceptionReporter] ORA-00001: unique

constraint (UST.UK_PROJECT_REFERENCE) violated

2007-12-11 12:47:27,898 WARN

[net.sf.hibernate.util.JDBCExceptionReporter] SQL Error: 1, SQLState:

23000
2007-12-11 12:47:27,898 ERROR

[net.sf.hibernate.util.JDBCExceptionReporter] ORA-00001: unique

constraint (UST.UK_PROJECT_REFERENCE) violated

2007-12-11 12:47:27,898 WARN

[net.sf.hibernate.util.JDBCExceptionReporter] SQL Error: 1, SQLState:

23000
2007-12-11 12:47:27,898 ERROR

[net.sf.hibernate.util.JDBCExceptionReporter] ORA-00001: unique

constraint (UST.UK_PROJECT_REFERENCE) violated

2007-12-11 12:47:27,898 WARN

[net.sf.hibernate.util.JDBCExceptionReporter] SQL Error: 1, SQLState:

23000
2007-12-11 12:47:27,898 ERROR

[net.sf.hibernate.util.JDBCExceptionReporter] ORA-00001: unique

constraint (UST.UK_PROJECT_REFERENCE) violated

2007-12-11 12:47:27,898 ERROR [net.sf.hibernate.impl.SessionImpl]

Could not synchronize database state with session
2007-12-11 12:47:27,898 INFO [STDOUT]

net.sf.hibernate.exception.ConstraintViolationException: Could not

execute JDBC batch update
at

net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConver

ter.java:73)
at

net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionH

elper.java:29)
at net.sf.hibernate.impl.BatcherImpl.convert(BatcherImpl.java:328)
at

net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:135)
at

net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:6

1)
at

net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:5

8)
at

net.sf.hibernate.impl.BatcherImpl.prepareBatchStatement(BatcherImpl.j

ava:111)
at

net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav

a:454)
at

net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav

a:436)
at

net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.j

ava:37)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2449)
at

net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2435)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2392)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2261)
at

net.sf.hibernate.transaction.JTATransaction.commit(JTATransaction.jav

a:52)
at

com.pms.ust.model.USTModelManager.createDossier(USTModelManager.java:

642)
at

com.pms.desyre.v2.EmptySpecialUpgradeSubmitter.submit(EmptySpecialUpg

radeSubmitter.java:56)
at com.jnc.novell.pagewizard.Wizard.finishPage(Wizard.java:600)
at com.jnc.novell.pagewizard.Wizard.perform(Wizard.java:456)
at

com.jnc.novell.pagewizard.Wizard.processWizardRequest(Wizard.java:718

)
at

com.jnc.novell.pagewizard.WizardComponent.ebi_processRequest(WizardCo

mponent.java:75)
at

com.jnc.migration.AbstractComponentWrapper.processRequest(AbstractCom

ponentWrapper.java:118)
at

com.novell.afw.component.core.EboComponentContainer.performInteractio

n(EboComponentContainer.java:140)
at

com.novell.afw.component.core.EboComponentContainer.performBlockingIn

teraction(EboComponentContainer.java:166)
at

com.novell.afw.portal.proxy.EboPortletContainerProxy.performBlockingI

nteraction(EboPortletContainerProxy.java:197)
at

com.novell.afw.portal.aggregation.EboPortletProxyHelper.callPortletAc

tionRequest(EboPortletProxyHelper.java:480)
at

com.novell.afw.portal.aggregation.EboPortalAggregationControllerImpl.

checkAndHandleActionURLType(EboPortalAggregationControllerImpl.java:1

987)
at

com.novell.afw.portal.aggregation.EboPortalAggregationControllerImpl.

initiateRendering(EboPortalAggregationControllerImpl.java:1261)
at

com.novell.afw.portal.aggregation.EboPortalAggregationControllerImpl.

renderPortalPIDResponse(EboPortalAggregationControllerImpl.java:883)
at

com.novell.afw.portal.aggregation.EboPortalAggregationControllerImpl.

renderPortalPIDResponse(EboPortalAggregationControllerImpl.java:832)
at

com.jnc.novell.rendering.EboDocumentRendererServlet.processContentReq

uest(EboDocumentRendererServlet.java:678)
at

com.jnc.novell.rendering.EboDocumentRendererServlet.service(EboDocume

ntRendererServlet.java:453)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at

org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl

icationFilterChain.java:252)
at

org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF

ilterChain.java:173)
at

com.pms.ost.security.OSTDossierSecurityFilter.doFilter(OSTDossierSecu

rityFilter.java:154)
at

org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl

icationFilterChain.java:202)
at

org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF

ilterChain.java:173)
at

com.pms.ust.security.USTDossierSecurityFilter.doFilter(USTDossierSecu

rityFilter.java:241)
at

org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl

icationFilterChain.java:202)
at

org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF

ilterChain.java:173)
at

com.jnc.novell.director.filter.UserSessionMonitorFilter.doFilter(User

SessionMonitorFilter.java:73)
at

org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl

icationFilterChain.java:202)
at

org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF

ilterChain.java:173)
at

org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFi

lter.java:81)
at

org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl

icationFilterChain.java:202)
at

org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF

ilterChain.java:173)
at

org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV

alve.java:213)
at

org.apache.catalina.core.StandardContextValve.invoke(StandardContextV

alve.java:178)
at

org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrinc

ipalValve.java:39)
at

org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(Securit

yAssociationValve.java:153)
at

org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValv

e.java:59)
at

org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j

ava:126)
at

org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j

ava:105)
at

org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal

ve.java:107)
at

org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav

a:148)
at

org.apache.coyote.http11.Http11Processor.process(Http11Processor.java

:856)
at

org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce

ssConnection(Http11Protocol.java:744)
at

org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpo

int.java:527)
at

org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWor

kerThread.java:112)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.BatchUpdateException: ORA-00001: unique

constraint (UST.UK_PROJECT_REFERENCE) violated

at

oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseEr

ror.java:367)
at

oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePrepare

dStatement.java:8739)
at

org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(Wrapped

Statement.java:701)
at

net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.

java:54)
at

net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:128)
... 57 more

===========================================================================


Could any one help me what would be the problem...
Please suggest me as early as possible, as it is an urgent issue.

Thanks And Regards,
Chandrakanth

[Mark Spritzler]

Are you setting your id field in your app and trying to get Hibernate to insert the record, but there is already a record in your database with that id.

Can you post you xml mapping for this resource, especially the <id> and also how you are generating the id.

Thanks

Mark

[chandrakanth boga]

No...i want to create a new order ...which inserts a new row in the table.
But it is not able to insert due to this unique constraint problem.
even if i disable the constraint for that particular coloumn , the error is same.
i will add the hbm file..

[chandrakanth boga]

i tried to post hbm file contents but javaranch site is not allowing me to update the file contents..
how do i do this?

[chandrakanth boga]

hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" > <hibernate-mapping auto-import="true" package="com.pms.ust.model"> <!-- Class mapping definition --> <class name="USTDossier" table="UST_DOSSIER" mutable="true" dynamic-insert="true" dynamic-update="true" > <id name="id" column="OID" type="long" unsaved-value="null" > <generator class="sequence"> <param name="sequence">UST_DOSSIER_SEQ</param> </generator> </id> <version name="version" column="DB_VERSION" unsaved-value="negative" /> <!-- Regular plain mapped columns --> <property name="creationUser" column="CREATION_USER" type="string" not-null="true" /> <property name="creationDate" column="CREATION_DATE" type="date" update="false" /> <property name="projectReference" column="PROJECT_REFERENCE" type="string" update="false" unique="true" /> <many-to-one name="dossierStatus" column="STATUS" class="USTDossierStatus" not-null="true" /> <property name="SSDDescription" column="SSD_DESCRIPTION" type="string" /> <property name="SSDPreferredResponseDate" column="SSD_DELIVERY_DATE" type="date" /> <property name="USTDescription" column="UST_DESCRIPTION" type="string" /> <property name="requestType" column="REQUEST_TYPE" type="string" />

[Mark Spritzler]

To post code, there is a CODE tag below the Add Reply button. So you open your file in some text editor, and copy and paste it into a post. Unfortunately, I cannot read what you posted because it has no indentation and all meshed into one paragraph.

So explain what you mean by you wanting to re-order? Based on what, I hope not your ID.

Mark

[chandrakanth boga]

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping auto-import="true" package="com.pms.ust.model">
 
<!-- Class mapping definition -->
<class name="USTDossier" table="UST_DOSSIER" mutable="true" dynamic-insert="true" dynamic-update="true" >
<id name="id" column="OID" type="long" unsaved-value="null" >
<generator class="sequence">
<param name="sequence">UST_DOSSIER_SEQ</param>
</generator>
 </id>
<version name="version" column="DB_VERSION" unsaved-value="negative" />
 
<!-- Regular plain mapped columns -->
<property name="creationUser" column="CREATION_USER" type="string" not-null="true" />
<property name="creationDate" column="CREATION_DATE" type="date" update="false" />
<property name="projectReference" column="PROJECT_REFERENCE" type="string" update="false" unique="true" />
<many-to-one name="dossierStatus" column="STATUS" class="USTDossierStatus" not-null="true" />
<property name="SSDDescription" column="SSD_DESCRIPTION" type="string" />
<property name="SSDPreferredResponseDate" column="SSD_DELIVERY_DATE" type="date" />
<property name="USTDescription" column="UST_DESCRIPTION" type="string" />
<property name="requestType" column="REQUEST_TYPE" type="string" />
<property name="feasabilityDate" column="FEASABLEAPPROVEDATE" type="date" />
<property name="QAReleaseDate" column="NORDRELEASEDATE" type="date" />
<property name="discussionRoot" column="DISCUSSIONROOT" type="string" />
 
<!-- SAP related associations are mapped here -->
<property name="SAPOrderNumber" column="SAP_ORDER_NUMBER" type="string"/>
        <property name="SAPOrderLineNumber" column="SAP_ORDER_LINE_NUMBER" type="string"/>
        <property name="SAPOrderQTYNumber" column="SAP_ORDER_QUANTITY_SEQ_NUMBER" type="string"/>
        <property name="SAPOrderReceivedDate" column="SAP_ORDER_RECEIVED_DATE" type="date"/>
<property name="SAPOrderManager" column="SAP_ORDER_MANAGER" type="string"/>
<property name="SAPDateMAD" column="SAP_MAD" type="date"/>
<property name="SAPDateRDD" column="SAP_RDD" type="date"/>
<property name="SAPOrderDeskAgreement" column="ORDERDESK_CONDITIONS" type="string"/>
 
<!-- These boolean based strings are private in the class and have logical method for class use -->
<property name="requiresServiceInstructions" column="SERVICEINSTRUCTIONNA" type="string"/>
<property name="USTApproved" column="UST_APPROVED" type="string"/>
<property name="requiresInstallSupport" column="INSTALL_SUPPORT_FLAG" type="string"/>
 
<!-- Initial System information is mapped here as a component excluding the blob initial system value -->
<component name="medicalSystem" class="USTDossierSystemObject" >
            <!-- setup the parent link at runtime -->
<parent name="dossier" />
 
<!-- Initial System information is mapped here as a component mapping -->
<property name="PONumber" column="OBJECT_PO" type="string" />
<property name="systemCode" column="OBJECT_CODE" type="string" />
<property name="description" column="OBJECT_DESC" type="string" />
<property name="invoice" column="OBJECT_INVOICE" type="string" />
<property name="upgradeHistory" column="OBJECT_PREVIOUS_UPG" type="string" />
<property name="softwareRelease" column="OBJECT_SOFTWARE_REL" type="string" />
<property name="city" column="OBJECT_CITY" type="string" />
<property name="hospital" column="OBJECT_HOSPITAL" type="string" />
<property name="country" column="OBJECT_COUNTRY" type="string" />
<property name="orderLineNumber" column="OBJECT_ORDER_LINE_NBR" type="string" />
<property name="quantitySeqNumber" column="OBJECT_QUANTITY_SEQ_NBR" type="string" />
</component>
 
<!-- Quote should also be wrapped in a component mapping one to one -->
<component name="quotation" class="USTDossierQuotation" >
            <!-- setup the parent link at runtime -->
<parent name="dossier" />
 
<!-- Basic quote information is mapped here as a component mapping -->
            <!-- ~pb: 4.4.2007 [Issue #9] Scenario level price method setting -->
            <property name="priceMethodType" column="QUOTE_SELLING_TYPE" type="string" length="1" />
            
            <property name="creationDate" column="QUOTE_CREATIONDATE" type="date" />
<property name="creationUser" column="QUOTE_CREATIONUSER" type="string" />
<property name="expirationDate" column="QUOTE_EXPIRYDATE" type="date" />
<property name="prefix" column="QUOTE_PREFIX" type="string" />
<property name="postfix" column="QUOTE_POSTFIX" type="string" />
<property name="offeringDate" column="QUOTE_OFFERING_DATE" type="date" />
<property name="currency" column="QUOTE_CURRENCY" type="string" />
            <property name="currencyFactor" column="QUOTE_CURRENCY_FACTOR" type="double" />
<property name="agreementDate" column="QUOTE_FINAL_DATE" type="date" />
 
<!-- lazy loaded references to the scenario's -->
<set name="scenarioos" inverse="true" cascade="all-delete-orphan" order-by="NAME ASC" outer-join="false" lazy="true" batch-size="10" >
<key column="UST_DOSSIER_OID" />
<one-to-many class="USTDossierScenario" />
</set>
 
</component>
 
<!-- Dossier Events mapped as unidirectional association with lazy fetching -->
<set name="m_csEvents" access="field" inverse="true" cascade="save-update" order-by="OID DESC" outer-join="true" lazy="true" batch-size="10" >
<key column="UST_DOSSIER_OID" />
<one-to-many class="USTDossierEvent" />
</set>
 

        <!-- Dossier Comments mapped as unidirectional association with lazy fetching -->
<set name="m_csComments" access="field" inverse="true" cascade="all-delete-orphan" order-by="OID DESC" outer-join="false" lazy="true" batch-size="10" >
<key column="UST_DOSSIER_OID" />
<one-to-many class="USTDossierComment"/>
</set>
 
<!-- Dossier OrderItems mapped as unidirectional association with lazy fetching -->
<set name="m_csOrderItems" access="field" inverse="true" cascade="all-delete-orphan" order-by="ITEMNUMBER ASC" outer-join="false" lazy="true" batch-size="10" >
<key column="UST_DOSSIER_OID" />
<one-to-many class="USTDossierOrderItem"/>
</set>
 
<!-- Blob mappings go here, using a set with just one object in it only...saves us from writing an interceptor -->
<!-- The where clause is required because I cannot figure out a way to make the subclass stuff in the AbstractBlob hibernate file
     to work as it is documented / intended by the hibernate folks. Without the where clause hibernate appears to ignore the discriminator
 resulting in errors because the wrong class types are returned -->
<set name="m_csSysConfigBlob" access="field" inverse="true" cascade="all-delete-orphan" outer-join="false" lazy="true" where="BLOB_DISCRIMINATOR='SystemConfigBlob'" >
<key column="UST_DOSSIER_OID" />
<one-to-many class="USTDossierSystemConfigBlob"/>
</set>
<set name="m_csQuoteOfferBlob" access="field" inverse="true" cascade="all-delete-orphan" outer-join="false" lazy="true" where="BLOB_DISCRIMINATOR='QuoteOfferBlob'" >
<key column="UST_DOSSIER_OID" />
<one-to-many class="USTDossierQuoteOfferBlob"/>
</set>
<set name="m_csQuoteAgreeBlob" access="field" inverse="true" cascade="all-delete-orphan" outer-join="false" lazy="true" where="BLOB_DISCRIMINATOR='QuoteAgreementBlob'" >
<key column="UST_DOSSIER_OID" />
<one-to-many class="USTDossierQuoteAgreementBlob"/>
</set>
 

</class>
 
<!-- Named Query definitions for this class -->
<query name="USTDossier_all_instances"><![CDATA[from USTDossier u order by u.creationDate, u.projectReference asc]]></query>
 
</hibernate-mapping>    

[Bougnon Kipre]

You said you disabled the constraint, but that still looks to me like a database constraint.
Could you check constraint UK_PROJECT_REFERENCE in your database
to make sure that is the one you in fact disabled?
[ December 15, 2007: Message edited by: Bougnon Kipre ]

[Mark Spritzler]

OK, here is what I think is happening.

You have

<generator class="sequence">
  <param name="sequence">UST_DOSSIER_SEQ</param>
</generator>

What is the next val from the UST_DOSSIER_SEQ, is it a value that already has a record in the database with that PK id? Did you reset your sequence to 0 because you wanted it re-ordered, but you already have records in your database with those PK values?

Mark

[chandrakanth boga]

Hello Bougnon Kipre,

I have disabled the constraint for UK_PROJECT_REFERENCE.
I checked twice to confirm .
But still the problem is same.
I am surprising.

Thanks and Regards,
Chandrakanth

[chandrakanth boga]

Hello Mark Spritzler,

Actually i changed the sequence and still the problem remains same.
Before the row is going to insert in UST_DOSSIER table, a TRIGGER will create the UST_PROJECTREFERENCE number and isert in to the coloumn.


Thanks and Regards,
Chandrakanth

[Mark Spritzler]

OK, sorry, I kept reading and thinnking the unique constrain violation was on the PK, which is generated from the sequence.

But what you are getting is a different violation, from a different field. Which will not be affected by the sequence. I apologize for not seeing that clearly.

OK, so the column that is in question here is being filled in by the trigger. So the problem is in your trigger, and has absolutely nothing to do with ORM or Hibernate.

Mark

[chandrakanth boga]

Hello Mark Spritzler,

Nevermind.
Even i thought the problem could be trigger.
But since many months it did not given any problem.
Just recently the problem has arised.
And we dont know why??
Here is hte trigget which fills the coloumn:--

CREATE OR REPLACE TRIGGER "UST"."UCV_PROJECT_REFERENCE" 
 BEFORE INSERT ON "UST"."UST_DOSSIER" 
  FOR EACH ROW 
   DECLARE NEW_VALUE VARCHAR(12); UCV_FILTER VARCHAR(12); FOUND BOOLEAN; 
    CURSOR NEW_VALUE_CURSOR (FILTER VARCHAR) IS 
     SELECT 'UCV ' || 
     SUBSTR(TO_CHAR(SYSDATE, 'YYYYMM'),3) || 
     '-' || 
     SUBSTR(TO_CHAR(TO_NUMBER(ALLROWS.LAST_NUMBER + 1001)),2) AS NEW_VALUE FROM (SELECT ROWNUM AS ROW_NUM, 
     SUBSTR(PROJECT_REFERENCE,10) AS LAST_NUMBER FROM UST.UST_DOSSIER WHERE SUBSTR(PROJECT_REFERENCE,0,8) = FILTER) ALLROWS, 
     (SELECT MAX(ROWNUM) AS ROW_NUM  FROM UST.UST_DOSSIER WHERE SUBSTR(PROJECT_REFERENCE,0,8) = FILTER) LASTROW WHERE ALLROWS.ROW_NUM = LASTROW.ROW_NUM; 
    
    BEGIN 
     SELECT 'UCV ' || SUBSTR(TO_CHAR(SYSDATE, 'YYYYMM'),3) INTO UCV_FILTER FROM DUAL; 
      OPEN NEW_VALUE_CURSOR (UCV_FILTER); 
      
      FETCH NEW_VALUE_CURSOR INTO NEW_VALUE; 
      
      IF NEW_VALUE_CURSOR%FOUND 
       THEN :NEW.PROJECT_REFERENCE := NEW_VALUE; 
      ELSE 
       SELECT 'UCV ' || SUBSTR(TO_CHAR(SYSDATE, 'YYYYMM'),3) || '-001' INTO :NEW.PROJECT_REFERENCE FROM DUAL; 
      END IF; 
     CLOSE NEW_VALUE_CURSOR; 
    END; 

Do you find any problem in this trigger.

Cheers

[ Edited to use code tags - Paul Sturrock ]
[ December 18, 2007: Message edited by: Paul Sturrock ]

[Adeel Ansari]

Originally posted by chandrakanth boga:
But since many months it did not given any problem.
Just recently the problem has arised.

Nothing surprising it happens in real life.
Please format your trigger code using '[code]' tag. Thanks.

[Mark Spritzler]

Nothing pops out from reading it.

Just have this question.

What is this for?
SUBSTR(TO_CHAR(SYSDATE, 'YYYYMM'),3)

To me it looks like you are trying to get the current date in YYMM format. The last two digits of the Year then the two digits of the Month. So why use YYYYMM and Substring combined, can't you just use TO_CHAR(SYSDATE, 'YYMM') ?

So this trigger for this field, what is its purpose, do you really need it? What value is it creating that your application needs? Waht is the result it is returning that is a value that is already in the database? That is what you need to look for.

Good Luck

Mark