PL/SQL problem with 'drop sequence' when called within procedure

Posted on 2004-11-24
Last Modified: 2012-05-05
Hello there,

I want to create a procedure that creates then drops a sequence (the actual procedure does a lot more but this is the part that does not work).

So I assumed that I would use the following:

create or replace procedure create_then_drop_seq is

   create sequence my_seq
   start with 1
   increment by 1
  drop sequence my_seq;

But when I try and execute the procedure it does not work. Even though the lines of code in the procedure work when I input them raw into the SQL console?

Hopefully there will be a nice and simple answer to this question (he says with fingers crossed!).


Question by:Beam
    LVL 2

    Accepted Solution

    The problem is that not all of SQL is valid from within PL/SQL.  Specifically, DDL is prohibited.  This is the workaround:

    create or replace procedure create_then_drop_seq is
    x number ;
       execute immediate 'create sequence my_seq start with 1 increment by 1 nomaxvalue';
       execute immediate 'select my_seq.nextval from dual' into x ;
       dbms_output.put_line(x) ;
       execute immediate 'drop sequence my_seq';
       when others then
       execute immediate 'drop sequence my_seq';
       raise ;

    Note that the sequence is NOT local to the procedure, so if two sessions were to use the procedure they would
    share the same sequence.  I assume that this is what you want, because otherwise you could just use a local (or package) variable and then just say:

    x := x + 1 ;

    Hope this helps

    Author Comment

    Its works now thanks Tony.

    Also thanks for the clear explaination.

    Its good to actually understand why you are doing something.



    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now