Re: Check table existance

Hi Ryan,

How about something like this? It's rudimentary, but it doesn't seem like you need fancy..just need the job done.

CREATE OR REPLACE PROCEDURE DROPPROCEDURE (p_tablename varchar2)
as

v_table varchar2(30);

begin

select table_name
into v_table
from user_tables
where table_name = p_tablename;

EXECUTE IMMEDIATE 'drop table '||p_tablename;

exception
when no_data_found then
null;
end;
/

The 'execute immediate' syntax only works as of 8.1 (I think)..otherwise you need DBMS_SQL.

SQL> EXECUTE DROPPROCEDURE('TEST1'); - the table exists

PL/SQL procedure successfully completed.

SQL> EXECUTE DROPPROCEDURE('TEST1'); - table doesn't exist

PL/SQL procedure successfully completed.

hope this is of some help,

Steve

In article <3A677797.2010703_at_East.Sun.COM>,   Ryan Lubke <Ryan.Lubke_at_East.Sun.COM> wrote:
> Hello,
>
> I would like to be able to check for existence of a table,
> if it does exist, then I would like to drop that table.
>
> if ( <table_exits )
> drop table <tablename>
>
> So far, I have been unsuccessful in what I've tried.
>
> The following select could work:
>
> SELECT 1 from user_tables where EXISTS ( select TABLE_NAME from
> user_tables where TABLE_NAME = 'test' )
>
> This seems a bit "clunky" from the SQL dialect I'm used to.
>
> Additionally, if I put this select anywhere near an IF
> statement, I get complaints.
>
> I've search the internet for suggestions and have not found
> any so, I'm hoping you folks will have one.
>
> Thanks,
>
> Ryan Lubke
>
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Jan 19 2001 - 07:20:59 CST