Increment the date field by a second and Insert.

[James G. Kurikesu]

Hi:

I am writing a Microfocus COBOL program. I am building up records and inserting into an Oracle table which is giving me unique constraint error because of one of the date filed has the same value. I would like to save sysdate and compare it with the current sysdate value. If it is the same can I increment the sysdate while inserting into an oracle table? How do I define it in COBOL to store the date to increment, and how can I use it in the Oracle insert statement?

Any help is appreciated. Thanks.

[A. Clay Stephenson]

Only the super-user is allowed to change the system date -- AND THIS WOULD BE DUMB. The last thing that you want is a system that is changing the date. There is a far from zero probability that more than 1 row might be inserted during the same second.

MF COBOL does have a CURRENT-DATE function that has hundredth of a second resolution BUT the hundredth second part is not supported on all platforms. If the hundedth part always returns 00 then you could increment this portion if an insert fails.

A much smarter method would be to include a SEQUENCE as part of your unique constraint (index). Oracle sequences are very easy to setup and that would completely solve your uniqueness problem.

[Jean-Luc Oudart]

I will go with Clay option.
use the Oracle object sequence to generate a unique id for your record.

TIMESTAMP used to be (and may still be) a pb for databases such as DB2 as it may not be unique !

Then sequence objects can cached into memory increasing performance.

Rgds,
JL

[Yogeeraj_1]

hi,

you can try to do a "sysdate+.001", like in the example below:

yd@MYDB.MU> select to_char(sysdate,'dd/mm/yyyy hh24:mi'), to_char(sysdate+.001,'dd/mm/yyyy hh24:mi')
2* from dual

TO_CHAR(SYSDATE, TO_CHAR(SYSDATE+
________________ ________________
01/08/2003 11:12 01/08/2003 11:14

Elapsed: 00:00:00.00
yd@MYDB.MU>

hope this helps!

regards
Yogeeraj

[Hein van den Heuvel]

If you expect most inserts to work, and only a few to cause duplicate key values (perhaps 1 in 20 or better) then I would consider to simply always attempt the plain insert and just handle the specific condition.
If, and only if, you do get the constraint violation error, then branch of into an alternate code path where you insert with sysdate + retry count. (Hmmm, I suppose you could use the same loop for the initial attempt with rery count = 0). Supposedly the round-trip trhough Oracle will inject enough time skew, but you might need a (micro-)sleep. Also, you probably want to monitor the retry count and give up after a while (10 times?) and/or start sleeping longer between retries.

hth,
Hein.

[Brian Crabtree]

Yogeeraj is correct. You can also do it in fractions (which I find easier). A "1" is one full 24hr day, so 1/24 is one hour, 1/1440 is one minute, etc.

I would recommend looking at your current constraint situation though. I wouldn't place a unique constraint on just the date field, as you won't gain anything from it other than enforcing that no single date can be at the same time. Why dont put the unique constraint across the date field and another column, or across some other columns.

Thanks,

Brian