Oracle - duplicate keys found for novalidate unique constraint

by GarciaPL on Tuesday 10 January 2017

I was trying recently to add unique constraint on few fields on Oracle. At the beginning I thought that it might be a easy task to do, but at the end some workaround was needed. So, in case of error you might get below message which indicates that Oracle found some duplicate records in your table, even though you specify NOVALIDATE flag which allows you to do not check existing data against that constraint.

ALTER TABLE MY_TABLE ADD CONSTRAINT MY_UNIQUE_CONSTRAINT UNIQUE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE) NOVALIDATE
Error report -
SQL Error: ORA-02299: cannot validate (DB.MY_UNIQUE_CONSTRAINT) - duplicate keys found
02299. 00000 - "cannot validate (%s.%s) - duplicate keys found"
*Cause:    an alter table validating constraint failed because the table has
           duplicate key values.
*Action:   Obvious

That's why we need to define simple INDEX on fields which will be used during setting UNIQUE CONSTRAINT before setting our UNIQUE CONSTRAINT.
CREATE INDEX INDEX_UNIQUE_CONSTRAINT ON MY_TABLE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE);
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_UNIQUE_CONSTRAINT UNIQUE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE) NOVALIDATE;

References : [1] ENABLE NOVALIDATE validating existing data - Ask Tom [2] NOVALIDATE Constraints – No really - richardfoote

Spring Integration - Header Routing by Header Key

by GarciaPL on Sunday 8 January 2017

As you can see Spring Integration posts are going to be continued! This time I would like to share with you small hint of code which will help you to route your message based on existing or not key Signature in your headers.

<int:router id="headerRouter" expression="headers.containsKey('Signature')"
            default-output-channel="processFurther">
    <int:mapping value="false" channel="drop"/>
    <int:mapping value="true" channel="processFurther"/>
</int:router>

References :  [1] Spring Docs - Message Routing

Spring Integration - Cache requests for inbound/outbound gateway

by GarciaPL

Do you use inbound or outbound http gateway in Spring Integration and you was thinking about caching some requests ? There is a solution for that! It's can be achieved with <request-handler-advice-chain> and Spring Cache Advice.

<int-http:outbound-gateway>
   <int-http:request-handler-advice-chain>
        <cache:advice>
              <cache:caching cache="cacheKey">
                    <cache:cacheable method="handle*Message" key="#a0.payload.id"/>
              </cache:caching>
        </cache:advice>
   </int-http:request-handler-advice-chain>
</int-http:outbound-gateway>

You might see above that handle*Message will be considered by Spring Cache Advice to invoke cache functionality. To be more specific we are thinking about handleRequestMessage method and it's parameters defined as a part of HttpRequestExecutingMessageHandler which will be considered as a key - id in our case. Moreover you need to define explicitly cacheManager bean and define key for cache entry (cacheKey in our case) using for instance EhCache.

References : [1] Spring Integration - Cache for inbound/outbound gateway