I have a requirement to increment zero padded value. One of the column in my table is of datatype varchar2. Since the database is a vendor supported product I dont have the ability to create sequence or do auto increment. So the idea here is to get the max varchar2 value and then increment by 1. The output may be either one value or a list of values based on the number of value required. So the data stored in the column is of format "LST000001". So say for example I want to increment and get the next number it would be "LST000002". If I want a list of four number then it would be
The sql to get the max value of the column textvalue and increment by 1. I have not included my actual table name or my actual name rather used dual just get to give some idea. This works perfectly
But the question is how do I get the list? I tried the below statement but this does not work
I am not pl/sql developer. Is my approach to the requirement right? Or is there a easy/different way to do this using oracle sql? I want to do this using oracle sql rather than using java.
Can anyone please suggest me on how to approach this requirement.
Sequences would, of course, be much better for this kind of functionality. If you could create a sequence, even in different schema, it would be much better.
Nevertheless, there's a simple trick to select more than one row from dual in Oracle:
This is a special case of Oracle's hierarchical query.
Now, you can use this trick to select a continuous sequence starting from a maximum value in some table:
I'll leave decoding and encoding the numerical value from/to your desired format to you.
One note though: there are no reading locks in Oracle. You therefore need to make sure that this code for generating sequence of new IDs won't be called twice at the same time, until the generated values are stored and committed to the database. The easiest way would be this sequence of steps:
1) lock the table containing your data (lock table some_table in exclusive mode)
2) generate and read the sequence of IDs using the above queries
3) write the generated IDs back into the table
4) commit the transaction
If you don't lock the table, you risk generating identical IDs from two concurrent processes, which would probably mean that one of them would fail due to unique index violation, or you would have duplicates.
Locking the table means that your processing won't scale (using properly configured sequences is the way to avoid problems with scaling). But since your scheme supports at most a million of distinct IDs, you probably aren't going to generate tens or hundreds of new IDs per second, so this need not be a problem for you.
Note: I've edited your post to wrap a long line in your code tag. See UseCodeTags.
Joined: Feb 23, 2009
Martin thank you so much. Using the hierarchial query and by tweaking my original sql was able to generate the required sequence. Also thank you for the note.