Rem  Copyright (c) Oracle Corporation 1999 - 2007. All Rights Reserved.
Rem
Rem    NAME
Rem      apexins.sql
Rem
Rem    DESCRIPTION
Rem
Rem    NOTES
Rem      Assumes the SYS user is connected.
Rem
Rem    REQUIRENTS
Rem      - Oracle 9.2.0.3 or later
Rem
Rem    Arguments:
Rem     Position 1: Name of tablespace for Application Express application user
Rem     Position 2: Name of tablespace for Application Express files user
Rem     Position 3: Name of temporary tablespace
Rem     Position 4: Virtual directory for APEX images
Rem
Rem    Example:
Rem
Rem    1)Local
Rem      sqlplus "sys/syspass as sysdba" @apexins SYSAUX SYSAUX TEMP /i/
Rem
Rem    2)With connect string
Rem      sqlplus "sys/syspass@10g as sysdba" @apexins SYSAUX SYSAUX TEMP /i/
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem      jkallman  09/10/2004 - Created
Rem      jstraub   10/25/2004 - Changed the name of the log file to end in .log and include date/time
Rem      jkallman  01/04/2005 - Adjusted APPUN to FLOWS_020000
Rem      jduan     07/07/2005 - Modify for upgrade to 2.0
Rem      jkallman  09/14/2005 - Adjusted APPUN to FLOWS_020100
Rem      jkallman  09/22/2005 - Add substitution variable XE
Rem      jkallman  01/23/2006 - Adjusted APPUN to FLOWS_020200
Rem      jkallman  02/28/2006 - Copied from original htmldbins.sql
Rem      jstraub   06/27/2006 - Added exit logic if XE
Rem      jstraub   08/11/2006 - Changed calling coreins with new prefix parameter
Rem      jkallman  09/29/2006 - Adjusted APPUN to FLOWS_030000
Rem      jkallman  02/15/2007 - Remove XE check
Rem      jstraub   02/21/2007 - Removed 6th positional connect parameter
Rem      jstraub   04/10/2007 - Added INSTALL_TYPE parameter for use with dynamically setting WHENEVER SQLERROR exit in coreins
Rem      jkallman  08/02/2007 - Change FLOWS_030000 references to FLOWS_030100
Rem      jstraub   08/16/2007 - Removed password as positional parameter and made random
Rem      jstraub   09/14/2007 - Updated comments to reflect correct number of arguments and updated examples
Rem      jstraub   12/19/2007 - Added logic to exit if not connected as SYSDBA
Rem      jstraub   01/10/2008 - Added prerequisite checks for XDB and CONTEXT
Rem      jstraub   02/08/2008 - Added alter session set nls_length_semantics = byte
Rem      jkallman  09/09/2008 - Change FLOWS_030100 references to APEX_030200
Rem      jstraub   03/24/2009 - Added spool off, removed from coreins.sql for catupgrd

alter session set nls_length_semantics = byte;

prompt .  ____   ____           ____        ____
prompt . /    \ |    \   /\    /     |     /
prompt .|      ||    /  /  \  |      |    |
prompt .|      ||---    ----  |      |    |---

prompt .|      ||   \  /    \ |      |    |
prompt . \____/ |    \/      \ \____ |____ \____
prompt .
prompt . Application Express Installation.
prompt ...................................
prompt .
prompt ... Checking prerequisites

set define '^'
set concat on
set concat .
set verify off
set termout off
spool off
set termout on

Rem
Rem  Check that user has SYSDBA privilege, CONTEXT and XDB are installed before proceeding
Rem


whenever sqlerror exit
set serveroutput on

Rem  Check SYSDBA Privilege
define foo2 = 'NOSYSDBA'
column foo new_val foo2 NOPRINT

select privilege foo from session_privs where privilege = 'SYSDBA';

begin
    if '^foo2' = 'NOSYSDBA' then
        dbms_output.put_line('Application Express installation requires a connection with the SYSDBA privilege.');
        execute immediate 'bogus statement to force exit';
    end if;
end;
/

Rem  Check for CONTEXT
define foo2 = 'NOCONTEXT'
column foo new_val foo2 NOPRINT

select comp_id foo from dba_registry where comp_id = 'CONTEXT' and status = 'VALID';

begin
    if '^foo2' = 'NOCONTEXT' then
        dbms_output.put_line('Application Express installation requires the Oracle Text database component.');
        execute immediate 'bogus statement to force exit';
    end if;
end;
/

Rem  Check for XDB
define foo2 = 'NOXDB'
column foo new_val foo2 NOPRINT

select comp_id foo from dba_registry where comp_id = 'XDB' and status = 'VALID';

begin
    if '^foo2' = 'NOXDB' then
        dbms_output.put_line('Application Express installation requires the Oracle XML Database database component.');
        execute immediate 'bogus statement to force exit';
    end if;
end;
/

whenever sqlerror continue

prompt .
prompt ... Prerequisite check complete. Result passed.
prompt .

column foo3 new_val LOG1

select 'install'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' foo3 from dual;

define LOG2 = ^LOG1.english.log
define LOG3 = ^LOG1.english.bad
spool ^LOG1
define UPGRADE = '1'

define INSTALL_TYPE = 'MANUAL'

define APPUN = 'APEX_030200'

column foo2 new_val UPGRADE

select '2' foo2 from dba_users where (username in ('FLOWS_030100','FLOWS_030000','FLOWS_020200','FLOWS_020100','FLOWS_020000','FLOWS_010600','FLOWS_010500')) and rownum = 1;

define DATTS     = '^1'
define FF_TBLS   = '^2'
define TEMPTBL   = '^3'

define IMGPR     = '^4'

define DB_VERSION = '10'
define PREFIX     = '@'

column :rnd_pwd new_value ADM_PWD NOPRINT
variable rnd_pwd varchar2(30)

begin
    :rnd_pwd := dbms_random.string('X',30);
end;
/

select :rnd_pwd from dual;

@@coreins.sql ^LOG1 ^UPGRADE ^APPUN ^TEMPTBL ^IMGPR ^DATTS ^FF_TBLS ^ADM_PWD ^PREFIX

spool off

exit