Re: Newbie Q: DROP Table in PL/SQL

"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 Mon Jul 16 2001 - 13:39:53 CEST