system-generated codes

[orasbot]

Hi! is there somebody who can help?
My problem is something like this...

I want to add several records in a table. The table has a field whose value must be system generated but not autogenerated by the database. The value must come from "SELECT max(code) FROM table" statement.

Well, this is quite easy if you're on a standalone environment but my application is on a WAN with quite a large number of users and generating unique code is not easy considering users can ADD SEVERAL NEW RECORDS BEFORE SAVING. That complicates an easy problem.

Now, the question is how can i generate a sequential code on every record I inserted in the table before saving the records?

anybody? With Oracle Developer/2000, it can be accomplished with the Pre-Insert Trigger of a block.

thanks!

[kmchong]

You want to have just a unique key, or must be a sequential number without skipping?

[KWell]

Use another table where you save the last key generate.
Before add a new record, get this number, add 1, save the result in the table and use the result like code for you new record.

[orasbot]

yeah, that's a nice idea but that is almost the same as having an autogenerated key. Suppose the user changed his mind, the sequence number has already incremented. What i need here is to generate the code upon saving the records.

thanks anyway.

[KWell]

In this case, the making of the record must be in memory. Before saving the new record, update the ID fields (with any method you want). Then save the record in the table.

[PaulLewis]

I have good Oracle DB support people here who always do the setup of my tables so I am no Oracle guru. What little I do know about Oracle is that we always use a Sequence (Oracle term). By definition, when you do a select on the sequence (treat it like a table that is only ever selectable, not updateable), the sequence is auto incremented by 1 (or whatever you have specified in the sequence setup), regardless of whether we use the returned value or not!

Sounds perfect for what you want, and it is a 0 line solution. Surely I must be on the wrong track for what you need since it must be available in all version of Oracle?

Hope it helps...

Paul Lewis

[orasbot]

yeah! I know what you're saying. That sequence is much like the same with the autonumber of Access but you need to execute a select nextval to generate code. I use also use that and my main concern is how to capture(if there is any) the event similar to PRE-INSERT TRIGGER of blocks in Oracle Developer/2000.
I cannot immediately get the sequence since it can't be decreased supposed the user changed his mind in adding records. In that way, your sequence has already incremented although the user haven't really inserted a record yet. Just imagine, if the user clicked on addnew then cancelupdate then addnew then cancelupdate and so on for a hundred times, your sequence has already incremented a hundred times. What if you have 100 users doing that everyday? That's what i'm trying to avoid. I need to fill the fields with codes only after the user trigger the SAVE button so that no matter how many times the user changes his mind, the sequence won't be affected at all.

KWell got my idea. The problem is, i want a more faster and simpler code that would do the job.

[kmchong]

This is a problem for a long time already, in my memory, more than 20 years old problem. Disregard which database you are using.

In you are running in a single user environment (only one user update to the database), then you can easily achieve the objective.

If your are running under a multiple users environment, this is a no-solution problem. (There is only one ERP application I saw before which solve it by building a table containing all available numbers and set a temporary flag)

In multiple users environment, you either :

get the number when press 'New' - once you get the number from a table, you must commit changes, otherwise deadlock other users. But in this case, you will have skipping numbers.
get the number before save - this is the most common one, but this cannot solve your problem.

I can't see any alternative for the past 15 years, unless it is in a single user environment.

If any brilliant idea can solve this simply, I do think that quite a lot of ERP software vendor will be interested.

[PaulLewis]

So all you are worried about is conserving sequence numbers? I have never designed an application where it was necessary to ensure each number in the sequence was accounted for. That would cause me the exact headache you now have and I simply refuse to have headaches

You must use the sequence and live with the consequences. Otherwise you will be definitely be undone once you are in production. You can get pretty elaborate as per other suggestions and try your hardest to maintain the sequence manually, but I can tell you you WILL cause yourself more problems than you solve.

The only thing *similar* to what you seem to want to do that I have done is to create a "transaction table" and a set of stored procedures which take as input the values in the transaction table to do even simple tasks like insert/delete etc. Each transaction has a unique id (from the sequence) but your tables being operated on will have an unbroken sequence because if the user did decide to bail out 100 times, then there would be nothing in the transaction table and hence no breaks in the sequence for that table.

I don't like this overly much because it meant that most of my code ended up on the Oracle guy's lap which made it difficult for me to justify my existence

But, I haven't had years of experience solving these sort of problems so I am no authority! I like to leave these sort of complexities to the nice people at Oracle (and other DB platforms).

Regards
Paul Lewis