Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Question Unanswered: create dynamic sequence

    Hi,
    the next big problem-maybe it's only a syntax problem.

    this is a st.proc to create a dynamic sequence - tablename is DATA_YYYYMMDD (every day a new table is created - this is a requirement!! :-(( )


    i got an error code because of "SQL command not properly ended" -

    it's a stored procedure creating a sequence for the DATA_YYYYMMDD table


    ________________________________________

    create or replace procedure proc_seq
    as
    v_sql_text varchar(255);
    v_seq_text varchar(512);
    begin
    select (max(cdr_id)+1) into v_sql_text from DATA_'|| to_char(sysdate,'YYYYMMDD') ||';
    v_seq_text :='create or replace sequence data_id_seq incremented by 1 start with '|| v_sql_text ||'';
    execute immediate v_seq_text;
    end;
    __________________________________________



    thx...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    1) Try doing this to see what your dynamic SQL actually contains:
    Code:
    dbms_output.put_line(v_seq_text);
    2) You should RTM to see the correct syntax for CREATE SEQUENCE:
    a) There is no "OR REPLACE" option with sequences
    b) It is "INCREMENT BY" not "INCREMENTED BY"

    i.e.
    Code:
    create sequence data_id_seq increment by 1 start with 1;

  3. #3
    Join Date
    Nov 2003
    Posts
    14

    Unhappy

    thx Tony.

    My new procedure looks like this: but i got an error: MISSING KEYWORD in the select statement?

    ________________________________________

    create or replace procedure proc_seq
    as
    v_sql_text varchar(255);
    v_seq_text varchar(512);
    begin
    execute immediate 'select (max(cdr_id)+1) into v_sql_text from DATA_'|| to_char(sysdate,'YYYYMMDD');
    v_seq_text :='create sequence data_id_seq increment by 1 start with '|| v_sql_text;
    execute immediate v_seq_text;
    end;
    __________________________________


    THX....

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It should be:

    execute immediate 'select (max(cdr_id)+1) from DATA_'|| to_char(sysdate,'YYYYMMDD') into v_sql_text;

  5. #5
    Join Date
    Nov 2003
    Posts
    14

    Thumbs up

    that's it --- THX for your help!!!!

    but i got another error because of an invalid number (the max(cdr_id) - but that does'nt care anyway. For each DATA_YYYYMMMDD table a new sequence is created.

    create sequence cdr_id increment by 1 start with 1;

    thanks again for your help!!!!

Posting Permissions

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