Re: Create a table only if the table doesn't exists

Mark C. Stock wrote:
> "fred" <r5tr_at_free.fr> wrote in message
> news:e7egsu$598$1_at_s1.news.oleane.net...
> : Hello,
> :
> : I want to write a sql script that creates a table only if the table
> doesn't
> : already exists (but if the table creation fails for another reason than
> : "table already exists" I want to know about it).
> : Is it possible to do that in sql only or is pl/sql needed ?
> : If needed, how do you do that in pl/sql ?
> :
> : Thanks a lot for your help.
> :
> : Fred.
> :
> :
>
> i'm assuming you are using SQL*Plus to run a script.
>
> SQL*Plus does not do conditional processing based on SQL Errors -- however
> it does have a WHENEVER SQLERROR command that may be of some use to you
>
> in PL/SQL, you want EXECUTE IMMEDIATE
>
> depending on your OS and/or OS utilities, you can always filter out the
> ORA-00955s from your output file
>
> ++ mcs

In your pl/sql routine you can select againt all_tables or dba_tables to see if the table exists. A select count(*) always returns a value so zero would indicate the table does not exist and to run the create table logic while > 0 would tell that the table already exists.

If you are planning on creating tables on the fly then I would question why? You generally do not need to create tables as parts of jobs to accomplish the task as hand. I know that creating #tables (temp tables) is common with SQL Server but in Oracle you can usually solve the same problem without a temp table.

HTH -- Mark D Powell -- Received on Thu Jun 22 2006 - 14:50:31 CDT