We can use "create or replace view" to create a view if it does not exist or replace it if it exits. However, there is not such thing as "create or replace table". How do we create a table if it does not exist or replace the table if it exists?
SolutionThe following PL/SQL does preisely that. We store the script as a file create_or_replace.sql. It first tries to drop the table. If it does not exist, an exception will be raised and nothing is done. Then the script continues to create the table. We put the exception inside the begin/end block so that the exception will only break the first block and will continue to execute the "create table" statement outside of the block.
begin begin execute immediate 'drop table tbl_test'; exception when others then NULL; end; execute immediate 'create table tbl_test (id number, value number)'; end; /We run the scripts twice. In the first time, table tbl_test were created. In the second run, table tbl_test was dropped and then created.
SQL> @create_or_replace.sql PL/SQL procedure successfully completed. SQL> @create_or_replace.sql PL/SQL procedure successfully completed.