How do I use CREATE OR REPLACE?

Solution 1:

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.

Solution 2:

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;
/

Solution 3:

There is no create or replace table in Oracle.

You must:

DROP TABLE foo;
CREATE TABLE foo (....);

Solution 4:

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