Results 1 to 7 of 7

Thread: drop sequences

  1. #1
    Join Date
    Jul 2004
    Posts
    26

    Unanswered: drop sequences

    how can I drop a sequence which I don't know its full name.
    I'm using the following syntax and its not working
    ------------------------------------------------------
    DECLARE
    cursor seq_cur is
    select object_name
    from dba_objects
    where object_type ='SEQUENCE' AND OWNER = 'scott' AND (object_name like'%er' ) ;

    BEGIN
    for z in seq_cur loop
    drop sequence z.object_name;
    end loop;
    END;
    -------------------------------------------------
    but its not working
    plz help
    thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    EXECUTE THIS QUERY

    SELECT 'DROP SEQUENCE '||SEQUENCE_NAME||';' FROM USER_SEQUENCES WHERE SEQUENCE_NAME LIKE '%er';

    U VLL GET A STATEMENT LIKE THIS

    'DROPSEQUENCE'||SEQUENCE_NAME||';'
    ---------------------------------------------
    DROP SEQUENCE er;

    THEN COPY AND EXCEUTE THE 'DROP SEQUENCE er;'

    GUD LUCK

  3. #3
    Join Date
    Apr 2004
    Posts
    35

    Arrow you cannot use ddl command directly

    please use

    string='drop sequence abc';
    execute immediate string;

    or use dbms_sql package for any ddl command.

    pl/sql engine restrictions?

    skg

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    I am pretty sure all sequence names in USER_SEQUENCES are in UPPER case.

    So, your current query will return nothing
    (unless you created the sequence name with quotes,
    but why would you do that?).

    Code:
    topicadm@Topic_Dev> create sequence mytest1;
    
    Sequence created.
    
    Elapsed: 00:00:00.00
    topicadm@Topic_Dev> select sequence_name from user_sequences;
    
    SEQUENCE_NAME     
    -------------------
    MYTEST1
    I would suggest something along the lines of:
    PHP Code:
    select sequence_name from user_sequences
    where upper
    (sequence_namelike '%ER'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2004
    Posts
    26
    if u can be more specific for
    execute immediate
    and
    dbms_sql package
    thanks in advance

  6. #6
    Join Date
    Jul 2004
    Posts
    26
    help plz
    more details plz

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try this:
    Code:
    Begin
    For Z In (Select Sequence_Name 
                From All_Sequences
               Where Sequence_Owner = 'SCOTT'
                 And Sequence_Name Like'%ER')
    Loop
      Execute Immediate 'Drop Sequence '||Z.Sequence_Name;
    End Loop; 
    End;

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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