Results 1 to 13 of 13
  1. #1
    Join Date
    May 2004
    Posts
    14

    Unanswered: Adding sequence in exiting table.

    Hi All,

    I created a table with a primary key field. My Java application increment this value by 1. Now I want to add sequence in this table so that I don't have to create new code. I already have data in this table and other table refers to this table.

    Manually I can check the latest value in the table in my local development environment and create the sequence but I want to write some script so that anyone can use in any environment.

    Can anyone help in this?

    Thanks,
    Anjib

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone help in this?
    I don't know about your capabilities, but I do know my limitations.
    I am unable to write code going against the DB when I don't know table name or column names.

    COPY & PASTE results from following SQL:

    SELECT * FROM V$VERSION;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Posts
    14

    Schema and more info

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    "CORE 11.2.0.2.0 Production"
    TNS for Linux: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
    =============================

    AGENCY (agency_id, agency_code, agency_name)
    GROUP (group_id, group_name, agency_id)

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SEQUENCE & TABLE are 100% independent objects.
    SEQUENCE can exist without TABLE.
    TABLE can exist without SEQUENCE.

    >I want to write some script so that anyone can use in any environment.
    what exactly does this script need to do?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2004
    Posts
    14
    My problem is I had already create a table without sequence and have data in it. Now how can i add sequence in that table without corrupting existing data?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >My problem is I had already create a table without sequence and have data in it.
    >Now how can i add sequence in that table without corrupting existing data?
    In some/many/most cases SEQUENCE is used by INSERT TRIGGER.
    By definition application is NOT currently implemented to expect/use TRIGGER to populate AGENCY_ID
    So application code must be changed at the same time TRIGGER & SEQUENCE are deployed.
    If application is working OK now, I see no advantage to make any change to use SEQUENCE.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You can create an on insert trigger using the sequence and igore what the application wants to insert. for example in the trigger you can have

    :new.agency_id := agency_s1.nextval;

    and no matter what the application wants to put in, you would use the sequence.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    May 2004
    Posts
    14
    I understand that whenever TRIGGER & SEQUENCE are deployed code need to be changed too. Thanks for that information.

    Now consider the situation where you do development and push the build to production. Some other person will do production deployment.You have no control of production.
    You have already pushed the schema w/o sequence to the production and people are using it. So there are millions of data and some number to represent latest data. Suppose in production you have record from 1,2,.....555. Now if I want to add the sequence and change in production DB if I can know last number is 555 I can say my sequence start at 556 or 600 or whatever value greater than 555 to create a sequence. Real problem is when you don't know what is the latest increment value. You can't just write script and say create a sequence with 200. That will surely bring conflict.

    So I am trying to find the solution to avoid that conflict.

    And yes application is running by creating helper method to generate next sequence number for table. But I think adding sequence will be easy. Isn't so?

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But I think adding sequence will be easy. Isn't so?
    Nothing is impossible for the person who does not have to do it.
    If the change was trivial, then you would not be here asking for us to do your job for you.

    SELECT MAX(AGENCY_ID) FROM AGENCY;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    May 2004
    Posts
    14
    SQL Script

    create sequence test_seq
    start with (SELECT MAX(AGENCY_ID) FROM AGENCY)
    increment by 1
    nomaxvalue;

    no good...

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First find maximum AGENCY_ID (let's say it is equal to 51345).

    Then create a sequence, using the "above number + 1" as the starting value:
    Code:
    create sequence whatever start with 51346;

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >First find maximum AGENCY_ID (let's say it is equal to 51345).
    Between above & below AGENCY_ID could be incremented by other session.
    >Then create a sequence, using the "above number + 1" as the starting value:
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    By all means. But applications have to be maintained (as if you don't know that). Usually, it is done during non-working hours. If there are none (i.e. the application & database are up 24/7), @unzip will probably say so. For other situations, that shouldn't be a problem.

Tags for this Thread

Posting Permissions

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