Re: PL/SQL question

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_at_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 Received on Tue Sep 09 1997 - 00:00:00 CDT