Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Setting start value in a sequence from max value of a field

    Hi

    I need to create a sequence with the start value set to the max value of a field within the same table. When I create the table, I populate it with data, then the sequence is created. I know how to get the max value+1 of the field, but how do I set that as the start value in the sequence? If I have to use PL/SQL, then I'm stuffed as I have no clue.

    Any help much appreciated.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Specify the start with <x> parameter in the create sequence statement.
    If this is a repeated job then I would write a simple PLSQL proc which uses execute immediate 'create sequence...' to create the sequence or you could do a sqlplus script which did the same.

    Alan

    CREATE SEQUENCE XSEQ
    START WITH 2241
    NOMAXVALUE
    MINVALUE 1
    NOCYCLE
    CACHE 20
    NOORDER;

  3. #3
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Setting start value in a sequence from max value of a field

    I Think you want to do this only once. Yes or not ?

    or you will have to do this a lot of times ?

    The option that you can use is to enter the create sequence statement inside an EXECUTE IMMEDIATE command in a PL/SQL block.

    Examples
    The following PL/SQL block contains several examples of dynamic SQL:

    DECLARE
    sql_stmt VARCHAR2(200);
    plsql_block VARCHAR2(500);
    emp_id NUMBER(4) := 7566;
    salary NUMBER(7,2);
    dept_id NUMBER(2) := 50;
    dept_name VARCHAR2(14) := 'PERSONNEL';
    location VARCHAR2(13) := 'DALLAS';
    emp_rec emp%ROWTYPE;
    BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

    sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
    EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

    sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
    EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

    plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
    EXECUTE IMMEDIATE plsql_block USING 7788, 500;

    sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
    RETURNING sal INTO :2';
    EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;

    EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
    USING dept_id;

    EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
    END;

    how EXECUTE IMMEDIATE executes a string you will be able to built the string.
    Joel Pérez

  4. #4
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    EXECUTE IMMEDIATE Statement
    The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or anonymous PL/SQL block. For more information, see Chapter 11.

    http://download-west.oracle.com/docs...ms18.htm#33889
    Joel Pérez

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I believe you said you do not want to use PLSQL correct? All the responses have used PLSQL. With just plain SQL try this (I capitalized the fields you want to modify):

    select 'create sequence SEQ start with ' || to_char(I + 1) ||'; '
    from TABLE

    This will create a line like:
    create sequence seq start with 712;

    capture that line in a spool file or cut&paste to a SQL line in your done.

    HTH

  6. #6
    Join Date
    Jan 2004
    Posts
    2
    Thanks guys for the input. much appreciated.

    I'm guessing I'll have to go down the Dynamic SQL road as this needs to run from a script, one time, when the table is built.

    This is what I have so far

    DECLARE
    sql_stmt VARCHAR(200)
    max Number(9)
    BEGIN
    sql_stmt := 'select max(GRTE_ROUTE_ID_N) + 1 from QRTE_ROUTE
    RETURNING max';
    EXECUTE IMMEDIATE sql_stmt ;
    sql_stmt := 'DROP SEQUENCE QRGP_ROUTE_GROUP_ID_SEQ;
    CREATE SEQUENCE QRGP_ROUTE_GROUP_ID_SEQ
    increment by 1
    start with :1
    NOMAXVALUE
    minvalue 0
    nocycle
    nocache
    noorder';
    EXECUTE IMMEDIATE sql_stmt using max;
    END:

    Of course it doesn't work yet, but I'm not sure how to get teh value returned in the first sql_stmt into the second sql_stmt.

    Any thoughts ??

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Seems to me you are making it more difficult than it needs to be ... it is your data base however.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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