Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    26

    Unanswered: execute immediate

    can anyone explain me
    execute immediate

    and how can i use dbms_sql to create a sequence or drop one

    in details plz

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    The good news is that there's loads of people willing to answer your query.
    Just type 'execute immediate' into any search engine.

    Here's one site, to get you started...

    http://www.databasejournal.com/featu...le.php/2109681
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The second question is why would you want to dynamictly create and drop a sequence. What are you using it for?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2004
    Posts
    26
    here is my problem

    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

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    duplicate thread.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    This should work for you...
    Of course you can declare the cursor... I just like much more this way...

    Code:
    declare
      v_sql varchar(100);
    begin
      for i in (select object_name
                   from dba_objects
                  where object_type = 'SEQUENCE'
                     and owner = 'SCOTT'
                     and (object_name like '%ER'))
    loop
      v_sql := 'drop sequence ' || i.object_name;
      execute immediate v_sql;
    end loop;
    end;

Posting Permissions

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