How to generate alpha-numeric sequence in Oracle?

[k***@gmail.com]

Hello All,

After searching the web unsuccessfully for what I am looking for, I
have come here.
I need to generate a 6 digit alpha-numeric sequence of values that
looks like the following.
00001A
00001B
:
00001Z
00002A
00002B
:

Do any of you know how to generate the above using sequence in Oracle?
Any information and/or pointers will be very helpful to me. And, of
course, any sample code would be greatly appreciated. I mainly work in
Java. We are using Oracle 9i.

Thanks in advance for all the help.

[G Quesnel]

I can think of a couple of options, but it would help a lot to
understand what are your expectations...
Are you looking for a solution that will support multiple (1000?)
concurrent users ?
Do you care about performance for insert/creates ?
Can the solution tolerate holes in the alpha component.
Can the solution tolerate holes in the numeric part.

Sorry for asking so many questions, but if we understood better the
business requirements are you trying to satisfy, the better the
solution.

The obvious answer is don't do it - make it a numeric field only.
Otherwise,
have you considered creating two columns / is it an option ?
have you considered precreating a table with all valid values with a
'available/used' flag

hth

[kes]

Post by k***@gmail.com
Hello All,
After searching the web unsuccessfully for what I am looking for, I
have come here.
I need to generate a 6 digit alpha-numeric sequence of values that
looks like the following.
00001A
00001B
00001Z
00002A
00002B

What about creating a regular sequence, then using this:
http://www.oracle.com/technology/sample_code/products/rdb/files/convert_hex.txt

to create your requisite values?

Alex
http://www.lifesabirch.org/

[k***@gmail.com]

Quesnel & kes,

Thanks for your replies. To answer Quesnel's question, the solution can
tolerate holes both in the numeric component and the alpha component.
But, I am not sure about your other question: "Do you care about
performance for insert/creates ? ". Of course, performance should be
taken into account. But, I do not think performance is an issue for the
solution (please see below) I have in mind. Please let me know if that
is not the case.

I could create a function "alphaSequence" wich contains the following
code
offSet := mod(in_val,26);
ret_val := CHR(ASCII('A') + offSet);

I create a sequence, "alpha_seq" starting from -1 for the alpha part.
I create another sequence, "numeric_seq" starting with 0 for my numeric
part.

Then my alpha-numeric sequence can be generated with a function
containing the following code (please note that it is just a psuedo
code):

alpha = alphaSequence(alpha_seq.nextval)
if (alpha = 'A')
then
num_val = numeric_seq.nextval
end if

return alphanum_seq = num_val || alpha (assuming, I format
the numeric part with proper padding).

Post by kes

Post by k***@gmail.com
Hello All,
After searching the web unsuccessfully for what I am looking for, I
have come here.
I need to generate a 6 digit alpha-numeric sequence of values that
looks like the following.
00001A
00001B
00001Z
00002A
00002B
:What about creating a regular sequence, then using this:http://www.oracle.com/technology/sample_code/products/rdb/files/conve...

to create your requisite values?
Alexhttp://www.lifesabirch.org/

[G Quesnel]

What you are proposing is not the worst I have seen, but you could
improve performance by avoiding the call to a user function. If you
check other threads in this forum you will see that your SQL statement
take a performance hit when they must execute PL/SQL.
Trying to build with your proposed solution...
- if you have a second sequence then why not have it cycle back to 1
after it reaches 26 (avoid the MOD function)
- why not use just one sequence, generating the number part, and then
also used with a MOD function to generate the alpha part
- replace the user function by a straight decode function on the MOD
function
(native Oracle function will perform better then user PL/SQL)

The other thing about performance is when mixing data types in index
column, the CBO has less/wrong information and can create less then
optimal plans (see Tom Kytes - Effective Oracle by Design - Chap7)

hth

[DA Morgan]

Post by G Quesnel
What you are proposing is not the worst I have seen, but you could
improve performance by avoiding the call to a user function. If you
check other threads in this forum you will see that your SQL statement
take a performance hit when they must execute PL/SQL.
Trying to build with your proposed solution...
- if you have a second sequence then why not have it cycle back to 1
after it reaches 26 (avoid the MOD function)
- why not use just one sequence, generating the number part, and then
also used with a MOD function to generate the alpha part
- replace the user function by a straight decode function on the MOD
function
(native Oracle function will perform better then user PL/SQL)
The other thing about performance is when mixing data types in index
column, the CBO has less/wrong information and can create less then
optimal plans (see Tom Kytes - Effective Oracle by Design - Chap7)
hth

With this in mind here is a possible solution.

One sequence that is created as:
CREATE sequence leading_digits;

And a second created with MINVALUE 1, MAXVALUE 26, CYCLE
used with the CHR function to convert the digits into letters
A through Z.

I don't like it. But I don't like it primarily because I
question a design that implements a system such as:

00001A
00001B

I'd really like to see the business case.

[Ed Prochak]

Post by k***@gmail.com
Quesnel & kes,
Thanks for your replies. To answer Quesnel's question, the solution can
tolerate holes both in the numeric component and the alpha component.
But, I am not sure about your other question: "Do you care about
performance for insert/creates ? ". Of course, performance should be
taken into account. But, I do not think performance is an issue for the
solution (please see below) I have in mind. Please let me know if that
is not the case.
I could create a function "alphaSequence" wich contains the following
code
offSet := mod(in_val,26);
ret_val := CHR(ASCII('A') + offSet);
I create a sequence, "alpha_seq" starting from -1 for the alpha part.
I create another sequence, "numeric_seq" starting with 0 for my numeric
part.
Then my alpha-numeric sequence can be generated with a function
containing the following code (please note that it is just a psuedo
alpha = alphaSequence(alpha_seq.nextval)
if (alpha = 'A')
then
num_val = numeric_seq.nextval
end if
return alphanum_seq = num_val || alpha (assuming, I format
the numeric part with proper padding).

Post by kes

Post by k***@gmail.com
Hello All,
After searching the web unsuccessfully for what I am looking for, I
have come here.
I need to generate a 6 digit alpha-numeric sequence of values that
looks like the following.
00001A
00001B
00001Z
00002A
00002B
:What about creating a regular sequence, then using this:http://www.oracle.com/technology/sample_code/products/rdb/files/conve...

to create your requisite values?
Alexhttp://www.lifesabirch.org/

Keep in mind that an Oracle sequence can have gaps, so if the alpha_seq
is a looping sequence, isn't it possible to miss the 'A'?

I would suggest one sequence and a function that reformats it. What the
customer wants is just the lower digits of a number represented in base
26 (A-Z) and the rest of the number in decimal. So infact you might
store the value as a number and convert it when you need to display it.

X:=coded_seq.nextval;

letter part := CHR(ASCII('A') + mod(X,26);
digit part := trunc(X,0);
then format the digit part appropriately.

Advantages:
No special cases, miscoded IFs. The code is straight line calculations.

And you can use a numeric attribute to the table instead of character,
so you cannot store incorrectly formatted values. (convert to/from the
character format only in the UI and reports)
If you use CHAR or VARCHAR to store your key, then you need to insert
triggers to reject bad data like '0JUNKIT'

And BTW, try not to top post.
HTH,
ed


A: Because it fouls the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

[Ed Prochak]

On Dec 1, 3:45 pm, "Ed Prochak" <***@gmail.com> wrote:
[]

Post by Ed Prochak
I would suggest one sequence and a function that reformats it. What the
customer wants is just the lower digits of a number represented in base
26 (A-Z) and the rest of the number in decimal. So infact you might
store the value as a number and convert it when you need to display it.
X:=coded_seq.nextval;
letter part := CHR(ASCII('A') + mod(X,26);
digit part := trunc(X,0);

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
That should be
digit part := trunc(X/26,0);
(I hate making typos! My apologies!)

Post by Ed Prochak
then format the digit part appropriately.
No special cases, miscoded IFs. The code is straight line calculations.
And you can use a numeric attribute to the table instead of character,
so you cannot store incorrectly formatted values. (convert to/from the
character format only in the UI and reports)
If you use CHAR or VARCHAR to store your key, then you need to insert
triggers to reject bad data like '0JUNKIT'

[Brian Peasland]

Post by k***@gmail.com
Hello All,
After searching the web unsuccessfully for what I am looking for, I
have come here.
I need to generate a 6 digit alpha-numeric sequence of values that
looks like the following.
00001A
00001B
00001Z
00002A
00002B
Do any of you know how to generate the above using sequence in Oracle?
Any information and/or pointers will be very helpful to me. And, of
course, any sample code would be greatly appreciated. I mainly work in
Java. We are using Oracle 9i.
Thanks in advance for all the help.

You could do this programmatically by creating your own function. An
Oracle sequence will only generate integer values. However, you can
extend this. Think of hexadecimal....with values from 0 to 9 and A to F.
If you were given a decimal integer, you would easily write a routine to
convert it to hex. Just do the same thing, but the values are from 0 to
9 and A to Z. Store your code in a function. Have that code get the
sequence's next value and the do the conversion.

HTH,
Brian
--
===================================================================

Brian Peasland
***@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

[k***@gmail.com]

Hello,

My sincere thanks to everybody for all the suggestions given above and
I apologize for not replying earlier as I got pulled into another
project that needed immediate attention.

As regarding the justification for the business case, the client is
saying they have always used this type of sequence and they want our
system (which they just bought now) to be capable of generating this
sequence. As far as performance goes, our client says that at most they
might use about 5 sequences a month.

Thanks again for all the help.

Post by Brian Peasland

Post by k***@gmail.com
Hello All,
After searching the web unsuccessfully for what I am looking for, I
have come here.
I need to generate a 6 digit alpha-numeric sequence of values that
looks like the following.
00001A
00001B
00001Z
00002A
00002B
Do any of you know how to generate the above using sequence in Oracle?
Any information and/or pointers will be very helpful to me. And, of
course, any sample code would be greatly appreciated. I mainly work in
Java. We are using Oracle 9i.
Thanks in advance for all the help.You could do this programmatically by creating your own function. An

Oracle sequence will only generate integer values. However, you can
extend this. Think of hexadecimal....with values from 0 to 9 and A to F.
If you were given a decimal integer, you would easily write a routine to
convert it to hex. Just do the same thing, but the values are from 0 to
9 and A to Z. Store your code in a function. Have that code get the
sequence's next value and the do the conversion.
HTH,
Brian
--
===================================================================
Brian Peasland
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown- Hide quoted text -- Show quoted text -

[Robert Klemme]

Post by k***@gmail.com
As regarding the justification for the business case, the client is
saying they have always used this type of sequence and they want our
system (which they just bought now) to be capable of generating this
sequence. As far as performance goes, our client says that at most they
might use about 5 sequences a month.

"sequences" or "individual values"?

I would go with Brian's suggestion - that roughly the same thing that I
would have suggested. An alternative might be to create a view that
does the conversion on the fly but if you have dependent tables things
can get messy (and slow) soon.

Kind regards

robert

[steve]

Post by k***@gmail.com
Hello,
My sincere thanks to everybody for all the suggestions given above and
I apologize for not replying earlier as I got pulled into another
project that needed immediate attention.
As regarding the justification for the business case, the client is
saying they have always used this type of sequence and they want our
system (which they just bought now) to be capable of generating this
sequence. As far as performance goes, our client says that at most they
might use about 5 sequences a month.
Thanks again for all the help.

Post by Brian Peasland

Post by k***@gmail.com
Hello All,
After searching the web unsuccessfully for what I am looking for, I
have come here.
I need to generate a 6 digit alpha-numeric sequence of values that
looks like the following.
00001A
00001B
00001Z
00002A
00002B
Do any of you know how to generate the above using sequence in Oracle?
Any information and/or pointers will be very helpful to me. And, of
course, any sample code would be greatly appreciated. I mainly work in
Java. We are using Oracle 9i.
Thanks in advance for all the help.You could do this programmatically by
creating your own function. An

Oracle sequence will only generate integer values. However, you can
extend this. Think of hexadecimal....with values from 0 to 9 and A to F.
If you were given a decimal integer, you would easily write a routine to
convert it to hex. Just do the same thing, but the values are from 0 to
9 and A to Z. Store your code in a function. Have that code get the
sequence's next value and the do the conversion.
HTH,
Brian
--

===================================================================

Post by k***@gmail.com

Post by Brian Peasland
Brian Peasland
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown- Hide quoted text -- Show quoted text -

Yep it's something like base 36,
anyway you get the idea.


steve