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

I'll take it a step further and ask 'Why are you dropping the sequence?'

Just reset it to the value you need.

Jared

On Wed, 28 Jun 2000, Larry G. Elkins wrote:

> 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-----
> Turner
> Sent: Wednesday, June 28, 2000 10:25 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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';
>
> EXECUTE IMMEDIATE Temp;
>
> I would really appreciate some help here!
>
> Michael Rose
> mmrose_at_home.com
>
> --
> Author: Larry G. Elkins
> INET: elkinsl_at_flash.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Received on Thu Jun 29 2000 - 12:00:31 CDT