How do I use CREATE OR REPLACE?

Question

Am I correct in understanding that CREATE OR REPLACE basically means "if the object exists, drop it, then create it either way?"

If so, what am I doing wrong? This works:

CREATE TABLE foo (id NUMBER,
title VARCHAR2(4000) DEFAULT 'Default Title')

And this doesn't (ORA-00922: missing or invalid option):

CREATE OR REPLACE TABLE foo (id NUMBER,
title VARCHAR2(4000) DEFAULT 'Default Title')

Am I doing something stupid? I don't seem to be able to find much documentation about this syntax.

Answer 1

Following script should do the trick on Oracle:

BEGIN
  EXECUTE IMMEDIATE 'drop TABLE tablename';
EXCEPTION
  WHEN OTHERS THEN
    IF sqlcode != -0942 THEN RAISE; 
    END IF;
END;

Answer 2

CREATE OR REPLACE can only be used on functions, procedures, types, views, or packages - it will not work on tables.

Answer 3

One of the nice things about the syntax is that you can be sure that a CREATE OR REPLACE will never cause you to lose data (the most you will lose is code, which hopefully you'll have stored in source control somewhere).

The equivalent syntax for tables is ALTER, which means you have to explicitly enumerate the exact changes that are required.

EDIT: By the way, if you need to do a DROP + CREATE in a script, and you don't care for the spurious "object does not exist" errors (when the DROP doesn't find the table), you can do this:

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE owner.mytable';
EXCEPTION
  WHEN OTHERS THEN
    IF sqlcode != -0942 THEN RAISE; END IF;
END;
/

Answer 4

Does not work with Tables, only functions etc.

Here is a site with some examples.

Answer 5

-- To Create or Replace a Table we must first silently Drop a Table that may not exist
DECLARE
  table_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT (table_not_exist , -00942);
BEGIN
   EXECUTE IMMEDIATE('DROP TABLE <SCHEMA>.<TABLE NAME> CASCADE CONSTRAINTS');
   EXCEPTION WHEN table_not_exist THEN NULL;
END;
/

Answer 6

This works on functions, procedures, packages, types, synonyms, trigger and views.

Update:

After updating the post for the third time, I'll reformulate this:

This does not work on tables :)

And yes, there is documentation on this syntax, and there are no REPLACE option for CREATE TABLE.