Re: Newbie Q: DROP Table in PL/SQL

Hi Anette

Thanks for the input, all is working

Mat.

"Anette Hansen" <Info_at_AHa-EDV.de> wrote in message news:<9iujq9$69h$1_at_ds10701a.msro.detemobil.de>...
> "Mathew" <mbsev_at_yahoo.com> wrote:
>
> > Hi
> >
> > I want to drop an ORACLE table in a PL/SQL script, but before I drop
> > the table I want to first check that it exists.
> >
> > I tried something like:
> >
> > BEGIN
> > if TableName.EXISTS(1) THEN
> > drop table TableName;
> > end if;
> > END;
> >
> > I got a really horrible cryptic error message as follows:
> >
> > -----------------------------------------------------------------------
> > ERROR at line 3:
> > ORA-06550: line 3, column 4:
> > PLS-00103: Encountered the symbol "DROP" when expecting one of the
> > following:
> > begin declare exit for goto if loop mod null pragma raise
> > return select update while <an identifier>
> > <a double-quoted delimited-identifier> <a bind variable> <<
> > close current delete fetch lock insert open rollback
> > savepoint set sql execute commit forall
> > <a single-quoted SQL string>
> > ------------------------------------------------------------------------
> >
> > Any Help will be greatly appreciated.
> >
> > Thanks in advance
> >
> > Matt.
>
> Hello,
> DDL Command's is in PL/SQL not supported.
> The drop table table_name is a SQL-Statement.
>
> You can use NDS in PL/SQL like:
>
> Begin
>
> If 1=1
> Then
>
> Execute Imediate 'truncate table ... '
> Execute Imediate 'drop table ... '
>
> End If;
> Exception
>
> When Others
> Null; -- nothing to drop
> End;
>
> Note: If you drop a Table the referenced SP and/or Function
> will be invalid
>
> Regards,
>
> Anette
Received on Tue Jul 17 2001 - 11:24:05 CEST