nextval sequence for varchar in sql?

Question

I was wondering if there was a way of making a nextval sequence for a varchar value? I am using iSQL plus, oracle. For e.g. I have a sales table which consists of sale_id, prod_id, cust_name, sale_name. where I insert the data by doing the following:

insert into sales
select sale_id_seq.nextval,
trunc(dbms_random.value(1,100)),
(code for assigning random string for sale_name)
cust_id
from customer;

Answer 1

Your question doesn't make sense. What would a NEXTVAL for a varchar be?

If you want to stored a number in varchar column you can but you shouldn't. It is always best to hold numbers in numeric columns.

If want you want is to generate a stream of A, B, C ... Z, AA, AB ... AZ, AAA then you could write a function which takes a regular sequence and converts it into base 26 (or base 36 if you want to go 1,2 ...9, A, B ... Z, 10, 11 ... 1A). But it is a lot of effort for something with no clear benefit.

If you want a more sophisticated series such as 2009/00000001, 2009/00000002, etc that can be done by using what is known as a code control table. This is a handcrafted table which holds the last assigned value. Again you would need some bespoke code to generate the next value. There are some severe limitations to this approach, not the least of which is that it serializes your inserts: your code has to issue a lock on the table, so a second user cannot insert a record into CUSTOMER until the first user issues a commit (or rollback).

edit

I don't understand why you say DBMS_RANDOM doesn't work. It clearly does:

SQL> select dbms_random.string('U', 5)
  2  from dual
  3  /

DBMS_RANDOM.STRING('U',5)
--------------------------------------------------------

IROGT

SQL>

Answer 2

If you want something for generating test data, have a look at Swingbench/Data Generator