Databases and Refreshing Test Data

2. Clear Schema

The purpose of the procedure SCHEMA_REFRESH.CLEAR_SCHEMA is to prepare the schemas of the Test database for the import process. This procedure deletes/drops selected database objects from the R11 and WEB_USER schemas. Listing 2 shows a small sample of the database objects that will be drop/delete using dynamic SQL.

Note: This script works for the following Oracle database versions: 9i, 10g, and 11g. This script was sent to me by my friend Mike Haggerty.

procedure clear_schema
  as
           --
        v_oracle9 boolean := false;
        v_oracle10 boolean := false;
                v_oracle11 boolean := false;
        --
        --
        --  determine the Oracle version
        --
        PROCEDURE set_version AS
           v_version varchar2(20);
           v_comp varchar2(20);
        BEGIN
           DBMS_UTILITY.DB_VERSION ( v_version, v_comp);

           IF substr( v_version, 1, instr( v_version, '.') - 1 ) = '9' THEN
                    v_oracle9 := true;
           ELSIF substr( v_version, 1, instr( v_version, '.') - 1 ) = '10' THEN
                    v_oracle10 := true;
           ELSIF substr( v_version, 1, instr( v_version, '.') - 1 ) = '11' THEN
                    v_oracle11 := true;
           END IF;
        END;
        --
begin
    --
    --  just add a new entry for each schema
    --
    v_schema.delete;
    v_schema(1) := 'WEB_USER';
      v_schema(2) := 'R11';
        --
        set_version;
        --
    FOR i IN v_schema.FIRST..v_schema.LAST LOOP
        FOR t IN (SELECT owner, object_name 
                          from all_objects 
                        where object_type = 'VIEW' 
                        and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop view ' || t.owner || '.' || t.object_name;          
        END LOOP;
        --
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'MATERIALIZED VIEW' and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop MATERIALIZED VIEW ' || t.owner || '.' || t.object_name;  
     END LOOP;
         --       
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'PACKAGE' and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop package ' || t.owner || '.' || t.object_name;
          END LOOP;
         --
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'PROCEDURE' and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop procedure ' || t.owner || '.' || t.object_name;
        END LOOP;
         --
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'TYPE' and owner = upper( v_schema(i) ) ) LOOP
            EXECUTE IMMEDIATE 'drop type ' || t.owner || '.' || t.object_name;
            END LOOP;
--
        FOR t IN (SELECT owner, object_name from all_objects where object_type = 'TABLE' and owner = upper( v_schema(i) ) ) LOOP
               if v_oracle9 = true then
                EXECUTE IMMEDIATE 'drop table ' || t.owner || '.' || t.object_name || ' cascade constraints';
            elsif v_oracle10 = true or v_oracle11 = true then
               EXECUTE IMMEDIATE 'drop table ' || t.owner || '.' || t.object_name || ' cascade constraints purge';
              end if;
        END LOOP;
         --   
    END LOOP;
end clear_schema;