[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 780
  • Last Modified:

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.
0
eryckop
Asked:
eryckop
1 Solution
 
randydCommented:
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.
0
 
peterside7Commented:
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


0
 
eryckopAuthor Commented:
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,
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
randydCommented:
if you have any reference book.. look up:

CREATE OR REPLACE TRIGGER...

for the full syntax.
0
 
sujit_kumarCommented:
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

0
 
awking00Commented:
You might try using a function in conjunction with a sequence. Create sequence pk_seq as peterside7 indicated, then create function:

create or replace function get_new_pk return varchar2 is
new_pk  VARCHAR2(5);
begin
select pk_seq.nextval
into new_pk
from dual;
new_pk := 'AB'||lpad(to_char(new_pk),3,'0');
return new_pk;
end;
/

I don't know what the significance of the 'AB' is but, if it varies, you can parameterize the function to pass in its value, assign it to a variable, then do the concatenation.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now