1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Initial value of sequence

Discussion in 'SQL PL/SQL' started by Marco, Dec 5, 2012.

  1. Marco

    Marco Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    this is part of my sql script:

    /.../

    create table1 /.../
    create table2 /.../

    create sequence test_seq;


    CREATE OR REPLACE FUNCTION forward_test_seq(how_many_times integer) RETURN INTEGER IS
    sequence_val integer := 0;
    BEGIN
    FOR i IN 1..how_many_times LOOP
    select test_seq.nextval into sequence_val from dual;
    END LOOP;

    return sequence_val;
    END forward_test_seq;
    /

    declare
    sequence_val integer := 0;
    begin
    sequence_val := forward_test_seq(999);
    end;

    create table3 /.../
    create table4 /.../

    /.../


    So in one script I create few tables, sequence and I want to put some value to sequence but I don't want to do it while creating sequence I have to do it with special function when sequence is already created, but it must be done in the same script. I wanted to do it like is described above but unfortunatelly it doesn't work :( What should I do to make it work ?

    Thanks for your help,
    Marco
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    My question is why do you not want to set the starting value for the sequence when you create it? And why does it not work? I have a working example here:

    Code (SQL):
    SQL>
    SQL> --
    SQL> -- Labor-intensive way
    SQL> --
    SQL>
    SQL> CREATE TABLE table1 (yadda NUMBER, womp varchar2(12), sneep varchar2(40));
     
    TABLE created.
     
    SQL> CREATE TABLE table2 (yipp NUMBER, wappa varchar2(60), umqua varchar2(17));
     
    TABLE created.
     
    SQL>
    SQL> CREATE SEQUENCE test_seq;
     
    SEQUENCE created.
     
    SQL>
    SQL> CREATE OR REPLACE FUNCTION forward_test_seq (p_newval NUMBER)
      2  RETURN NUMBER
      3  IS
      4          v_val NUMBER;
      5  BEGIN
      6          FOR i IN 1..p_newval loop
      7                  SELECT test_seq.NEXTVAL INTO v_val FROM dual;
      8          END loop;
      9
     10          SELECT last_number - 1 INTO v_val FROM user_sequences;
     11
     12          RETURN v_val;
     13  END;
     14  /
     
    FUNCTION created.
     
    SQL>
    SQL> DECLARE
      2          sequence_val INTEGER := 0;
      3  BEGIN
      4          sequence_val := forward_test_seq(999);
      5          dbms_output.put_line('Current sequence value is '||sequence_val);
      6  END;
      7  /
    CURRENT SEQUENCE VALUE IS 1000
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT test_seq.NEXTVAL FROM dual;
     
       NEXTVAL
    ----------
          1000
     
    SQL>
    SQL> DROP SEQUENCE test_seq;
     
    SEQUENCE dropped.
     
    SQL>
    SQL> --
    SQL> -- Preferred way (if the starting sequence value is known)
    SQL> --
    SQL>
    SQL> CREATE SEQUENCE test_seq START WITH 1000 INCREMENT BY 1 nomaxvalue nocycle noorder cache 20;
     
    SEQUENCE created.
     
    SQL>
    SQL> SELECT sequence_name, last_number
      2  FROM user_sequences;
     
    SEQUENCE_NAME                  LAST_NUMBER
    ------------------------------ -----------
    TEST_SEQ                              1000
     
    SQL>
     
  3. Marco

    Marco Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    part of sql script that I use is generated by hibernate and unfortunately there is a bug in hibernate that makes it unable to pass initial value to the sequence, so my solution is a kind of workaround.

    I run your script but it didn't work, I got [ORA-01422: exact fetch returns more than requested number of rows] error when I tried to call forward_test_seq function.

    Marco
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then you're using an older version of Oracle and DUAL has more than one row in it, a violation of how DUAL was created and supposed to be used. DUAL should have ONE value:

    Code (SQL):
     
    SQL> SELECT * FROM dual;
     
    D
    -
    X
     
    SQL>
     
    In later releases of Oracle (10.2 and onward) it's impossible to 'hose' DUAL as it's implemented differently than it was when Oracle was first released. If you are using DUAL you need to find out how many records there are in the table and remove all but the one it started with (as displayed above).
     
    Marco likes this.