Re: PLSQL: how to test if a table exists?

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: Tue, 28 Jun 2005 23:30:46 -0400
Message-ID: <8e8bf$42c213e9$471d7821$3061@ALLTEL.NET>

Chris ( Val ) wrote:

>
> deanbrown3d wrote:
>

>>if (select table_name from all_tables where owner = 'me' and table_name
>>= 'MYTABLE') is null........

>
>
> This seems like a very common query to perform.
>
> I would probably wrap it up in a function, and place
> it in my own package for later use:
>
> CREATE OR REPLACE FUNCTION
> tableExists( O VARCHAR2, T VARCHAR2 ) RETURN VARCHAR2 AS
>
> v_TableName all_tables.table_name%type;
>
> BEGIN
>
> SELECT table_name INTO v_TableName
> FROM all_tables
> WHERE owner = UPPER( O )
> AND table_name = UPPER( T );
>
> RETURN 'Table ' || '"' || T || '"' || ' Exists';
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> RETURN 'Table ' || '"' || T || '"' || ' Does Not Exist';
> WHEN OTHERS THEN
> RETURN 'Unknown error occurred';
>
> END tableExists;
> /
>
> Of course, you could return a boolean type or even a char(1)
> to signify 'T', 'F' or 'U' if you like.
>
> Cheers,
> Chris Val
>

GUYS! Check this tread in GOOGLE It went dead 2 YEARS ago!!!

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Tue Jun 28 2005 - 22:30:46 CDT