Generating Serial Numbers from Database

Hi,

Recently, in one of my e-commerce applications, there was a need for generating a unique serial number. I tried for all possibilities from java code but some one suggested to do this from backend. I tried for below mentioned way and it worked finally.

CREATE TYPE ARRAY AS TABLE OF VARCHAR2(10)
/

CREATE FUNCTION MY_NUMBERS(N IN NUMBER DEFAULT NULL)
RETURN ARRAY
PIPELINED
AS
BEGIN
FOR j IN 1 .. NVL(N,10)
LOOP
PIPE ROW('ACUPRO'||lpad(j,5,0));
END LOOP;
RETURN;
END;
/

select * from TABLE(MY_NUMBERS(3));

COLUMN_VAL
----------
ACUPRO00001
ACUPRO00002
ACUPRO00003


Regards,

Ujjwal Soni