Create or replace table in Oracle pl/sql

Question

I need a script which creates table or if it already exist drops it, and when recreates table. After some research I have found out that CREATE OR REPLACE TABLE in pl/sql doesn't exist. So I come up with this script :

DECLARE
   does_not_exist   EXCEPTION;
   PRAGMA EXCEPTION_INIT (does_not_exist, -942);
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE foobar';
EXCEPTION
   WHEN does_not_exist
   THEN
      NULL;
END;
/ 

CREATE TABLE foobar (c1 INT);

Is there any proper way to achieve this functionality?

Based on what you are attempting, maybe temporary tables would be useful. — Dan Bracuk, May 19 '13 at 12:44
nope, i need to use table, not temporary table. But thanks for your suggestion. — kuldarim, May 19 '13 at 12:53
i never understood this thing. just drop the table - if it dosent exists then no harm done. — haki, May 19 '13 at 15:08

score 9 · Answer 1 · edited May 23 '17 at 11:53

You really shouldn't be doing this in PL/SQL, tables created at runtime would be indicative of a flaw in your data model. If you're really convinced you absolutely have to do this then investigate temporary tables first. Personally, I'd reassess whether it's necessary at all.

You seem to be going for the EAFP as opposed to LBYL approach, which is described in a few answers to this question. I would argue that this is unnecessary. A table is a fairly static beast, you can use the system view USER_TABLES to determine whether it exists before dropping it.

declare

   l_ct number;

begin

   -- Determine if the table exists.
   select count(*) into l_ct
     from user_tables
    where table_name = 'THE_TABLE';

   -- Drop the table if it exists.
   if l_ct = 1 then
      execute immediate 'drop table the_table';
   end if;

   -- Create the new table it either didn-t exist or
   -- has been dropped so any exceptions are exceptional.
   execute immediate 'create table the_table ( ... )';

end;
/

Thanks Ben, i know, that it is bad approach to recreate tables at runtime, but i have to do task given to me. — kuldarim, May 19 '13 at 12:59

score 5 · Accepted Answer · answered May 19 '13 at 12:55

Using a global temporary table would seem to be a better option. However, if you insist on dropping and re-adding tables at runtime you could query one of the _TABLES views (i.e. USER_TABLES, DBA_TABLES, ALL_TABLES) to determine if the table exists, drop it if it does, then create it:

SELECT COUNT(*)
  INTO nCount
  FROM USER_TABLES
  WHERE TABLE_NAME = 'FOOBAR';

IF nCount <> 0 THEN
  EXECUTE IMMEDIATE 'DROP TABLE FOOBAR';
END IF;

EXECUTE IMMEDIATE 'CREATE TABLE FOOBAR(...)';

Share and enjoy.

@Ben - beat me by one minute! – Bob Jarvis - Слава Україні May 19 '13 at 12:56 — Bob Jarvis - Слава Україні, May 19 '13 at 12:56

Create or replace table in Oracle pl/sql

2 Answers2

Linked