CODEWITHSUNDEEP
sqloracle-databaseauto-increment
Tr1et
Tr1et

Reputation: 895

Oracle - How to make auto-increment column with varchar type?

In my assignment with Oracle 11g, I am asked to make a table with column has this structure:

    [NL|TE|][0-9]^10

Where NL or TE is inputed when INSERT row and [0-9]^10 is an auto-increment 10 digits number. Example:

    NL1234567890 or TE0253627576

When INSERT, the user should only write this:

    INSERT INTO TableA VALUES ('NL');

And the DBMS take care of the rest. So how can I do so? Im still a newbie in this thing.

Upvotes: 2

Views: 4368

Answers (3)

Gary_W
Gary_W

Reputation: 10360

CREATE SEQUENCE your_seq;
/

CREATE OR REPLACE TRIGGER your_tablename_BI
BEFORE INSERT
ON your_tablename
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
 :NEW.your_col := :NEW.your_col || trim(to_char(your_seq.nextval, '0000000000'));

END your_tablename_BI;
/

Upvotes: 3

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

I would keep them as separate columns. One is a VARCHAR2 that takes NL or whatever, the other is a NUMBER which is populated by the sequence.

You can then concatenate them at query time (put it in a view if you want) or use a virtual column.

Why? I can almost guarantee you that at some point you'll have a requirement to query the table on the character portion, or the numeric portion, or to sort on one or the other. Since you kept them separate, this is easy. If you had squashed them into a single column, you would have had to parse the values out at query time which leads to more complicated code than you need.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Sample code?

'NL' || to_char(yoursequence.nextval)

Upvotes: 1

Related Questions