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

User · Answer

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

User · Answer

-- 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  lt SCHEMA gt   lt TABLE NAME gt  CASCADE CONSTRAINTS       EXCEPTION WHEN table not exist THEN NULL  END

User · Answer

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

User · Answer

If you are doing in code then first check for table in database by using query   SELECT table name   FROM user tables  WHERE table name    XYZ   if record found then truncate table otherwise create Table  Work like Create or Replace

User · Answer

There is no create or replace table in Oracle   You must    DROP TABLE foo  CREATE TABLE foo

User · Answer

So I ve been using this and it has worked very well  - it works more like a DROP IF EXISTS but gets the job done  DECLARE        VE TABLENOTEXISTS EXCEPTION  PRAGMA EXCEPTION INIT VE TABLENOTEXISTS  -942         PROCEDURE DROPTABLE PIS TABLENAME IN VARCHAR2  IS               VS DYNAMICDROPTABLESQL VARCHAR2 1024                       BEGIN                        VS DYNAMICDROPTABLESQL     DROP TABLE      PIS TABLENAME                        EXECUTE IMMEDIATE VS DYNAMICDROPTABLESQL                       EXCEPTION                         WHEN VE TABLENOTEXISTS THEN                              DBMS OUTPUT PUT LINE PIS TABLENAME      NOT EXIST  SKIPPING                                WHEN OTHERS THEN                              DBMS OUTPUT PUT LINE SQLERRM                       RAISE                      END DROPTABLE       BEGIN       DROPTABLE  YOUR TABLE HERE    END DROPTABLE         Hope this helps  Also reference  PLS-00103 Error in PL SQL Developer

User · Answer

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

User · Answer

Create or replace table  is not possible  As others stated  you can write a procedure and or use begin execute immediately        Because I don t see an answer with how to  re create the table  I putted a script as an answer   PS  in line of what jeffrey-kemp mentioned  this beneath script will NOT save data that is already present in the table you are going to drop  Because of the risk of loosing data  at our company it is only allowed to alter existing tables on the production environment  and it is not allowed to drop tables  By using the drop table statement  sooner or later you will get the company police standing at your desk    --Create the table  A TABLE X   and drop the table in case it already is present BEGIN EXECUTE IMMEDIATE    CREATE TABLE A TABLE X   COLUMN1 NUMBER 15 0   COLUMN2  VARCHAR2 255 CHAR   COLUMN3  VARCHAR2 255 CHAR       EXCEPTION WHEN OTHERS THEN   IF SQLCODE    -955 THEN -- ORA-00955  object name already used      EXECUTE IMMEDIATE  DROP TABLE A TABLE X     END IF  END

User · Answer

I would do something like this     begin      for i in  select table name from user tables where table name    FOO   loop         execute immediate  drop table    i table name       end loop    end     execute immediate  CREATE TABLE FOO  id NUMBER                                         title VARCHAR2 4000

User · Answer

If this is for MS SQL    The following code will always run no matter what if the table exist already or not    if object id  mytablename   is not null   has the table been created already in the db Begin      drop table mytablename End  Create table mytablename

User · Answer

You can use CORT  www softcraftltd co uk cort   This tool allows to CREATE OR REPLACE table in Oracle  It looks like   create     or replace    table MyTable        -- standard table definition      It preserves data

User · Answer

A usefull procedure for oracle databases without using exeptions  under circumstances you have to replace user tables with dba tables and or constrain the tablespace in the query    create or replace procedure NG DROP TABLE tableName varchar2  is    c int  begin    select count    into c from user tables where table name   upper tableName      if c   1 then       execute immediate  drop table    tableName     end if  end

User · Answer

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

User · Answer

Does not work with Tables  only functions etc   Here is a site with some examples

[oracle] How do I use CREATE OR REPLACE?

Examples related to oracle

Examples related to oracle10g

Examples related to ddl