# DCapex.ctl:292:Collects APEX Information
# $Id: DCapex.ctl,v 1.4 2013/11/05 13:51:08 RDA Exp $
# ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/DB/DCapex.ctl,v 1.4 2013/11/05 13:51:08 RDA Exp $
#
# Change History
# 20131105  MSC  Improve code consistency.

=head1 NAME

DB:DCapex - Collects APEX Information

=head1 DESCRIPTION

This module collects APEX-related information.

=cut

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

# Initialization
var $APEX_CHECK_HTTP  = ${B_HTTP}
var $APEX_ORACLE_HOME = ${D_ORACLE_HOME}

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

# Load the common macros
run DB:DBinfo()
run RDA:library()

=head2 Configuration Files

When requested, gathers important HTTP server configuration files.

=cut

if $APEX_CHECK_HTTP
{debug ' Inside the APEX module, collecting important 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*|PlsqlDatabasePassword\s*).*$')
 call cat_report(catDir($APEX_ORACLE_HOME,'Apache','modplsql','conf'),\
   'dads.conf','cfg','(PlsqlDatabasePassword\s*).*$')
 call cat_report(catDir($APEX_ORACLE_HOME,'ohs','modplsql','conf'),\
   'dads.conf','cfg','(PlsqlDatabasePassword\s*).*$')
 unpretoc
}

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

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

 # Change the database context
 call setSqlTarget($tgt)

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

 # Return context information
 return $ver
}

# 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 Objects',\
             '---+ 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',\
             '---+ PL/SQL Web Toolkit 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*|',\
             '|*Owner*|*Status*|*Count*|',\
             '|*Owner*|*Object Name*|*Object Type*|',\
             '|*Object Name*|*Object Type*|',\
             '|*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','','DBA_NETWORK_ACLS')
 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%'
 "    OR username LIKE 'APEX%';
 "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 '|' ||
 "       owner || ' |' ||
 "       status || ' | ' ||
 "       COUNT(1) || '|'
 " FROM dba_objects
 " WHERE owner LIKE 'FLOWS%'
 "    OR owner LIKE 'APEX%'
 "    OR owner = ':1'
 " GROUP BY owner,status
 " ORDER BY owner DESC,status;
 "PROMPT ___Macro_separator(6)___
 "SELECT '|' ||
 "       owner || ' |' ||
 "       object_name || ' |' ||
 "       object_type || ' |'
 " FROM dba_objects
 " WHERE (owner LIKE 'FLOWS%' OR owner LIKE 'APEX%')
 "   AND status = 'INVALID'
 " ORDER BY owner DESC, object_type, object_name;
 "PROMPT ___Macro_separator(7)___
 "SELECT '|' ||
 "       object_name || ' |' ||
 "       object_type || ' |'
 " FROM all_objects
 " WHERE status = 'INVALID'
 "   AND owner = ':1'
 " ORDER BY object_name;
 "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 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 ${B_DB_AUTO}
{# Get information using the detected database connections
 pretoc '2:Database Information'
 loop $itm (@{T_DB_SET})
 {if ${B_FLAG_${VAR.itm}}
  {# Change the database context
   var $loc = ${T_LOC_${VAR.itm}}
   var $ver = change_context($loc,${I_DB_${VAR.itm}})

   # Collect the database information
   if match($ver,'^(92|10|11|12)')
    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,']]'
   }
  }
 }
 unpretoc
}
else
{# Get information from a single database
 var $ver = change_context('DB',${I_DB})

 # Collect the database information
 if match($ver,'^(92|10|11|12)')
  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]]'
 }
}

=head1 SEE ALSO

L<DB:DBinfo|collect::DB:DBinfo>,
L<RDA:library|collect::RDA:library>

=begin credits

=over 10

=item RDA 4.10: Vikash Palisetti, Nipun Suri.

=item RDA 4.17: Sunny Patel.

=item RDA 4.18: Sunny Patel.

=back

=end credits

=head1 COPYRIGHT NOTICE

Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved.

=head1 TRADEMARK NOTICE

Oracle and Java are registered trademarks of Oracle and/or its
affiliates. Other names may be trademarks of their respective owners.

=cut