cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Creating a number Sequence in Windchill - Gotcha

Creating a number Sequence in Windchill - Gotcha

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.SequenceGeneratorSmiley FrustratedEQ_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)

6 REPLIES 6

Re: Creating a number Sequence in Windchill - Gotcha

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.

Re: Creating a number Sequence in Windchill - Gotcha

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

Re: Creating a number Sequence in Windchill - Gotcha

Where does "nocache" go in the command?

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

Re: Creating a number Sequence in Windchill - Gotcha

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.

Re: Creating a number Sequence in Windchill - Gotcha

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

 

 

 

Re: Creating a number Sequence in Windchill - Gotcha

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');

Announcements

Thingworx Navigate content has a new home! Click here to access the new Thingworx Navigate forum! ______________________________ Check out the Windchill Tips Board! We're talking about Whirlpool's use of digital twin, augmented reality, and data-driven design!

The NAVIGATE WORKING GROUP is here! Come innovate with PTC!

Sign up for a Working Group