Solved

Generate Alphanumeric Sequence

Posted on 2013-06-25
22
1,515 Views
Last Modified: 2013-08-31
I am creating a form application using APEX. It has 4 fields as follows.

The channel gives a list of values to be filled into form. Lets say it has 3 values (AGG, SUB, MOB). When AGG is selected, the code should be automatically filled with an alphanumeric sequence let say AGG001. When we are adding a new row in the form if again AGG is selected code should be AGG002 and so forth. Similarly if MOB is selected MOB001, MOB002 etc should be selected. Please let me know if this is possible to implement.

Also can alphanumeric Sequences be created in Oracle. If we can, do we have to create as many sequences as that of list of values in the in the channel or can we have just one sequence to generate different set of alphanumeric sequence..

Please let me know..I am new to APEX. Here is a sample form..

Channel              Code                  Name       Cntry
MOB                    MOB000001     abc              us
AGG                     AGG000001     xyz               us
MOB                    MOB000002      lmn            us
..
..

Thanks
0
Comment
Question by:gs79
  • 6
  • 6
  • 5
  • +2
22 Comments
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 166 total points
ID: 39277041
There are no alpha numeric sequences in oracle but you can just workaround your requirement by creating 3 different sequences in oracle database and then prefix the generated sequence number the with the required code AGG, MOB etc. right.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 334 total points
ID: 39277049
not familiar with APEX, but is there any expectation that the 'next in sequence' is always +1?

assuming there are 3 concurrent users, all choose AGG at roughly the same time, "magic" then grants each form:
AGG001 to user A
AGG002 to user B
AGG003 to user C -- this however gets cancelled (or is incomplete/timesout etc.)

AGG003 would never hit the database
next in line would be
AGG004

is this the sort of thing you are after?
(nb: not sure I'm the person to produce that piece of magic - it's just a common issue I hit with a J2EE app I work with and user expectations can be extreme)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39277051
:) and yes, using sequences is how I've seen it achieved too
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39277059
If you wanted to avoid that scenario of having gaps in the generated sequences because of concurrent users/sessions/timeouts as mentioned by PortletPaul , then you will have to fetch the last committed generated number from the table / manipulate it with global package variables/global temporary tables etc but this will again have its own side affects like getting into unique constraints violation errors etc if the coding/manipulation is not 100% fail proof coverign all possible scenarios.

Thanks,
0
 

Author Comment

by:gs79
ID: 39277072
Maybe we can generate a random number? In that case I have to use a routine inside the application to check if the number exists?

@Nav_kum_v is there a program construct that you can share with me thats one the lines what you are proposing. I would like to look into that option as well..

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39277075
I would advise against random numbers - more trouble than value IMHO
my suggestion is you live with 'gaps' - just be prepared to fight the good fight IF there are expectations as I suggested. (e.g. include in your design docs, get it signed off)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39277098
@gs79, i have seen that kind of stuff while working on production support / development projects several years ago but again i still remember that it was not 100% fail proof which means that as i mentioned sometime 1 or 2 times in 1000 times, some end users used to get those unique constraint violation errors.  You can google around or check in asktom etc websites for more information but my view is that by creating a gap in the generated sequence numbers should not be having any technical / business / functional impact right. So why not you just go with the easy autogenerated sequence numbers as oracle can handle that automatically with no additioal program constructs but yes we will have gaps in the sequence numbers if we go for this option.

Thanks,
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39277284
>>should not be having any technical / business / functional impact
correct (IMHO)

but business users (I have more than once discovered) don't "get it"
- that's the only real impact (people)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39277338
Agreed and right. some are always like that who cannot be convinced and make them understand for what soever reasons :) though the underlying reasons are genuine.

Thanks,
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39277760
Depending on you usage levels and how long you want the code's to be, maybe a timestamp down to fractional seconds appended to the code?  Then you just have the very rare chance of two people inserting in the same fractional second for the same code.
to_char(systimestamp,'YYYYMMDDHH24MISSFF')


Plan-B (but 32 characters), a GUID:  sys_guid()
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39279149
And why not use sequences as someone suggested?
You can combine with a function:
CREATE OR REPLACE FUNCTION next_val ( p_seq_id VARCHAR2)
  RETURN VARCHAR2
IS
  v_nxt_num   NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT ' || p_seq_id || '.NEXTVAL FROM DUAL'
     INTO v_next_num;

  RETURN UPPER ( p_seq_id) || TO_CHAR ( v_next_num, 'FM00000');
END;
/

Open in new window

:p
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:gs79
ID: 39283311
I am using a custom plsql function to generate the PK and i am using the following code:

I want to generate the seqence based on the value of other column i am selecting. For some reason its not recognizing the input.. Here is the custom plsql code i am embedding in the APEX..please let me know if I am doing anything wrong. It works perfectly fine when I use trigger.

But using trigger is not an option due to some requirement..

Here is teh custom plsql function I am using:

declare
  function get_pk(p_channel varchar2) return varchar2
  is
v_code varchar2(100);
begin
if p_channel = 'AGGREGATOR' then
v_code:= 'AGG'||ltrim(to_char(DEMO_CUST_SEQ.nextval, '0000000000'));
elsif p_channel = 'AFFILIATES' then
v_code:= 'AFF'||ltrim(to_char(DEMO_CUST_SEQ.nextval, '0000000000'));
else
v_code:='UNKNOWN';
end if;
return v_code;
end;
begin
  :new.TRCKG_CODE := get_pk(:new.CHNL_NAME);
end;
/

Now when I add a row with CHNL_NAME as AFFILIATES and hit submit it throws following error:



Error computing default value for column ApplyMRU.

ORA-01008: not all variables bound


Please let me know how to resolve this. How to modify the function so that It reads the input and provides a PK (trckg_code)

Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39283316
I'm not an APEX person but :new is typically for triggers.  My guess is APEX doesn't recognize it and sees :new as a bind variable.

I'm also not seeing the reason for the internal function.  Just set the values and be done with it.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39283372
:new or :old works only in triggers right, so it does not work in the functions, procedures, packages etc.
0
 

Author Comment

by:gs79
ID: 39283389
My initial code didnt have a new. I have seen this method of capturing apex field has worked but its not working here in the form page I am trying to design.

Basically track_code value should be based on CHNL_NAME value..as coded in the function:
Here was my original code:

declare
  function get_pk(p_channel varchar2) return varchar2
  is
v_code varchar2(100);
begin
if p_channel = 'AGGREGATOR' then
v_code:= 'AGG'||ltrim(to_char(DEMO_CUST_SEQ.nextval, '0000000000'));
elsif p_channel = 'AFFILIATES' then
v_code:= 'AFF'||ltrim(to_char(DEMO_CUST_SEQ.nextval, '0000000000'));
else
v_code:='UNKNOWN';
end if;
return v_code;
end;
begin
  :TRCKG_CODE := get_pk(:CHNL_NAME);
end;
/
Is there any other way to do it?

Thanks
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39283400
If you NAME the sequences correctly you avoid many headaches:
CREATE SEQUENCE AGG_SEQ;
CREATE SEQUENCE AFF_SEQ;
CREATE SEQUENCE MOB_SEQ;

CREATE OR REPLACE FUNCTION get_pk ( p_channel VARCHAR2)
  RETURN VARCHAR2
IS
  v_nxt_num   NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT ' || p_channel || '_SEQ.NEXTVAL FROM DUAL'
     INTO v_next_num;

  RETURN UPPER ( p_channel) || TO_CHAR ( v_next_num, 'FM0000000000');
END;
/
CREATE OR REPLACE TRIGGER
   BEFORE INSERT ON TABLE MyTable
IS   
BEGIN
  :new.trckg_code := GET_PK(:new.chnl_name);
END;
/

Open in new window

0
 

Author Comment

by:gs79
ID: 39283404
@sligtwv I am sorry i didnt understand set the values and be done with it..Please let me know if there is any way to do it..

Triggers work but the same table is being populated using the same application by uploading the spreadsheet, at that time the tracking code will be manually inserted in spreadsheet and I should be using the values in the spreadsheet but when the user use the form to key in the records it the trck code should be generated by application.

So since same apex application is loading the data in two different ways (form and spreadsheet) I cannot have a trigger..

Thanks
0
 

Author Comment

by:gs79
ID: 39283427
@Mike

As I said using a trigger is not a option..

Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39284073
>> i didnt understand set the values and be done with it..

Inside the pl/sql block you declare an internal function, get_pk, then in the actual code, call the function.

I'm not seeing the reason for the internal function.

You have this (I removed the actual function code):
declare
  function get_pk(p_channel varchar2) return varchar2 
  is
v_code varchar2(100);
begin
... function code
end;
begin
  :new.TRCKG_CODE := get_pk(:new.CHNL_NAME); 
end;
/

Open in new window



Just perform what you need:
declare
    v_code varchar2(100);
begin
   ...what used to be the function code
end;
/

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284160
I am really uncomfortable with this:
the same table is being populated using the same application by uploading the spreadsheet, at that time the tracking code will be manually inserted in spreadsheet

I wouldn't be vouching for the integrity of this
0
 

Author Comment

by:gs79
ID: 39289028
You are right portletpaul, I too am uncomfortable. I dont see any other option due to weird requirement. But I have put some checks in order to ensure that no duplicate tracking code is inserted
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 334 total points
ID: 39289054
:) shared discomfort then :(

>>no duplicate tracking code is inserted
that's a good thing

Cheers, Paul
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now