RE: Dropping & re-Creating Sequences in PL/SQL

While DDL statements aren't directly supported in PL/SQL, since the person is on 8.1.6 (8i Release 2), there isn't (normally) a need to use DBMS_SQL in this case. Dynamic SQL is still needed, but, the old DBMS_SQL construct is not. The "execute immediate", as the person spoke of, sounds like it should work just fine. There are still cases where the old DBMS_SQL stuff is preferable; but, creating and dropping sequences should be able to be done with "execute immediate" without resorting to DBMS_SQL:

SQL> begin
  2 execute immediate 'create sequence xxx';   3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> select xxx.nextval from dual;

   NEXTVAL

         1

SQL> begin
  2 execute immediate 'drop sequence xxx';   3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> select xxx.nextval from dual;
select xxx.nextval from dual

       *
ERROR at line 1:
ORA-02289: sequence does not exist

So, what type of errors are you seeing? When you use the execute immediate with the sequence stuff in-line, you say it doesn't compile. When you assign the command to a variable, then, reference the variable in your execute immdediate, you say it compiles. Does it work then?

I bring this up because I can do what you are trying do. This is on an NT workstation with SP5. And using the newer dynamic SQL approach of execute immediate like you did.

There could be other things in play here (permissions and roles versus a direct priv thing, maybe bugs). I have no idea. I have no problem doing what you want to do.

Regards,

Larry G. Elkins
The Elkins Organization Inc.
elkinsl_at_flash.net
214.954.1781

-----Original Message-----

From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Christine Turner
Sent: Wednesday, June 28, 2000 10:25 AM
To: Multiple recipients of list ORACLE-L Subject: RE: Dropping & re-Creating Sequences in PL/SQL

Since those particular statements are considered DDL statements, you need to use the DBMS_SQL commands to execute them within a pl/sql package.

Christine Turner
Database Administrator
IPS-Sendero
Scottsdale, Arizona
Phone: (800) 321-6899 ext. 3286
Fax: (480) 946-8224
E-mail: christine.turner_at_ips-sendero.com

-----Original Message-----

From:	Mike & Martha [SMTP:mmrose_at_home.com]
Sent:	Wednesday, June 28, 2000 7:09 AM
To:	Multiple recipients of list ORACLE-L
Subject:	Dropping & re-Creating Sequences in PL/SQL

Hello,

I've been unable to get the following commands to 'compile' in a PL/SQL package body in Oracle 8.1.6 Version 2 (8i with NTS 4.0 SP 6a):

DROP SEQUENCE sequence_name;

CREATE SEQUENCE sequence_name;

I get errors like the commands aren't recognized.

When I try to use EXECUTE IMMEDIATE 'DROP SEQUENCE sequence_name'

I still get compile errors.

I can compile when I use to following:

Temp VARCHAR2(60);

Temp := 'DROP SEQUENCE sequence_name'; Received on Wed Jun 28 2000 - 20:23:46 CDT