Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: Sequence Question

    hi

    Can some explain to me this piece of code..how does it work? when it increments? what does cache means? And how can i directly write to the sequence table...

    CREATE SEQUENCE S1.T1_SEQ
    START WITH 370032
    MAXVALUE 999999999999999999999999999
    MINVALUE 1
    NOCYCLE
    CACHE 20
    NOORDER;


    thankxxx a lott

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Try this site:

    http://www-eleves-isia.cma.fr/docume...-SEQUENCE.html

    What exactly do you mean by 'write directly to the sequence table'? Are you talking about adding records? Or changing one of the existing values?

    HTH
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    CACHE 20 means when it grabs a new number with the NEXTVAL method it will grab 20 and cache them, thus making future selects against the sequence easier.

    If you mean, how do you use the sequence to put a value in a table on insert, you use a trigger...I can post code here if you like.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  4. #4
    Join Date
    Feb 2005
    Posts
    116
    If you mean, how do you use the sequence to put a value in a table on insert, you use a trigger...I can post code here if you like.[/QUOTE]

    Yeah, it would be nice if can paste any sample code on trigger as you said

    thx

  5. #5
    Join Date
    Nov 2004
    Posts
    14
    Quote Originally Posted by a1jit
    Yeah, it would be nice if can paste any sample code on trigger as you said

    thx
    Code:
    create or replace trigger s1.t1_seq_trigger
    before insert
    on table1
    for each row
    begin
    
      select S1.T1_SEQ.nextval into :NEW.ID from dual;
    
    end;
    /
    where table1 is the name of your table and :NEW.ID is the name of the field that you want to insert into

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    If you mean, how do you use the sequence to put a value in a table on insert, you use a trigger
    Maybe I don't exactly get what you mean, but for a standard insert, you don't need a trigger in order to use the sequence :

    Code:
    INSERT INTO table1(int_field)
    VALUES (S1.T1_SEQ.nextval);
    No need for any trigger.

    Regards,

    RBARAER

    PS : BTW, try to use as few triggers as you can, only use them when you have to. They include performance overhead and may well end up in unexpected side effects when triggers fire other triggers (sometimes when you did not expect them to do so !).
    Last edited by RBARAER; 03-03-05 at 10:48.

  7. #7
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Quote Originally Posted by RBARAER
    Maybe I don't exactly get what you mean, but for a standard insert, you don't need a trigger in order to use the sequence :

    Code:
    INSERT INTO table1(int_field)
    VALUES (S1.T1_SEQ.nextval);
    No need for any trigger.

    Regards,

    RBARAER

    PS : BTW, try to use as few triggers as you can, only use them when you have to. They include performance overhead and may well end up in unexpected side effects when triggers fire other triggers (sometimes when you did not expect them to do so !).
    Though depending on your environment, it may be better to use triggers to insert a sequence value than do it in code. I worked in an environment with 50 developers writing code; in order to make sure we had proper integrity, we used triggers.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    OK, I've tried this :

    Code:
    drop table test;
    
    drop sequence seq;
    
    create table test(id number primary key);
    
    create sequence seq start with 1 increment by 1;
    And then from 2 different sessions I launched at the same time :

    Code:
    insert into test(id) select seq.nextval from table1;
    There was absolutely no problem, sequence numbers were correctly serialized.

    However, I also did

    Code:
    drop table test;
    
    drop sequence seq;
    
    drop sequence seq2;
    
    create table test(id number primary key);
    
    create sequence seq start with 1 increment by 1;
    
    create sequence seq2 start with 1 increment by 1;
    And then one session doing this :

    Code:
    insert into test(id) select seq.nextval from table1;
    And the other one doing that :

    Code:
    insert into test(id) select seq2.nextval from table1;
    Of course here the second session waited for the first one to commit, and then raised a PK violation error.

    So I don't see any problem here, and no need for any trigger, even in a multi-user environment.

    Anyone please tell me if I'm wrong (and prove it ).

    Or maybe I misunderstood what you meant by "in order to make sure we had proper integrity", SteveKaram ?

    Regards,

    RBARAER

    PS : Table1 contains about 52000 records. Test done with 10g v10.1.0.2 on AIX.
    Last edited by RBARAER; 03-03-05 at 12:27. Reason: Misunderstanding ?

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes, I expect a small misunderstanding :-)

    It's a case of ensuring the developers are pulling values from the correct sequence for the correct table/column. The simplest and most effective way is to always populate sequence columns with the insert trigger.

    As a new developer, I turn up on site, insert into a table - all columns requiring a sequence are done - without me knowing where from or how.

    By inserting the sequence value with your code, if a new column is added (also possibly a sequence column) then you'll be searching for all those inserts to change throughout your PL/SQL and client apps. By having it trigger based, this is not an issue.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  10. #10
    Join Date
    Aug 2004
    Location
    France
    Posts
    754


    OK, so it was a misunderstanding... sorry.

    I see your point now, and the benefit of having a trigger here in some cases (depending on the development environment, as SteveKaram said...).

    I hope this discussion will have helped the OP anyway .

    Regards,

    RBARAER
    Last edited by RBARAER; 03-03-05 at 12:49.

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    However, since we're already into this.

    I also understand that good written client app software, shouldn't be firing inserts/delete or any dml on themselves, instead they would use a simple API call of a PL/SQL program, which will contain the whole business and integrity logic that demands the transaction, which therefore would be shared for every one of them ( triggers are not ). So the problem of changing/adding columns in client's app, should not be in good written applications.

  12. #12
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    It fully depends on your application. Oracle's official stance is that for integrity, your tools are application level, triggers, and constraints. All three are permissible.

    In my last environment before Oracle, we had 50 developers and over 200 tables all requiring a sequence driven primary key. Asking the developers to remember which sequence to do every time so there comes no primary key problems down the road was impossible. Asking us to write code to situate all those sequences in an API was way too tedious and lengthy. Triggers became the best possible solution.

    Not only that, but a trigger also ensured we could maintain proper auditing. We used create_date and last_update columns on every table, and so our trigger coule be used for all our integrity concerns in one place. It also ensured that there was NO manual modification possible by developers or other DBAs to the table that was outside of the rules. You can easily go around a procedure, just don't run it. A trigger must be explicitly disabled, and that is a command you can audit.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  13. #13
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Quote Originally Posted by JMartinez
    However, since we're already into this.

    I also understand that good written client app software, shouldn't be firing inserts/delete or any dml on themselves, instead they would use a simple API call of a PL/SQL program, which will contain the whole business and integrity logic that demands the transaction, which therefore would be shared for every one of them ( triggers are not ). So the problem of changing/adding columns in client's app, should not be in good written applications.
    Hi,

    I entirely agree - as you say the client shouldn't be doing direct dml. But even when you use PL/SQL API's you can find similar issues.

    If you conceptually imagine that PL/SQL is just another layer of code (albeit closer to the database), the same problems apply. Code has to be identified, modified, re-tested etc. You have to be sure that all inserts are using the correct sequence so on and so forth.

    I'm not saying triggers are the right way, just another way. A lot of it is down to personal preference/style and individual requirements (I think!).

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  14. #14
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Quote Originally Posted by billm
    I'm not saying triggers are the right way, just another way. A lot of it is down to personal preference/style and individual requirements (I think!).
    Indeed.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •