asked on

Auto-Increase Char DataType in Toad

Hi All,
I have a database created in Oracle using TOAD, I want to know if anyone knows how to make a column(primary key) auto-increase. The column datatype is char and I want to know if its possible to insert values like AB001 and anytime a new record is inserted the last three digits will be increased by one. ie. first reocrds has the id AB001, next record will have ID AB002 and so on.
If this is not possible how can it be done with numeric datatype (remember I'm using Toad), I know is MSSQL there is auto-inrease datatype.

Thanks
Regards.

randyd

you can use a sequence and a trigger.

the sequence will give you a new unique number, and on Insert, you can append that number into your pseudo-key thing.

peterside7

yes, create a sequence, I will give you you the syntax since you're starting with Oracle.

create sequence seq_tabname INCREMENT BY 1 START WITH 1 MAXVALUE 999 MINVALUE 1 NOCYCLE;

to get the sequence number you do :

select seq_tabname.nextval from dual;

Now, you will have to concatenate the sequence value with the string 'AB' and also padding the sequence with '0' to the left to get 'AB001'

You will do all this in a pre-insert trigger

eryckop

ASKER

Thanks for the quick response.
could you please explain a bit on how to implement this, I'm not quite sure where and how to implement this as part of the table that I want to have the auto increase.
if it will not be a big problem could you also include a stepwise description on how to create the trigger for this.

Thanks, hope I'm not asking too much.
Regards,

randyd

if you have any reference book.. look up:

CREATE OR REPLACE TRIGGER...

for the full syntax.

sujit_kumar

I wwas trying for a solution, But faaced a problem.

CREATE TABLE AUTO_INCREASE (A VARCHAR2(3));

CREATE OR REPLACE TRIGGER TRG_AUTO_INCREASE BEFORE INSERT OR UPDATE OF A ON AUTO_INCREASE FOR EACH ROW
BEGIN
LEN_COL NUMBER;
STR VARCHAR2(500);
BEGIN
SELECT DATA_LENGTH INTO LEN_COL FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'AUTO_INCREASE'
AND COLUMN_NAME = 'A';

IF LEN_COL < LENGTH(:NEW.A) THEN
STR := 'ALTER TABLE AUTO_INCREASE MODIFY (A VARCHAR2('||LENGTH(:NEW.A)||'))';
EXECUTE IMMEDIATE STR;
END IF;
END;

INSERT INTO AUTO_INCREASE VALUES ('ABC');

1 Row Inserted.

INSERT INTO AUTO_INCREASE VALUES ('ABCD');

ORA-01401: Inserted value too large for column.

The reason being that the insert statement is pearsed before executed, And we will get this error(ORA-01401) before the insert statement being executed. So the trigger will never be executed.

In my opinion it's not possible through triggers. If somebody knows better solution, is always welcome.

Sujit

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial