generate 4 digit sequence number

just4u_sharath · Post by **just4u_sharath** » Sat Oct 27, 2007 6:55 pm

i have to generate a 4 digit sequence number after the whole job is created. And the when i run the job second time the generates sequence number should be equal to old number +1. i,e for each run sequence number should be increased. for this i cannot use oracle sequencer because of permissions. is there any stage i can use. can i get this number using unix script.

chulett · Post by **chulett** » Sat Oct 27, 2007 7:35 pm

Why not use one of the 'Key Management' routines in the sdk? By the way, what happens when your last number used is 9999?

ArndW · Post by **ArndW** » Sat Oct 27, 2007 10:20 pm

I would recommend having your DBA create a sequence for you. This is what they are designed for and doing it another way is going to be more work to write and much more work to maintain - particularly when things go wrong.
You could use the server key management routines as Craig suggested, or you could create your own control file and update that. Try searching the forum as this topic has come up many times already.
Again - use your database's builtin facilities. Question why there are "permission" issues.

ray.wurlod · Post by **ray.wurlod** » Sat Oct 27, 2007 10:58 pm

How do you propose to guarantee the "+1" requirement across an arbitrary number of parallel processing nodes? Are you going to introduce a bottleneck by forcing a Column Generator stage to execute in sequential mode? Is this, indeed, a sensible requirement in a parallel job?

Minhajuddin · Post by **Minhajuddin** » Sun Oct 28, 2007 10:21 am

You can use the Surrogate key generator stage available in the parallel jobs..
It generates unique numbers on any number of processing nodes....

Hope this helps..

chulett · Post by **chulett** » Sun Oct 28, 2007 11:24 am

They don't want to generate the sequence during the job but rather after it completes, from the way I read the OP. Hence my KeyMgnt suggestion, probably leveraged in a Sequence job.