Generate Alphanumeric Sequence

:) and yes, using sequences is how I've seen it achieved too

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,

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

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)

@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,

>>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)

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,

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()

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;
/

Select allOpen in new window

:p

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

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.

:new or :old works only in triggers right, so it does not work in the functions, procedures, packages etc.

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

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;
/

Select allOpen in new window

@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

@Mike

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

Thanks

>> 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;
/

Select allOpen in new window

Just perform what you need:

declare
    v_code varchar2(100);
begin
   ...what used to be the function code
end;
/

Select allOpen in new window

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

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