Home » SQL & PL/SQL » SQL & PL/SQL » alphanumeric sequence generator (oracle 10g r2)
 alphanumeric sequence generator [message #451157] |
Mon, 12 April 2010 13:22  |
caiti1012
Messages: 3
Registered: April 2010
|
Junior Member |
|
|
Is there a function or has anyone developed a process to generate and maintain an alphanumeric sequence?
I need to be able to sequentially generate both a 3 and 5 byte case sensitive alphanumeric primary key that uses A-Za-z0-9 characters intermittently of which the 5 byte MAY be a subset of the 3 byte. e.g. the key can be 00000 or a3BD7.
so I need to be able to generate a new 5 char using 3 char base and if that value is not available, generate a unique 5 char value and allocate the substr(1,3) of that value backwards.
Ex1: unique customer location comes in - not a preexisting customer name (same name,different location address doesn't exist) --> select nextval.[5char] into v_long_c to insert into customer_loc table, and substr (v_long_c, 1, 3) for customer name table.
EX 2: unique customer location comes in. customer name exists in customer table with 3char val A39, but that location doesn't exist in customer_location table (5chars are location specific).
I need to take the A39 and generate new 5char for that unique location using that prefix (A39) if possible.
However, a number of a39-- already exist, though not all assigned to the same customer name (we're trying to keep them grouped together but that might not be logically feasible)
How do I select next a39||[2char] for that unique location - and if that value is not available (all 62*62 possibilities have been used for A39--), select nextval.[5charseq] into long_key.
I suspect someone out there knows the functions I can use to create this or has written a package to do just this; I suspect with enough time I could do it, but I don't currently have the time or knowledge to develop it within these deadlines. I thought it would be easier.
My short-sighted solution was to create a static table of all iterations of A-z0-9 5 char values, select one, mark it used and move on. Unfortunately 62^5 is a substantial number (913+million records) and that table took a LOT of space, causing my development server to groan and crack miserably. indexing on it takes a lot of space too (and trying to build multiple indexes exceeded database size). But without an index on the 3char field, selecting an available 5 code from it based on the customer_3char prefix took five minutes - much longer than the fraction of a second I need.
|
|
|
|
| Re: alphanumeric sequence generator [message #451158 is a reply to message #451157] |
Mon, 12 April 2010 13:31  |
 |
Michel Cadot
Messages: 68760
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You have to do it with a procedure that implements exactly what you said just keeping the last 3 bytes in a single row table.
BUT you have to lock the tables before any insert or update (and maybe delete) statement and so you decrease concurrency.
Regards
Michel
[Updated on: Mon, 12 April 2010 13:32] Report message to a moderator |
|
|
|
| Re: alphanumeric sequence generator [message #451167 is a reply to message #451158] |
Mon, 12 April 2010 14:45 |
caiti1012
Messages: 3
Registered: April 2010
|
Junior Member |
|
|
Michel,
If anyone has a procedure which does this kind of sequence generation using alphanumeric fields, looping and referencing existing data for possible beginning codes, without selecting from a static table of pregenerated codes, I would love to see it.
However, I lack the ability to formulate and/or implement a design in 24 hours for a procedure that could do what I stated I needed and throw back a complete alphanumeric code on the fly in the time frame I have without using a static reference table. I am now two weeks behind on development, and my deadline to put into test environment for users to try to break it is in two days and my brain, unfortunately, is close to melting point. I'm just not that good.
I had already decided to do what you're suggesting - to keep a static list of my 3char prefixes (62*62*62 = 238,328 potential customer values and build both master_customer_name and customer_location pk values off of those values using numeric 00-99 appendings. There will be a couple of passes for data existing or non-existing, but it shouldn't be near as extensive as I was having to go through with a full table scan (even indexed) on 918Mil records. Boy, did I get big eyes when I quoted that number... and was appropriately chided for being a little too zealous  .
Using numeric for the last two values will give me the ability to +1 to a selected short (possible)/long code and provides 23,832,800 total possibilities.
That should suffice us for a few years on unique customer locations. (we are currently running about 75K-1mil of these annually, and I would expect there should be growth of some nature, barring the apocolypse, in which case it doesn't really matter now, does it?)
And since These 5char fields (and 3char) are actually 6char and 4char preceded by a non-alphanumeric character (we are currently using '#') to indicate that they are not really contracted customers, but allow us to keep up with the data, should I actually somehow run through that 23million codes, I can generate a new set of short_codes preceded by a new character such as %, @, * etc, and have devised the system to do it.
However, let's say I don't see that happening in the next 10 years by which time technology will have evolved to provide me with something far more beautiful elegant and simple, but I - hopefully - will be somewhere else letting my greys actually stay grey and climbing mountains in africa, exploring new zealand or touring the celtic cultures while minds more brilliant and learned than mine solve these issues.
The critical part was to try to keep the number down on our customer_name table if possible and yet still try to keep them tied to a common short code. For reporting purposes we needed to keep the code generation to 3 and 5(actually 4 and 6 as explained above) bytes, so we could merge this process with our existing customer tables (This process I'm developing is to maintain a separate table source for non-contractual customers).
Thanks for any and all suggestions. If something better or more efficient comes up I can always implement as a modification later!
Kathleen
|
|
|
|
| Re: alphanumeric sequence generator [message #451169 is a reply to message #451167] |
Mon, 12 April 2010 15:16 |
|
Michel Cadot
Messages: 68760
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Here's how you can get 3 characters sequence from a standard sequence number:
SQL> with
2 data as (
3 select level-1 a, 100+level-1 b, 3000+level-1 c, 10000+level-1 d, 100000+level-1 e
4 from dual connect by level <= 30
5 ),
6 vals as (select 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' val from dual)
7 select a,
8 substr(val,trunc(a/2704)+1,1)
9 ||substr(val,trunc(mod(a,2704)/52)+1,1)
10 ||substr(val,mod(a,52)+1,1) val_a,
11 b,
12 substr(val,trunc(b/2704)+1,1)
13 ||substr(val,trunc(mod(b,2704)/52)+1,1)
14 ||substr(val,mod(b,52)+1,1) val_b,
15 c,
16 substr(val,trunc(c/2704)+1,1)
17 ||substr(val,trunc(mod(c,2704)/52)+1,1)
18 ||substr(val,mod(c,52)+1,1) val_c,
19 d,
20 substr(val,trunc(d/2704)+1,1)
21 ||substr(val,trunc(mod(d,2704)/52)+1,1)
22 ||substr(val,mod(d,52)+1,1) val_d,
23 e,
24 substr(val,trunc(e/2704)+1,1)
25 ||substr(val,trunc(mod(e,2704)/52)+1,1)
26 ||substr(val,mod(e,52)+1,1) val_e
27 from data, vals
28 /
A VAL B VAL C VAL D VAL E VAL
---------- --- ---------- --- ---------- --- ---------- --- ---------- ---
0 aaa 100 abW 3000 bfK 10000 dKq 100000 KZe
1 aab 101 abX 3001 bfL 10001 dKr 100001 KZf
2 aac 102 abY 3002 bfM 10002 dKs 100002 KZg
3 aad 103 abZ 3003 bfN 10003 dKt 100003 KZh
4 aae 104 aca 3004 bfO 10004 dKu 100004 KZi
5 aaf 105 acb 3005 bfP 10005 dKv 100005 KZj
6 aag 106 acc 3006 bfQ 10006 dKw 100006 KZk
7 aah 107 acd 3007 bfR 10007 dKx 100007 KZl
8 aai 108 ace 3008 bfS 10008 dKy 100008 KZm
9 aaj 109 acf 3009 bfT 10009 dKz 100009 KZn
10 aak 110 acg 3010 bfU 10010 dKA 100010 KZo
11 aal 111 ach 3011 bfV 10011 dKB 100011 KZp
12 aam 112 aci 3012 bfW 10012 dKC 100012 KZq
13 aan 113 acj 3013 bfX 10013 dKD 100013 KZr
14 aao 114 ack 3014 bfY 10014 dKE 100014 KZs
15 aap 115 acl 3015 bfZ 10015 dKF 100015 KZt
16 aaq 116 acm 3016 bga 10016 dKG 100016 KZu
17 aar 117 acn 3017 bgb 10017 dKH 100017 KZv
18 aas 118 aco 3018 bgc 10018 dKI 100018 KZw
19 aat 119 acp 3019 bgd 10019 dKJ 100019 KZx
20 aau 120 acq 3020 bge 10020 dKK 100020 KZy
21 aav 121 acr 3021 bgf 10021 dKL 100021 KZz
22 aaw 122 acs 3022 bgg 10022 dKM 100022 KZA
23 aax 123 act 3023 bgh 10023 dKN 100023 KZB
24 aay 124 acu 3024 bgi 10024 dKO 100024 KZC
25 aaz 125 acv 3025 bgj 10025 dKP 100025 KZD
26 aaA 126 acw 3026 bgk 10026 dKQ 100026 KZE
27 aaB 127 acx 3027 bgl 10027 dKR 100027 KZF
28 aaC 128 acy 3028 bgm 10028 dKS 100028 KZG
29 aaD 129 acz 3029 bgn 10029 dKT 100029 KZH
Regards
Michel
[Updated on: Mon, 12 April 2010 15:26] Report message to a moderator |
|
|
|
|
|
| Re: alphanumeric sequence generator - thanks! [message #451179 is a reply to message #451170] |
Mon, 12 April 2010 21:50 |
caiti1012
Messages: 3
Registered: April 2010
|
Junior Member |
|
|
lol - awesome. Thank you both!
Blackswan, what's great is that I decided on that same solution after getting michel's solution, merging the two together. Talk about confirmation. Thank you!
For your critique and for future readers who might need a solution for a similar situation, here's now the plan:
Create a prefix table for master_code possibilities in design like Black Swans.
Create a suffix table with a numeric PK for each suffix.
Store the most recently assigned suffix_pk in the prefix table.
When assigning a new code to a customer that has an existing prefix, select next suffix from suffix value based on that numeric value+1, update the prefix table with the new suffix value.
if the customer is new, randomly select (where rownum = 1) an available prefix table and go from there.
when I select the max suffix pk (currently 62*62) I will either delete the calling prefix from the prefix table or set an indicator on that prefix to N (I lean towards indicators). I have already written the package that generates new long and short codes; I'll modify that to generate short only; the suffix table should remain unchanged.
I had already written into the daily customer code assignment process that if for some reason we ran out of available codes before a weekly or monthly maintenance package caught that we were approaching the threshold, it would automatically generate a new code short code set. so I'm covered there. The routine maintenance program will count available prefix' and if it approaches or is less than that threshold, a new set of prefixes will be generated with a new leading character (already stored in code_chars table indicated as reserved for leading character only.
voila.
Is it too simple?
Michel, your code does something I have not seen, showing my level of (in)expertise - wow. I created my initial long codes set by creating a code_chars table of characters that could be used in the code, and then generated the short and long codes by cross joining the table to itself 3 and 5 times to populate the individual short and long code reference tables. I couldn't figure out how to cross join an array. to be honest, I am going to have to explore what yours does so I know how to use it. amazing.
Thanks so much for both of you giving this situation thought and responding. If you have any further suggestions or corrections to my current solution, feel free to respond. And again I apologize for the lack of brevity, but hope that someone with the same issue can use these concepts to solve their own or perhaps they can offer something better!
Have a wonderful evening.
Kathleen
[Updated on: Mon, 12 April 2010 21:57] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Jun 20 21:52:39 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
