How to generate alphanumeric id in Oracle which can be shown in vb

Question

In my vb application I want an autogenerated id of alphanumeric characters, like prd100. How can I increment it using Oracle as backend?

Answer 1

Any particular reason it needs to be alphanumeric? If it can just be a number, you can use an Oracle sequence.

But if you want just a random string, you could use the dbms_random function.

select dbms_random.string('U', 20) str from dual;

So you could probably combine these 2 ideas (in the code below, the sequence is called oid_seq):

SELECT dbms_random.string('U', 20) || '_' || to_char(oid_seq.nextval) FROM dual

Answer 2

There are two parts to your question. The first is how to create an alphanumeric key. The second is how to get the generated value.

So the first step is to determine the source of the alpha and the numeric components. In the following example I use the USER function and an Oracle sequence, but you will have your own rules. I put the code to assemble the key in a trigger which is called whenever a row is inserted.

SQL> create table t1 (pk_col varchar2(10) not null, create_date date)
  2  /

Table created.

SQL> create or replace trigger t1_bir before insert on t1 for each row
  2  declare
  3      n pls_integer;
  4  begin
  5      select my_seq.nextval
  6      into n
  7      from dual;
  8      :new.pk_col := user||trim(to_char(n));
  9  end;
 10  /

Trigger created.

SQL> 

The second step requires using the RETURNING INTO clause to retrieve the generated key. I am using SQL*PLus for this example. I confess to having no idea how to wire this syntax into VB. Sorry.

SQL> var new_pk varchar2(10)
SQL> insert into t1 (create_date)
  2      values (sysdate)
  3      returning pk_col into :new_pk
  4  /

1 row created.

SQL> print new_pk

NEW_PK
--------------------------------
APC61

SQL>

Finally, a word of warning.

Alphanumeric keys are a suspicious construct. They reek of "smart keys" which are, in fact, dumb. A smart key is a value which contains multiple parts. At somepoint you will find yourself wanting to retrieving all rows where the key starts with 'PRD', which means using SUBSTR() or LIKE. Even worse someday the definition of the smart key will change and you will have to cascade a complicated update to your table and its referencing foreign keys. A better ides is to use a surrogate key (number) and have the alphanumeric "key" defined as separate columns with a UNIQUE composite constraint to enforce the business rule.

SQL> create table t1 (id number not null
  2                   , alpha_bit varchar2(3) not null
  3                   , numeric_bit number not null
  4                   , create_date date
  5     , constraint t1_pk primary key (id)
  6     , constraint t1_uk unique (alpha_bit, numeric_bit)
  7   )
  8  /

Table created.

SQL>