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

Posted on 2004-11-24
Medium Priority
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

Accepted Solution

Tony_Hasler earned 200 total points
ID: 12670814
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

ID: 12672918
Its works now thanks Tony.

Also thanks for the clear explaination.

Its good to actually understand why you are doing something.



Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

864 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