Rem  Copyright (c) Oracle Corporation 2014. All Rights Reserved.
Rem
Rem    NAME
Rem      ords_verify_tablespace.sql
Rem
Rem    DESCRIPTION
Rem      This script checks if the default and temporary tablespace exists.
Rem
Rem    NOTES
Rem
Rem    Arguments:
Rem      1  : Default tablespace for ORDS_METADATA
Rem      2  : Temporary tablespace for ORDS_METADATA
Rem      3  : Default tablespace for ORDS_PUBLIC_USER
Rem      4  : Temporary tablespace for ORDS_PUBLIC_USER
Rem
Rem    MODIFIED    (MM/DD/YYYY)
Rem     epaglina    05/18/2015 Created.
Rem     epaglina    12/10/2015 Warns user that the tablespace does not exist and will default
Rem                            to SYSAUX for default tablespace and TEMP for temporary tablespace.
Rem

set define '^'
set verify off
set serveroutput on

define ORDS_DATATBS = '^1'
define ORDS_TEMPTBS = '^2'
define PUB_DATATBS  = '^3'
define PUB_TEMPTBS  = '^4'

declare 
  procedure check_tablespace(p_def_tablespace in varchar2, p_temp_tablespace in varchar2) is
  begin
    
	  -- Check if the default tablespace exists
	  for c1 in (select p_def_tablespace tbs_name from dual 
	             where upper(p_def_tablespace) not in 
	                (select tablespace_name from sys.dba_tablespaces where tablespace_name = upper(p_def_tablespace)))
	  loop
            raise_application_error(-20005, 'ERROR: The default tablespace ' || c1.tbs_name || 
                                            ' does not exist. An existing tablespace is required for ORDS installation.');	  
	  end loop;

	  -- Check if the temporary tablespace exists
	  for c1 in (select p_temp_tablespace tbs_name from dual 
	             where upper(p_temp_tablespace) not in 
	                (select distinct(tablespace_name) from dba_temp_files where tablespace_name = upper(p_temp_tablespace)))
	  loop
            raise_application_error(-20005, 'ERROR: The temporary tablespace ' || c1.tbs_name || 
                                            ' does not exist. An existing tablespace is required for ORDS installation.');	  
	  end loop;
	  
    dbms_output.put_line('Completed verifying the tablespaces ' || p_def_tablespace || ' and ' || p_temp_tablespace || ' for ' || sys_context('USERENV','SESSION_USER') );
  end check_tablespace;
    
begin
   if ('^ORDS_DATATBS' <> '-DEFAULT' and '^ORDS_TEMPTBS' <> '-DEFAULT') then
      check_tablespace('^ORDS_DATATBS', '^ORDS_TEMPTBS');
   end if;
   
   if ('^PUB_DATATBS' <> '-DEFAULT' and '^PUB_TEMPTBS' <> '-DEFAULT') then
     check_tablespace('^PUB_DATATBS', '^PUB_TEMPTBS');
   end if;
end;
/
