#!/usr/local/bin/perl
#
# $Header: rdbms/admin/catcdb.pl /main/8 2018/07/05 10:05:25 akruglik Exp $
#
# catcdb.pl
#
# Copyright (c) 2015, 2018, Oracle and/or its affiliates. All rights reserved.
#
#    NAME
#      catcdb.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#      <short description of component this file declares/defines>
#
#    NOTES
#      Usage: perl catcdb.pl [-h] -s <path for SQL*Plus script to be invoked>
#        if -h (for Hide) is specified, user input will not be echoed
#        -s must be supplied
#
#    MODIFIED   (MM/DD/YY)
#    akruglik    06/12/18 - Bug 28165545: make sure the password supplied to
#                           catcon corresponds to the user name
#    akruglik    08/09/17 - LRG 20361666: remove 'hlpbld' string in front of
#                           hlpbld.sql script name + remove reference to
#                           deprecated script catbundleapply.sql
#    rthatte     02/10/17 - Bug 25522489 : No password expire for users with 
#                           no authentication
#    hohung      01/23/16 - Remove catcdb_int.sql and dynamicall generate the
#                           SQL script
#    akruglik    11/06/15 - Perl script to obtain SYS and SYSTEM passwords and
#                           store them in environment variables so
#                           catcdb_int.sql can call catcon.pl without passing
#                           them where they can be seen by others
#    akruglik    11/06/15 - Creation
#

use Term::ReadKey;              # to not echo password
use Getopt::Long;
use Cwd;
use File::Spec;
use Data::Dumper;
use util qw(trim, splitToArray);
use catcon qw(catconSqlplus);

if (@ARGV < 2) {
  print STDERR <<USAGE;

Usage: catcdb.pl --logDirectory: directory where log files generated by
                    all scripts will be stored; log files names will start
                    with name of the option to which they pertain
                 --logFilename: the name of the log file generated
                    by the catcdb script.
                 [--optionContainers: option(s)=container(s) key value pair(s);
                    e.g. --optionContainers opt1="con1 con2"
                    if not specified, no options will be installed in any containers.
                    Special values: __ALL means all cotainers, __EXCEPT means all
                    containers except for the ones listed]
                 [--optionTablespace: option(s)=tablespace key value pair(s);
                    e.g. --optionTablespace "opt1 opt2"=ts1]
                 [--hide: no echo]

USAGE
  exit;
}

my $logDirectory = '';
my $logFileName = '';
my $hideOpt = '';
my %optConHash;
my %optTSHash;

GetOptions('hide' => \$hideOpt, 'logDirectory=s' => \$logDirectory, 'logFilename=s' => \$logFilename, 'optionContainers=s' => \%optConHash, 'optionTablespace=s' => \%optTSHash);

if ($hideOpt) {
  ReadMode 'noecho';
}

if (!$logFilename) {
  print STDERR "Log file name must be specified\n";
  exit;
}

if (!$logDirectory) {
  print STDERR "Log directory must be specified\n";
  exit;
}

my $spoolFile;
if (-d $logDirectory) {
  $spoolFile = File::Spec->catfile($logDirectory, $logFilename);
} else {
  print STDERR "Requested Logging Directory $logDirectory does not exist\n";
  exit;
}

# SYS password needs to be stored in the environment variable whose name 
# will be passed to catcon.pl by catcdb_int.sql
if (! (exists $ENV{CATCDB_SYS_PASSWD})) {
  # Get SYS password
  print "Enter new password for SYS: ";
  my $sysPasswd = ReadLine 0;
  chomp $sysPasswd;

  # and store it in the env var
  $ENV{CATCDB_SYS_PASSWD} = $sysPasswd;
}

# SYSTEM password needs to be stored in the environment variable whose name 
# will be passed to catcon.pl by catcdb_int.sql
if (! (exists $ENV{CATCDB_SYSTEM_PASSWD})) {
  # get System password
  print "Enter new password for SYSTEM: ";
  my $systemPasswd = ReadLine 0;
  chomp $systemPasswd;

  # and store it in the env var
  $ENV{CATCDB_SYSTEM_PASSWD} = $systemPasswd;
}

# restore ReadMode if it was altered above (because we don't neeed to hide 
# temporary tablespace name
if ($hideOpt) {
  ReadMode 'normal';
}

my $tempTSname;

# temporary tablespace name can be passed by the caller; if it was not
# passed, we will prompt for it interactively
# NOTE: catcdb_int.sql does not need to have this name stored inside an 
#       environment variable, so, unlike SYS and SYSTEM password, we will 
#       not be setting the env var using value entered by the user
if (! (exists $ENV{CATCDB_TEMPTS})) {
  # get temporary tablespace name
  print "Enter temporary tablespace name: ";
  $tempTSname = ReadLine 0;
  chomp $tempTSname;
} else {
  # copy temporary tablespace name from the env var set by the caller
  $tempTSname = $ENV{CATCDB_TEMPTS};
}

# Store password for CTXSYS in the env var that catcdb_int.sql will pass to 
# catcon.pl (so it cannot be seen on the command line)
$ENV{CATCDB_CTXSYS_PASSWD} = "CTXSYS";

# Set up some variables
my $slash = ($^O =~ /^MSWin/) ? "\\" : "/";
my $oracle_home = $ENV{ORACLE_HOME};
my $rdbms_admin = $oracle_home.$slash.'rdbms'.$slash.'admin';
my $rdbms_admin_catcon = $rdbms_admin.$slash.'catcon.pl';
my $sqlplus_admin = $oracle_home.$slash.'sqlplus'.$slash.'admin';
my $sqlplus_admin_help = $sqlplus_admin.$slash. 'help';
my $jvm_install = $oracle_home.$slash.'javavm'.$slash.'install';
my $xdk_admin = $oracle_home.$slash.'xdk'.$slash.'admin';
my $ctx_admin = $oracle_home.$slash.'ctx'.$slash.'admin';
my $ctx_admin_defaults = $ctx_admin.$slash.'defaults';
my $ord_admin = $oracle_home.$slash.'ord'.$slash.'admin';
my $im_admin = $oracle_home.$slash.'ord'.$slash.'im'.$slash.'admin';
my $olap_admin = $oracle_home.$slash.'olap'.$slash.'admin';
my $md_admin = $oracle_home.$slash.'md'.$slash.'admin';
my $apex_home = $oracle_home.$slash.'apex';
my $hr_schema = $oracle_home.$slash.'demo'.$slash.'schema'.$slash.'human_resources';

use constant SYSTEM_USER => 'SYSTEM';
use constant SYS_USER => 'SYS';
use constant DEFAULT_TABLESPACE => 'SYSAUX';

###############################################
###  Hash keys for the SQL scripts hash map ###
###############################################
use constant LOG_FILENAME_OPT => 'logFilename';
use constant LOG_DIRECTORY_OPT => 'logDirectory';
# username for connecting to database to run the sql scripts
use constant USERNAME_OPT => 'username';

# name of the env var containing password for the user specified by the 
# USERNAME_OPT hash entry; ONLY needs to be specified if the password 
# contained in $ENV{CATCDB_SYS_PASSWD} is NOT acceptable
use constant USER_PASSWD_ENV_VAR => 'user_passwd_env_var';

# directory where the sql script is located
use constant SCRIPT_DIRECTORY_OPT => 'scriptDirectory';
# the full SQL script command to run
use constant SCRIPT_COMMAND_OPT => 'scriptCommand';
# SQL statement to run before the script starts
use constant PRE_PROCESS_SQL => 'pre process SQL';
# SQL statement to run after the script finishes
use constant POST_PROCESS_SQL => 'post process SQL';

my @START_SCRIPTS = (
                     {
                      LOG_FILENAME_OPT => 'catalog',
                      SCRIPT_COMMAND_OPT => sub { return 'catalog.sql' },
                      USERNAME_OPT => SYS_USER,
                      SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                     },
                     {
                      LOG_FILENAME_OPT => 'catproc',
                      SCRIPT_COMMAND_OPT => sub { return 'catproc.sql' },
                      USERNAME_OPT => SYS_USER,
                      SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                     },
                     {
                      LOG_FILENAME_OPT => 'catoctk',
                      SCRIPT_COMMAND_OPT => sub { return 'catoctk.sql '},
                      USERNAME_OPT => SYS_USER,
                      SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                     },
                     {
                      LOG_FILENAME_OPT => 'owminst',
                      SCRIPT_COMMAND_OPT => sub { return 'owminst.plb' },
                      USERNAME_OPT => SYS_USER,
                      SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                     },
                     {
                      LOG_FILENAME_OPT => 'pupbld',
                      SCRIPT_COMMAND_OPT => sub { return 'pupbld.sql' },
                      USERNAME_OPT => SYSTEM_USER,
                      USER_PASSWD_ENV_VAR => 'CATCDB_SYSTEM_PASSWD',
                      SCRIPT_DIRECTORY_OPT => $sqlplus_admin,
                     },
                     {
                      LOG_FILENAME_OPT => 'pupbld',
                      SCRIPT_COMMAND_OPT => sub { return 'hlpbld.sql --p"helpus.sql"' },
                      USERNAME_OPT => SYSTEM_USER,
                      USER_PASSWD_ENV_VAR => 'CATCDB_SYSTEM_PASSWD',
                      SCRIPT_DIRECTORY_OPT => $sqlplus_admin_help,
                     },
                    );

my @END_SCRIPTS = (
                   {
                    LOG_FILENAME_OPT => 'catclust',
                    SCRIPT_COMMAND_OPT => sub { return 'catclust.sql' },
                    USERNAME_OPT => SYS_USER,
                    SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                   },
                   {
                    LOG_FILENAME_OPT => 'catfinal',
                    SCRIPT_COMMAND_OPT => sub { return 'catfinal.sql' },
                    USERNAME_OPT => SYS_USER,
                    SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                   },
                   {
                    LOG_FILENAME_OPT => 'utlrp',
                    SCRIPT_COMMAND_OPT => sub { return 'utlrp.sql' },
                    USERNAME_OPT => SYS_USER,
                    SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                   },
                  );

my @LOCK_ACCOUNTS_AND_FINISH_SQLS = (
                                     # lock accounts
                                     "SET VERIFY OFF\n",
                                     "connect SYS/".$ENV{CATCDB_SYS_PASSWD}." as SYSDBA\n",
                                     qq#alter session set "_oracle_script"=true;\n#,
                                     'alter pluggable database pdb$seed close;'."\n",
                                     'alter pluggable database pdb$seed open READ WRITE;'."\n",
                                     "BEGIN\n",
                                     "FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ('SYS','SYSTEM') )\n",
                                     "LOOP\n",
                                     "IF item.AUTHENTICATION_TYPE='PASSWORD' THEN\n",  
                                       "dbms_output.put_line('Locking and expiring: ' || item.USERNAME);\n",
                                       "execute immediate 'alter user ' || sys.dbms_assert.enquote_name(sys.dbms_assert.schema_name(item.USERNAME),false) || ' password expire account lock';\n",
                                     "ELSE\n", 
                                       "dbms_output.put_line('Locking: ' || item.USERNAME);\n",
                                       "execute immediate 'alter user ' || sys.dbms_assert.enquote_name(sys.dbms_assert.schema_name(item.USERNAME),false) || ' account lock';\n",
                                     "END IF;\n",
                                     "END LOOP;\n",
                                     "END;\n",
                                     "/\n",
                                     'alter session set container=pdb$seed;'."\n",
                                     "BEGIN\n",
                                     "FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ('SYS','SYSTEM') )\n",
                                     "LOOP\n",
                                     "IF item.AUTHENTICATION_TYPE='PASSWORD' THEN\n", 
                                       "dbms_output.put_line('Locking and expiring: ' || item.USERNAME);\n",
                                       "execute immediate 'alter user ' || sys.dbms_assert.enquote_name(sys.dbms_assert.schema_name(item.USERNAME),false) || ' password expire account lock';\n",
                                     "ELSE\n",
                                       "dbms_output.put_line('Locking: ' || item.USERNAME);\n",
                                       "execute immediate 'alter user ' || sys.dbms_assert.enquote_name(sys.dbms_assert.schema_name(item.USERNAME),false) || ' account lock';\n",
                                     "END IF;\n",
                                     "END LOOP;\n",
                                     "END;\n",
                                     "/\n",
                                     'alter session set container=cdb$root;'."\n",
                                     'alter pluggable database pdb$seed close;'."\n",
                                     'alter pluggable database pdb$seed open READ ONLY;'."\n",

                                     qq#alter session set "_oracle_script"=false;\n#,
                                     "\@?/rdbms/admin/sqlsessend.sql\n",
                                    );



# mapping from install option to scripts to script invocation options
my %OPTIONAL_SCRIPTS = (
                        'JSERVER_FULL' => [
                                           {
                                            LOG_FILENAME_OPT => 'jserver_full',
                                            SCRIPT_COMMAND_OPT => sub { return 'initjvm.sql' },
                                            USERNAME_OPT => SYS_USER,
                                            SCRIPT_DIRECTORY_OPT => $jvm_install,
                                           }
                                           ,
                                           {
                                            LOG_FILENAME_OPT => 'jserver_full',
                                            SCRIPT_COMMAND_OPT => sub { return 'initxml.sql' },
                                            USERNAME_OPT => SYS_USER,
                                            SCRIPT_DIRECTORY_OPT => $xdk_admin,
                                           },
                                           {
                                            LOG_FILENAME_OPT => 'jserver_full',
                                            SCRIPT_COMMAND_OPT => sub { return 'xmlja.sql' },
                                            USERNAME_OPT => SYS_USER,
                                            SCRIPT_DIRECTORY_OPT => $xdk_admin,
                                           },
                                           {
                                            LOG_FILENAME_OPT => 'jserver_full',
                                            SCRIPT_COMMAND_OPT => sub { return 'catjava.sql' },
                                            USERNAME_OPT => SYS_USER,
                                            SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                           },
                                           {
                                            LOG_FILENAME_OPT => 'jserver_full',
                                            SCRIPT_COMMAND_OPT => sub { return 'catexf.sql' },
                                            USERNAME_OPT => SYS_USER,
                                            SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                           },
                                           {
                                            LOG_FILENAME_OPT => 'jserver_full',
                                            SCRIPT_COMMAND_OPT => sub { return 'catxdbj.sql' },
                                            USERNAME_OPT => SYS_USER,
                                            SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                           },
                                           {
                                            LOG_FILENAME_OPT => 'jserver_full',
                                            SCRIPT_COMMAND_OPT => sub { return 'catrul.sql' },
                                            USERNAME_OPT => SYS_USER,
                                            SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                           },
                                          ],
                        'JSERVER' => [
                                      {
                                       LOG_FILENAME_OPT => 'jserver',
                                       SCRIPT_COMMAND_OPT => sub { return 'initjvm.sql' },
                                       USERNAME_OPT => SYS_USER,
                                       SCRIPT_DIRECTORY_OPT => $jvm_install,
                                      }
                                      ,
                                      {
                                       LOG_FILENAME_OPT => 'jserver',
                                       SCRIPT_COMMAND_OPT => sub { return 'initxml.sql' },
                                       USERNAME_OPT => SYS_USER,
                                       SCRIPT_DIRECTORY_OPT => $xdk_admin,
                                      },
                                      {
                                       LOG_FILENAME_OPT => 'jserver',
                                       SCRIPT_COMMAND_OPT => sub { return 'xmlja.sql' },
                                       USERNAME_OPT => SYS_USER,
                                       SCRIPT_DIRECTORY_OPT => $xdk_admin,
                                      },
                                      {
                                       LOG_FILENAME_OPT => 'jserver',
                                       SCRIPT_COMMAND_OPT => sub { return 'catjava.sql' },
                                       USERNAME_OPT => SYS_USER,
                                       SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                      },
                                      {
                                       LOG_FILENAME_OPT => 'jserver',
                                       SCRIPT_COMMAND_OPT => sub { return 'catxdbj.sql' },
                                       USERNAME_OPT => SYS_USER,
                                       SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                      },
                                     ],
                        'ORACLE_TEXT' => [
                                          {
                                           LOG_FILENAME_OPT => 'oracle_text',
                                           SCRIPT_COMMAND_OPT => sub { return qq#catctx.sql --p"change_on_install" --p"$_[0]" --p"$tempTSname" --p"LOCK"# },
                                           USERNAME_OPT => SYS_USER,
                                           SCRIPT_DIRECTORY_OPT => $ctx_admin,
                                          },
                                          {
                                           LOG_FILENAME_OPT => 'oracle_text',
                                           SCRIPT_COMMAND_OPT => sub { return 'dr0defin.sql --p"AMERICAN"' },
                                           USERNAME_OPT => 'CTXSYS',
                                           USER_PASSWD_ENV_VAR => 'CATCDB_CTXSYS_PASSWD',
                                           SCRIPT_DIRECTORY_OPT => $ctx_admin_defaults,
                                           PRE_PROCESS_SQL => 'alter user CTXSYS account unlock identified by "CTXSYS";',
                                           POST_PROCESS_SQL => 'alter user CTXSYS password expire account lock;',
                                          },
                                          {
                                           LOG_FILENAME_OPT => 'oracle_text',
                                           SCRIPT_COMMAND_OPT => sub { return 'dbmsxdbt.sql' } ,
                                           USERNAME_OPT => SYS_USER,
                                           SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                          },
                                         ],
                        'IMEDIA' => [
                                     {
                                      LOG_FILENAME_OPT => 'multimedia',
                                      SCRIPT_COMMAND_OPT => sub { return 'iminst.sql' },
                                      USERNAME_OPT => SYS_USER,
                                      SCRIPT_DIRECTORY_OPT => $im_admin,
                                     },
                                    ],
                        'CWMLITE' => [
                                      {
                                       LOG_FILENAME_OPT => 'olap',
                                       SCRIPT_COMMAND_OPT => sub { return qq#olap.sql --p"$_[0]" --p"$tempTSname"# },
                                       USERNAME_OPT => SYS_USER,
                                       SCRIPT_DIRECTORY_OPT => $olap_admin,
                                      },
                                     ],
                        'SPATIAL' => [
                                      {
                                       LOG_FILENAME_OPT => 'spatial',
                                       SCRIPT_COMMAND_OPT => sub { return 'mdinst.sql' },
                                       USERNAME_OPT => SYS_USER,
                                       SCRIPT_DIRECTORY_OPT => $md_admin,,
                                      },
                                     ],
                        'OMS_OID' => [
                                      {
                                       LOG_FILENAME_OPT => 'oms_oid',
                                       SCRIPT_COMMAND_OPT => sub { return 'catolsd.sql' },
                                       USERNAME_OPT => SYS_USER,
                                       SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                      }
                                     ],
                        'OMS' =>  [
                                   {
                                    LOG_FILENAME_OPT => 'oms',
                                    SCRIPT_COMMAND_OPT => sub { return 'catols.sql' },
                                    USERNAME_OPT => SYS_USER,
                                    SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                   },
                                  ],
                        'APEX' => [
                                   {
                                    LOG_FILENAME_OPT => 'apex',
                                    SCRIPT_COMMAND_OPT => sub { return qq#catapx.sql --p"change_on_install" --p"$_[0]" --p"$_[0]" --p"$tempTSname" --p"/i/" --p"NONE"# },
                                    USERNAME_OPT => SYS_USER,
                                    SCRIPT_DIRECTORY_OPT => $apex_home,
                                   },
                                  ],
                        'DV' => [
                                 {
                                  LOG_FILENAME_OPT => 'dv',
                                  SCRIPT_COMMAND_OPT => sub { return qq#catmac.sql --p"$_[0]" --p"$tempTSname" --e"CATCDB_SYS_PASSWD"# },
                                  USERNAME_OPT => SYS_USER,
                                  SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                 },
                                ],
                        'SAMPLE_SCHEMA' => [
                                            {
                                             LOG_FILENAME_OPT => 'sample_schema',
                                             SCRIPT_COMMAND_OPT => sub { return qq#hr_main.sql --p"change_on_install" --p"$_[0]" --p"$tempTSname" --e"CATCDB_SYS_PASSWD" --p"$logDirectory"# },
                                             USERNAME_OPT => SYS_USER,
                                             SCRIPT_DIRECTORY_OPT => $hr_schema,
                                            },
                                           ],
                        'NET_EXTENSIONS' => [
                                             {
                                              LOG_FILENAME_OPT => 'net_extensions',
                                              SCRIPT_COMMAND_OPT => sub { return 'dbmsclr.plb' },
                                              USERNAME_OPT => SYS_USER,
                                              SCRIPT_DIRECTORY_OPT => $rdbms_admin,
                                             },
                                            ],
                       );



# mapping from DB installation options to containers and tablespace
my %optionMap;

######################################################
###  Hash keys for the installation options mapping ##
######################################################
# list of containers to be included/excluded for this DB installation option
use constant CONTAINER_LIST => 'container list';
# switch that indicates what containers get the option
use constant CONTAINER_SWITCH => 'container switch';
# table space for this option
use constant TABLESPACE => 'tablespace';

############################################
### Hash values for the container switch ###
############################################
use constant INCLUDED_CONTAINERS => 'included containers';
use constant EXCLUDED_CONTAINERS => 'excluded containers';
use constant ALL_CONTAINERS => 'all containers';

my %optionCount;

foreach my $key (keys %optConHash) {
  my @requestedOptions = util::splitToArray($key);
  foreach my $option (@requestedOptions) {
    # make sure that the option specified exists
    if (!exists($OPTIONAL_SCRIPTS{$option})) {
      print STDERR "Requested option $option does not exist\n";
      exit;
    }
    # make sure that no duplicate option keys are provided
    if ($optionCount{$option}) {
      print STDERR qq#Duplicate option $option specified\n#;
      exit;
    }
    $optionCount{$option}++;
  }

  my $valueStr = util::trim($optConHash{$key});

  if (uc(substr($valueStr, 0, 4)) eq '_ALL') {
    foreach my $option (@requestedOptions) {
      $optionMap{$option}{CONTAINER_SWITCH} = ALL_CONTAINERS;
    }
  } elsif (uc(substr($valueStr, 0, 7)) eq '_EXCEPT') {
    foreach my $option (@requestedOptions) {
      $optionMap{$option}{CONTAINER_SWITCH} = EXCLUDED_CONTAINERS;
      # skip the delimit characters as well
      $optionMap{$option}{CONTAINER_LIST} = substr($valueStr, 8, length($valueStr) - 8);
    }
  } else {
    foreach my $option (@requestedOptions) {
      $optionMap{$option}{CONTAINER_SWITCH} = INCLUDED_CONTAINERS;
      $optionMap{$option}{CONTAINER_LIST} = $valueStr;
    }
  }
}

%optionCount = ();

foreach my $key (keys %optTSHash) {
  my @requestedOptions = util::splitToArray($key);
  foreach my $option (@requestedOptions) {
    # make sure that the option specified is 
    if (!exists($optionMap{$option})) {
      print STDERR "Requested option $option is not specified under the optionContainers flag\n";
      exit;
    }
    # make sure that no duplicate option keys are provided
    if ($optionCount{$option}) {
      print STDERR qq#Duplicate option $option specified\n#;
      exit;
    }
    $optionCount{$option}++;
  }

  my $valueStr = util::trim($optTSHash{$key});

  foreach my $option (@requestedOptions) {
    $optionMap{$option}{TABLESPACE} = $valueStr;
  }
}

if (%optionMap) {
  print "The following installation options to containers and tablespace mapping is requested:\n";
  print Dumper(\%optionMap);
} else {
  print "No options to container mapping specified, no options will be installed in any containers\n";
}

###########################
# Generate the sql script #
###########################

# generate the sql statements based on the options and switches specified
sub generateSqlStmt {
  my @scriptList = @{$_[0]};
  my %containerHash;
  my $containerSwitch;
  my $containerList;
  my $tablespaceName = DEFAULT_TABLESPACE;

  if (@_ == 2) {
    %containerHash = %{$_[1]};
    $containerSwitch = $containerHash{CONTAINER_SWITCH};
    $containerList = $containerHash{CONTAINER_LIST};
    if ($containerHash{TABLESPACE}) {
      $tablespaceName = $containerHash{TABLESPACE};
    }
  }

  my @generatedSqls;

  foreach my $scriptHash (@scriptList) {
    my $username = $scriptHash->{USERNAME_OPT};

    # Bug 28165545:
    #   most scripts are run as SYS, and for those cases name of the env var 
    #   containing user password will default to 'CATCDB_SYS_PASSWD', but in a 
    #   few cases where that password is not acceptable, %$scriptHash will 
    #   specify the correct env var to use
    my $userPasswdEnvVar = 
      (! exists $scriptHash->{USER_PASSWD_ENV_VAR}) 
        ? 'CATCDB_SYS_PASSWD' 
        : $scriptHash->{USER_PASSWD_ENV_VAR};

    my $scriptCommand = $scriptHash->{SCRIPT_COMMAND_OPT}($tablespaceName);
    my $logFilename = $scriptHash->{LOG_FILENAME_OPT};
    my $scriptDirectory = $scriptHash->{SCRIPT_DIRECTORY_OPT};
    my $preProcessSQL = $scriptHash->{PRE_PROCESS_SQL};
    my $postProcessSQL = $scriptHash->{POST_PROCESS_SQL};

    my $containerOption = "";

    if ($containerSwitch) {
      if ($containerSwitch eq INCLUDED_CONTAINERS) {
        $containerOption = qq#-c "$containerList"#;
      } elsif ($containerSwitch eq EXCLUDED_CONTAINERS) {
        $containerOption = qq#-C "$containerList"#;
      }
    }

    if ($preProcessSQL) {
      push(@generatedSqls, $preProcessSQL);
    }
    my $perlCommand = qq#host perl -I $rdbms_admin $rdbms_admin_catcon -u $username -w $userPasswdEnvVar -U SYS -W CATCDB_SYS_PASSWD -d $scriptDirectory -n 1 -l $logDirectory -b $logFilename $containerOption $scriptCommand\n#;
    push(@generatedSqls, $perlCommand);
    if ($postProcessSQL) {
      push(@generatedSqls, $postProcessSQL);
    }
  }
  return @generatedSqls;
}

# Step 1: Default start scripts for every containers
my @newSqlScript = (
                    "\@\@?/rdbms/admin/sqlsessstart.sql\n",
                    "connect SYS/".$ENV{CATCDB_SYS_PASSWD}." as sysdba\n",
                    "set echo on\n",
                    "set serveroutput on\n",
                    "spool $spoolFile\n",
                   );

push(@newSqlScript, generateSqlStmt(\@START_SCRIPTS));

# Step 2: Optional Scripts for individual containers
foreach my $installOption (keys %optionMap) {
  push(@newSqlScript, "exec dbms_output.put_line('DBCA_TIMESTAMP OPTION $installOption STARTED');\n");
  push(@newSqlScript, generateSqlStmt(\@{$OPTIONAL_SCRIPTS{$installOption}}, \%{$optionMap{$installOption}}));
  push(@newSqlScript, "exec dbms_output.put_line('DBCA_TIMESTAMP OPTION $installOption ENDED');\n");
}

# Step 3: Default ending scripts 
push(@newSqlScript, generateSqlStmt(\@END_SCRIPTS));

push(@newSqlScript, @LOCK_ACCOUNT_AND_FINISH_SQLS);

# Call a newly defined catcon subroutine to execute this new script.
catcon::catconSqlplus(@newSqlScript, getcwd()."/catcdb_");