Rem    XRAD ORACLE INSTALLATION SCRIPT
Rem     
Rem    How to use: 
Rem     Run script using sqlplus command:
Rem         sqlplus "sys/syspass as sysdba" @xorainst SYSAUX TEMP /i/
Rem
Rem    Script accepts two arguments:
Rem     1) Default tablespace for xrad_user and tables to use;
Rem     2) Temporary tablespace for xrad_user
Rem    


define DEF_TBSP   = '&1'
define TEMP_TBSP  = '&2'


DECLARE
  c_def_tbsp    constant varchar2(30) := 'SYSAUX';
  c_temp_tbsp   constant varchar2(30) := 'TEMP';
  L_FOUND BOOLEAN := FALSE;
  L_CNT NUMBER;
  
BEGIN
    SELECT COUNT(1) INTO L_CNT FROM SYS.DBA_USERS WHERE USERNAME = 'XRAD_USER';

    IF L_CNT > 0 THEN
        L_FOUND := TRUE;
    END IF; 

    IF NOT L_FOUND THEN
        EXECUTE IMMEDIATE 'CREATE USER XRAD_USER IDENTIFIED BY "XRAD_USER" PASSWORD EXPIRE ACCOUNT LOCK'||
                          ' DEFAULT TABLESPACE '|| c_def_tbsp||
                          ' TEMPORARY TABLESPACE '||c_temp_tbsp||
                          ' QUOTA UNLIMITED ON '||c_def_tbsp;
    END IF;
                        
		L_FOUND := FALSE;
    SELECT COUNT(1) INTO L_CNT FROM SYS.DBA_TABLES WHERE OWNER = 'XRAD_USER' AND TABLE_NAME = 'XRAD_FILES';   
		
		IF L_CNT > 0 THEN
        L_FOUND := TRUE;
    END IF; 

    IF NOT L_FOUND THEN
        EXECUTE IMMEDIATE Q'{CREATE GLOBAL TEMPORARY TABLE XRAD_USER.XRAD_FILES(
          name          varchar2(4000),
		  file_name     varchar2(4000),
		  file_mime     varchar2(4000),
		  file_content  blob)
          on commit preserve rows}';
		  
        EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM XRAD_FILES FOR XRAD_USER.XRAD_FILES';  

        EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE ON XRAD_USER.XRAD_FILES TO PUBLIC'; 
    END IF;
END;
/
