Home » SQL & PL/SQL » SQL & PL/SQL » Create sequence and trigger with nocache
Create sequence and trigger with nocache [message #206528] Thu, 30 November 2006 07:55 Go to next message
florida
Messages: 82
Registered: April 2006
Member
I need to create a sequence and tigger to autonumber my primary key after each record is input for my Oracle 9i database.

I do not want to use cache and was wondering if this is okay:

create sequence my_seq
start with 0 increment by 1
minvalue 1
nocache cycle order;


Then my trigger:
create or replace trigger my_trg
before insert on mytable
for each row
begin
      select my_seq.nextval into :new.my_id from dual;
end;
/


Can you also advise what difference is with cycle and nocycle and also order and noorder which I assume is sorting?
Re: Create sequence and trigger with nocache [message #206533 is a reply to message #206528] Thu, 30 November 2006 08:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Documentation is a wonderful thing.

In short, the only advantage to using NOCACHE is it won't loose any sequence values if your system fails. On the other hand, it will run slower than if you use CACHE

I believe that ORDER is only needed for RAC or Parallel processing.
Re: Create sequence and trigger with nocache [message #206536 is a reply to message #206528] Thu, 30 November 2006 08:10 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
This will give you all the info you need:

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_6014.htm
Re: Create sequence and trigger with nocache [message #206537 is a reply to message #206536] Thu, 30 November 2006 08:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Eerily similar to my docs.

I wonder how many mirrors of the Oracle docs there are out there.
Re: Create sequence and trigger with nocache [message #206541 is a reply to message #206537] Thu, 30 November 2006 08:28 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Yeah, sorry, just as I was typing my answer, I got called away for a few minutes and when I came back and hit "send message" yours was already there.
All I did was go to Google and type "create sequence oracle noorder" which could have easily been done by the original poster.
Re: Create sequence and trigger with nocache [message #206543 is a reply to message #206528] Thu, 30 November 2006 08:34 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi florida,

in your script two errors are coming i.e

Quote:
1. ORA-04015: ascending sequences that CYCLE must specify MAXVALUE

2. ORA-04006: START WITH cannot be less than MINVALUE


mpdify it like ...

create sequence my_seq
 start with 1
 increment by 1
 minvalue 1
 maxvalue 100
 nocache
 cycle order


rest is ok.

bye
Ashu

Re: Create sequence and trigger with nocache [message #206547 is a reply to message #206541] Thu, 30 November 2006 08:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm only joking. Cool

Re: Create sequence and trigger with nocache [message #206548 is a reply to message #206528] Thu, 30 November 2006 08:47 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If you are using this value as a primary key do NOT use cycle, your table will fail and since sequences can go up to a maximum of number(23), You will never run out of numbers as long as you make your column wide enough to handle a reasonably sized key. Also your use of cache depends on how often you write to the table. If it is only one a minute, then who cares. If it is 100 a second, you will kill performance by not having a cache. Try the following

create your column my_id as number(15)

create sequence my_seq
start with 1
increment by 1
minvalue 1
nomaxvalue
cache 20;








Re: Create sequence and trigger with nocache [message #206575 is a reply to message #206548] Thu, 30 November 2006 10:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And PLEASE don't think you are creating a gap-free sequence!
Re: Create sequence and trigger with nocache [message #206596 is a reply to message #206547] Thu, 30 November 2006 12:23 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Thu, 30 November 2006 09:44
I'm only joking. Cool




I know. It makes me feel better though to justify when I post the same answer Smile
Previous Topic: PROBEL WITH RPOCEDURE
Next Topic: My SQL script (transaction) desnt work
Goto Forum:
  


Current Time: Tue Dec 06 08:51:51 CST 2016

Total time taken to generate the page: 0.16526 seconds