How can I run a DDL script over and over without error-CodePudding

I need to create a DDL script for school and have it be able to run over and over. To run over and over, I need to drop everything and then run the scripts to recreate and populate my tables, including creating views, triggers, and sequences. How can I do this? I am thinking of adding this, but that seems like a lot for the scipt and not the intention of the assignment. Is there another way to do it or do I need to create a procedure like below for all objects?

This one is for a sequence, which runs fine. Now I'd have to do this for 15 other objects. Also, how do I clear cnt for each block?

DECLARE cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO cnt FROM all_sequences WHERE sequence_name = 'EMPLOYEE_SEQ';
    
    IF cnt <> 0 THEN
        EXECUTE IMMEDIATE 'DROP SEQUENCE EMPLOYEE_SEQ';
    ELSE
        EXECUTE IMMEDIATE 'CREATE SEQUENCE EMPLOYEE_SEQ  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL';
    END IF;
END;

CodePudding user response：

Try something like this: place the DROP commands in a PLSQL block and use an exception to suppress any error if the objects don't exist. Then create them normally:

set serveroutput on;

begin
   dbms_output.enable();
   execute immediate 'drop table test1';
   EXECUTE IMMEDIATE 'DROP SEQUENCE EMPLOYEE_SEQ';
   -- place other DROP commands here
   dbms_output.put_line('Objects dropped');
exception
    when others then
        dbms_output.put_line('Objects not found');
end;
/

create table test1 (col1 number);
CREATE SEQUENCE EMPLOYEE_SEQ  MINVALUE 1 MAXVALUE 9999999999999999999999999999 
    INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL;

First time execution:

Objects not found


PL/SQL procedure successfully completed.


Table TEST1 created.


Sequence EMPLOYEE_SEQ created.

Second execution:

Objects dropped


PL/SQL procedure successfully completed.


Table TEST1 created.

    
Sequence EMPLOYEE_SEQ created.

I tested this script using SQL*Developer. It assumes that all objects will be present; if one drop statement throws an error, any subsequent drop statements will not be run. Use this only for objects like tables or sequences that do not support CREATE OR REPLACE ... syntax. For views, stored procedures, types, etc., use CREATE OR REPLACE syntax and don't worry about explicitly dropping them.