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.