Rem  Copyright (c) Oracle Corporation 2014. All Rights Reserved.
Rem
Rem    NAME
Rem      ords_create_schema.sql
Rem
Rem    DESCRIPTION
Rem      This script creates the schema for the Oracle REST Data Services (ORDS).
Rem      Do not invoke this script directly.  If you are doing a manual installation, use ords_manual_install.sql
Rem
Rem    NOTES
Rem      Assumes user with SYSDBA privilege is connected.
Rem
Rem    REQUIREMENTS
Rem      - Oracle Database Release 11.1 or later
Rem
Rem    Arguments:
Rem      1  : Schema name for Oracle Rest Data Services
Rem      2  : Default tablespace name for ords schema
Rem      3  : Temporary tablespace name for ords schema
Rem
Rem    MODIFIED    (MM/DD/YYYY)
Rem     epaglina    01/28/2014 - Created.
Rem     epaglina    03/14/2014 - Updated to provide user-specified password for ords schema.
Rem     epaglina    11/18/2014 - Remove user-specified password.
Rem     epaglina    12/10/2015 - Create user using the database default tablespaces if the default and temporary
Rem                              tablespaces are set to -DEFAULT.
Rem

set define '^'
set verify off
set termout on

define ORDSSCHEMA   = '^1'
define ORDS_DATATBS = '^2'
define ORDS_TEMPTBS = '^3'

whenever sqlerror exit sql.sqlcode

prompt INFO: Creating schema for Oracle REST Data Services

--  Check if the Oracle REST Data Services schema already exists
begin
   for c1 in (
        select null from sys.dba_users where username = upper('^ORDSSCHEMA')
    ) loop
        raise_application_error(-20002, 'ERROR: The schema ^ORDSSCHEMA already exists. Refer to ORDS documentation to re-install ORDS.');
    end loop;
end;
/


--  Create the schema
declare
    pwd       VARCHAR2(50);
    l_def_tbs VARCHAR2(30) := upper('^ORDS_DATATBS');
    l_tmp_tbs VARCHAR2(30) := upper('^ORDS_TEMPTBS');
begin
	pwd := 'X' || sys.dbms_random.string('X',29);
	
	if (l_def_tbs = '-DEFAULT' and l_tmp_tbs = '-DEFAULT') then
      begin
   	    execute immediate 'create user ^ORDSSCHEMA identified by ' || pwd  || ' password expire account lock';
         select default_tablespace into l_def_tbs from sys.dba_users where username = '^ORDSSCHEMA';
         execute immediate 'alter user ^ORDSSCHEMA quota unlimited on ' || l_def_tbs;

      exception
        when others then
          raise_application_error(-20015, 'ERROR: An error occurred during ^ORDSSCHEMA creation.' || sqlerrm);
      end;
	else	
	  execute immediate 'create user ^ORDSSCHEMA identified by ' || pwd  || ' password expire account lock default tablespace ^ORDS_DATATBS temporary tablespace ^ORDS_TEMPTBS quota unlimited on ^ORDS_DATATBS';
	end if;
end;
/

prompt INFO: Created schema ^ORDSSCHEMA
