Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    14

    Unanswered: alter sequence dynamically

    How can I change the MIN value of a sequence in case I already have data into the table?! The sequence has Min Value = 1.
    I created the following stored procedure:

    CREATE OR REPLACE PROCEDURE "SP_GET_SEQUENCE"

    is
    curr_val number;
    begin
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || S_PROJECT ||' INCREMENT BY ' || ' select max(entityid) from PROJECT';
    EXECUTE IMMEDIATE 'SELECT ' || S_PROJECT ||'.NEXTVAL FROM DUAL' INTO curr_val;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || S_PROJECT ||'INCREMENT BY 1';
    end;
    /

    but I am getting the following error:
    PLS-00357: Table,View Or Sequence reference 'S_PROJECT' not allowed in this context

    Any ideas what might goes wrong?!

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just curious, where is S_Project being set?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2004
    Posts
    14
    S_project is an already created sequence for table Project. The migration tool that I used can not synchronize the sequences it produces for each table, so I have to update the already created sequences by the migration tool.

    Kostas.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    For

    EXECUTE IMMEDIATE 'blah blah ' || WHATEVER ||' blah';

    to be valid syntax, "WHATEVER" has to be an in-scope variable.

    Also, in the case of ALTER SEQUENCE, you will need to follow INCREMENT BY with an integer literal.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What I was trying to get to is that you are using

    S_PROJECT like a variable, not as part of the command. What you want to do is


    EXECUTE IMMEDIATE 'ALTER SEQUENCE S_PROJECT INCREMENT BY ' || ' select max(entityid) from PROJECT';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by beilstwh
    EXECUTE IMMEDIATE 'ALTER SEQUENCE S_PROJECT INCREMENT BY ' || ' select max(entityid) from PROJECT';
    That will have to be more like:
    Code:
    select max(entityid) into v_num from PROJECT
    EXECUTE IMMEDIATE 'ALTER SEQUENCE S_PROJECT  INCREMENT BY ' || v_num;

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by andrewst
    That will have to be more like:
    Code:
    select max(entityid) into v_num from PROJECT
    EXECUTE IMMEDIATE 'ALTER SEQUENCE S_PROJECT  INCREMENT BY ' || v_num;
    True,
    Sorry when I fixed the first error, I missed the second.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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