| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Dropping & re-Creating Sequences in PL/SQL
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFE52C.ABEF5D90
Content-Type: text/plain;
charset="iso-8859-1"
How would you reset the sequence? From Oracle 8i SQL reference "to restart the sequence from the different number you must drop and recreate the sequence".
Alex Hillman
-----Original Message-----
From: Jared Still [mailto:jkstill_at_bcbso.com]
Sent: Thursday, June 29, 2000 2:09 PM
To: Multiple recipients of list ORACLE-L
Subject: 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 ;-)
Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address
jkstill_at_teleport.com - private
-- Author: Jared Still INET: jkstill_at_bcbso.com 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). ------_=_NextPart_001_01BFE52C.ABEF5D90 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: Dropping & re-Creating Sequences in PL/SQL</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>How would you reset the sequence? From Oracle 8i SQL = reference "to restart the sequence from the different number you = must drop and recreate the sequence".</FONT></P> <P><FONT SIZE=3D2>Alex Hillman</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Jared Still [<A = HREF=3D"mailto:jkstill_at_bcbso.com">mailto:jkstill_at_bcbso.com</A>]</FONT> <BR><FONT SIZE=3D2>Sent: Thursday, June 29, 2000 2:09 PM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: RE: Dropping & re-Creating Sequences in = PL/SQL</FONT> </P> <BR> <BR> <P><FONT SIZE=3D2>I'll take it a step further and ask 'Why are you = dropping the sequence?'</FONT> </P> <P><FONT SIZE=3D2>Just reset it to the value you need.</FONT> </P> <P><FONT SIZE=3D2>Jared</FONT> </P> <P><FONT SIZE=3D2>On Wed, 28 Jun 2000, Larry G. Elkins wrote:</FONT> </P> <P><FONT SIZE=3D2>> While DDL statements aren't directly supported = in PL/SQL, since the person</FONT> <BR><FONT SIZE=3D2>> is on 8.1.6 (8i Release 2), there isn't = (normally) a need to use DBMS_SQL in</FONT> <BR><FONT SIZE=3D2>> this case. Dynamic SQL is still needed, but, = the old DBMS_SQL construct is</FONT> <BR><FONT SIZE=3D2>> not. The "execute immediate", as the = person spoke of, sounds like it should</FONT> <BR><FONT SIZE=3D2>> work just fine. There are still cases where the = old DBMS_SQL stuff is</FONT> <BR><FONT SIZE=3D2>> preferable; but, creating and dropping = sequences should be able to be done</FONT> <BR><FONT SIZE=3D2>> with "execute immediate" without = resorting to DBMS_SQL:</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> SQL> begin</FONT> <BR><FONT SIZE=3D2>> 2 execute immediate 'create = sequence xxx';</FONT> <BR><FONT SIZE=3D2>> 3 end;</FONT> <BR><FONT SIZE=3D2>> 4 /</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> PL/SQL procedure successfully completed.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> SQL> select xxx.nextval from dual;</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> NEXTVAL</FONT> <BR><FONT SIZE=3D2>> ----------</FONT> <BR><FONT = SIZE=3D2>> = 1</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> SQL> begin</FONT> <BR><FONT SIZE=3D2>> 2 execute immediate 'drop = sequence xxx';</FONT> <BR><FONT SIZE=3D2>> 3 end;</FONT> <BR><FONT SIZE=3D2>> 4 /</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> PL/SQL procedure successfully completed.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> SQL> select xxx.nextval from dual;</FONT> <BR><FONT SIZE=3D2>> select xxx.nextval from dual</FONT> <BR><FONT SIZE=3D2>> = *</FONT> <BR><FONT SIZE=3D2>> ERROR at line 1:</FONT> <BR><FONT SIZE=3D2>> ORA-02289: sequence does not exist</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> So, what type of errors are you seeing? When = you use the execute immediate</FONT> <BR><FONT SIZE=3D2>> with the sequence stuff in-line, you say it = doesn't compile. When you assign</FONT> <BR><FONT SIZE=3D2>> the command to a variable, then, reference the = variable in your execute</FONT> <BR><FONT SIZE=3D2>> immdediate, you say it compiles. Does it work = then?</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> I bring this up because I can do what you are = trying do. This is on an NT</FONT> <BR><FONT SIZE=3D2>> workstation with SP5. And using the newer = dynamic SQL approach of execute</FONT> <BR><FONT SIZE=3D2>> immediate like you did.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> There could be other things in play here = (permissions and roles versus a</FONT> <BR><FONT SIZE=3D2>> direct priv thing, maybe bugs). I have no idea. = I have no problem doing what</FONT> <BR><FONT SIZE=3D2>> you want to do.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Regards,</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Larry G. Elkins</FONT> <BR><FONT SIZE=3D2>> The Elkins Organization Inc.</FONT> <BR><FONT SIZE=3D2>> elkinsl_at_flash.net</FONT> <BR><FONT SIZE=3D2>> 214.954.1781</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> -----Original Message-----</FONT> <BR><FONT SIZE=3D2>> Turner</FONT> <BR><FONT SIZE=3D2>> Sent: Wednesday, June 28, 2000 10:25 AM</FONT> <BR><FONT SIZE=3D2>> To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Since those particular statements are = considered DDL statements, you need</FONT> <BR><FONT SIZE=3D2>> to use the DBMS_SQL commands to execute them = within a pl/sql package.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Christine Turner</FONT> <BR><FONT SIZE=3D2>> Database Administrator</FONT> <BR><FONT SIZE=3D2>> IPS-Sendero</FONT> <BR><FONT SIZE=3D2>> Scottsdale, Arizona</FONT> <BR><FONT SIZE=3D2>> Phone: (800) 321-6899 ext. 3286</FONT> <BR><FONT SIZE=3D2>> Fax: (480) = 946-8224</FONT> <BR><FONT SIZE=3D2>> E-mail: = christine.turner_at_ips-sendero.com</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> -----Original Message-----</FONT> <BR><FONT SIZE=3D2>> From: Mike & Martha = [SMTP:mmrose_at_home.com]</FONT> <BR><FONT SIZE=3D2>> Sent: Wednesday, June 28, 2000 7:09 AM</FONT> <BR><FONT SIZE=3D2>> To: Multiple recipients of list = ORACLE-L</FONT> <BR><FONT SIZE=3D2>> Subject: Dropping = & re-Creating Sequences in PL/SQL</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Hello,</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> I've been unable to get the following commands = to 'compile' in a PL/SQL</FONT> <BR><FONT SIZE=3D2>> package body in Oracle 8.1.6 Version 2 (8i with = NTS 4.0 SP 6a):</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> DROP SEQUENCE sequence_name;</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> CREATE SEQUENCE sequence_name;</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> I get errors like the commands aren't = recognized.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> When I try to use EXECUTE IMMEDIATE 'DROP = SEQUENCE sequence_name'</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> I still get compile errors.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> I can compile when I use to following:</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Temp VARCHAR2(60);</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Temp :=3D 'DROP SEQUENCE sequence_name';</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> EXECUTE IMMEDIATE Temp;</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> I would really appreciate some help = here!</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Michael Rose</FONT> <BR><FONT SIZE=3D2>> mmrose_at_home.com</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> -- </FONT> <BR><FONT SIZE=3D2>> Author: Larry G. Elkins</FONT> <BR><FONT SIZE=3D2>> INET: elkinsl_at_flash.net</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Fat City Network Services -- = (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>> San Diego, California &nb= sp; -- Public Internet access / Mailing Lists</FONT> <BR><FONT SIZE=3D2>> = --------------------------------------------------------------------</FO= NT> <BR><FONT SIZE=3D2>> To REMOVE yourself from this mailing list, send = an E-Mail message</FONT> <BR><FONT SIZE=3D2>> to: ListGuru_at_fatcity.com (note EXACT spelling = of 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>> the message BODY, include a line containing: = UNSUB ORACLE-L</FONT> <BR><FONT SIZE=3D2>> (or the name of mailing list you want to be = removed from). You may</FONT> <BR><FONT SIZE=3D2>> also send the HELP command for other = information (like subscribing).</FONT> <BR><FONT SIZE=3D2>> </FONT> </P> <BR> <P><FONT SIZE=3D2>Jared Still</FONT> <BR><FONT SIZE=3D2>Certified Oracle DBA and Part Time Perl = Evangelist ;-)</FONT> <BR><FONT SIZE=3D2>Regence BlueCross BlueShield of Oregon</FONT> <BR><FONT SIZE=3D2>jkstill_at_bcbso.com - Work - preferred address</FONT> <BR><FONT SIZE=3D2>jkstill_at_teleport.com - private</FONT> </P> <BR> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Jared Still</FONT> <BR><FONT SIZE=3D2> INET: jkstill_at_bcbso.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =Received on Mon Jul 03 2000 - 15:24:21 CDT
 |
 |