CODEWITHSUNDEEP
oracle11g
Anirudh
Anirudh

Reputation: 28

how to turn off sequence generation in oracle 11g

Does the sequence number get generated again when DML statements are executed?? i have the scenario that two tables say ID1,date1,name 1 in table1 and ID2,ID1,date2 in table 2. both ID1,and ID2 are generated by sequence. when i update the table 2,the ID1 gets changed but the sequence number in table 1 remains the same.I have inserted the ID1 into table 2, still the update is changing the sequence number in table 2 but its either not reflecting back in table 1. Can some one kindly help me out to update the table 2, without changing the sequence number which was generated and inserted into table 2.

Upvotes: 0

Views: 1599

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

Sequence properties are quite simple.

  1. New sequence number is generated every time when you call nextval function. If you inserting a row in a table, new number is generated once per row. For example, you have new sequence:

    insert into my_table (num_field) values (my_sequence.nextval);

You will get 1 in num_field.

  1. For query

    insert into my_table (num_field1, num_field2) values (my_sequence.nextval, my_sequence.nextval);

You will get 1 in BOTH fields.

  1. If you use INSERT .. SELECT my_sequence.nextval ... statement, you will get as many new values as many rows SELECT statement returns.

  2. If you call nextval many times in a row of SELECT statement, you will get ONLY ONE new value per row.

  3. You can use sequence only in a top level SELECT.

  4. To know values that was inserted, use sequence_name.currval function or RETURNING statement:

    insert ... select ... from ... returning <field for sequence>

  5. All of that could be used in both SQL and PL/SQL.

That's all what you need to know about sequences.

P. S. I didn't understand your question, but I'm sure, you can solve all your problems now.

Upvotes: 1

Related Questions