From: Gary England <gengland@hiwaay.net>
Subject: Re: PL/SQL question
Date: 1997/09/09
Message-ID: <3415DCA3.5129@hiwaay.net>#1/1
References: <01bcb963$68791d80$16e879a1@dmitry40e> <Pine.GSO.3.96.970908105347.7401A-100000@sol10>
Reply-To: gengland@hiwaay.net
To: Pepe Galindo <ppgg@lcc.uma.es>
Newsgroups: comp.databases.oracle,comp.databases.oracle.misc,comp.databases.oracle.server,fido7.rdbms.oracle



Pepe Galindo wrote:
> 
> On 5 Sep 1997, Dmitry Babitsky wrote:
> 
> > I need to find out in my procedure if a table exists and drop and recreate
> > it.
> 
> A possible form is:
> 
> BEGIN
>   select count(*) into aux from TABLA;
> EXCEPTION
>   WHEN OTHERS THEN
>     dbms_output.put_line('The table TABLA does not exists');
> END;
> 
> To create and drop it, you must use Dynamic SQL.
> 
> Be lucky!
> ppgg@lcc.uma.es

Wrong answer,  The procedure will be marked invalid and will not
recompile or execute if TABLA is deleted.

SELECT 'X' INTO flag FROM USER_TABLES where TABLE_NAME = 'TABLA';

Will give you NO_RECORD_FOUND raised if the table is not defined.

I cannot remember the sequence for drop/create/recreate from PL/SQL. 
Seems like you have to get clever with that.  Check with the PL/SQL
documentation for these verbs.

Gary