Why will this query only produce 1 row and not 2? And why does it have a value of 11 and not 1?



  • What do I need to change to make this query output 2 rows, first value 1 and second value 10?

    CREATE TABLE cars (car_id NUMBER, top_speed NUMBER);
    

    CREATE SEQUENCE car_counter START WITH 1 INCREMENT BY 10;

    INSERT INTO cars VALUES (car_counter.NEXTVAL, ‘Ninety’);

    INSERT INTO cars VALUES (car_counter.NEXTVAL, 200);

    COMMIT;



    1. It produces 1 row because 'Ninety' is not a number so the first insert failed
    2. Even though the first insert failed, it still consumed a number from car_counter (i.e., 1), then Oracle increment the sequence by 10 to 11 for next value.
    3. You can change the sequence to INCREMENT BY 9 so that first value is 1, second is 10, but the third is 19. You may START WITH 10 or even 100 if you want things end with 0.

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2