Skip to Main Content
  • Questions

  • Create a unique alpha numeric reference for a record

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Emma.

Asked: March 23, 2018 - 9:21 am UTC

Last updated: March 27, 2018 - 5:28 am UTC

Version: Apex 4.1

Viewed 1000+ times

You Asked

Hi,

I am developing my first application in Oracle Apex 4.1.

I am developing an application that will hold contract information. Each contract has an ID that is a primary key and I am using a sequence and trigger to create this.

The end users of the application would also like for a unique alpha numeric reference number for each contract created. For example - ED1, ED2, ED3 etc

How would I be able to create this? Would I still use a trigger and sequence?


Thanks


and Connor said...

Some simple options

1) Simply prefix the sequence the "S". Voila, now you have a unique alphanumeric reference
2) Convert the sequence number to hex
3) select sys_guid() from dual


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sequence and Trigger to create prefixed reference number

Emma Hannan, March 26, 2018 - 2:47 pm UTC

Thank you Connor, this has been most useful.

I have created a sequence and a trigger. The trigger uses an if then else to prefix letters depending on what type of case is created.

Example trigger below:

</>
begin
if :NEW.column = '1' THEN
select 'ED' || to_char("sequence_SEQ".nextval) into :NEW."column" from dual;
ELSE
if:NEW.column = '2' THEN
select 'SW1A' || to_char("sequence_SEQ".nextval) into :NEW."column" from dual;
ELSE
</>

Connor McDonald
March 27, 2018 - 5:28 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database