Creating a number Sequence in Windchill - Gotcha

[ChrisCollinson]

As you may know, you can create a number sequence in Oracle (and MS SQL) for use with documents wuithin Windchill, applied via the Object Initialisation Rules,  This is from PTC support to set up the sequence and the settings for the OIR: -

1. Log in sqlplus with Windchill schema user.

2. Create Oracle sequence by running command:

exec wtpk.createSequence ('<sequence_name>',<start number>,<increment value>);

For example:

exec wtpk.createSequence ('SEQ_test',00000001,1);

3. Download OIR file of the object, then change the sequence name with new created sequence.

              <!-- set the number to a generated number -->

              <AttrValue id="number" algorithm="com.ptc.windchill.enterprise.revisionControlled.server.impl.NumberGenerator">

                 <Arg>{GEN:wt.enterprise.SequenceGenerator:SEQ_test:8:0}</Arg>

              </AttrValue>

4. Upload this OIR.

5. Now the new created object will follow customized sequence.

I found that when my users started to create documents on the first day it was fine, 0001, 0002 etc, when the users came in the following day and started to create new documents the number jumped up to 0021, 0022 etc.  Very strange!!

I did some research on Oracle Sequences and found that the sequence number uses a cache to store the next available numbers (20 by default) if not set to nocache in the sql statement.

This means that if Oracle is stopped, crashes or some other act of IT, all the numbers that have been stored in the Cache are lost and on restart and the sequence starts at the next number above what was stored in the cache.

I changes the sequence not to use the cache now and set the sequence number back to what it should be, (tested on a dev server first)

[MikeLockwood]

We've known about this for a long time (learned the hard way as you have).  Seems that it should be prominently noted in PTC's Windchill documentation, and also very likely addressed in PTC's scripts to create the sequences. 

Oracle uses 20 as the cached value by default, so new sequences definitely should be created with NOCACHE.

[ptc-1480421]

Does this support Alpha Numeric sequence (i.e. A011A001)?

[rdanner]

Where does "nocache" go in the command?

? exec wtpk.createSequence nocache ( 'Seq_test',000001,1); 

[d_graham]

There is no argument for NOCACHE in the command.

Best to run the command then alter the sequence cache size.

ALTER SEQUENCE <the sequence name> NOCACHE;

or

ALTER SEQUENCE <the sequence name> CACHE 0;

These will both yield the a cache size of 0 for the sequence.

[pricolsupport]

Hi,

When trying to create OIR uisng exec wtpk.createSequence command. im getting bash error such that

bash: syntax error near unexpected token '('

these are the commands used: 

exec WTPK.createSequence('Test', 40001,1);

exec WTPK.createSequence ('Test', 40001,1);

exec wtpk.createSequence('Test', 40001,1);

exec wtpk.createSequence ('Test', 40001,1);

none works. but i could able to create thorugh sqlplus. i wanted to know where the issue is. Kindly give some feedback

Regards,

Logeshwar

[RoyCrerar]

Hi Logeshwar,

I think I see where you are going wrong. 

exec wtpk.createSequence ('Test', 40001,1);

should be 

exec wtpk.createSequence ('Test', '40001','1');