# OWBr11g.ctl: Collects Oracle Warehouse Builder Information (11g)
# $Id: OWBr11g.ctl,v 1.9 2015/08/21 15:30:22 RDA Exp $
# ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/OFM/OWBr11g.ctl,v 1.9 2015/08/21 15:30:22 RDA Exp $
#
# Change History
# 20150821  MSC  Improve time consistency.

=for stopwords OWBr

=head1 NAME

OFM:OWBr11g - Collects Oracle Warehouse Builder Information (11g)

=head1 DESCRIPTION

This module collects Oracle Warehouse Builder-related information (11g).

=cut

# Initialization
var $AGE  = ${R_AGE/T:3}
var $TAIL = ${N_TAIL:1000}

import $TOC,$TOP

# Load the common macros
run DB:DBinfo()

# Check 11g repository presence'
debug ' Inside OWB module, checking 11g repository objects'
set $sql
{SELECT DECODE(COUNT(*),0,'NOTFOUND','PRESENT')
" FROM all_objects
" WHERE object_name = 'WB_RTV_SERVICE_NODES'
"   AND OWNER = 'OWBSYS';
}
if grepSql($sql,'NOTFOUND')
{echo 'No 11g repositories found'
 return
}

=head2 report11g - Repository Information (11g)

Gathers release-related information.

=cut

debug ' Inside OWB module, getting 11g repository information'
report report11g
var $TTL = '---+!! Repository Information (11g)'
var @TTL = ('',\
            "---+ OWBSYS Repository Version\012\
             ---## From View owbsys.all_iv_installations",\
            "---+ Workspaces and Owners\012\
             ---## From View owbsys.all_iv_workspaces",\
            "---+ Workspaces Users\012\
             ---## From Views owbsys.workspace_assignment, \
                              owbsys.all_iv_workspaces",\
            "---+ Control Center in Current Instance\012\
             ---## From View owbsys.wb_rtv_service_nodes",\
            "---+ OWBSYS Size",\
            "---+ OWBSYS WB_RT_Platform_Repository\012\
             ---## From Table owbsys.wb_rt_platform_properties",\
            "---+ OWB OWBSYS.OWBRTPS",\
            "---+ OWB OWBSYS.RAW_OWBREPOS",\
            "---+ Registered Locations",\
            "---+ OWB WB_RT_Service_Job_Logs\012\
             ---## Last 10 Entries from Table owbsys.wb_rt_service_job_logs",\
            "---+ Control Center Connections\012\
             ---## From Views owbsys.cmpfcoclasses, owbsys.all_iv_workspaces",\
            "---+ OWBSYS Profile",\
            "---+ OWB DBA_JAVA_POLICY",\
            "---+ OWB OWBSYS.OWB$WB_RT_AUDIT_EXECUTIONS",\
            "---+ OWB OWBSYS.OWB$WB_RT_AUDIT_DEPLOYMENTS")
var @HDR = ('',\
            '|*Version*|*Release*|*Creation Date*|*Update Date*|*Created By*|\
              *Updated By*|',\
            '|*Name*|*Owner*|*Creation Date*|*Update Date*|*Created By*|\
              *Updated By*|',\
            '|*Name*|*Owner*|*User*|*Creation Timestamp*|*Update Timestamp*|\
              *Created By*|*Updated By*|',\
            undef,\
            '|*Table*| *Rows*|*Platform Properties Seeded Correctly*|')
var $HDR[11] = '|*Workspace*|*Control Center*|*Host*| *Port*|*Schema*|\
                 *Service*|'
var $HDR[13] = '|*Kind*|*Grantee*|*Type Schema*|*Type Name*|*Name*|*Action*|\
                 *Enabled*| *Seq*|'
var $HDR[14] = '| *Count*|'
var $HDR[15] = '| *Count*|'
set $sql
{SET serveroutput on
"ALTER session SET current_schema = OWBSYS;
"SELECT '|' ||
"       installed_version || '|' ||
"       release || '|' ||
"       TO_CHAR(created_on,'DD-Mon-YYYY') || '|' ||
"       TO_CHAR(updated_on,'DD-Mon-YYYY') || '|' ||
"       created_by || '|' ||
"       updated_by || '|'
" FROM all_iv_installations;
"PROMPT ___Macro_separator(2)___
"SELECT '|' ||
"       workspace_name || '|' ||
"       workspace_owner || '|' ||
"       TO_CHAR(created_on,'DD-Mon-YYYY') || '|' ||
"       TO_CHAR(updated_on,'DD-Mon-YYYY') || '|' ||
"       created_by || '|' ||
"       updated_by || '|'
" FROM all_iv_workspaces;
"PROMPT ___Macro_separator(3)___
"SELECT '|' ||
"       w.workspace_name || '|' ||
"       a.workspace_owner || '|' ||
"       w.user_name || '|' ||
"       w.creationtimestamp || '|' ||
"       w.updatetimestamp || '|' ||
"       w.createdby || '|' ||
"       w.updatedby || '|'
" FROM owbsys.workspace_assignment w, owbsys.all_iv_workspaces a
" WHERE w.workspace_id = a.workspace_id
"   AND isworkspaceowner = 0
" ORDER BY w.workspace_name,a.workspace_owner;
"PROMPT ___Macro_separator(4)___
"DECLARE
" l_act NUMBER(2);
" l_ena NUMBER(2);
" l_flg VARCHAR2(1);
" l_hom wb_rtv_service_nodes.server_side_home%TYPE;
" l_hst wb_rtv_service_nodes.host%TYPE;
" l_ins NUMBER(2);
" l_lck VARCHAR2(128);
" l_lim INTEGER := 0;
" l_nam wb_rtv_service_nodes.instance_name%TYPE;
" l_nod NUMBER(2);
" l_prt wb_rtv_service_nodes.port%TYPE;
" l_sql VARCHAR2(1500) := NULL;
" l_sta NUMBER;
" l_svc wb_rtv_service_nodes.service_name%TYPE;
" l_ver wb_rtv_service_nodes.runtime_version%TYPE;
"
" TYPE t_cursor IS REF CURSOR;
" c_dyn t_cursor;
"BEGIN
" l_sql := 'SELECT node_id,
           "       instance_number,
           "       host,
           "       port,
           "       service_name,
           "       enabled,
           "       runtime_version,
           "       server_side_home,
           "       instance_name,
           "       currently_active
           " FROM wb_rtv_service_nodes';
" OPEN c_dyn FOR l_sql;
" FETCH c_dyn
"  INTO l_nod,l_ins,l_hst,l_prt,l_svc,l_ena,l_ver,l_hom,l_nam,l_act;
" IF c_dyn%FOUND
" THEN
"  dbms_output.enable(100000);
"  dbms_output.put_line('| *Available* | *Node*| *Inst*|*Host*| ' ||
"                       '*Port*|*Service Name*| *Enabled*|*RT Version*|' ||
"                       '*Server Side Home*|*Instance Name*| ' ||
"                       '*Currently Active*|');
"  LOOP
"   EXIT WHEN c_dyn%NOTFOUND;
"   dbms_lock.allocate_unique('WBLK_OWBSYS',l_lck);
"   l_sta := dbms_lock.request(l_lck,dbms_lock.x_mode,l_lim,TRUE);
"   IF l_sta = 0
"   THEN
"    l_sta := dbms_lock.release(l_lck);
"    l_flg := 'N';
"   ELSE
"    l_flg := 'Y';
"   END IF;
"   dbms_output.put_line('|' ||
"                         l_flg || ' | ' ||
"                         l_nod || '| ' ||
"                         l_ins || '|' ||
"                         l_hst || ' | ' ||
"                         l_prt || '|' ||
"                         l_svc || ' | ' ||
"                         l_ena || '|' ||
"                         l_ver || ' |' ||
"                         l_hom || ' |' ||
"                         l_nam || ' | ' ||
"                         l_act || '|');
"   FETCH c_dyn
"   INTO l_nod,l_ins,l_hst,l_prt,l_svc,l_ena,l_ver,l_hom,l_nam,l_act;
"  END LOOP;
" END IF;
" CLOSE c_dyn;
"END;
"/
"PROMPT ___Macro_separator(5)___
"DECLARE
" l_cnt NUMBER;
"BEGIN
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM owbsys.cmpmmmstorage;
" dbms_output.put_line('|owbsys.cmpmmmstorage| ' || l_cnt || '|N/A|');
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM owbsys.cmpmmmclasses;
" dbms_output.put_line('|owbsys.cmpmmmclasses| ' || l_cnt || '|N/A|');
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM owbsys.allinterlink;
" dbms_output.put_line('|owbsys.allinterlink| ' || l_cnt || '|N/A|');
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM owbsys.allpctree;
" dbms_output.put_line('|owbsys.allpctree| ' || l_cnt || '|N/A|');
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM owbsys.wb_rt_platform_properties;
" IF l_cnt > 200
" THEN
"  dbms_output.put_line('|owbsys.wb_rt_platform_properties| '||l_cnt||'|Y|');
" ELSE
"  dbms_output.put_line('|owbsys.wb_rt_platform_properties| '||l_cnt||'|N|');
" END IF;
"END;
"/
"PROMPT ___Macro_separator(6)___
"DECLARE
" l_flg BOOLEAN        := TRUE;
" l_sql VARCHAR2(1500) := NULL;
" TYPE t_cursor IS REF CURSOR;
" c_dyn t_cursor;
" TYPE rec_sql IS RECORD(l_pth VARCHAR2(75),l_val VARCHAR2(75));
" TYPE tab_sql IS TABLE OF rec_sql INDEX BY BINARY_INTEGER;
" dst_sql TAB_SQL;
"BEGIN
" dbms_output.enable(100000);
" l_sql := 'SELECT SUBSTR(property_path,1,75),SUBSTR(property_value,1,75)
           " FROM owbsys.wb_rt_platform_properties
           " WHERE property_path IN (
           "  ''property.RuntimePlatform.0.delete_all_job_on_complete'',
           "  ''property.RuntimePlatform.0.exceptions'',
           "  ''property.RuntimePlatform.0.logfile_max_size'',
           "  ''property.RuntimePlatform.0.max_number_logfiles'',
           "  ''property.RuntimePlatform.0.messages'',
           "  ''property.RuntimePlatform.0.platform'',
           "  ''property.RuntimePlatform.0.platform_net_host_name'',
           "  ''property.RuntimePlatform.0.platform_net_service_name'',
           "  ''property.RuntimePlatform.0.purge_delay_hours'',
           "  ''property.RuntimePlatform.0.purge_minimum_minutes'',
           "  ''property.RuntimePlatform.0.recovery'',
           "  ''property.RuntimePlatform.0.runtime_home'',
           "  ''property.RuntimePlatform.0.service_command'',
           "  ''property.RuntimePlatform.0.service_parameters'',
           "  ''property.RuntimePlatform.0.temp_dir_name'',
           "  ''property.RuntimePlatform.0.trace'',
           "  ''property.RuntimePlatform.0.trace_control_on'',
           "  ''property.RuntimePlatform.0.uoid'',
           "  ''property.RuntimePlatform.0.version'',
           "  ''property.RuntimePlatform.0.min_service_version'')';
" OPEN c_dyn FOR l_sql;
" FETCH c_dyn BULK COLLECT INTO dst_sql;
" IF (dst_sql.COUNT > 0)
" THEN
"  dbms_output.put_line('|*Property Path*|*Property Value*|');
"  FOR i IN dst_sql.FIRST..dst_sql.LAST
"  LOOP
"   dbms_output.put_line('|' ||
"                         dst_sql(i).l_pth || ' |' ||
"                         dst_sql(i).l_val || ' |');
"  END LOOP;
"  l_flg := TRUE;
" END IF;
" CLOSE c_dyn;
" l_sql := 'SELECT SUBSTR(property_path,1,75),SUBSTR(property_value,1,75)
           " FROM owbsys.wb_rt_platform_properties
           " WHERE property_path LIKE ''%11g''
           "   AND property_path NOT LIKE ''%OEM%''';
" OPEN c_dyn FOR l_sql;
" FETCH c_dyn BULK COLLECT INTO dst_sql;
" IF (dst_sql.COUNT > 0)
" THEN
"  IF l_flg
"  THEN
"   dbms_output.put_line('[[#Top][Back to top]]');
"  END IF;
"  dbms_output.put_line(
"    '---+ Runtime Platform Properties for SQLPLUS and SQL*Loader ');
"  dbms_output.put_line('|*Property Path*|*Property Value*|');
"  FOR i IN dst_sql.FIRST..dst_sql.LAST
"  LOOP
"   dbms_output.put_line('|' ||
"                        dst_sql(i).l_pth || ' |' ||
"                        dst_sql(i).l_val || ' |');
"  END LOOP;
" END IF;
" CLOSE c_dyn;
"END;
"/
"PROMPT ___Macro_separator(7)___
"DECLARE
" l_key owbsys.owbrtps.key%TYPE;
" l_val owbsys.owbrtps.value%TYPE;
" l_str VARCHAR2(800);
" l_cnt NUMBER;
" TYPE t_cursor IS REF CURSOR;
" l_cursor t_cursor;
"BEGIN
" SELECT COUNT(*)
"  INTO l_cnt
"  FROM dba_objects
"  WHERE object_name='OWBRTPS'
"    AND OWNER='OWBSYS';
" IF l_cnt = 0 THEN
"  DBMS_OUTPUT.PUT_LINE('|*Description*|');
"  DBMS_OUTPUT.PUT_LINE('|Warning: Table OWBSYS.OWBRTPS not found.|');
" ELSE
"  SELECT COUNT(*)
"   INTO l_cnt
"   FROM OWBSYS.OWBRTPS;
"  IF l_cnt = 0 THEN
"   DBMS_OUTPUT.PUT_LINE('|*Description*|');
"   DBMS_OUTPUT.PUT_LINE('|Warning: Table OWBSYS.OWBRTPS is empty.|');
"  ELSE
"   DBMS_OUTPUT.PUT_LINE('|*Key*|*Value*|');
"   l_str := 'SELECT key,
"                    value
"              FROM owbsys.owbrtps
"              ORDER BY key';
"   OPEN l_cursor FOR l_str;
"   LOOP
"    FETCH l_cursor INTO l_key, l_val;
"    EXIT WHEN l_cursor%notfound;
"    DBMS_OUTPUT.PUT_LINE('|' || l_key || ' |' || l_val || '|');
"   END LOOP;
"   CLOSE l_cursor;
"  END IF;
" END IF;
"END;
"/
"PROMPT ___Macro_separator(8)___
"DECLARE
" l_usr owbsys.raw_owbrepos.schemaname%TYPE;
" l_ver owbsys.raw_owbrepos.version_no%TYPE;
" l_str VARCHAR2(800);
" l_cnt NUMBER;
" TYPE t_cursor IS REF CURSOR;
" l_cursor t_cursor;
"BEGIN
" SELECT count(*)
"  INTO l_cnt
"  FROM dba_objects
"  WHERE object_name='RAW_OWBREPOS'
"    AND OWNER='OWBSYS';
" IF l_cnt = 0 THEN
"  DBMS_OUTPUT.PUT_LINE('|*Description*|');
"  DBMS_OUTPUT.PUT_LINE('|Warning: OWBSYS.RAW_OWBREPOS not found.|');
" ELSE
"  SELECT count(*)
"   INTO l_cnt
"   FROM OWBSYS.RAW_OWBREPOS;
"  IF l_cnt = 0 THEN
"   DBMS_OUTPUT.PUT_LINE('|*Description*|');
"   DBMS_OUTPUT.PUT_LINE('|Warning: Table OWBSYS.RAW_OWBREPOS is empty.|');
"  ELSE
"   DBMS_OUTPUT.PUT_LINE('|*Schema Name*|*Version*|');
"   l_str := 'SELECT schemaname,
"                    version_no
"              FROM owbsys.raw_owbrepos
"              ORDER BY schemaname';
"   OPEN l_cursor FOR l_str;
"   LOOP
"    FETCH l_cursor INTO l_usr, l_ver;
"    EXIT WHEN l_cursor%notfound;
"    DBMS_OUTPUT.PUT_LINE('|' || l_usr || ' |' || l_ver || '|');
"   END LOOP;
"   CLOSE l_cursor;
"  END IF;
" END IF;
"END;
"/
"PROMPT ___Macro_separator(9)___
"DECLARE
" l_str_nam owbsys.owb$wb_rt_stores.store_name%TYPE;
" l_cur_loc l_str_nam%TYPE;
" l_str_typ owbsys.wb_rt_def_store_types.store_type_name%TYPE;
" l_str_ver owbsys.wb_rt_def_store_types.store_type_version%TYPE;
" l_dep_tgt NUMBER;
" l_par_nam owbsys.owb$wb_rt_store_params.parameter_name%TYPE;
" l_par_val owbsys.owb$wb_rt_store_params.parameter_value%TYPE;
" l_str VARCHAR2(1500) :=  null;
" l_own all_iv_workspaces.workspace_owner%TYPE;
" l_wrk_id NUMBER(9);
" l_wrk_nam all_iv_workspaces.workspace_name%TYPE;
" TYPE t_cursor IS REF CURSOR;
" l_cursor t_cursor;
" CURSOR l_owner_cursor IS
" SELECT workspace_owner,
"        workspace_name,
"        workspace_id
"  FROM all_iv_workspaces
"  WHERE workspace_name <> 'GLOBAL_WORKSPACE';
"BEGIN
" OPEN l_owner_cursor;
" FETCH l_owner_cursor INTO l_own, l_wrk_nam, l_wrk_id;
" IF l_owner_cursor%notfound THEN
"  DBMS_OUTPUT.PUT_LINE('|*Description*|');
"  DBMS_OUTPUT.PUT_LINE('|No Repository installed|');
" ELSE
"  DBMS_OUTPUT.ENABLE(1000000);
"  LOOP
"   DBMS_OUTPUT.PUT_LINE('---+ Locations for ' || l_own ||
"                        '(Workspace ' || l_wrk_nam || ')');
"   l_str := 'SELECT store_name,
"                    store_type_name,
"                    store_type_version,
"                    parameter_name,
"                    parameter_value,
"                    is_deployment_target
"              FROM owbsys.owb$wb_rt_stores,
"                   owbsys.owb$wb_rt_store_params,
"                   owbsys.wb_rt_def_store_types
"              WHERE owbsys.owb$wb_rt_stores.store_id =
"                    owb$wb_rt_store_params.store_id
"                AND owb$wb_rt_stores.store_type_id =
"                    wb_rt_def_store_types.store_type_id
"                AND owbsys.owb$wb_rt_stores.workspace_id= ' || l_wrk_id;
"   l_str :=  l_str || ' AND parameter_name<>''Password''
"                        AND parameter_value is not null
"                        ORDER BY store_name, parameter_name';
"   OPEN l_cursor FOR l_str;
"   DBMS_OUTPUT.PUT_LINE('|*Location*|*Type*|*Version*|*Parameter*|*Value*| \
                           *Deployment Target*|');
"   l_cur_loc := 'X';
"   LOOP
"    FETCH l_cursor INTO l_str_nam, l_str_typ, l_str_ver,l_par_nam, l_par_val,
"                        l_dep_tgt;
"    EXIT WHEN l_cursor%notfound;
"    IF l_cur_loc <> l_str_nam THEN
"     DBMS_OUTPUT.PUT_LINE('|' || l_str_nam || ' |' || l_str_typ || ' |' ||
"                          l_str_ver || ' |' || l_par_nam || ' |' || l_par_val
"                          || ' | ' || l_dep_tgt || '|');
"     l_cur_loc := l_str_nam;
"    ELSE
"     DBMS_OUTPUT.PUT_LINE('| |' || l_str_typ || ' |' || l_str_ver || ' |' ||
"                          l_par_nam || ' |' || l_par_val ||  ' | ' ||
"                          l_dep_tgt || '|');
"    END IF;
"   END LOOP;
"   CLOSE l_cursor;
"   FETCH l_owner_cursor into l_own, l_wrk_nam, l_wrk_id;
"   EXIT WHEN l_owner_cursor%notfound;
"  END LOOP;
" END IF;
" CLOSE l_owner_cursor;
"END;
"/
"PROMPT ___Macro_separator(10)___
"DECLARE
" l_cnt NUMBER;
" l_max NUMBER;
" l_msg owbsys.wb_rt_service_job_logs.message%TYPE;
" l_off NUMBER;
" l_sql VARCHAR2(800) := NULL;
" l_tim owbsys.wb_rt_service_job_logs.time_stamp%TYPE;
"
" TYPE t_cursor IS REF CURSOR;
" c_dyn t_cursor;
"BEGIN
" dbms_output.enable(300000);
" l_sql := 'SELECT COUNT(*)
           " FROM owbsys.wb_rt_service_job_logs';
" EXECUTE IMMEDIATE l_sql
"  INTO l_max;
" IF l_max > 0
" THEN
"  dbms_output.put_line('|*Time Stamp*|*File Path*|*Message*|');
"  l_sql := 'SELECT time_stamp,
            "       REPLACE(SUBSTR(message,9+instr(message,''command''),500),
            "               CHR(10),''%BR%'')
            " FROM owbsys.wb_rt_service_job_logs
            " ORDER BY time_stamp';
"  OPEN c_dyn FOR l_sql;
"  l_cnt := 0;
"  LOOP
"   FETCH c_dyn
"    INTO l_tim,l_msg;
"   EXIT WHEN c_dyn%NOTFOUND;
"   IF l_cnt >= l_max - 10
"   THEN
"    -- display last 10 entries
"    l_off := INSTR(l_msg,'run_service');
"    IF l_off > 0
"    THEN
"     dbms_output.put_line('|' ||
"       TO_CHAR(l_tim,'DD-Mon-YYYY HH24:MI:SS') || ' |' ||
"       SUBSTR(l_msg,1,14 + l_off) || ' |\');
"     dbms_output.put_line(
"       SUBSTR(l_msg,15 + l_off,200) || ' |');
"    ELSE
"     dbms_output.put_line('|' || l_tim || ' |\');
"     dbms_output.put_line(SUBSTR(l_msg,1,240) || ' ||');
"    END IF;
"   END IF;
"   l_cnt := l_cnt + 1;
"  END LOOP;
"  CLOSE c_dyn;
" END IF;
"END;
"/
"PROMPT ___Macro_separator(11)___
"SELECT '|' ||
"       ws.workspace_name || ' |' ||
"       tbl.s4_2 || ' |' ||
"       tbl.s2_4 || ' | ' ||
"       tbl.i_7 || '|' ||
"       tbl.s2_5 || ' |' ||
"       tbl.s1_14 || ' |'
" FROM owbsys.cmpfcoclasses tbl,owbsys.all_iv_workspaces ws
" WHERE ws.workspace_id = tbl.workspaceid
"   AND s2_1 IN ('CMPRuntimeLocation')
" ORDER BY ws.workspace_name,tbl.s4_2;
"PROMPT ___Macro_separator(12)___
"DECLARE
" l_profilename VARCHAR2(30);
" l_prf dba_profiles.profile%TYPE;
" l_nam dba_profiles.resource_name%TYPE;
" l_typ dba_profiles.resource_type%TYPE;
" l_lim dba_profiles.limit%TYPE;
" l_str VARCHAR2(800);
" TYPE t_cursor IS REF CURSOR;
" l_cursor t_cursor;
"BEGIN
" SELECT profile
"  INTO l_profilename
"  FROM dba_users
"  WHERE username = 'OWBSYS';
" IF l_profilename <> 'DEFAULT'
" THEN
"  dbms_output.put_line('Warning: OWBSYS is not assigned the default \
                         profile.%BR%');
" END IF;
" dbms_output.put_line('|*Profile*|*Resource Name*|*Resource Type*|*Limit*|');
" l_str := 'SELECT profile,
"                  resource_name,
"                  resource_type,
"                  limit
"            FROM dba_profiles
"            WHERE profile IN (SELECT profile
"                               FROM dba_users
"                               WHERE username = ''OWBSYS'')
"            ORDER BY resource_name';
" OPEN l_cursor FOR l_str;
" LOOP
"  FETCH l_cursor INTO l_prf, l_nam, l_typ, l_lim;
"  EXIT WHEN l_cursor%notfound;
"  dbms_output.put_line('|' ||
"                       l_prf || ' |' ||
"                       l_nam || ' |' ||
"                       l_typ || ' |' ||
"                       l_lim || ' |');
" END LOOP;
" CLOSE l_cursor;
"END;
"/
"PROMPT ___Macro_separator(13)___
"SELECT '|' ||
"       kind || ' |' ||
"       grantee || ' |' ||
"       type_schema || ' |' ||
"       type_name || ' |' ||
"       name || ' |' ||
"       action || ' |' ||
"       enabled || ' | ' ||
"       seq || '|'
" FROM dba_java_policy
" WHERE grantee = 'OWBSYS';
"PROMPT ___Macro_separator(14)___
"SELECT '| ' ||
"       COUNT(*) || '|'
" FROM owbsys.owb$wb_rt_audit_executions;
"PROMPT ___Macro_separator(15)___
"SELECT '| ' ||
"       COUNT(*) || '|'
" FROM owbsys.owb$wb_rt_audit_deployments;
}
call separator(1)
call writeSql($sql)
call separator(0,'Repository Information (11g)')

# RUN_SERVICE.SH / RUN_SERVICE.BAT
set $sql
{SET serveroutput on
"ALTER session SET current_schema = OWBSYS;
"DECLARE
" l_hom owbsys.wb_rt_service_nodes.server_side_home%TYPE;
" l_pth owbsys.wb_rt_platform_properties.property_value%TYPE;
" l_sql VARCHAR2(400) := NULL;
" TYPE t_cursor IS REF CURSOR;
" c_dyn t_cursor;
"BEGIN
" l_sql := 'SELECT server_side_home
           " FROM owbsys.wb_rt_service_nodes';
" OPEN c_dyn FOR l_sql;
" LOOP
"  FETCH c_dyn
"   INTO l_hom;
"  EXIT WHEN c_dyn%NOTFOUND;
"  l_sql := 'SELECT property_value
            " FROM owbsys.wb_rt_platform_properties
            " WHERE property_path =
            "       ''property.RuntimePlatform.0.service_command''';
"  EXECUTE IMMEDIATE l_sql
"   INTO l_pth;
"  dbms_output.put_line( l_hom || '|' || l_pth);
" END LOOP;
" CLOSE c_dyn;
"END;
"/
}
prefix
{write  '---+ RUN_SERVICE.SH / RUN_SERVICE.BAT'
 write '|*Runtime Repository*|*Run Service*|'
}
call loadSql($sql)
var @hom
var ($cnt,$err) = (0,0)
loop $lin (getSqlLines())
{break match($lin,'^\s*(ORA-|ERROR)')
 var ($hom,$pth) = split('\|',$lin,2)
 call push(@hom,$hom)
 if or($hom,$pth)
 {if match($pth,'run_service\.sh')
   var $fil = catFile($hom,'owb','bin','unix','run_service.sh')
  else
   var $fil = catFile($hom,'owb','bin','win32','run_service.bat')
  var $lnk = encode($fil)
  if ?testFile('r',$fil)
  {var $siz = getSize($fil)
   if $siz
   {output d,"run_service"
    if ${CUR.O_LAST}->write_data($fil)
     var $lnk = concat('[[',${CUR.O_LAST}->get_raw(true),'][_blank][',$lnk,']]')
    end ${CUR.O_LAST}
   }
   incr $cnt
  }
  else
   incr $err
  write '|run_service|',$lnk,'|'
 }
 else
  write '|run_service| |'
}
if hasOutput(true)
{if $cnt
  write '   * Links point to files that have been collected in their original \
              format. Opening them directly in your browser can present \
              security risks. To prevent them, access the file outside the \
              browser or use the link to save them and use an adequate viewer.'
 if $err
 {if $OWB_LOCAL
   write '   * Files are not accessible for RDA. Permission problems or \
               incorrect ``SERVER_SIDE_HOME`` in table ``WB_RT_SERVICE_NODES`` \
               are possible causes.'
  else
   write '   * Files are not accessible for RDA. When not located on a remote \
               system, permission problems or incorrect ``SERVER_SIDE_HOME`` \
               in table ``WB_RT_SERVICE_NODES`` are possible causes.'
 }
 write $TOP
}

# Runtime properties
var ($cnt,$err) = (0,0)
if @hom
{prefix
 {write  '---+ Runtime Properties'
  write '|*Runtime Properties*|'
 }
 loop $hom (@hom)
 {var $fil = catFile($hom,'owb','bin','admin','Runtime.properties')
  var $lnk = encode($fil)
  if ?testFile('r',$fil)
  {var $siz = getSize($fil)
   if $siz
   {output d,"runtime_properties"
    if ${CUR.O_LAST}->write_data($fil)
     var $lnk = concat('[[',${CUR.O_LAST}->get_raw(true),'][_blank][',$lnk,']]')
    end ${CUR.O_LAST}
   }
   incr $cnt
  }
  else
   incr $err
  write '|',$lnk,'|'
 }
}
if hasOutput(true)
{if $cnt
  write '   * Links point to files that have been collected in their original \
              format. Opening them directly in your browser can present \
              security risks. To prevent them, access the file outside the \
              browser or use the link to save them and use an adequate viewer.'
 if $err
 {if $OWB_LOCAL
   write '   * Files are not accessible for RDA. Permission problems or \
               incorrect ``SERVER_SIDE_HOME`` in table ``WB_RT_SERVICE_NODES`` \
               are possible causes.'
  else
   write '   * Files are not accessible for RDA. When not located on a remote \
               system, permission problems or incorrect ``SERVER_SIDE_HOME`` \
               in table ``WB_RT_SERVICE_NODES`` are possible causes.'
 }
 write $TOP
}

# Log files
set $sql
{SELECT 'hom=' || server_side_home
" FROM owbsys.wb_rt_service_nodes;
}
prefix
{write '---+ Log Files'
 write '   * Links point to files that have been collected in their original \
             format. Opening them directly in your browser can present \
             risks. To prevent them, access the file outside the browser or \
             use the link to save them and use an adequate viewer.'
 write '|*File Name*| *Size*|*Last Modified Date*|'
}
var $opt = concat('inpm',$AGE)
if isUnix()
 var $sub = catDir('owb','bin','unix')
elsif or(isWindows(),isCygwin())
 var $sub = catDir('owb','bin','win32')
loop $lin (grepSql($sql,'^hom='))
{var $hom = value($lin)
 loop $fil (grepDir(catDir($hom,'owb','log'),'\.log$',$opt),\
            grepDir(catDir($hom,$sub),'^hs_err_pid.*\.log$',$opt))
 {var $lnk = encode($fil)
  var $siz = getSize($fil)
  if $siz
  {output => d,basename($fil)
   if ${CUR.O_LAST}->write_tail($fil,$TAIL)
    var $lnk = concat('[[',${CUR.O_LAST}->get_raw(true),'][_blank][',$lnk,']]')
   end ${CUR.O_LAST}
  }
  write '|',$lnk,' | ',$siz,'|',getLastModify($fil,'<UTC>'),' |'
 }
 if isUnix()
 {loop $fil (grepDir(catDir($hom,'owb','log','unix'),'HS_ERR_PID.*\.log$',$opt))
  {var $lnk = encode($fil)
   var $siz = getSize($fil)
   if $siz
   {output => d,basename($fil)
    if ${CUR.O_LAST}->write_tail($fil,$TAIL)
     var $lnk = concat('[[',${CUR.O_LAST}->get_raw(true),'][_blank][',$lnk,']]')
    end ${CUR.O_LAST}
   }
   write '|',$lnk,' | ',$siz,'|',getLastModify($fil,'<UTC>'),' |'
  }
 }
}

=begin credits

=over 10

=item RDA 4.10: Sarath Babu, Jean-Philippe Peelman.

=item RDA 4.20: Mark Rovers.

=item RDA 4.24: Mark Rovers.

=item RDA 4.26: Mark Rovers.

=item RDA 8.00: Mark Rovers.

=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