# S292APEX.def: Collects APEX Information
# $Id: S292APEX.def,v 1.2 2008/02/25 17:32:55 mschenke Exp $
# ARCS: $Header: /home/cvs/repository/RDA_4/src/scripting/lib/modules/S292APEX.def,v 1.2 2008/02/25 17:32:55 mschenke Exp $
#
# Change History
# 20080225  MSC  Change the copyright notice.

=head1 NAME

S292APEX - Collects APEX Information

=head1 DESCRIPTION

This module collects APEX-related information.

=cut

echo tput('bold'),'Processing APEX module ...',tput('off')

# Initialization
var $APEX_CHECK_HTTP = getSetting('APEX_CHECK_HTTP')
var $APEX_ORACLE_HOME = getSetting('APEX_ORACLE_HOME')

toc '1:APEX Information'
var $TOC = '%TOC%'
var $TOP = '[[#Top][Back to top]]'

# Load the common macros
run library()
run DBinfo()

=head2 Configuration Files

Gathers the following configuration files:

=over 2

=item o F<$ORACLE_HOME/Apache/modplsql/conf/marvel.conf>

=item o F<$ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app>

=back

=cut

# Getting the configuration files
if $APEX_CHECK_HTTP
{debug ' Inside the APEX Module, collecting the configuration files'
 pretoc '2:Configuration Files'
 call cat_report(catDir($APEX_ORACLE_HOME,'Apache','modplsql','conf'),\
   'marvel.conf','cfg','(PlsqlDatabasePassword\s*).*$')
 call cat_report(catDir($APEX_ORACLE_HOME,'Apache','modplsql','cfg'),\
   'wdbsvr.app','cfg','(password\s*=\s*).*$')
 unpretoc
}

# Define a macro to change the database context
macro change_context
{var ($sid,$usr,$dba,$loc) = @arg

 debug ' Inside APEX module, changing database context for ',$loc

 # Determine the user password
 if compare('eq',$usr,'/')
  var ($usr,$pwd) = ('','')
 elsif hasSqlPassword($usr,$sid)
  var $pwd = undef
 else
  var $pwd = askPassword(concat('Enter ',$usr,'@',$sid,' user password:'))

 # Change the database context
 var $old = setSqlLogin($usr,$pwd,$sid,$dba)
 var $try = setSqlFailure(0)

 # Test the database connection
 if !testSql()
  var $ver = get_db_version(true)

 # Return context information
 return ($ver,$old,$try)
}

# Define a macro to get database information
macro get_db_info
{var ($rpt,$loc,$itm) = @arg
 import $TOP,$TOC,$TTL,@TTL,@HDR
 keep $TOP,$TOC,$TTL,@TTL,@HDR

 debug ' Inside APEX module, getting database information for ',nvl($loc,'DB')
 report $rpt
 if $loc
  var $TTL = concat('---+!! Database Information for ',$loc)
 else
  var $TTL = '---+!! Database Information'
 var @TTL = ('',\
             '---+ Status of XML DB',\
             '---+ Count of valid and invalid objects',\
             '---+ List of invalid packages',\
             '---+ Dafault and temporary tablespace for APEX_PUBLIC_USER',\
             '---+ Database NLS parameters',\
             '---+ OWA.UTIL version',\
             '---+ APEX component version',\
             '---+ APEX users')

 var @HDR = ('',\
             '|*Owner*|*Object Name*|*Status*|*Object Type*|',\
             '|*Status*|*Count*|',\
             '|*Owner*|*Object Name*|',\
             '|*Default Tablespace*|*Temporary Tablespace*|',\
             '|*Parameter*|*Value*|',\
             '|*Version*|',\
             '|*Status*|*Version*|',\
             '|*Username*|')
 set $sql
 {SELECT '|' ||
 "       SUBSTR(owner,1,10) || ' |' ||
 "       SUBSTR(object_name,1,20) || ' |' ||
 "       status || ' |' ||
 "       object_type || ' |'
 " FROM dba_objects
 " WHERE object_name='DBMS_XMLPARSER';
 "PROMPT ___Macro_separator(2)___
 "SELECT '|' ||
 "       status || ' | ' ||
 "       count(1) || '|'
 " FROM dba_objects
 " WHERE owner like 'FLOWS_%'
 " GROUP BY status;
 "PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       owner || ' |' ||
 "       object_name || ' |'
 " FROM dba_objects
 " WHERE owner like 'FLOWS_%'
 "   AND status='INVALID'
 "   AND object_type='PACKAGE BODY';
 "PROMPT ___Macro_separator(4)___
 "SELECT '|' ||
 "       temporary_tablespace || ' |' ||
 "       default_tablespace || ' |'
 " FROM dba_users
 " WHERE username = 'APEX_PUBLIC_USER';
 "PROMPT ___Macro_separator(5)___
 "SELECT '|' ||
 "       parameter || ' |' ||
 "       value || ' |'
 " FROM nls_database_parameters;
 "PROMPT ___Macro_separator(6)___
 "SELECT '|' ||
 "       owa_util.get_version || ' |'
 " FROM sys.dual;
 "PROMPT ___Macro_separator(7)___
 "SELECT '|' ||
 "       status || ' |' ||
 "       version || ' |'
 " FROM dba_registry
 " WHERE comp_id='APEX';
 "PROMPT ___Macro_separator(8)___
 "SELECT '|' ||
 "       username || ' |'
 " FROM dba_users
 " WHERE username like 'FLOWS%';
 }
 call separator(1)
 call writeSql($sql)
 if $loc
  call separator(0,$loc,3)
 else
  call separator(0,'Database Information')
}

=head2 Database Information

Gathers APEX information from all databases specified at setup.

=cut

var ($TTL,@TTL,@HDR) = (undef)
if getSetting('APEX_DB_AUTO')
{# Get information using the detected database connections
 pretoc '2:Database Information'
 loop $itm (split('\|',getSetting('APEX_DB_SET')))
 {if getSetting(concat('APEX_FLAG_',$itm))
  {# Change the database context
   var $sid = getSetting(concat('APEX_SID_',$itm))
   var $usr = uc(getSetting(concat('APEX_USER_',$itm),'SYSTEM'))
   var $dba = getSetting(concat('APEX_SYSDBA_',$itm))
   var $loc = getSetting(concat('APEX_LOC_',$itm))
   var ($ver,$old,$try) = change_context($sid,$usr,$dba,$loc)

   # Collect the database information
   if match($ver,'^(92|10|11)')
    call get_db_info(concat('dbinfo_',$itm),$loc)
   else
   {report concat('not_applicable_',$itm)
    write 'This section requires a database connection and can only be \
           executed on 9i Release 2 or later.'
    toc '3:[[',getFile(),'][rda_report][For ',$loc,']]'
   }

   # Restore the previous database context
   call setSqlLogin($old)
   call setSqlFailure($try)
  }
 }
 unpretoc
}
elsif getSetting('APEX_LOCAL')
{# Get information from the database to analyze
 if match(get_db_version(true),'^(92|10|11)')
  call get_db_info('db_info')
 else
 {report not_applicable
  write 'This section requires a database connection and can only be executed \
         on 9i Release 2 or later.'
  toc '2:[[',getFile(),'][rda_report][Database Information]]'
 }
}
else
{# Get information from a single database
 var $sid = getSetting('APEX_SID')
 var $usr = uc(getSetting('APEX_LOGIN','SYSTEM'))
 var $dba = getSetting('APEX_SYSDBA')
 var ($ver,$old,$try) = change_context($sid,$usr,$dba,'DB')

 # Collect the database information
 if match($ver,'^(92|10|11)')
  call get_db_info('db_info')
 else
 {report not_applicable
  write 'This section requires a database connection and can only be executed \
         on 9i Release 2 or later.'
  toc '2:[[',getFile(),'][rda_report][Database Information]]'
 }

 # Restore the previous database context
 call setSqlLogin($old)
 call setSqlFailure($try)
}

=head1 SEE ALSO

See also L<DBinfo.def|modules::DBinfo>,
L<library.def|modules::library>.

=begin credits

=over 10

=item RDA 4.10:  Vikash Palisetti, Nipun Suri.

=back

=end credits

=head1 COPYRIGHT NOTICE

Copyright (c) 2002-2008, Oracle. All rights reserved.

=head1 TRADEMARK NOTICE

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle
Corporation and/or its affiliates. Other names may be trademarks of their
respective owners.

=cut