Rem  Filename : ckvalqa.sql
Rem
Rem  Script for validating user responses. It checks to see if the database objects already exists
Rem
Rem  Author   : Oracle Corporation UK
Rem

set verify off
set feedback on

prompt
prompt Started Validation ......

Rem *************** Delete records from the CKRAU_TEMP table.
delete from ckrau_temp;

Rem *************** Insert the parameter values in the CKRAU_TEMP table
prompt Inserting parameter values into temporary table CKRAU_TEMP ...

insert into ckrau_temp values ( 'TBS_NAME1','TABLESPACE','&TBS_NAME1');
insert into ckrau_temp values ( 'TBS_NAME2','TABLESPACE','&TBS_NAME2') ;
insert into ckrau_temp values ( 'TBS_NAME3','TABLESPACE','&TBS_NAME3') ;
insert into ckrau_temp values ( 'TBS_NAME4','TABLESPACE','&TBS_NAME4') ;
insert into ckrau_temp values ( 'TBS_NAME5','TABLESPACE','&TBS_NAME5') ;
insert into ckrau_temp values ( 'TBS_NAME6','TABLESPACE','&TBS_NAME6') ;
insert into ckrau_temp values ( 'TBS_NAME7','TABLESPACE','&TBS_NAME7') ;
insert into ckrau_temp values ( 'TBS_NAME8','TABLESPACE','&TBS_NAME8') ;
insert into ckrau_temp values ( 'TBS_NAME9','TABLESPACE','&TBS_NAME9') ;
insert into ckrau_temp values ( 'TBS_NAME10','TABLESPACE','&TBS_NAME10') ;
insert into ckrau_temp values ( 'TBS_NAME11','TABLESPACE','&TBS_NAME11') ;
insert into ckrau_temp values ( 'TBS_NAME12','TABLESPACE','&TBS_NAME12') ;
insert into ckrau_temp values ( 'TBS_NAME13','TABLESPACE','&TBS_NAME13') ;
insert into ckrau_temp values ( 'TBS_NAME14','TABLESPACE','&TBS_NAME14') ;
insert into ckrau_temp values ( 'TBS_NAME15','TABLESPACE','&TBS_NAME15') ;
insert into ckrau_temp values ( 'RTBLSPACE','TABLESPACE','&RTBLSPACE') ;
insert into ckrau_temp values ( 'TEMPTBLSPACE','TABLESPACE','&TEMPTBLSPACE') ;

insert into ckrau_temp values ('RBSNAME','ROLLBACK SEGMENT','&RBSNAME');
insert into ckrau_temp values ('REPOSOWNER','USERNAME','&REPOSOWNER');

prompt Insertion complete ...

commit;

set heading off
set feedback off
set pages 30
col tablespace_name format a30

Rem Check if the tablespace already exists
prompt Following tablespaces already exists
prompt ------------------------------------

select tablespace_name
from   dba_tablespaces dt
where  exists ( select 1 from ckrau_temp
                where  paramtype = 'TABLESPACE'
                and    value = dt.tablespace_name
              );

prompt  

Rem Check if the rollback segment already exists
prompt Following Rollback segment already exists
prompt -----------------------------------------

select  segment_name
from    dba_rollback_segs drs
where   exists ( select 1 from ckrau_temp
                 where paramtype = 'ROLLBACK SEGMENT'
                 and value = drs.segment_name
                );

prompt

Rem Check if the user already exists
prompt Following Username already exists
prompt -----------------------------------------

select username
from   dba_users du
where   exists ( select 1 from ckrau_temp
                 where paramtype = 'USERNAME'
                 and value = du.username
                );

prompt End of Validation ....
prompt
