I’ve always appreciated the CREATE OR REPLACE functionality in Oracle for views, packages and triggers, and I’ve often wanted a similar CREATE OR REPLACE TABLE. Many times, I’ll find myself needing to quickly drop and re-create a table while I’m designing it.
This is tedious, because you have to drop the table, then re-define it. If you try to drop the table while it doesn’t exist, you get an error. If you try to define the table and it already exists, then you get an error.
Wouldn’t it be nice if you could have a CREATE OR REPLACE TABLE option?
Below is the closest thing I’ve found to a CREATE OR REPLACE TABLE.
WARNING: This will DESTROY ALL DATA IN THE TABLE AND REMOVE ANY PERMISSIONS GRANTED ON THE TABLE TO A ROLE OR USER. Only use this if you’re sure you want to destroy the table and everything in it.
declare
v_table_definition varchar2(32767);
table_or_view_not_exists exception;
pragma exception_init(table_or_view_not_exists, -942);
begin
begin
execute immediate 'drop table MY_NEW_TABLE';
exception when table_or_view_not_exists then
null;
end;
v_table_definition := q'<
CREATE TABLE MY_NEW_TABLE
(
YOUR_NEW_TABLE_DEFINITION char
, GOES_HERE int
)
>';
execute immediate v_table_definition;
end;
/Running this statement will drop your existing table and deploy a new table definition in one fell swoop. All you need is to change replace all instances of MY_NEW_TABLE with whatever your table name is. Then, adjust the definition of the table to your needs.
A Brief Explanation of the CREATE OR REPLACE TABLE Code
The variable v_table_definition is just a string variable with a max length of 32,767 bytes. We also created an exception which we’ve loaded with the exception number -942 (which is Oracle’s exception number for “TABLE OR VIEW DOES NOT EXIST”).
This allows us to issue a DROP TABLE command for the table, and only catch the situations where there is an error as a result of the table not existing. In other words, if there are other errors, such as invalid permissions when trying to drop the table, those errors will not be captured.
This is a good thing, because we want to know if there’s an unexpected error. We don’t want Oracle to “quietly swallow” any problems. We want Oracle to tell us if it runs into issues.
Within the body of the code (after the BEGIN) we first issue the drop statement. If it returns with a “TABLE OR VIEW DOES NOT EXIST” error, then we disregard that issue.
Next, we issue our table definition to create the new table. And that’s it!
Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
