Nethence Newdoc Olddoc Lab Your IP BBDock  


Warning: those guides are mostly obsolete, please have a look at the new documentation.

UnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigs

Oracle auto increment
 
 
Connect,
. oraenv
sqlplus USERNAME/PASSWORD
 
Create table with an autoincrement id column,
create table table1 (id number, col2 varchar2(255));
create sequence table1_seq
    start with 1 increment by 1 nomaxvalue;
create trigger table1_trigger
    before insert on table1 for each row begin
      select table1_seq.nextval into :new.id from dual;
    end;
    /
commit;
check the sequence and trigger is configured,
select sequence_name from user_sequences;
select trigger_name from user_triggers;
note. to delete sequences and triggers,
#drop sequence test_seq;
#drop trigger test_trigger;
note. enable/disable a trigger,
#alter trigger test_trigger disable;
#alter trigger test_trigger enable;
note. instead of the trigger, it's also possible to autoincrement from the INSERT statement,
#insert into test values(test_seq.nextval, 'voila!');
ref. http://jen.fluxcapacitor.net/geek/autoincr.html
 
Insert a row in that table (first column is autoimcremented) and commit,
insert into table1 values(NULL, 'lala');
insert into table1 values(NULL, 'lili');
commit;
check everything is there,
select * from table1;
 
Delete a row,
delete from table1 where id=2;
delete all table's content,
--truncate table1;
delete table1;
note. same as "delete from table1;"
drop a table,
drop table table1;
 
Other queries,
select table_name from user_tables;
insert into table1 values(NULL, 'lala');
insert into table1 values(NULL, 'lili');
insert into table1 values(NULL, 'lolo');
commit;
 

(obsolete, see the new doc)