Tutorial :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.


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.


Solution:5

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;  


Solution:6

Does not work with Tables, only functions etc.

Here is a site with some examples.


Solution:7

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;  


Solution:8

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


Solution:9

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.


Solution:10

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.


Solution:11

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


Solution:12

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


Solution:13

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 (...  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »