Rem  Copyright (c) Oracle Corporation 2014. All Rights Reserved.
Rem
Rem    NAME
Rem      ords_create_rest_users.sql
Rem
Rem    DESCRIPTION
Rem      This script creates the ORDS_PUBLIC_USER database user.
Rem        - The ORDS_PUBLIC_USER user is used for the execution of resource templates or sessions.
Rem      Do not invoke this script directly.  If you are doing a manual installation, use ords_manual_install.sql
Rem
Rem    Arguments:
Rem      1  : Password for ORDS_PUBLIC_USER
Rem      2  : Default tablespace name for ORDS_PUBLIC_USER
Rem      3  : Temporary tablespace name for ORDS_PUBLIC_USER
Rem
Rem    NOTES
Rem      Assumes user with SYSDBA privilege is connected.
Rem
Rem    REQUIREMENTS
Rem      - Oracle Database Release 11.1 or later
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    epaglina    03/14/2014  Creates the ORDS_PUBLIC_USER. 
Rem

set define '^'
set verify off
set termout on
whenever sqlerror exit sql.sqlcode

define PWD = '^1'
define PUB_DATATBS  = '^2'
define PUB_TEMPTBS  = '^3'

--  Check if ORDS_PUBLIC_USER already exists
begin
   for c1 in (
        select null from sys.dba_users where username = upper('ORDS_PUBLIC_USER')
    ) loop
        raise_application_error(-20002, 'ERROR: The user ORDS_PUBLIC_USER already exists. Refer to ORDS documentation to re-install ORDS.');
    end loop;
end;
/

declare
   l_def_tbs VARCHAR2(30) := upper('^PUB_DATATBS');
   l_tmp_tbs VARCHAR2(30) := upper('^PUB_TEMPTBS');
begin
	if (l_def_tbs = '-DEFAULT' and l_tmp_tbs = '-DEFAULT') then
       execute immediate 'create user ORDS_PUBLIC_USER identified by "^PWD" account unlock';
	else	
       execute immediate 'create user ORDS_PUBLIC_USER identified by "^PWD" account unlock default tablespace ^PUB_DATATBS temporary tablespace ^PUB_TEMPTBS';
	end if;	
end;
/

grant create session to ORDS_PUBLIC_USER;

prompt INFO: Created ORDS_PUBLIC_USER
