• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

increment zero padded value using oracle sql

 
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

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
LST000002
LST000003
LST000004
LST000005

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.

Thanks!
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Romeo Ranjan
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
reply
    Bookmark Topic Watch Topic
  • New Topic