# S292APEX.def: Collects APEX Information
# $Id: S292APEX.def,v 1.7 2009/08/07 14:07:54 mschenke Exp $
# ARCS: $Header: /u02/webapps/workbench/cvs/RDA_4/src/scripting/lib/modules/S292APEX.def,v 1.7 2009/08/07 14:07:54 mschenke Exp $
#
# Change History
# 20090716  JCR  Enhance APEX collections.

=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')
 set $sql
 {SELECT 'usr=' || schema
 " FROM dba_registry
 " WHERE comp_id = 'APEX';
 }
 var $usr = value(grepSql($sql,'^usr=','f'))

 report $rpt
 if $loc
  var $TTL = concat('---+!! Database Information for ',$loc)
 else
  var $TTL = '---+!! Database Information'
 var @TTL = ('',\
             '---+ Component Versions and Statuses',\
             '---+ APEX Users',\
             '---+ Status of XML DB',\
             '---+ XDB HTTP Port',\
             '---+ Count of Valid and Invalid Objects',\
             '---+ List of Invalid Packages',\
             '---+ Invalid Objects in APEX Schema',\
             '---+ V, NV, DV Objects',\
             '---+ Schema Granted APEX_ADMINISTRATOR_ROLE',\
             '---+ System Privileges Granted to APEX Schema',\
             '---+ Roles Granted to APEX Schema',\
             '---+ Privileges on Tables Granted to APEX Schema',\
             '---+ Database NLS Parameters',\
             '---+ Instance Settings',\
             '---+ Default and Temporary Tablespace',\
             '---+ OWA.UTIL Version',\
             '---+ Workspaces',\
             '---+ Applications',\
             '---+ Access Control List Assignments to Network Hosts')

 var @HDR = ('',\
             '|*Identifier*|*Name*|*Version*|*Status*|',\
             '|*Username*|',\
             '|*Owner*|*Object Name*|*Status*|*Object Type*|',\
             '|*Port Number*|',\
             '|*Status*|*Count*|',\
             '|*Owner*|*Object Name*|',\
             '|*Object Name*|',\
             '|*Owner*|*Object Name*|*Object Type*|',\
             '|*Grantee*|',\
             '|*Privilege*|',\
             '|*Granted Role*|',\
             '|*Owner*|*Table Name*|*Privilege*|',\
             '|*Parameter*|*Value*|',\
             '|*Name*|*Value*|',\
             '|*Username*|*Default Tablespace*|*Temporary Tablespace*|',\
             '|*Version*|',\
             '|*Workspace Identifier*|*Workspace*|',\
             '|*Workspace*|*Application ID*|*Application Name*|')
 var($HDR[19],$col19) = getSqlColumns('RDA','','V$PARAMETER')
 call clearSqlColumns('RDA')
 set $sql
 {SELECT '|' ||
 "       comp_id || ' |' ||
 "       comp_name || ' | ' ||
 "       version || '|' ||
 "       status || ' |'
 " FROM dba_registry
 " WHERE comp_id IN ('APEX','CONTEXT','XDB')
 " ORDER BY comp_id;
 "PROMPT ___Macro_separator(2)___
 "SELECT '|' ||
 "       username || ' |'
 " FROM dba_users
 " WHERE username like 'FLOWS%';
 "PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       SUBSTR(owner,1,10) || ' |' ||
 "       SUBSTR(object_name,1,20) || ' |' ||
 "       status || ' |' ||
 "       object_type || ' |'
 " FROM dba_objects
 " WHERE object_name = 'DBMS_XMLPARSER';
 "PROMPT ___Macro_separator(4)___
 "SELECT '| ' ||
 "       dbms_xdb.gethttpport || '|'
 " FROM sys.dual;
 "PROMPT ___Macro_separator(5)___
 "SELECT '|' ||
 "       status || ' | ' ||
 "       COUNT(1) || '|'
 " FROM dba_objects
 " WHERE owner like 'FLOWS%'
 " GROUP BY status;
 "PROMPT ___Macro_separator(6)___
 "SELECT '|' ||
 "       owner || ' |' ||
 "       object_name || ' |'
 " FROM dba_objects
 " WHERE owner like 'FLOWS%'
 "   AND status = 'INVALID'
 "   AND object_type = 'PACKAGE BODY';
 "PROMPT ___Macro_separator(7)___
 "SELECT '|' ||
 "       object_name || ' |'
 " FROM all_objects
 " WHERE status = 'INVALID'
 "   AND owner = ':1';
 "PROMPT ___Macro_separator(8)___
 "SELECT '|' ||
 "       owner || ' |' ||
 "       object_name || ' |' ||
 "       object_type || ' |'
 " FROM all_objects
 " WHERE object_name IN ('DV','NV','V');
 "PROMPT ___Macro_separator(9)___ 
 "SELECT '|' ||
 "       grantee || ' |'
 " FROM dba_role_privs
 " WHERE granted_role = 'APEX_ADMINISTRATOR_ROLE';
 "PROMPT ___Macro_separator(10)___
 "SELECT '|' ||
 "       privilege || ' |'
 " FROM dba_sys_privs
 " WHERE grantee = ':1';
 "PROMPT ___Macro_separator(11)___
 "SELECT '|' ||
 "       granted_role || ' |'
 " FROM dba_role_privs
 " WHERE grantee = ':1';
 "PROMPT ___Macro_separator(12)___
 "SELECT '|' ||
 "       owner || ' |' ||
 "       table_name || ' |' ||
 "       privilege || ' |'
 " FROM dba_tab_privs
 " WHERE grantee = ':1';
 "PROMPT ___Macro_separator(13)___
 "SELECT '|' ||
 "       parameter || ' |' ||
 "       value || ' |'
 " FROM nls_database_parameters;
 "PROMPT ___Macro_separator(14)___
 "SELECT '|' ||
 "       name || ' |' ||
 "       value || ' |'
 " FROM :1.wwv_flow_platform_prefs;
 "PROMPT ___Macro_separator(15)___
 "SELECT '|' ||
         username || ' |' ||
 "       temporary_tablespace || ' |' ||
 "       default_tablespace || ' |'
 " FROM dba_users
 " WHERE username IN ('APEX_PUBLIC_USER','FLOWS_FILES',':1');
 "PROMPT ___Macro_separator(16)___
 "SELECT '|' ||
 "       owa_util.get_version || ' |'
 " FROM sys.dual;
 "PROMPT ___Macro_separator(17)___
 "SELECT '|' ||
 "       workspace_id || ' |' ||
 "       workspace || ' |'
 " FROM apex_workspaces;
 "PROMPT ___Macro_separator(18)___
 "SELECT '|' ||
 "       workspace || ' |' ||
 "       application_id || ' |' ||
 "       application_name || ' |'
 " FROM apex_applications
 " ORDER BY workspace;
 "PROMPT ___Macro_separator(19)___
 "SELECT :2
 " FROM v$parameter
 " WHERE name = 'dba_network_acls';
 }
 call separator(1)
 call writeSql(bindSql($sql,$usr,$col19))
 if $loc
  call separator(0,concat('For ',$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

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

=begin credits

=over 10

=item RDA 4.10: Vikash Palisetti, Nipun Suri.

=item RDA 4.17: Sunny Patel.

=back

=end credits

=head1 COPYRIGHT NOTICE

Copyright (c) 2002-2009, 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