We are running MS SQL across our landscape so I can't offer any Oracle specific examples but take a look at the PTC Windchill Customization Guide (~page 1845 in the 10.2 M010 version). It offers some good instruction and examples re; creating the DB sequence.
Note; we're also not using any sort of 'smart' numbering schemes in SAP or Windchill. For this type of functionality (particualrly on the Windchill side) we've found defining additional soft type attributes has been more beneficial and a lot less complex.
An example scenario could be; we have a Part sub-type (we'll call OurPartType) which we want to assign a unique ID starting at 20000000 and incrementing by 1 for each occurance.
For our use case we defined an External Number Range in SAP for Materials (T-Code MMNR) for 20000000 - 29999999.
We then defined a Sequence (non-modeled) in our Windchill MS SQL database using the following:
USE Admin
CREATE TABLE Admin.wt_sequence_wtourparttypeid (dummy CHAR(1), value BIGINT IDENTITY(20000000, 1))
go
CREATE PROCEDURE Admin.wt_get_next_sequence_wtourparttypeid @returnValue BIGINT OUTPUT
AS
INSERT wt_sequence_wtourparttypeid (dummy) VALUES ('x')
SELECT @returnValue = SCOPE_IDENTITY()
Go
The final step was to update the OIR for the sub-type Part OurPartType to call the Custom Sequence:
<Arg>{GEN:wt.enterprise.SequenceGenerator:wtourparttypeid:8:0}</Arg>
