Re: How to check the existance of a table ?

Robert Vabo wrote at 01-Sep-00 07:00:48 on the subject "Re: How to check the existance of a table ?":  

>Thanks a lot for the help. It works fine.
 

>Another "freshman" question.
 

>I'm going to test if the table exists and if not I am going to create it.
 

>bolExist = sp_TableExist(TempTableName)
>if bolExist = False
>THEN
> CREATE TABLE || TempTableName || || ' (ID_COL int IDENTITY, INPUT_ID int )
>'; (Where ID_COL is a autonumber)
>END IF;
Hello Robert,
I think you have basically two options:

The fist is too simply create ther table and catch the exception if it already exists. If you you want to create the table from PL/SQL, you have to use dynamic SQL since PL/SQL does not allow DDL statements.

The second option is to query USER_TABLES, ALL_TABLES or DBA_TABLES if the table exists and create it if it does not.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | lothar.armbruester_at_rheingau.vistec.net
Hauptstr. 26            | la_at_oktagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de